Learn PL SQL, Data Science and Automation

DECLARE CURSOR C1 IS SELECT MAX (COLUMN_NAME1) AS HEADERID FROM TABLE_NAME WHERE CONDITIONS ; CURSOR C2 IS SELECT ID FROM TABLE_NAME WHERE CONDITIONS AND COLUMN_NAME1 IS NULL ; L_MAX_HEADERID number ; BEGIN OPEN C1; FETCH C1 INTO L_MAX_HEADERID; CLOSE C1; FOR R1 IN C2 LOOP L_MAX_HEADERID : = L_MAX_HEADERID + 1 ; UPDATE TABLE_NAME SET COLUMN_NAME1 = L_MAX_HEADERID WHERE ID = R1.ID; END LOOP; END ;

DISABLE SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" DISABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";' ENABLE_CONSTRAINTS FROM USER_CONSTRAINTS A WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME IN ( SELECT A1.CONSTRAINT_NAME FROM USER_CONSTRAINTS A1 WHERE A1.TABLE_NAME = DECODE( UPPER ( '&1' ), 'ALL' ,A.TABLE_NAME, UPPER ( '&1' )) ); ENABLE SELECT 'ALTER TABLE "' || A.TABLE_NAME || '" ENABLE CONSTRAINT "' || A.CONSTRAINT_NAME || '";' FROM USER_CONSTRAINTS A WHERE A.CONSTRAINT_TYPE = 'R' AND A.TABLE_NAME = DECODE( UPPER ( '&1' ), 'ALL' ,A.TABLE_NAME, UPPER ( '&1' )) AND A.STATUS = 'DISABLED' ;

MINUS SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE MINUS SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; INTERSECT SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE INTERSECT SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; UNION SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE UNION SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE; UNION ALL SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE UNION ALL SELECT * FROM TABLE_NAME WHERE CONDITION = :VALUE;

Example 01: DECLARE CURSOR NAME_CUR IS SELECT LAST_NAME FROM STUDENT WHERE ROWNUM <= 10 ; TYPE LASTNAME_TYPE IS VARRAY( 10 ) OF STUDENT.LAST_NAME % TYPE; LAST_NAME_VARRAY LAST_NAME = LASTNAME_TYPE(); V_COUNT INTEGER = 0 ; BEGIN FOR NAME_REC IN NAME_CUR LOOP V_COUNT = V_COUNT + 1 ; LAST_NAME_VARRAY(V_COUNT) = NAME_REC.LAST_NAME; DBMS_OUTPUT.PUT_LINE( 'LAST NAME(' || V_COUNT || '):' || LAST_NAME_VARRAY(V_COUNT)); END LOOP; END ; Example 02: DECLARE TYPE MY_VARRAY_TYPE IS VARRAY( 10 ) OF NUMBER ; VARRAY MY_VARRAY_TYPE = MY_VARRAY_TYPE( 1 , 2 , 3 , 4 ); BEGIN DBMS_OUTPUT.PUT_LINE( 'MY VARRAY COUNT=' || VARRAY. COUNT ); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LIMIT=' || VARRAY.LIMIT); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY FIRST=' || VARRAY.FIRST); DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LAST=' || VARRAY.LAST); VARRAY.EXTEND( 2 , 4 ) DBMS_OUTPUT.PUT_LINE( 'MY VARRAY LAST=' || VARRAY.LAST); DBMS_OUTPUT.PUT_LINE( 'MY...

--CREATE FLASHBACK TABLE CREATE TABLE TABLE_NAME_TEMP AS SELECT * FROM TABLE_NAME AS OF TIMESTAMP TO_TIMESTAMP( 'YYYY-MM-DD HH:MI:SS' , 'YYYY-MM-DD HH:MI:SS' ); --UPDATE FROM FLASHBACK TABLE UPDATE TABLE_NAME SET COLUMN_NAME = 'VALUE' WHERE COLUMN_NAME = 'VALUE' AND ID_COLUMN IN ( SELECT ID_COLUMN FROM TABLE_NAME_TEMP WHERE COLUMN_NAME = 'VALUE' AND COLUMN_NAME = 'VALUE' );

