MERGE UPDATE

MERGE INTO Movie_Ratings M
USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
ON (M.mid = N.mid AND M.aid = N.aid)
WHEN MATCHED THEN UPDATE
SET M.rating = N.rating
WHEN NOT MATCHED THEN INSERT( mid, aid, rating)
VALUES(N.mid, N.aid, N.rating); (Syntax not verified.)

Update cascade Tom kyte Example (Programatic)

When you update the primary key of a parent table, you might want to
cascade the update to the children. This is hard to do for many
reasons and can be problematic. This package works around the lack of
an update cascade option.



This package uses three triggers to perform it magic.






A before update trigger; used to reset some package variables

A before update, for each row trigger; used to capture the before
and after images of the primary keys in pl/sql tables.
It also 'undoes' the update to the primary key.

An After update trigger that does the following steps:




'Clones' the parent records with their new primary key, eg:
insert into parent select NEW_KEY, other_cols
from parent where CURRENT_KEY = ( SELECT OLD_KEY
FROM DUAL)

for example, given "update dept set deptno=deptno+1", this would
insert the values of 11, 21, 31, 41 into the dept table. 11 would
have the values in the rest of the columns that 10 had. 21 would
look like 20 and so on.


If p_preserve_rowids = TRUE, then the primary keys of the row that
was cloned and the clone would be flip flopped. For example, if you
issue: update dept set deptno = 11 where deptno = 10 we would make 10
become the new value 11 and 11 become the old value 10.


Re-Parents the child records in all subordinate tables.
Performs the equivalent of:
update child set fkey = ( select new_key
from DUAL )
where fkey = ( select old_key from DUAL )


It then removes the 'cloned' parent records or the record with the
old primary key value.








A look at the code


The follow annotated code is the generated packages and triggers you
would create by generating support for the dept table.



The following package spec is what would be generated for the
typical 'DEPT' table
found in the scott schema (when declaritive RI is used). See the annotations in
the spec for a description of what each entry means and how it is used. The
annotations are not found in the generated code, the generated code is not
commented.



This generated code preserves rowids. The code that preserves rowids will
be in bold. This code would not be present in the generated package if
rowid preservation was disabled.




--------------------------------------------------------------------------------



SQL> exec update_cascade.on_table('dept',true,true);

The following is a typical package specification generated for a table.
The package spec name is always u || TABLE_NAME || p. The package name is
in mixed case (to prevent collisions with other user objects).


create or replace package "uDEPTp" as --
Rowcnt is used to collect the number of rows processed by a given update
statement. It is reset in the uDEPTp.reset routine in a before update
trigger. The 'inTrigger' variable is used to prevent recursive firing of
triggers when p_preserve_rowid = TRUE;

rowCnt number default 0; inTrigger boolean default FALSE; --
For each element in the primary key, a table type will be declared and then
an array of that type will be declared to 1.) hold the before image, 2.) the
after image, and 3.) an empty array used to zero out the previous two
arrays.

type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer; -- empty_C1 C1_type; old_C1 C1_type; new_C1 C1_type; -- --
Reset is the routine fired by the BEFORE UPDATE trigger that resets the
rowcnt variable and empties out the arrays from the previous invocation.

procedure reset; --
Do cascade is the work horse routine. It performs the actual cascade when
fired from an AFTER UPDATE trigger.

procedure do_cascade; --
Add Entry simply increments the rowcnt and collects the before/after images
of the primary keys. It also 'undoes' the update to the primary key by
accessing the :new and :old variables.

procedure add_entry ( p_old_C1 in "DEPT"."DEPTNO"%type ,p_new_C1 in out "DEPT"."DEPTNO"%type ); -- end "uDEPTp";/
--------------------------------------------------------------------------------

This is the package body generated. It implements the above specification

create or replace package body "uDEPTp" as -- procedure reset is begin --
This line is present in all routines when p_preserve_rowids = TRUE. It
prevents recursive firing of the triggers.

