Learn PL SQL, Data Science and Automation

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;