Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to execute pl/sql commands stored in a file

Re: How to execute pl/sql commands stored in a file

From: Craig Warman <>
Date: 2 Nov 2004 18:30:10 -0800
Message-ID: <>

Well you could certainly kick off a shell from your C program by running

   "sqlplus username/password_at_tnsname @yourfile.sql > logfile.sql" (assuming unix here)
That may do it for you, assuming that the .sql file's PL/SQL block is properly enclosed by BEGIN and END statements.

A more complicated alternative would be to use something called "dynamic SQL", which is nicely supported in Pro*C (if that's what you would like to use). In this case you'll want to parse through the file and read the PL/SQL block into a local variable, then pass this on to Oracle for processing. I use the word "parse" here because you mention that you have PL/SQL commands in a file. In such a case you'll want your code to locate the beginning portion of the PL/SQL block, read to the end of the block, pass that on to Oracle, and then go back to the file for more (if any). Well that's definitely more complicated but it does provide you with a little better error handling (because you can get the ORA-xxxxx error code back if something goes wrong, such as an unhandled exception) and take appropriate action. If this is the approach that you would like to take, then the following link should help get you started:

Your English just fine! Au revoir!

Craig (Gmooron) wrote in message news:<>...
> Hello
> Well, i hope someone could help me ...
> I have some pl/sql commands stored in a .sql file (creation of tables,
> procedures ...)
> And I have to do a pro*c program that execute these commands ...
> How can I do ?
> I've tried "exec sql start file.sql" but I cannot compile ...
> Thank you for your help and excuse my English ???
> (I know that a solution is to include all these commands in my pro*c
> file but I'd like to keep them in the .sql file if possible ...)
Received on Tue Nov 02 2004 - 20:30:10 CST

Original text of this message