if ( inTrigger ) then return; end if; -- rowCnt := 0; old_C1 := empty_C1; new_C1 := empty_C1; end reset; -- procedure add_entry ( p_old_C1 in "DEPT"."DEPTNO"%type ,p_new_C1 in out "DEPT"."DEPTNO"%type ) is begin -- if ( inTrigger ) then return; end if; --
This code saves the before and after images in pl/sql tables and 'undoes'
the primary key update by setting the new columns back to the old columns.

if ( p_old_C1 <> p_new_C1 ) then rowCnt := rowCnt + 1; old_C1( rowCnt ) := p_old_C1; new_C1( rowCnt ) := p_new_C1; p_new_C1 := p_old_C1; end if; end add_entry; -- procedure do_cascade is begin -- if ( inTrigger ) then return; end if; inTrigger := TRUE; --
For every row that was updated we will perform the clone, cascade and
delete....

for i in 1 .. rowCnt loop
This insert clones the parent row, duping the old values with the new
primary key.

insert into DEPT ( "DEPTNO" ,"DNAME","LOC") select new_C1(i) ,"DNAME","LOC" from "DEPT" a where ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
This code is generated only when p_preserve_rowids=true and will flip-flop
the old and new primary keys, hence preserving the rowid of the original
parent.

update "DEPT" set ( "DEPTNO" ) = ( select decode( "DEPTNO", old_c1(i), new_c1(i), old_c1(i) ) from dual ) where ( "DEPTNO" ) = ( select new_C1(i) from dual ) OR ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
Do a cascade update to all children tables.

update "EMP" set ( "DEPTNO" ) = ( select new_C1(i) from dual ) where ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
Removing the old primary key value.

delete from "DEPT" where ( "DEPTNO" ) = ( select old_C1(i) from dual); end loop; -- inTrigger := FALSE; reset; exception when others then inTrigger := FALSE; reset; raise; end do_cascade; -- end "uDEPTp";/

--------------------------------------------------------------------------------




Lastly, we have the three triggers placed on
the parent table to effect the update
cascade. The first trigger simply 'resets' the package variables above.


create or replace trigger "uc$DEPT_bu"before update of"DEPTNO"on "DEPT"begin "uc$DEPT_pkg".reset; end;

The next trigger, the for each row trigger,
simply calls add_entry for each changed
row.


create or replace trigger "uc$DEPT_bufer"before update of"DEPTNO"on "DEPT"for each rowbegin"uc$DEPT_pkg".add_entry(:old."DEPTNO",:new."DEPTNO");end;

The last trigger, calls do_cascade to effect the change


create or replace trigger "uc$DEPT_au"after update of"DEPTNO"on "DEPT"begin "uc$DEPT_pkg".do_cascade; end;

Oracle DBMS_SQL

http://psoug.org/reference/dbms_sql.html

identify the columns and data types in a Weakly Typed REF CURSOR

Reference : http://www.morganslibrary.com/hci/hci003.html

I can't even count the number of times I have found myself staring at the output of a pipelined table function, or a weakly typed ref cursor, and wondered about the column names, data types, and other attributes.

If you have faced this then you too can stop cursing the darkness. Heres a few demonstrations of the pain.
SELECT COUNT(*)
FROM dba_arguments
WHERE data_type = 'REF CURSOR';

SELECT DISTINCT package_name
FROM dba_arguments
WHERE data_type = 'REF CURSOR'
AND package_name LIKE 'DBMS%'
ORDER BY 1;
And that isn't counting all of the code written by internal developers and developers from software vendors. Lets start by building a demo table and loading it with a few rows.
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;

SELECT * FROM employees;
Then we will create a small stored procedure that uses a weakly typed ref cursor to receive input from an anonymous block.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor IN SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/
Lets test the anonymous block and see if it all works.
set serveroutput on

DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';

pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/
The block calls the pass_ref_cursor procedure which uses dbms_output to show us that it read the employee table.

Here we can see the source code and can identify the source and its data type. But what if we couldn't?

