Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    28

    Unanswered: Running SQL script from stored procedure

    Greetings,
    So when one logs into sqlplus one can run a unix sql script by issuing the @/path/filename.sql command. Is there a way to accomplish the same thing from inside a table trigger or stored procedure? I tried to put the @filename string in a variable then doing an execute immediate or dbms_output.put_line ('@xxxxx.sql') which doesn't work. Is there a way to do this? Thanks for any pointers!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a way to do this?
    NO, PL/SQL can not invoke external file.
    Place SQL inside a PL/SQL procedure & invoke it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by arobinson98 View Post
    Greetings,
    So when one logs into sqlplus one can run a unix sql script by issuing the @/path/filename.sql command. Is there a way to accomplish the same thing from inside a table trigger or stored procedure? I tried to put the @filename string in a variable then doing an execute immediate or dbms_output.put_line ('@xxxxx.sql') which doesn't work. Is there a way to do this? Thanks for any pointers!
    No, that's not possible (as anacedent as already said).

    If you want to make that piece of code "re-usable", then put it into a stored procedure of it's own and call that procedure from the SQL script and from the other procedure.

  4. #4
    Join Date
    Jan 2004
    Posts
    28
    Thanks for the fast replies. The sql script spools to an html file using html markup so I was hoping to be able to essentially "host out" from a stored procedure to run it. I can probably regenerate it from a stored procedure using utl_file but if there was just a way to invoke that sql script that would have been faster with less coding changes to the existing process. Suppose I'll have to get off my ass and do some actual coding now. Thanks again for the quick reply!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DBMS_SCHEDULE can invoke OS script; which could invoke sqlplus user/pass @html.sql
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2011
    Posts
    75
    Quote Originally Posted by arobinson98 View Post
    Greetings,
    So when one logs into sqlplus one can run a unix sql script by issuing the @/path/filename.sql command. Is there a way to accomplish the same thing from inside a table trigger or stored procedure? I tried to put the @filename string in a variable then doing an execute immediate or dbms_output.put_line ('@xxxxx.sql') which doesn't work. Is there a way to do this? Thanks for any pointers!
    Hello,

    I hope this link helps you

    http://asktom.oracle.com/pls/asktom/...ID:16212348050

Posting Permissions

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