-- DEFINE &TableOwnerPattern = &1 -- DEFINE &TableNamePattern = &2 -- COLUMN owner FORMAT A25 HEADING 'Owner' -- COLUMN table_name FORMAT A30 HEADING 'Table' -- COLUMN constraint_name FORMAT A30 HEADING 'FK constraint' -- COLUMN column_list FORMAT A62 HEADING 'FK columns(s)' WORD WRAP WITH constr AS ( SELECT c.owner, c.table_name, c.constraint_name, listagg(col.column_name, ', ' ) within group ( ORDER BY col.column_name) column_list_alphabetic, listagg(col.column_name, ', ' ) within group ( ORDER BY col.position) column_list FROM dba_constraints c JOIN dba_cons_columns col ON ( col.owner = c.owner AND col.constraint_name = c.constraint_name AND col.table_name = c.table_name ) WHERE c.constraint_type = 'R' AND c.owner like '&TableOwnerPattern' AND c.table_name like '&TableNamePatte...

CREATE TABLE TABLE_NAME (ID INT ,NAME_01 VARCHAR2 ( 100 ),NAME_02 VARCHAR2 (1OO) ) INSERT INTO TABLE_NAME (ID,NAME_01,NAME_02 ) SELECT 11 , 'A1' , 'A2' UNION ALL SELECT 12 , 'B1' , 'B2' UNION ALL SELECT 13 . 'C1' , 'C2' ;

UPDATING ID OF TABLE 1 FROM THE ID IN TABLE 2 UPDATE T1 SET T1.ID = T2.T2_ID FROM TABLE_NAME_01 AS T1 INNER JOIN TABLE_NAME_02 AS T2 ON T1.ID = T2.ID;

XML INSERT INTO TABLE_NAME_1 (ID,NAME) SELECT C3.VALUE( 'ID[1]' , 'INT' ) AS ID, C3.VALUE( 'NAME[1]' , 'VARCHAR2(10)' ) AS NAME FROM ( SELECT CAST (C1 AS XML) FROM OPENROWSET ( BULK 'FILE PATH' ,SINGLE_BLOB) AS T1(C1)) AS T2(C2) CROSS APPLY C2.NODES( '/TABLE_NAME/TABLE_NAME' ) T3(C3); CSV BULK INSERT TABLE_NAME_1 FROM 'FILE PATH' WITH ( FIELDTERMINATOR = ',' , FIRSTROW = 2 , ROWTERMINATOR = '\n' ); XLS INSERT INTO TABLE_NAME_1 SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0' , 'Excel 8.0;DATABASE=FILE PATH;' , 'SELECT * FROM [SHEET1$];' );

DECLARE VALUE VARCHAR2 ( 8 ) = 'RUN' ; NUM NUMBER = 2 ; BEGIN WHILE VALUE <> 'STOP' LOOP IF NUM > 5 THEN VALUE = 'STOP' ; END IF ; dbms_output.put_line(VALUE || ': ' || NUM); NUM = NUM + 1 ; END LOOP; VALUE = 'DOWN' ; WHILE NUM > 1 AND VALUE = 'DOWN' LOOP dbms_output.put_line(VALUE || ': ' || NUM); NUM = NUM - 1 ; END LOOP; WHILE 7 = 4 LOOP NULL ; END LOOP; END ;

SAMPLE FOR LOOPING in SQL script DECLARE num NUMBER : = 1 ; BEGIN LOOP dbms_output.put_line( 'Number is: ' || num); num: = num + 1 ; IF num > 10 THEN dbms_output.put_line( 'Existing the basic Loop.' ); EXIT ; END IF ; END LOOP; END ;

SELECT s.session_id, r.status, r.blocking_session_id 'Blk by' , r.wait_type, wait_resource, r.wait_time / ( 1000.0 ) 'Wait Sec' , r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / ( 1000.0 ) 'Elaps Sec' , Substring (st. TEXT ,(r.statement_start_offset / 2 ) + 1 , (( CASE r.statement_end_offset WHEN - 1 THEN Datalength (st. TEXT ) ELSE r.statement_end_offset END - r.statement_start_offset) / 2 ) + 1 ) AS statement_text, Coalesce ( Quotename ( Db_name (st.dbid)) + N '.' + Quotename (Object_sch...

Find the locking sessions from following select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid = l1.sid and s2.sid = l2.sid and l1.BLOCK = 1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; Get Session details from this sql example : if session id = 421 select * from V$Session where sid = 421 ; Kill Sessions from following alter system kill session '421,7951' ; Temporary Tables Get the out put from following sql and run the out put. SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' FROM V$SESSION WHERE SID IN ( SELECT SID FROM V$LOCK WHERE id1 = ( SELECT object_id FROM all_objects WHERE owner = 'CMDC...

SELECT ID,NAME, COUNT ( * ) FROM TABLE_NAME WHERE ID IN ( SELECT ID_2 FROM TABLE_NAME) GROUP BY ID,NAME HAVING COUNT ( * ) > 1 ;

Create a Sequence  CREATE SEQUENCE SEQUENCE_NAME START WITH 22486 NOCACHE; GRANT SELECT ON SEQUENCE_NAME TO USER_NAME ; 2248 -Is a value which we want to start the sequence from Drop a Sequence DROP SEQUENCE SEQUENCE_NAME ;

This stored procedure will help to select and delete records from table with passing variables. create or replace PROCEDURE SP_DELETE_DATA (ID IN INTEGER ,NAME IN VARCAHR2) AS CUR_01_F_COUNT NUMBER ; CURSOR CUR_01 IS SELECT COUNT ( * ) FROM TABLE_NAME CF WHERE ID = ID AND NAME = NAME; BEGIN OPEN CUR_01; FETCH CUR_01 INTO CUR_01_F_COUNT; CLOSE CUR_01; dbms_output.put_line( ' COUNT FOR IS :' || CUR_01_F_COUNT); IF CUR_01_F_COUNT > 0 THEN DELETE FROM TABLE_NAME WHERE ID = ID AND NAME = NAME; dbms_output.put_line( 'REMOVED COUNT IS :' || sql % ROWCOUNT ); IF SQL % ROWCOUNT > 0 THEN COMMIT ; END IF ; END IF ;

Update First Table Employee_id with 2nd Table Employee ID SET TABLEOUTPUT ON DECLARE CURSOR CUR1 IS SELECT A.ID, A.EMPLOYEE_ID FROM TABLE2 A, WHERE A.EMPLOYEE_NAME = 'NEW' ; COUNT_UP NUMBER ; BEGIN BEGIN COUNT_UP : = 0 ; FOR CUR2 IN CUR1 LOOP UPDATE TABLE1 SET EMPLOYEE_ID = CUR2.EMPLOYEE_ID WHERE ID = CUR2.ID; COUNT_UP : = COUNT_UP + 1 ; END LOOP; EXCEPTION WHEN OTHERS THEN NULL ; END ; DBMS_OUTPUT.PUT_LINE( 'Record Update : ' || COUNT_UP); END ;

Below Script will be helpful to remove duplicated records from a table. DELETE FROM TABLE_NAME WHERE ROWID IN ( SELECT MAX ( ROWID ) FROM TABLE_NAME WHERE (OPTION_01,OPTION_02) IN ( SELECT OPTION_01,OPTION_02 FROM ( SELECT OPTION_01,OPTION_02, COUNT (*) FROM TABLE_NAME GROUP BY OPTION_01,OPTION_02 HAVING COUNT (*) > 1 ) ) GROUP BY OPTION_01,OPTION_02 );