Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: calling sql script from a procedure

    How can I do following. I want to call a sql scripts from procedure depending on some if condition. This works fine if I call from any other code like shell script etc.

    procedure file is as follows:
    declare
    var1 number;
    begin
    if .... then
    ...
    else
    @something.sql
    end if;
    end;
    /

    sql script : something.sql is as follows
    create table a( f1 number);
    create table b(d1 number);
    create or replace trigger .....
    alter table a ...


    Thanks
    YDJ

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Generally speaking, you cannot do that. PL/SQL does not make calls outside the database unless you go through the painful process of configuring and using plsextproc.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can submit jobs to dbms_jobs with a procedure and let it handle it ...

    HTH
    Gregg

  4. #4
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    I don't know about that. DBMS_JOB executes your code as a PL/SQL block, and you cannot execute host files (something.sql) from within PL/SQL blocks.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  5. #5
    Join Date
    Nov 2003
    Posts
    39

    found work around

    Hey, I found the work around on orafaq site.
    just output it to a spool file and then
    execute the file at the end. Here is the example.

    set serveroutput on size 4000;
    set trimspool on
    set hea off
    set feed off
    spool run_y.sql
    Declare
    BEGIN
    dbms_output.put_line('@y.sql');
    END ;
    /
    spool off
    @run_y.sql

    there some ways to do it by using ... dbms_pipe, but
    I haven't tried

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    why not change "@something.sql" to be an actual procedure.

    then throw everything in a package and call whatever script/procedure you want whenever you want.

    Just sounds like you are making this harder than it has to be.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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