Results 1 to 4 of 4

Thread: SQL output

  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Unanswered: SQL output

    Hi
    I have the following simple SQL file with simple queries
    delete policy1;
    delete policy2;
    delete policy3;
    delete policy4;

    I want to capture the output of each statement, to make sure that all the statements, return the same number of rows, i.e. 5 row(s) deleted.

    I have the above statements in .sql file. I know I should start by creating a procedure for the same.
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    add this to the .sql file:

    Code:
    set feedback on
    set echo on
    
    spool delete_policy.log
    
    delete policy1;
    delete policy2;
    delete policy3;
    delete policy4;
    
    spool off;

    note: you can spool the file anywhere you want so if you have a folder on your C drive called C:\logs then you could spool to that (c:\logs\delete_policy.log)

    If on unix then spool to the proper path (spool $HOME/logs/delete_policy.log)

    Have a nice day.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow SQL Output

    Hi
    Spooling is fine, and that is what I am doing right now.
    What I want that I want to use the output in the query itself, as it needs to have a cascading effect.
    If policy3 deletes has more number of records, it would go and clean up policy5 table too.
    It is more like conditional deletions.
    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You should consider creating constraints with the ON DELETE CASCADE option.

    1: Oracle will usually outperform any code you can write
    2: It ensures or enforces data integrity.

    If the ON DELETE CASCADE isn't appropriate, consider creating triggers as
    AFTER/BEFORE DELETE ON MyTable
    FOR EACH ROW
    .... delete appropriate child records.

    Constraints and triggers are one of the features which make Oracle such a powerful beast (behemoth). They're preferable to writing your own "on delete" code.

    Hth
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •