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: Gmooron <>
Date: 4 Nov 2004 02:41:13 -0800
Message-ID: <> (Craig Warman) wrote in message news:<>...
> 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 ...)

Well, thanks for your answer ... The "dirty" solution I found was the first one ... Use the command line ...
We have also thought my friends and I (yes, that's a thing I haven't said ... I'm a student and THIS is a project ... (and our teacher is on holiday)) to read the file and use exec immediate commands ... The reason that we need to create tables like that is directly linked to the project ... we have to create the tables and the procedure after doing another thing on another account ... (well, it must be quite confused ...) And the procedures don't compile until we have made this other thing ... Received on Thu Nov 04 2004 - 04:41:13 CST

Original text of this message