can we call an sql script from the code of a stored procedure [message #173971] |
Thu, 25 May 2006 07:26 |
santosh_only
Messages: 8 Registered: May 2006 Location: MUMBAI
|
Junior Member |
|
|
Hi All,
I would like to compile a package body which is placed at
c:/package_1.pkb from the procedure proc_1
e.g.,
procedure proc_1
is
begin
--I want to call the package_1.pkb from here and create package end;
IS THIS POSSIBLE IF YES HOW ?????
thanks in advance for your suggestions
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: can we call an sql script from the code of a stored procedure [message #174422 is a reply to message #174018] |
Mon, 29 May 2006 04:10 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
I can't find where it's documented, but it is a feature of SQL*Plus that it expands lines beginning with "@" into the contents of the specified file, giving some unusual possibilities for installation scripts. Possibly this is simply a side-effect of the standard script-calling behaviour.
I'm not sure how it helps the OP though if the requirement is for a stored procedure to invoke SQL*Plus and run an arbitrary script, which I must say sounds like a rather bad idea in the first place. Why would you want to do that?
|
|
|
|
|
|
|
|
|
|
|
|
Re: can we call an sql script from the code of a stored procedure [message #676285 is a reply to message #676241] |
Mon, 27 May 2019 04:24 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
kankanala_nag wrote on Wed, 22 May 2019 18:00Hi Team,
In the below code, the path and name of the file is static (@/tmp/test.sql) and it is working good for me as well.
SQL>create or replace procedure test_proc
2 is
3 begin
4 @/tmp/test.sql
5 end;
6 /
But I have a requirement to write bunch of insert statements into a file and execute that file in single shot. Here the problem is I need to create the filename dynamically.
When I used local variable in procedure for file name, it is NOT working. Its doing nothing and no error message as well.
This does not create a procedure that calls any SQL*Plus script. Procedures cannot call SQL*Plus scripts.
This SQL*Plus syntax reads /tmp/test.sql, substitutes its contents in place of line 4, and then submits the resulting code to the database to be compiled as a procedure. It does not validate it, evaluate it or execute it. The resulting procedure does not contain any references to the file - if you look at the procedure code you will see that SQL*Plus has simply used the file contents to build the procedure. The procedure will not change to reflect any subsequent changes to the file.
[Updated on: Mon, 27 May 2019 04:26] Report message to a moderator
|
|
|