-
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!
-
can't do it that way. better to define the code in the script as a procedure, then execute it.
-
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"
-
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
-
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?
-
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.
-
School of Hard Knocks: 1 OCP: 0
Jeff Hunter
-
Then what's the good practice?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|