How to run .sql script from pl/sql [message #357146] |
Tue, 04 November 2008 02:54  |
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 #357218 is a reply to message #357146] |
Tue, 04 November 2008 05:41   |
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 #357223 is a reply to message #357146] |
Tue, 04 November 2008 05:51   |
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 #357228 is a reply to message #357146] |
Tue, 04 November 2008 06:12   |
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 #357233 is a reply to message #357146] |
Tue, 04 November 2008 06:41   |
flyboy
Messages: 1903 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   |
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   |
ThomasG
Messages: 3212 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?
|
|
|
|