Home » SQL & PL/SQL » SQL & PL/SQL » How to run .sql script from pl/sql
How to run .sql script from pl/sql [message #357146] Tue, 04 November 2008 02:54 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I have a .sql file that contains alter statement "alter table t1 add c1 number;". How can I run this srcipt from pl/sql block. I need to run it from pl/sql only not from sql plus command line.

Thanks,
Ramesh.
Re: How to run .sql script from pl/sql [message #357147 is a reply to message #357146] Tue, 04 November 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the file and execute it as dynamic SQL.

Regards
Michel
Re: How to run .sql script from pl/sql [message #357151 is a reply to message #357146] Tue, 04 November 2008 03:04 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I need to execute the file itself, can use EXECUTE IMMEDIATE alter table....How can I read the file. There may be several such commands in a .sql file. Thanks.
Re: How to run .sql script from pl/sql [message #357155 is a reply to message #357151] Tue, 04 November 2008 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to execute the file itself,

You can't. The way to do it, is the way I mentioned.

Quote:
can use EXECUTE IMMEDIATE alter table....

Yes.

Quote:
How can I read the file.

UTL_FILE

Quote:
There may be several such commands in a .sql file.

Loop on each of them.

Regards
Michel
Re: How to run .sql script from pl/sql [message #357159 is a reply to message #357146] Tue, 04 November 2008 03:11 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Thanks. I think it would be better if I convert the .sql file to stored procedure and use dynamic sql. I can run that stored procedure from pl/sql. Thanks for the prompt reply.
Re: How to run .sql script from pl/sql [message #357218 is a reply to message #357146] Tue, 04 November 2008 05:41 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I have following code which reads from flat file which has procedures calls inside it separated by new line. It runs them one by one.

But this procedure is not getting compiled. It gives me error where I call the procedure using the value I get from utl_file.fopen call.

error is

12/5 PL/SQL: Statement ignored
12/5 PLS-00221: 'V_LINE' is not a procedure or is undefined

CREATE OR REPLACE PROCEDURE proc_procedure_executer
(p_line IN INTEGER) AS
v_file UTL_FILE.FILE_TYPE;
v_line VARCHAR2(4000);
BEGIN
v_file := UTL_FILE.FOPEN('e:\ramesh','ProcFile.txt', 'R');

FOR i IN 1..p_line
LOOP
UTL_FILE.GET_LINE(v_file, v_line);

v_line; -- ERROR AT THIS LINE
END LOOP;

UTL_FILE.FCLOSE(v_file);
END proc_procedure_executer;
/


How can I run the procedures reading from flat file?

Thanks,
Ramesh
Re: How to run .sql script from pl/sql [message #357222 is a reply to message #357218] Tue, 04 November 2008 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
v_line; -- ERROR AT THIS LINE

What does this mean?

Carefully read what you have posted in previous messages and you will have the answer.

Regards
Michel
Re: How to run .sql script from pl/sql [message #357223 is a reply to message #357146] Tue, 04 November 2008 05:51 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I read from flat file into variable v_line. This holds the procedure name I need to run. But when I run it inside PL/SQL, it looks for procedure with name v_line and not the value which is stored in v_line.

I have list of procedure calls sotored in a flat file and I need to read them and run using PL/SQL.

Thanks for your reply. Please suggest how to get around to it.

Regards,
Ramesh
Re: How to run .sql script from pl/sql [message #357227 is a reply to message #357223] Tue, 04 November 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 04 November 2008 09:56
Read the file and execute it as dynamic SQL.

Regards
Michel


Re: How to run .sql script from pl/sql [message #357228 is a reply to message #357146] Tue, 04 November 2008 06:12 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
It can not be run dynamically. Please see below what I intend to do. I store a procedure name in variable now I want to run the procedure using that variable.

------------------------------------------------------------
SQL> begin
2 proc_one;
3 end;
4 /
MESSAGE: proc_One run successfully.

PL/SQL procedure successfully completed.

SQL> declare
2 v_var varchar2(100) := 'ProcOne';
3 begin
4 v_var;
5 end;
6 /
v_var;
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00221: 'V_VAR' is not a procedure or is undefined
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

SQL> declare
2 v_var varchar2(100) := 'ProcOne';
3 begin
4 execute immediate v_var;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 4

------------------------------------------------------------

Thanks for the reply...
Re: How to run .sql script from pl/sql [message #357230 is a reply to message #357228] Tue, 04 November 2008 06:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
As Michel already said three or four times, you need to use Dynamic SQL

And format your post.
Re: How to run .sql script from pl/sql [message #357231 is a reply to message #357223] Tue, 04 November 2008 06:29 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You have defined procedure as
proc_one but the variable definition is ProcOne (under score is missing between proc and one)

Re: How to run .sql script from pl/sql [message #357233 is a reply to message #357146] Tue, 04 November 2008 06:41 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Also, as procedure call it is not SQL statement, you have to enclose it into anonymous PL/SQL block.

I just wonder that you were not yet pointed to the documentation, which contains many useful information with full syntax and examples. It is available e.g. online on http://tahiti.oracle.com/. Have a look at PL/SQL User's Guide and Reference, chapter 13 PL/SQL Language Elements, EXECUTE IMMEDIATE Statement
Re: How to run .sql script from pl/sql [message #357307 is a reply to message #357233] Tue, 04 November 2008 17:04 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Thanks for the replies. But I think EXECUTE IMMEDIATE will work fine for any SQL command stored in a variable. I don't think it will run a procedure whose name is stored in a variable.

Is there any alternate method you can think of please.

Just to elaborate, I have a flat file which stores information about the procedures to run like:

Proc_One(param1, paramTwo.....)
Proc_Two(param1, paramTwo.....)

I read this file using UTL_FILE and store this in a variable. Now how can I run these procedures using EXECUTE IMMEDIATE?

Is there any way. I am really in a trouble as I have deadline.

Please suggest me alternate approach.

Thanks for your help.

Regards,
Ramesh.
Re: How to run .sql script from pl/sql [message #357310 is a reply to message #357307] Tue, 04 November 2008 17:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is an example how to execute a procedure in dynamic SQL in the very first example (7-1) in the documentation flyboy pointed you too.

PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
EXECUTE IMMEDIATE Statement

Have you even read it yet?
Re: How to run .sql script from pl/sql [message #357323 is a reply to message #357146] Tue, 04 November 2008 22:42 Go to previous message
ramesh_samane
Messages: 63
Registered: May 2006
Member
I got it. Thanks million.

SQL> declare
2 var1 varchar2(1000) := 'begin proc_one; end;';
3 begin
4 execute immediate var1;
5 end;
6 /
MESSAGE: proc_One run successfully.

PL/SQL procedure successfully completed.
Previous Topic: htp.formOpen(...) problem
Next Topic: Display calendar between two dates
Goto Forum:
  


Current Time: Fri Dec 02 16:46:06 CST 2016

Total time taken to generate the page: 0.14818 seconds