Archive for the ‘PL SQL’ Category

Finding time for Specific SCN

 

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, salary FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
———- —- ——
202553 Fudd 3000

1.2 SCN_TO_TIMESTAMP( nSCN NUMBER)

Converts a SCN to TIMESTAMP
It has a precision of +/- 3 seconds

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

1.3 TIMESTAMP_TO_SCN(dTimestamp)

Converts TIMESTAMP to SCN
It has a precision of +/- 3 seconds

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

 

1.4 Table: SMON_SCN_TIME

This table translate time to SCN approximately.

When you use time based flashback you get the data in a period between +- 5 mn.
DBMS_FLASHBACK.ENABLE_AT_TIME and AS OF TIMESTAMP maps to an SCN value.  As the SCN-time is recorded every X minutes. The time you specify is rounded down by up to X minutes from database startup.
This situation could create the ORA-01466 unable to read data – table definition has changed. There is only track of times up to a maximum of 5 days (Database up time).

SELECT TO_CHAR(TIME_DP,’DDMONYYYY HH24:MI’) DATE_TIME,SCN
FROM SMON_SCN_TIME
Date_time               SCN
04MAY2006 16:20 576601
04MAY2006 16:26 576799
04MAY2006 16:30 577003
04MAY2006 16:36 577393
04MAY2006 16:41 577585
04MAY2006 16:46 577790

LINK WIKI ORACLE

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 to run a job manually. To do this you can call the RUN procedure. This is especially useful if you need to run a job which has been marked as broken.

BEGIN
DBMS_JOB.RUN(JOB => 21);
END;

exec dbms_job.run(job_number);

How to tell if a job has failed

When a database job fails an error will be written to the Oracle alert log with the error number of ORA-12012 and will include the job number which failed.

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;

The following will mark job 21 as not broken and have it execute at 11:00pm on January 23.

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => FALSE, NEXT_DATE => TO_DATE(’23:00 01/23/06′, ‘HH24:MI MM/DD/YY’));
END;

Checking out Failed Jobs

select job, log_user, last_date, next_date, broken, interval,failures, WHAT from dba_jobs;

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;

Changing a Job

You can update a job with the DBMS_JOB.CHANGE procedure. This procedure takes job number, code, next date and interval, in that order, as conditions. Whatever you don’t want to change can be passed as NULL.

With that in mind, the command to change just the interval would look something like this:

BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => ‘SYSDATE + 7′);
END;

Remove a Job from the Job Queue

exec dbms_job.remove(job => 21);

 

 

 

First and Last Day of Quarter

select TRUNC(sysdate, ‘Q’),TRUNC(ADD_MONTHS(sysdate, +3), ‘Q’)-1 from dual ;

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 DBMS_DDL.alter_compile(‘PROCEDURE’,’MY_SCHEMA’,’MY_PROCEDURE’);

EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);

EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

