Accessing Table type/Record Type PLSQL function using Oracle SQL Statement.

SQL>
SQL>
SQL> create or replace type myRecordType as object
  2  ( seq int,
  3    a int,
  4    b varchar2(10),
  5    c date
  6  )
  7  /

Type created.

SQL>
SQL>
SQL> create or replace type myTableType
  2  as table of myRecordType
  3  /

Type created.

SQL>
SQL> create or replace function my_function return myTableType
  2  is
  3      l_data myTableType;
  4  begin
  5      l_data := myTableType();
  6
  7      for i in 1..5
  8      loop
  9          l_data.extend;
 10          l_data(i) := myRecordType( i, i, 'row ' || i, sysdate+i );
 11      end loop;
 12      return l_data;
 13  end;
 14  /

Function created.

SQL>
SQL> select *
  2    from TABLE ( cast( my_function() as mytableType ) )
  3   where c > sysdate+1
  4   order by seq desc
  5  /


       SEQ          A B          C
---------- ---------- ---------- ---------
         5          5 row 5      30-JUL-08
         4          4 row 4      29-JUL-08
         3          3 row 3      28-JUL-08
         2          2 row 2      27-JUL-08

SQL>
SQL> drop type myTableType;

Type dropped.

SQL> drop type myRecordType;

Type dropped.

SQL>
SQL>

Pragma Autonomous Transactions

AUTONOMOUS_TRANSACTION 

pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

You can apply this pragma to:

• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.

Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

Example 6-43 Declaring an Autonomous Function in a Package

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body
-- code for function raise_salary
   FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     new_sal NUMBER(8,2);
   BEGIN
     UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
     COMMIT;
     SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
     RETURN new_sal;
   END raise_salary;
END emp_actions;
/

Example 6-44 Declaring an Autonomous Standalone Procedure

CREATE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees SET salary = salary - amount WHERE employee_id = emp_id;
  COMMIT;
END lower_salary;
/

Example 6-45 Declaring an Autonomous PL/SQL Block

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  emp_id NUMBER(6);
  amount NUMBER(6,2);
BEGIN
  emp_id := 200;
  amount := 200;
  UPDATE employees SET salary = salary - amount WHERE employee_id = emp_id;
  COMMIT;
END;
/

Example 6-46 Declaring an Autonomous Trigger

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE, 
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

CREATE OR REPLACE TRIGGER audit_sal
   AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
   INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                 :new.salary, :old.salary );
  COMMIT;
END;
/

Example 6-47 Using Autonomous Triggers

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
                         new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

-- create an autonomous trigger that inserts into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
   BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE, 
                                :new.salary, :old.salary );
  COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 115;
COMMIT;

-- update another salary, then roll back the update
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 116;
ROLLBACK;

-- show that both committed and rolled-back updates add rows to audit table
SELECT * FROM emp_audit WHERE emp_audit_id = 115 OR emp_audit_id = 116;

Subquery Factoring with WITH Clause from Oracle 9i onwards.

Subquery Factoring

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Select each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Rename the existing Column in Oracle Table from 9iR2

In Oracle9ir2, Oracle provides "alter table" syntax to rename data columns in-place in this form:

alter table
   table_name
rename column
   old_column_name  
TO
   new_column_name;

Here are some examples of Oracle "alter table" syntax to rename data columns.

alter table
   cust_table
rename column
   cust_sex 
TO
   cust_gender;

Flash Back Query and to insert and to recover the data in Oracle 11G/12C using AS OF TIMESTAMP

Example of Examining and Restoring Past Data

Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the employees table, and you know that at 9:30AM the data for Chung was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.

Example 12-1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

Example 12-1 Retrieving a Lost Row with Oracle Flashback Query

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

Example 12-2 restores Chung's information to the employees table:

Example 12-2 Restoring a Lost Row After Oracle Flashback Query

INSERT INTO employees (
  SELECT * FROM employees
  AS OF TIMESTAMP
  TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE last_name = 'Chung'
);

OERR utility in Oracle installed on Unix

 OERR utility in Oracle installed on Unix

Many Oracle products on UNIX have a utility called OERR that can be used to aid error investigation. The OERR utility is not documented so many new to Oracle on UNIX may not be aware of its existence.

The OERR utility is a shell script that is located in the ORACLE_HOME/bin directory. OERR requires the ORACLE_HOME to be set and two parameters when called: the facility which is the three to four letter code to the left of dash and the error number.

1 2 3 4 5 6 7 8 9

[oracle@prod ~]$ oerr Usage: oerr facility error   Facility is identified by the prefix string in the error message. For example, if you get ORA-7300, "ora" is the facility and "7300" is the error.  So you should type "oerr ora 7300".   If you get LCD-111, type "oerr lcd 111", and so on. [oracle@prod ~]$

OERR will extract the error description, possible cause and action steps the message text files. Below are some examples.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[oracle@prod ~]$ oerr ora 3113 03113, 00000, "end-of-file on communication channel" // *Cause: The connection between Client and Server process was broken. // *Action: There was a communication error that requires further investigation. //          First, check for network problems and review the SQL*Net setup. //          Also, look in the alert.log file for any errors. Finally, test to //          see whether the server process is dead and whether a trace file //          was generated at failure time. [oracle@prod ~]$   [oracle@prod ~]$ oerr tns 03505 03505, 00000, "Failed to resolve name" // *Cause:  The service name you provided could not be found in TNSNAMES.ORA, //          an Oracle Names server, or a native naming service. // *Action: Verify that you entered the service name correctly.  You may need //          to ensure that the name was entered correctly into the network //          configuration. [oracle@prod ~]$

In many cases the information provided by OERR can set you down the correct path to problem resolution. Sometimes OERRmay not provide cause or action information.

1 2 3 4 5

[oracle@prod mesg]$ oerr ora 00576 00576, 00000, "in-band break protocol error" // *Cause: // *Action: [oracle@prod mesg]$

If you are unfamiliar with the error OERR can provide a quick coming up to speed on identifying the problem to assist in resolution.

This section of code have been taken from the oracledistilled.com