How to call a sql script in a procedure?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How to call a sql script in a procedure?

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    Question How to call a sql script in a procedure?

    Hi, All:

    I try to call a SQL Script in a procedure, the procedure is showing below:

    BEGIN

    EXECUTE IMMEDIATE '@/export/home/oracle/test/test.sql';

    END;
    /

    I got the following error message:

    ERROR at line 1:
    ORA-00900: invalid SQL statement
    ORA-06512: at line 3

    Although I know I can define a variable and type the contents of the script and assign it to that variable, then execute immediate it, but is there a smart way to do it?

    Thanks a lot!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    can't do it that way. better to define the code in the script as a procedure, then execute it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Posts
    224
    You can do it by invoking a OS command in a procedure. Call SQLplus as OS command/utiliy and run the script.

    Example call this as an OS command.

    sqlplus cwater/cwater @"C:\sshar\clearwater.sql"

  4. #4
    Join Date
    Apr 2001
    Posts
    125
    Sharma, you are right if I only wants to run one script, but actually what I want to do is to have a procedure that call different sql script based on different conditions.

    I know the following link has an example by using Java:

    http://www.oracle-base.com/Articles/...sFromPLSQL.asp

    But I try to find out if there is another easier way.

    Thanks anyway.
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Sun Solaris8 SCSA
    MCDBA 2000

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    BEGIN
    EXECUTE IMMEDIATE '@/export/home/oracle/test/test.sql';
    END;
    /

    Although I know I can define a variable and type the contents of the script and assign it to that variable, then execute immediate it, but is there a smart way to do it?

    __________________
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Gee, and you have passed all those OCP exams?!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    Gee, and you have passed all those OCP exams?!
    Hey, that's a good point! This whole idea reeks of bad practice.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    School of Hard Knocks: 1 OCP: 0
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Apr 2001
    Posts
    125
    Then what's the good practice?

  9. #9
    Join Date
    May 2002
    Posts
    108
    Then what's the good practice?
    No OCP, no Oracle Masters, no nothing. Learned it all in the School of Hard Knocks!
    ps. Read the goddam documentation!

    Courtesy : slimdave
    Never give up !

    Nanda Kumar - Vellore

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Gee, and you have passed all those OCP exams?!
    Even more: the guy is Solaris certified!
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

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


Click Here to Expand Forum to Full Width