The following code demonstrates the full capability of the DBMS_SQL.DESC_TAB and takes advantage of the fact that with Database 11g we can convert ref cursors into DBMS_SQL API handles. The private procedure "p" is used to print the results using DBMS_OUTPUT.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30)||to_char(desctab(i).col_type));
p(rpad('COL_MAX_LEN:',30)||to_char(desctab(i).col_max_len));
p(rpad('COL_NAME:',30)||desctab(i).col_name);
p(rpad('COL_NAME_LEN:',30)||to_char(desctab(i).col_name_len));
p(rpad('COL_SCHEMA_NAME:',30)||desctab(i).col_schema_name);
p(rpad('COL_SCHEMA_NAME_LEN:',30)||to_char(desctab(i).col_schema_name_len));
p(rpad('COL_PRECISION:',30)||to_char(desctab(i).col_precision));
p(rpad('COL_SCALE:',30)||to_char(desctab(i).col_scale));
p(rpad('COL_CHARSETID:',30)||to_char(desctab(i).col_charsetid));
p(rpad('COL_CHARSETFORM:',30)||to_char(desctab(i).col_charsetform));

IF desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'TRUE');
ELSIF NOT desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'FALSE');
ELSE
p(rpad('COL_NULL_OK:',30)||'NULL');
END IF;
END LOOP;
END print_desc_tab;
/
Lets run an anonymous block that calls the print_desc_tab and test it.
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
It works well. the DBMS_SQL's DESCRIBE_COLUMNS API gives us access to the column data type in integer form, its max length in bytes, the column's name, the length of the column's name in bytes, the schema name that owns the column's data type if the data type is user defined, the length, in bytes, of the schema name, and the columns precision, scale, character set identifier, character set form, and whether it is nullable.

Lets try again but this time with a simplified version of print_desc_tab that only exposes two attributes: data type and column name.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
dtype VARCHAR2(30);

PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
SELECT DECODE(to_char(desctab(i).col_type), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE')
INTO dtype
FROM dual;

p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30) || dtype);
p(rpad('COL_NAME:',30) || desctab(i).col_name);
END LOOP;
END print_desc_tab;
/
We will again run the same anonymous block to call print_desc_tab
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
Now you know how to do it.The only thing left is to find a project where you can put it to good use.

Translate & Replace Oracle

TRANSLATE(
str1 VARCHAR2 CHARACTER SET ANY_CS,
src VARCHAR2 CHARACTER SET STR1%CHARSET,
dest VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

Example : In this demo a string is first encrypted then decrypted

SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM DUAL;

SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM DUAL;

Example 2 :The a is replaced with an e, the h has no complement and is dropped.
SELECT TRANSLATE('So What', 'ah', 'e')
FROM DUAL;


Overload 1
REPLACE(
srcstr VARCHAR2 CHARACTER SET ANY_CS,
oldsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET,
newsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;

Overload 2
REPLACE(
srcstr CLOB CHARACTER SET ANY_CS,
oldsub CLOB CHARACTER SET SRCSTR%CHARSET,
newsub CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;

Example 1: Single Character Replacement
REPLACE(, <'string_to_match'>,<'replacements_string'>)
SELECT REPLACE('So What', 'o', 'ay')
FROM DUAL;

Example 2: Multiple Character Replacement
Replacement of a single character with a phrase
SELECT REPLACE('An idea that is not dangerous is unworthy of being called an idea at all.', 'n idea', ' software program') TRUTH
FROM DUAL;

Count the occurence of a string in Oracle

WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) nbVowels
FROM data;

Reference Cursor

Using Ref Cursors To Return Recordsets
Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types. The example below uses a ref cursor to return a subset of the records in the EMP table.

The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/

The resulting cursor can be referenced from PL/SQL as follows.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);

LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;
/
If you are using a version of Oracle before 9i, then create the following package and replace any references to SYS_REFCURSOR with TYPES.cursor_type.

CREATE OR REPLACE PACKAGE types AS
TYPE cursor_type IS REF CURSOR;

Other examples for ADO or Java
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
END Types;
/