Most useful - but...
andrew, October 12, 2002 - 1:01 pm UTC
Merits of the following structure aside (although I have encountered one of these) the code can be made to break.
create table x1 (col1 number, col_fk number);
alter table x1 add constraint x1_pk primary key (col1);
create table x2 (col1 number, col_fk number);
alter table x2 add constraint x2_pk primary key (col1);
alter table x1 add constraint x1_x2_fk foreign key (col_fk)references x2(col1);
alter table x2 add constraint x2_x1_fk foreign key (col_fk)references x1(col1);
insert into x1 values (1, null);
insert into x2 values (1, null);
insert into x1 values (2, 1);
insert into x2 values (2, 1);
begin
util.recursive_truncate( 'X1' );
end;
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
...
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
October 12, 2002 - 3:36 pm UTC
cool -- now, whats the fix?
(and do you really use mutually referencing tables like that -- they are quite painful to actually insert into!)
Add some code, maybe something like this?
Jan van Mourik, October 12, 2002 - 1:36 pm UTC
package body util
as
type stack is table of varchar2(32000) index by binary_integer;
g_stack stack;
g_empty_stack stack;
type tabs is table of varchar2(30) index by binary_integer;
g_tab_to_truncate tabs;
g_empty_tabs tabs;
procedure do_ddl( p_stmt in long, p_level in number )
is
begin
dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || p_stmt );
execute immediate p_stmt;
end do_ddl;
procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 )
as
e_already_truncated exception;
begin
if ( p_level = 1 )
then
g_stack := g_empty_stack;
g_tab_to_truncate := g_empty_tabs;
end if;
dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname );
g_tab_to_truncate( g_tab_to_truncate.count+1 ) := p_tname;
for x in ( select *
from user_constraints
where constraint_type = 'R'
and table_name <> p_tname
and r_constraint_name in ( select constraint_name
from user_constraints
where table_name = p_tname
and constraint_type in ('P', 'U')
)
)
loop
begin
do_ddl( 'alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level );
g_stack( g_stack.count+1 ) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
for i in reverse 1 .. g_tab_to_truncate.count
loop
if g_tab_to_truncate(i) = x.table_name
then
raise e_already_truncated;
end if;
end loop;
recursive_truncate( x.table_name, p_level+1 );
do_ddl( 'truncate table ' || x.table_name, p_level );
exception
when e_already_truncated
then
dbms_output.put_line('Table '|| x.table_name ||' already truncated, skipping...');
end;
end loop;
if ( p_level = 1 )
then
do_ddl( 'truncate table ' || p_tname, p_level );
for i in 1 .. g_stack.count
loop
do_ddl( g_stack(i), p_level );
end loop;
end if;
end recursive_truncate;
end util;
/
exec util2.recursive_truncate( 'X1' );
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Table X1 already truncated, skipping...
truncate table X2
truncate table X1
alter table X2 enable constraint X2_X1_FK
alter table X1 enable constraint X1_X2_FK
Also added the truncate of p_tname at the end...
October 12, 2002 - 3:37 pm UTC
that was my first thought -- look in the stack I had....
thanks
A reader, October 16, 2002 - 6:10 pm UTC
Hi Tom
Im in a situation where I cant seem to use packages. So Instead of using do_ddl I locally used Execute Immediate statement . But this does not let the disabled constraints to be enabled at one level deeper. i.e
If x1 <- x2
<- x3
<-x4
<-x5
<-x6
Running the following procedure would enable x2,x3,x4 but not x5 & x6. I dont get it, even if I create a separate procedure do_ddl & call it from within the following procedure it does not Enable the embedded constraints.
create table x1(id number primary key);
create table x2 (id number , constraint fk2 foreign key(id) references x1);
create table x3 (id number , constraint fk3 foreign key(id) references x1);
create table x4 (id number primary key , constraint fk4 foreign key(id) references x1);
create table x5 (id number PRIMARY KEY, constraint fk5 foreign key(id) references x4);
create table x6 (id number , constraint fk6 foreign key(id) references x5);
Create or Replace procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 )
AS
type stack is table of varchar2(32000) index by binary_integer;
g_stack stack;
g_empty_stack stack;
type tabs is table of varchar2(30) index by binary_integer;
g_tab_to_truncate tabs;
g_empty_tabs tabs;
e_already_truncated exception;
stmt LONG;
begin
if ( p_level = 1 )
then
g_stack := g_empty_stack;
g_tab_to_truncate := g_empty_tabs;
end if;
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname );
g_tab_to_truncate( g_tab_to_truncate.count+1 ) := p_tname;
for x in ( select *
from user_constraints
where constraint_type = 'R'
and table_name <> p_tname
and r_constraint_name in ( select constraint_name
from user_constraints
where table_name = p_tname
and constraint_type in ('P', 'U'))
)
Loop
Begin
--do_ddl( 'alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level );
stmt:='alter table ' || x.table_name || ' disable constraint ' || x.constraint_name ;
execute immediate stmt;
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || stmt );
g_stack( g_stack.count+1 ) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
for i in reverse 1 .. g_tab_to_truncate.count
loop
if g_tab_to_truncate(i) = x.table_name
then
raise e_already_truncated;
end if;
end loop;
recursive_truncate( x.table_name, p_level+1 );
-- do_ddl( 'truncate table ' || x.table_name, p_level );
stmt:='truncate table ' || x.table_name;
execute immediate stmt;
Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
exception
when e_already_truncated
then
dbms_output.put_line('Table '|| x.table_name ||' already truncated, skipping...');
End;
end loop;
if ( p_level = 1 )
then
-- do_ddl( 'truncate table ' || p_tname, p_level );
stmt:= 'truncate table ' || p_tname ;
execute immediate stmt;
Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
for i in 1 .. g_stack.count
loop
-- do_ddl( g_stack(i), p_level );
stmt:= g_stack(i);
execute immediate stmt;
Dbms_output.put_line( g_stack(i));
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
end loop;
end if;
end recursive_truncate;
/
Thanks
October 16, 2002 - 6:52 pm UTC
why cannot you use packages?
lets fix the PROBLEM here.
A reader, October 16, 2002 - 7:13 pm UTC
In our application due to some legacy code practices we can only have stored procedures & not packages. Currently our application code that is dependent on the database has to be in the form of SPs. Believe me no amt of arguing with my bosses is of any use.
I am very curious of Oracle's behavior regarding this issue . Is it related to locally defined variable "stmt" that is dynamically executed?
Appreciate the response.
October 16, 2002 - 7:32 pm UTC
Well, sorry -- i cannot compete with what I will call "ignorance"
write a package -- create a standalone procedure that calls the package and makes it look like a standalone procedure. Dont' tell your managers you wrote a "bad old package"
I'm not even going to look at making this code a single procedure. (ok, so I peeked -- the problem is really quite simple -- you need a GLOBAL STACK, you have a LOCAL STACK, the stuff you push in the recursive calls all "disappears" as you unwind out of the recursion. You'll have to totally redo the logical to remove recursion.
FUNNY your managers let you do recursion, which many people out law, but won't let you use packages which only make things *better* -- never worse.... sigh, sigh, double sigh)
Because we did it in the past isn't any reason to keep propagating bad practices into the future. I firmly believe that ALL real production code MUST be in a package -- there should be NO standalone procedures or functions beyond simple utilities.
Why is not using bind variable faster sometimes?
Jacques, March 27, 2003 - 8:52 am UTC
Tom,
Dont know if I understand Bind variables correctly but I tried the following test and it seems that not using bind variables works faster 2 out of 3 times;
Am I missing the point - or doing something wrong?
SQL>Create or replace Procedure P4
2 as
3 begin
4 for i in 1 .. 1000
5 loop
6 execute immediate
7 'insert into t (x,y,z) values (:x,:y,:z)'
8 using i, sysdate+i, 'this is row ' || i;
9 end loop;
10* end;
11 /
Procedure created.
Elapsed: 00:00:00.11
SQL> create or replace procedure NO_BINDS
2 as
3 begin
4 for i in 1..1000
5 loop
6 insert into t(x,y,z) values(i, sysdate + i, 'test '||i);
7 end loop;
8 end;
9 /
Procedure created.
Elapsed: 00:00:00.71
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.50
SQL> exec p4;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.81
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.00
SQL> exec NO_BINDS;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.90
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.61
SQL> exec p4;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.82
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.11
SQL> exec NO_BINDS;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.41
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.50
SQL> exec p4;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.82
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.51
SQL> exec NO_BINDS;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.81
SQL>
March 27, 2003 - 10:16 am UTC
You were comparing
P4 -- dynamic sql using bind variables
with
NO_BINDS -- static sql That most certainly did use bind variables
static sql is more efficient then dynamic sql. You would expect the second routine to be more performant.
In this:
insert into t(x,y,z) values(i, sysdate + i, 'test '||i);
it was really:
insert into t(x,y,z) values( :i, sysdate + :i, 'test '|| :i );
All references to plsql variables in SQL in plsql are in fact bind variables.
That is the sheer beauty of doing it in plsql -- it makes it so hard to NOT use bind variables!!!
Truncate tables in 24x7 system
David Pujol, October 15, 2004 - 2:25 pm UTC
Hi Tom,
I want truncate tables but I cannot do it because there are a lot of processes inserting data in these tables (I use it for save query results) . Since now, I stoped pass to processes and then I trucated these tables. I cannot use temporary tables because same user (IAS) uses distinct database sessions.
How can I truncate these tables without stop insert processing?, can I use partitioning?, and if I use partitioning (range 1 hour for example), If I execute "alter table xxx drop partition" statement, will have problems with object invalidations?. How can I implement it?
A lot of thanks in advance!
October 15, 2004 - 5:49 pm UTC
why are you saving query results? On my systems, i just run queries, the only thing I remember is "what page you are on"
Might that be a better solution?
sure, you can use partitioning -- that'll work -- it'll invalidate cursors (the structure changes) but that should be it with a truncate. BUT do your queries all have "where this_magic_partition_key = :x"? if not, they will tend to want to get at this data you are trying to truncate too.
What is wrong with a delete everynow and again -- and a periodic "online redefine"? (if you are using 8i, perhaps this table should be an IOT, you can rebuild that online as well)
Restricting Truncate table
Farrukh Mahmood, December 20, 2004 - 1:20 am UTC
Tom,
Is it possible to restrict any oracle user for truncating his table? I mean an owner cannot truncate his table accidentaly or forcibly. If i am Oracle DBA and i want my development team cannot truncate any of the tables in their respective schemas. One way round is that i will create all the tables in some other Oracle user and give my development team access to those tables via synonyms and only allow them to do insert/update/delete/select on these synonyms. In this way i can prohibit truncate table.
Any direct way to restrict truncate table?
December 20, 2004 - 8:14 am UTC
why stop at truncate?
what about delete?
what about drop?
what update update? (i mean, if I update a column to null, i might as well have just deleted that column)
You can write DDL event triggers "before <ddl operation>" to fail the operation if you like.
Recursive truncate
Spawn, February 22, 2005 - 10:28 pm UTC
Hi Tom. I tried your package. How come this is what happens
when I execute it.
SQL> execute util.recursive_truncate('CS_OR_LOG');
PL/SQL procedure successfully completed.
SQL> @count
Enter value for table: CS_OR_LOG
old 1: SELECT COUNT(*) FROM &TABLE
new 1: SELECT COUNT(*) FROM CS_OR_LOG
COUNT(*)
----------
693529
I'm just wondering why it didnt output any message and why it didnt work for me. Any parameters I need to enable?
Thanks.
February 23, 2005 - 2:03 am UTC
SQL> set serveroutput on size 1000000
and looks like there is a missing truncate for the parent that needs be added after line 39
Recursive Truncate
Spawn, February 23, 2005 - 10:11 pm UTC
Thanks Tom. I added the truncate for the parent and set serveroutput. It's working great.
Enable Constraint without check
Anil Babu. K, May 19, 2005 - 2:29 pm UTC
How I can enable a constraint without the checks for its vialotion?
If i use the sql command
alter table EES_LES_AAREPR enable constraint SYS_C0016282;
It will check for each recod. I want to bypass it. Is there any option in Oracle 8i to do it. In MS-SQL we can use the key work "nocheck", then it won't check the validation.
May 19, 2005 - 2:48 pm UTC
ops$tkyte@ORA9IR2> create table t ( x int constraint t_check check(x>0) );
Table created.
ops$tkyte@ORA9IR2> alter table t disable constraint t_check;
Table altered.
ops$tkyte@ORA9IR2> insert into t values ( -1 );
1 row created.
ops$tkyte@ORA9IR2> alter table t enable novalidate constraint t_check ;
Table altered.
ops$tkyte@ORA9IR2> insert into t values ( -1 );
insert into t values ( -1 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.T_CHECK) violated
ops$tkyte@ORA9IR2> select * from t;
X
----------
-1
Drill-Down Report
Kumar, March 28, 2008 - 5:05 am UTC
Hi Tom,
I was given a problem to resolve. A table has 2 columns CUSTID and CUSTACC. I need to give the complete drill-down result. The input would be custid.
I will try to explain the problem:
Say the table is like this:
CUSTID CUSTACC
------------------------------ ------------------------------
Robert 4725
Robert 5425
Bill 4725
Bill 1111
James 1111
James 3465
Eddy 4725
Alan 5555
Say for eg., My input is 'Robert' then I should get all the below records:
1st LEVEL - This set is because SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Robert')
---------------------------------------------------------------------------------------------------
Robert 4725
Robert 5425
Bill 4725
Eddy 4725
2nd LEVEL - I take 'Bill' now from 1st level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Bill')
----------------------------------------------------------------------------------------------------
Bill 4725
Bill 1111
James 1111
2nd LEVEL - I take 'Eddy' now from 1st level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Eddy')
----------------------------------------------------------------------------------------------------
Eddy 4725
3rd LEVEL - I take 'James' from 2nd Level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'James')
----------------------------------------------------------------------------------------------------
James 1111
James 3465
My result should be the distinct values of all the above as shown below:
Robert 4725
Robert 5425
Bill 4725
Eddy 4725
Bill 1111
James 1111
James 3465
I tried recursion like below but was not successful.
create or replace PROCEDURE CUST_CHAIN(p_custid IN VARCHAR2, cv_1 IN OUT SYS_REFCURSOR) AS
TYPE pls_num_type IS TABLE OF NUMBER;
TYPE pls_vchr_type IS TABLE OF VARCHAR2(100);
Cust_collection CustTableType := CustTableType() ;
cnt NUMBER := 0;
arr_custid pls_vchr_type;
arr_custacc pls_num_type;
v_custid cust.custid%TYPE := p_custid;
BEGIN
IF p_custid IS NULL OR Cust_collection.COUNT = 20
THEN
OPEN cv_1 FOR
SELECT *
FROM TABLE(CAST( Cust_collection AS CustTableType));
ELSE
SELECT custid, custacc BULK COLLECT INTO arr_custid, arr_custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE UPPER(custid) = UPPER(v_custid);
FOR i IN 1..arr_custid.COUNT
LOOP
Cust_collection.EXTEND;
Cust_collection(I) := CustRecType(arr_custid(i),arr_custacc(i));
IF UPPER(arr_custid(i)) <> UPPER(v_custid)
THEN
CUST_CHAIN(arr_custid(i),cv_1);
END IF;
END LOOP;
END IF;
END CUST_CHAIN;
IS there a best way to achive this?
Use the below stmt:
Create table cust
(custid varchar2(100),
custacc number);
insert into cust values ('ROBERT', 4725);
insert into cust values ('ROBERT', 5425);
insert into cust values ('BILL', 4725);
insert into cust values ('BILL', 1111);
insert into cust values ('JAMES', 1111);
insert into cust values ('EDDY', 4725);
insert into cust values ('ALAN', 5555);