How to call a sql script in a procedure?

[zxmgh]

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!

[slimdave]

can't do it that way. better to define the code in the script as a procedure, then execute it.

[Sharma]

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"

[zxmgh]

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.

[jmodic]

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?!

[slimdave]

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.

[marist89]

School of Hard Knocks: 1 OCP: 0

[zxmgh]

Then what's the good practice?

[nandu]

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

[julian]

Originally posted by jmodic
Gee, and you have passed all those OCP exams?!

Even more: the guy is Solaris certified!