No, a long cannot be converted to a varchar2 "easily". See

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:55212348054 <code>
for a method you can use to return the first 4000 characters of a long as long as the overall long is <= 32k in length using a PLSQL function.

update one of the fkeys to null -- now you can delete the other parent.

common? no, not very common but definitely they happen and they exist.

consider EMP:

create table emp ( empno number primary key,
...
mgr references emp
);

there the single table is both parent and child.

i don't think they are necessary unless you want to do an update cascade operation (which means you have an ill designed model in the first place as you are updating a primary key -- meaning it is not really a primary key at all).


no reason 'not to', but then again no reason 'to'

I don't like it personally -- I'd rather have an error when I delete a parent that has child records. Doesn't "seem" right to have that stuff just disappear.


It is useful in limited cases.

You can only have on delete cascade when creating the constraint (so you drop and recreate the constraint)

if and only if EVERY time you delete from parent you want to delete from child, on delete cascade is OK.

but, if there are sometimes that is not the expected behavior -- don't do that, use a stored procedure.

make sure to index those foreign keys, else either approach would be slow slow slow.

and then some.

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96628/toc.htm <code>

manually, if you want to automate it in some script -- great, but it'll be "table by table" on your part (in the script)

You could even write a query against the data dictionary to generate the list of tables, loop over them in PLSQL and issue the necessary deletes.

have you investigated workspace management?

1) unindexed foreign key perhaps. maybe you were causing us to do 18,388 full table scans of the unindexed child table.

2) that means the create was created disabled - AND if the foriegn key has been enforced, someone came along later and enabled it. the DISABLE is not a modifier to the "delete cascade" clause, but rather to the entire constraint itself:

tkyte%ORCL> create table p( x int primary key );

Table created.

tkyte%ORCL> create table c( x int );

Table created.

tkyte%ORCL>
tkyte%ORCL> alter table c
2 add constraint c_fk_p
3 foreign key(x) references p(x)
4 on delete cascade
5 DISABLE;

Table altered.

tkyte%ORCL>
tkyte%ORCL> insert into c values ( 1 );

1 row created.

tkyte%ORCL> commit;

Commit complete.

constraint exists, but is NOT ENABLED, we are not enforcing it..

tkyte%ORCL>
tkyte%ORCL> alter table c modify constraint c_fk_p enable;
alter table c modify constraint c_fk_p enable
*
ERROR at line 1:
ORA-02298: cannot validate (TKYTE.C_FK_P) - parent keys not found


tkyte%ORCL> delete from c;

1 row deleted.

tkyte%ORCL> commit;

Commit complete.

tkyte%ORCL> alter table c modify constraint c_fk_p enable;

Table altered.

but now we are.

tkyte%ORCL> insert into c values ( 1 );
insert into c values ( 1 )
*
ERROR at line 1:
ORA-02291: integrity constraint (TKYTE.C_FK_P) violated - parent key not found

it did work, I'm guessing "unindexed foreign key"

it was working, it was just slow with over 18,000 full table scans.

i'm guessing....

the constraint was NOT DISABLED any longer.

Else, they would not have had to of disabled it themselves (see their step 1 in getting the data purged - "disable foreign key constraints").

I hate triggers, but this might be one case whereby they could be used.

ops$tkyte%ORA11GR2> create table p
  2  ( pk int primary key,
  3    /* ... other columns */
  4    child_cnt number default 0 not null constraint check_cnt check( child_cnt > 0 ) deferrable initially deferred
  5  )
  6  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table c
  2  ( fk references p
  3  )
  4  /

Table created.

ops$tkyte%ORA11GR2> create trigger c after delete or insert or update of fk on c for each row
  2  begin
  3          if (inserting or updating)
  4          then
  5                  update p set child_cnt = child_cnt+1 where pk = :new.fk;
  6          end if;
  7          if (updating or deleting)
  8          then
  9                  update p set child_cnt = child_cnt-1 where pk = :old.fk;
 10          end if;
 11  null;
 12  end;
 13  /

Trigger created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into p (pk) values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNT) violated


ops$tkyte%ORA11GR2> insert into p (pk) values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into c (fk) values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> delete from c;

1 row deleted.

ops$tkyte%ORA11GR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNT) violated

just be aware that updating child_cnt by any other program would break this (you could hid that column from view by using VIEWS for application access)

and be aware that direct path loads do NOT fire triggers

and be aware that triggers can be disabled

too bad it doesn't work.

You didn't think about creating a parent - but never creating a child.


think about that....


(this solution *does not work*)

ops$tkyte%ORA10GR2> alter table atleast_one_child add constraint con_cnt check(child_cnt > 0) ;

Table altered.

ops$tkyte%ORA10GR2> insert into p values (1, 'x') ;

1 row created.

ops$tkyte%ORA10GR2> commit ;

Commit complete.

and if you think about it - your example *does not have to work*. And - it might not work. When you delete from c - you basically wipe out the rows in your materialized view. You "got lucky" for this one.

The problem is you would need an outer join to C, but you cannot.

yup, that outer join does it - and the "is null" makes it consume no space...

... Do you mean that the hint /*+ append */ is silently ignored when trigger are
present in the inserted table?
...


correct


IF you use a direct path load (eg: sqlldr, OCI) then triggers are not fired.

ops$tkyte%ORA11GR2> create table t ( created timestamp, modified timestamp, msg varchar2(20) );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ append */ into t select systimestamp, systimestamp, null from dual;

1 row created.

ops$tkyte%ORA11GR2> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

<b>that shows the append hint was used - we did a direct path load..</b>

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace trigger t
  2  before insert on t for each row
  3  begin
  4          :NEW.MSG := 'I FIRED';
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ append */ into t select systimestamp, systimestamp, null from dual;

1 row created.

ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
         2

<b>that shows the append hint was ignored - we did NOT direct path, else we would NOT be able to read the table</b>

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from t;

2 rows deleted.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> !cat t.ctl
load data
INFILE *
INTO TABLE t
replace
FIELDS TERMINATED BY '|' trailing nullcols
(created timestamp "YYYY-MM-DD HH24:MI:SSXFF",
modified timestamp "YYYY-MM-DD HH24:MI:SSXFF")
begindata
2009-09-09 05:06:04.066162|2009-09-09 05:06:04.066162

ops$tkyte%ORA11GR2> !sqlldr / t.ctl direct=no

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Apr 20 09:43:05 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

ops$tkyte%ORA11GR2> select * from t;

CREATED
---------------------------------------------------------------------------
MODIFIED
---------------------------------------------------------------------------
MSG
--------------------
09-SEP-09 05.06.04.066162 AM
09-SEP-09 05.06.04.066162 AM
I FIRED

<b>sqlldr ran, loaded table, and fired trigger...</b>

ops$tkyte%ORA11GR2> !sqlldr / t.ctl direct=yes

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Apr 20 09:43:05 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 1.

ops$tkyte%ORA11GR2> select * from t;

CREATED
---------------------------------------------------------------------------
MODIFIED
---------------------------------------------------------------------------
MSG
--------------------
09-SEP-09 05.06.04.066162 AM
09-SEP-09 05.06.04.066162 AM


<b>sqlldr ran, direct path was used, trigger DID NOT fire</b>

you just delete from the parent table, if you have an on delete cascade foreign key from child to parent, it'll happen.

do not involve the child in the delete, that is what the on delete cascade DOES.