If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

How to run .sql file from Oracle Procedure.

Hi All

I have set of complex queries in a .sql file and I need to run it
from Oracle procedure. That SQL file also creates a spool file of output on the specified drive.

How can I use something like EXECUTE_IMMEDIATE here. I am using Oracle 8.0.1 .. Therefore output is not supporting REFCURSOR. Is there any way that I can get the output of these queries in file from a Stored Procedure.

I'm not 100% sure what you want to do. If you want to read a file of SQL statements and produce a file of output, have a look at UTL_FILE. You will have to read the input with UTL_FILE and construct the SQL statement in memory and recognise the end of the statement to know when to do an EXECUTE IMMEDIATE - the SQL will need to produce a single character string as output (otherwise you will have a **** of job constructing an INTO clause) - you then write the output with UTL_FILE.

BUT I suspect you can avoid all this if you look a what your real requirements are . . .

There are N number of selects in the below SQL. I just want to know that can I call this in stored Procedure. Then I want this procedure to be called from VB 6.0 . I have Oracle 8.0.5 . I searched earlier on this ground but only I found something similar that was EXECUTE IMMEDiATE but how in this case it will work ?

I agree with davey. If you were able to run it in PL/SQL or call the sqlplus script from the operating system, the result file would probably end up on the server and not the client. If you want it on the client, then it looks like you'll have to use VB or something on the client.