DBMS_UTILITY

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

 

  • Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
  • The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
  • The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and  DBMS_RANDOM to be present and valid.
  • — Schema level.

    EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

    EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

    — Database level.

    EXEC UTL_RECOMP.recomp_parallel(4);

    — Using job_queue_processes value.

    EXEC UTL_RECOMP.recomp_parallel();

    EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

     

    Custom Script for Compiling Invalid Packages in File

    CREATE OR REPLACE PROCEDURE COMPILE_PACKAGES

    IS

    outfile   UTL_FILE.file_type;

    stmt      VARCHAR2 (150);

    BEGIN

      outfile := UTL_FILE.fopen (‘PLSQL_FILE’, ‘INVALID_OBJECTS.txt’, ‘w’);

      FOR cur_rec IN (SELECT owner,

                             object_name,

                             object_type,

                             DECODE(object_type, ‘PACKAGE’, 1,

                                                 ‘PACKAGE BODY’, 2, 2) AS                    recompile_order

                      FROM   dba_objects

                      WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

                      AND    status != ‘VALID’

                      ORDER BY 4)

      LOOP

        BEGIN

          IF cur_rec.object_type = ‘PACKAGE’ THEN

            stmt := ‘ALTER ‘ || cur_rec.object_type || ‘ "’ || cur_rec.owner || ‘"."’ || cur_rec.object_name || ‘" COMPILE’;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

            –EXECUTE IMMEDIATE stmt;

          ElSE

            stmt:= ‘ALTER PACKAGE "’ || cur_rec.owner ||

                ‘"."’ || cur_rec.object_name || ‘" COMPILE BODY’;

            –EXECUTE IMMEDIATE stmt;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

          END IF;

          UTL_FILE.PUT_LINE(outfile, stmt);

        EXCEPTION

          WHEN OTHERS THEN

            DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||

                                 ‘ : ‘ || cur_rec.object_name);

        END;

      END LOOP;

      UTL_FILE.FCLOSE(outfile);

    END COMPILE_PACKAGES;

    Main Source of this article: Oracle Base link on Recompiling Objects

    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);
      DBMS_SQL.CLOSE_CURSOR(cursor_name);
    END;

     

    DBMS_DDL

    DBMS_DDL provides access to some SQL DDL statements from stored procedures. It includes ALTER_COMPILE and ANALYZE_OBJECT procedures. 

    DBMS_DDL.ALERT_COMPILE  (‘PROCEDURE’ ,’USER’, ‘OBJECT_NAME’ );   #DDL

    DBMS_DDL.ANALYZE_OBJECT(‘ OBJECT_TYPE’, ‘USER’, ‘NAME’ , ‘COMPUTE’);    

    DBMS_JOBS and DBMS_SCHEDULER

    It enables scheduling and execution of PL/SQL programs:

    Submitting jobs
    Executing jobs
    Removing and Suspending jobs

    begin
    --job:=101;   --job created to execute for every minute.
    sys.dbms_job.submit(job => job,
    what => 'del_test_info_p;',
    next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
    interval => 'TRUNC(SYSDATE,''MI'')+1/(24/60));
    
    commit;
    end;

    BEGIN

      DBMS_SCHEDULER.create_job (

        job_name        => ‘dummy_job’,

        job_type        => ‘PLSQL_BLOCK’,

        job_action      => ‘BEGIN NULL; /* Do Nothing */ END;’,

        start_date      => SYSTIMESTAMP,

        repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);

    END;

    DBMS_OUTPUTLINE

    DBMS_OUTPUT.PUT_LINE(‘I got here:’||:new.col||’ is the new value’);

    UTL_FILE

    The UTL_FILE package provides text file I/O  from within PL/SQL. In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIRCREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

        

    declare      
           outfile         UTL_FILE.file_type;

          locdataline   VARCHAR2 (150);

    BEGIN

         outfile := UTL_FILE.fopen (‘/u99/sas_data’, ‘VehData.dat’, ‘w’);

          FOR v1 IN veh_data

          LOOP

             BEGIN

                locdataline :=    v1.a  || ‘|’   || v1.b || ‘|’   || v1.c || ‘|’   || v1.d   || ‘|’;

                UTL_FILE.put_line (outfile, locdataline);

             END;

          END LOOP;

          UTL_FILE.fclose (outfile);

    END

    UTL_HTTP

    The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.

    UTL_TCP

    With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail.

    DBMS_STATS

    Below is a sample execution of dbms_stats with the options clause.

    exec dbms_stats.gather_schema_stats( –

    ownname          => ‘SCOTT’, –

    options          => ‘GATHER AUTO’, –

    estimate_percent => dbms_stats.auto_sample_size, –

    method_opt       => ‘for all columns size repeat’, –

    cascade          => true, –

    degree           => 15 –

    )

    Here is a representative example of invoking dbms_stats in 10g:

    DBMS_STATS.gather_schema_stats(

    ownname=>’<schema>’,

    estimate_percent=>dbms_stats.auto_sample_size

    cascade=>TRUE,

    method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

     

    OTHER USEFUL PACKAGES

    DBMS_ALERT

    DBMS_DESCRIBE

    DBMS_LOCK

    DBMS_SESSION

    DBMS_APPLICATION_INFO

    DBMS_TRANSACTION

    DBMS_SHARED_POOL

    DBMS_UTILITY

    DBMS_LOGMNR

    DBMS_PROFILER

    DBMS_REPAIR

    DBMS_SPACE

    DBMS_STATS

    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 not the same as rows in a database table.
    Treat a collection of fields as a logical unit.
    Are convenient for fetching a row of data from a table for processing.

    TYPE emp_record_type IS RECORD
    (last_name VARCHAR2(25),
    job_id VARCHAR2(10),
    salary NUMBER(8,2));
    emp_record emp_record_type;

    %ROWTYPE Attribute

    Declare a variable to store the information about a
    department from the DEPARTMENTS table.
                    dept_record departments%ROWTYPE;

    Declare a variable to store the information about an
    employee from the EMPLOYEES table.
                    emp_record employees%ROWTYPE;

    INDEX BY Tables
    • Are composed of two components:
          – Primary key of data type BINARY_INTEGER
          – Column of scalar or record data type
    • Can increase in size dynamically because they are
    unconstrained

    TYPE ename_table_type IS TABLE OF employees.last_name%TYPE
    INDEX BY BINARY_INTEGER;
    ename_table ename_table_type;

    DECLARE
    TYPE ename_table_type IS TABLE OF employees.last_name%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
    ename_table ename_table_type;
    hiredate_table hiredate_table_type;
    BEGIN
    ename_table(1) := ‘CAMERON’;
    hiredate_table(8) := SYSDATE + 7;
    IF ename_table.EXISTS(1) THEN
    INSERT INTO …

    END;

    The following methods make INDEX BY tables
    easier to use:
    – NEXT
    – TRIM
    – DELETE
    – EXISTS
    – COUNT
    – FIRST and LAST
    – PRIOR

    INDEX BY Table of Records
    • Define a TABLE variable with a permitted PL/SQL
    data type.
    • Declare a PL/SQL variable to hold department information.

    Example:
    DECLARE
    TYPE dept_table_type IS TABLE OF
    departments%ROWTYPE
    INDEX BY BINARY_INTEGER;
    dept_table dept_table_type;
    — Each element of dept_table is a record

     

    DECLARE
    TYPE emp_table_type is table of
    employees%ROWTYPE INDEX BY BINARY_INTEGER;
    my_emp_table emp_table_type;
    v_count NUMBER(3):= 104;
    BEGIN
    FOR i IN 100..v_count
    LOOP
    SELECT * INTO my_emp_table(i) FROM employees
    WHERE employee_id = i;
    END LOOP;
    FOR i IN my_emp_table.FIRST..my_emp_table.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
    END LOOP;
    END;

    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 Attributes

    %TYPE

    identifier Table.column_name%TYPE;

    v_name employees.last_name%TYPE;
    v_balance NUMBER(7,2);
    v_min_balance v_balance%TYPE := 10;

    %ROWTYPE

    The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

    dept_rec departments%ROWTYPE;

    DECLARE
      CURSOR c1 IS
        SELECT last_name, salary, hire_date, job_id FROM employees 
           WHERE employee_id = 120;
    --declare record variable that represents a row fetched from the employees table
       employee_rec c1%ROWTYPE; 
    BEGIN
    -- open the explicit cursor and use it to fetch data into employee_rec
      OPEN c1;
      FETCH c1 INTO employee_rec;
      DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
    END;

     

     

     

    CONTROL STRUCTURES

    IF THEN ELSIF Stataments

    IF v_start > 100 THEN

    v_start := 0.2 * v_start;

    ELSIF v_start >= 50 THEN

    v_start := 0.5 * v_start;

    ELSE

    v_start := 0.1 * v_start;

    END IF;

     

    CASE Statements

    CASE v_grade

    WHEN ‘A’ THEN ‘Excellent’

    WHEN ‘B’ THEN ‘Very Good’

    WHEN ‘C’ THEN ‘Good’

    ELSE ‘No such grade’

    END;

    CASE

       WHEN x = 1 THEN sequence_of_statements_1;

       WHEN x = 2 THEN sequence_of_statements_2;

       WHEN x = 3 THEN sequence_of_statements_3;

       WHEN (x = 4 && y <10) THEN sequence_of_statements_4;

       WHEN x = 5 THEN sequence_of_statements_5;

       ELSE sequence_of_statements_N;

    END CASE;

    BASIC LOOPS

    LOOP

    statement1;

    . . .

    EXIT [WHEN condition];

    END LOOP;

     

    LOOP

    INSERT INTO locations(location_id, city, country_id)

    VALUES((v_location_id + v_counter),v_city, v_country_id);

    v_counter := v_counter + 1;

    EXIT WHEN v_counter > 3;

    END LOOP;

     

    WHILE LOOP

    WHILE v_counter <= 3 LOOP

    INSERT INTO locations(location_id, city, country_id)

    VALUES((v_location_id + v_counter), v_city, v_country_id);

    v_counter := v_counter + 1;

    END LOOP;

     

    FOR LOOP

    FOR RecordIndex IN (SELECT person_code FROM people_table)
    LOOP
      DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
    END LOOP;
    FOR var IN 0 .. 10 LOOP 
              DBMS_OUTPUT.put_line(var);
         END LOOP;
    DECLARE
      CURSOR cursor_person IS
        SELECT person_code FROM people_table;
    BEGIN
      FOR RecordIndex IN cursor_person
      LOOP
        DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
      END LOOP;
    END;