SELECT CURRENT_SCN FROM V$DATABASE; To get the current SCN SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL 1.1 ORA_ROWSCN Is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row. For example: SELECT ora_rowscn, last_name, [...]
Archive for the ‘PL SQL’ Category
4 May
Scheduling Job using DBMS_JOBS
Create a Job (run it every day) VARIABLE v_jobnum NUMBER; BEGIN dbms_job.submit(:v_jobnum, ‘MY_STORED_PROCEDURE;’, sysdate,’sysdate+1′); END; print v_jobnum BEGIN DBMS_JOB.SUBMIT(JOB => :jobnumber, WHAT => ‘DBMS_STATS.GATHER_DATABASE_STATS(options => ”GATHER AUTO”);’, NEXT_DATE => to_date(’11:30 01/23/06′,’HH24:MI MM/DD/YY’), INTERVAL => ‘SYSDATE + 1′); COMMIT; END; Running Existing Job While the point is to automate jobs, occasionally you may find you need [...]
1 May
Custom Script to Identify Invalid Packages
Identifying Invalid Objects SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_type, object_name; Manual Approach ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE; DBMS_DDL EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’); EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’); EXEC [...]
25 Apr
Oracle Supplied Packages
DBMS_SQL Using DBMS_SQL, one can write stored procedures that use dynamic SQL. It can issue data definition language statements in PLSQL CREATE OR REPLACE PROCEDURE delete_all_rows (tab_name IN VARCHAR2, rows_del OUT NUMBER) IS cursor_name : = DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE ( cursor_name, ‘DELETE FROM ‘ ||tab_name, DBMS_SQL.NATIVE); rows_del := DBMS_SQL.EXECUTE(cursor_name); [...]
7 Apr
PL SQL Collections and Records
Composite Data Types Are of two types: – PL/SQL RECORDs – PL/SQL Collections – INDEX BY Table – Nested Table – VARRAY PL/SQL Records Must contain one or more components of any scalar, RECORD, or INDEX BY table data type, called fields. Are similar in structure to records in a third generation language (3GL). Are [...]
7 Apr
PL SQL BASICS
Declaring PL/SQL Variables v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := ‘Atlanta’; c_comm CONSTANT NUMBER := 1400; Scalar Variable Declarations v_job VARCHAR2(9); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE; Declaring Variables with %TYPE %ROWTYPE [...]
