Learn PL SQL, Data Science and Automation

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;

WITH S AS ( /*SELECT QUERY*/ ) SELECT /*COLUMN NAMES*/ , LENGTH( FLOOR ( /*DECIMAL COLUMN*/ )) + NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DEGITS, NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DECIMALS FROM ( SELECT /*COLUMN NAMES*/ , TO_CHAR( /*DECIMAL COLUMN*/ , 'FM99999999999999999D99999999999999999' , 'NLS_NUMERIC_CHARACTERS = ''. ''' ) CN FROM S );

SELECT 'curl ' || 'https://www.testapp.net/test/SupportClass!actionClass.action ' || '-H ' || 'Origin: https://www.testapp.net ' || '-H ' || 'Accept-Encoding: gzip, deflate ' || '-H ' || 'Accept-Language: en-US,en;q=0.8 ' || '-H ' || 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.87 Safari/537.36 ' || '-H ' || 'Content-Type: application/x-www-form-urlencoded; charset=UTF-8 ' || '-H ' || 'Accept: */* ' || '-H ' || 'Referer: https://www.testapp.net/test/application.jsp ' || '-H ' || 'Cookie: JSESSIONID=5C30005FF6BF00A2FDD18A3C0E258115.app-2; JSESSIONID=QIPalPyhOP2D8HMlGSl7Uw**.node1; SSOID=be2bb104392d40c4a4fea96162906b10; pnctest=1 ' || '-H ' || 'Connection: keep-alive ' || '--data ' || '_operat...

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;

To reset password need to log into system as sysdba user. command line or terminal #sqlplus sys as sysdba password SQL > SELECT * FROM DBA_PROFILES; > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; This will keep you from expiring passwords hereafter SQL > SELECT 'ALTER USER "' || D.USERNAME || '" IDENTIFIED BY VALUES ''' || U.PASSWORD || ''';' C FROM DBA_USERS D, SYS. USER $ U WHERE D.USERNAME = UPPER ( '&&USERNAME' ) AND U. USER # = D. USER_ID ; SQL> ALTER USER "SYSTEM" IDENTIFIED BY VALUES '563CF0CCDBBB0FE4' ;

SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = 'TABLE NAME' ;

DECLARE CURSOR C1 IS SELECT COLUMN_NAME FROM TABLE WHERE CONDITIONS; BEGIN FOR R1 IN C1 LOOP INSERT INTO TABLE 2(COLUMN DEFINING) VALUES (VALUES DECLARATION); END LOOP; END ;

UPDATE TABLE_NAME SET COLUMN_NAME = LPAD(COLUMN_NAME, 4 , '000' ) WHERE CONDITIONS;