posted by Thomas Kyte at 8:11 AM
42 Comments, Read/Add them...
posted by Thomas Kyte at 10:07 AM
31 Comments, Read/Add them...
posted by Thomas Kyte at 1:55 PM
7 Comments, Read/Add them...
posted by Thomas Kyte at 4:31 PM
1 Comments, Read/Add them...
posted by Thomas Kyte at 2:56 PM
7 Comments, Read/Add them...
SQL> create or replace procedure demo
2 as
3 begin
4 dbms_output.put_line( who_am_i );
5 end;
6 /
Procedure created.
SQL> exec demo;
TKYTE.DEMO
create or replace procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 )
as
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
--
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := null;
end if;
if ( n is not null ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
caller_t := 'TRIGGER';
end if;
line := substr( line, nvl(n,1) );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end;
/
create or replace function who_am_i return varchar2
is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);
begin
who_called_me( l_owner, l_name, l_lineno, l_type );
return l_owner || '.' || l_name;
end;
/
posted by Thomas Kyte at 2:52 PM
6 Comments, Read/Add them...
SQL> @unindex
STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES AI_APP_CODE
ok EMP DEPTNO DEPTNO
posted by Thomas Kyte at 2:48 PM
46 Comments, Read/Add them...
SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2 /
BIN HEX OCT
--------------- --------------- ---------------
1111011 7B 173
SQL>
SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2 to_dec('173',8) base8
3 from dual
4 /
BASE2 BASE16 BASE8
---------- ---------- ----------
123 123 123
ops$tkyte%ORA10GR2> select to_char( '1234', 'XXXX' ) from dual;
TO_CH
-----
4D2
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null )
then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null )
then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
show errors
create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/
posted by Thomas Kyte at 2:35 PM
3 Comments, Read/Add them...
This package supports:
"update dept set deptno = deptno+10""update dept set deptno = deptno+1"
PROCEDURE update_cascade.on_table
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TABLE_NAME VARCHAR2 IN
P_PRESERVE_ROWID BOOLEAN IN DEFAULT
P_USE_DBMS_OUTPUT BOOLEAN IN DEFAULT
| Input Name | Default | Usage |
|---|---|---|
| p-table-name | NO DEFAULT | is the name of the parent table |
| p-preserve-rowid | TRUE | affects the generation of the code used to implement the generated packages. If set to TRUE (the default) the rowid of the updated parent row will not change due to the update. If set to FALSE, the rowid of the updated row WILL change BUT the code executes in about 66% of the time. |
| p-use-dbms-output | FALSE | defaults to FALSE which means the update_cascade package will execute (create) the packages/triggers. If set to true, the generated code will be printed using dbms_output.put_line (make sure to set serveroutput on size 100000 before using TRUE if you want to see the code). |
SQL> REM will do the default cascade support on
SQL> REM the dept table
SQL> REM ROWIDS will be preserved (unchanged).
SQL> REM Package will be created in the database
SQL> exec update_cascade.on_table( 'dept' )
SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change). This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one
SQL> exec update_cascade.on_table( 'dept', false )
SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change). This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one. Also, the packages will be written
SQL> REM to the SCREEN, not into the database.
SQL> exec update_cascade.on_table( 'dept', false, true )
The other mode of calling update_cascade.on_table is as follows:
SQL> set feedback off
SQL> spool tmp.sql
SQL> exec update_cascade.on_table( p_table_name => 'dept', p_use_dbms_output =>TRUE )
SQL> spool off
The other mode of calling update_cascade.on_table is as follows:
SQL> exec update_cascade.on_table( p_table_name => 'dept', p_preserve_rowid =>FALSE )
SQL> update dept set deptno=deptno+1;
update dept set deptno=deptno+1
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005184) violated - child record
found
SQL> @uc
Table created.
Table altered.
Package created.
Package body created.
SQL> exec update_cascade.on_table('dept');
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select empno, deptno from emp where deptno = 10;
EMPNO DEPTNO
---------- ----------
7839 10
7782 10
7934 10
SQL> update dept set deptno = deptno+1;
4 rows updated.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
11 ACCOUNTING NEW YORK
21 RESEARCH DALLAS
31 SALES CHICAGO
41 OPERATIONS BOSTON
SQL> select empno, deptno from emp where deptno in ( 10, 11 );
EMPNO DEPTNO
---------- ----------
7839 11
7782 11
7934 11
SQL>
SQL> select empno, mgr from emp;
EMPNO MGR
---------- ----------
7839
7698 7839
7782 7839
7566 7839
7499 7698
7521 7698
7654 7698
7902 7566
7369 7902
7788 7566
7844 7698
7876 7788
7900 7698
7934 7782
14 rows selected.
SQL> update emp set empno = 8000 where empno = 7698;
update emp set empno = 8000 where empno = 7698
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found
SQL> exec update_cascade.on_table('emp')
PL/SQL procedure successfully completed.
SQL> update emp set empno = 8000 where empno = 7698;
1 row updated.
SQL> select empno, mgr from emp;
EMPNO MGR
---------- ----------
7839
7782 7839
7566 7839
7499 8000
7521 8000
7654 8000
7902 7566
7369 7902
7788 7566
7844 8000
7876 7788
7900 8000
7934 7782
8000 7839
14 rows selected.
CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) references emp,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) references dept);
SQL> update emp set empno = empno+1;
update emp set mgr = SOME_NEW_VALUE
where mgr = SOME_OLD_VALUE;
SQL> @unindex.sql
STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** EMP MGR
ok EMP DEPTNO DEPTNO
ok PROJECTS EMPNO EMPNO, PROJ_NO
ok T2 A A, B
ok T3 A, B A, B, C
SQL>
SQL> @src/update_cascade/generate
prompt Update Cascade on table: DEPT
execute update_cascade.on_table( 'DEPT' )
prompt Update Cascade on table: EMP
execute update_cascade.on_table( 'EMP' )
prompt Update Cascade on table: T1
execute update_cascade.on_table( 'T1' )
prompt Update Cascade on table: T2
execute update_cascade.on_table( 'T2' )
This package uses three triggers to perform it magic.
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);
create or replace package "uDEPTp"
as
--
rowCnt number default 0;
inTrigger boolean default FALSE;
--
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;
--
--
procedure reset;
--
procedure do_cascade;
--
procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
);
--
end "uDEPTp";
/
create or replace package body "uDEPTp"
as
--
procedure reset
is
begin
--
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;
--
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 i in 1 .. rowCnt loop
insert into DEPT (
"DEPTNO"
,"DNAME","LOC") select
new_C1(i)
,"DNAME","LOC"
from "DEPT" a
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--
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 );
--
update "EMP" set
( "DEPTNO" ) =
( select new_C1(i)
from dual )
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--
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";
/
create or replace trigger "uc$DEPT_bu"
before update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".reset; end;
create or replace trigger "uc$DEPT_bufer"
before update of
"DEPTNO"
on "DEPT"
for each row
begin
"uc$DEPT_pkg".add_entry(
:old."DEPTNO"
,:new."DEPTNO"
);
end;
create or replace trigger "uc$DEPT_au"
after update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".do_cascade; end;
posted by Thomas Kyte at 2:27 PM
5 Comments, Read/Add them...
posted by Thomas Kyte at 2:21 PM
220 Comments, Read/Add them...
The views expressed are my own and not necessarily those of Oracle and its affiliates. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine. I've been using Oracle since 1988. I've been working at Oracle since 1993 (version 7.0). I spend way too much time working on asktom.oracle.com...
