Home » SQL & PL/SQL » SQL & PL/SQL » can we call an sql script from the code of a stored procedure
can we call an sql script from the code of a stored procedure [message #173971] Thu, 25 May 2006 07:26 Go to next message
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 #173974 is a reply to message #173971] Thu, 25 May 2006 07:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sure it is. Have you tried it?
Re: can we call an sql script from the code of a stored procedure [message #173985 is a reply to message #173971] Thu, 25 May 2006 08:10 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

yah i tried it

Procedure proc_1
BEGIN
execute immediate
"@C:\ec_data_file_pkg\mock_ec_data_file_pkg.pkb";
end;

but it gives me an error :
[1]: (Error): ORA-00900: invalid SQL statement ORA-06512: at line 5

can u suggest any other method???


Re: can we call an sql script from the code of a stored procedure [message #173987 is a reply to message #173985] Thu, 25 May 2006 08:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You don't need the execute immediate and remove the double-quotes.

[Updated on: Thu, 25 May 2006 08:19]

Report message to a moderator

Re: can we call an sql script from the code of a stored procedure [message #173988 is a reply to message #173971] Thu, 25 May 2006 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
>can u suggest any other method???
Your syntax is WRONG!
I suggest you RTFM found at http://tahiti.oracle.com;
specifically the PL/SQL manual
Re: can we call an sql script from the code of a stored procedure [message #173991 is a reply to message #173987] Thu, 25 May 2006 08:21 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

even after removing the double quotes it doesn't work

it still gives me the same error:
[1]: (Error): ORA-00900: invalid SQL statement ORA-06512: at line 5
Re: can we call an sql script from the code of a stored procedure [message #173992 is a reply to message #173991] Thu, 25 May 2006 08:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Remove the execute immediate and as suggested by anacedent, look into the actual syntax just to create a procedure.

[Updated on: Thu, 25 May 2006 08:23]

Report message to a moderator

Re: can we call an sql script from the code of a stored procedure [message #173994 is a reply to message #173988] Thu, 25 May 2006 08:25 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

anyways is using EXECUTE IMMEDIATE the right option or i should use something else

And what is RTFM ???
Re: can we call an sql script from the code of a stored procedure [message #173996 is a reply to message #173994] Thu, 25 May 2006 08:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
santosh_only wrote on Thu, 25 May 2006 08:25

anyways is using EXECUTE IMMEDIATE the right option or i should use something else


Not in this case.

santosh_only wrote on Thu, 25 May 2006 08:25


And what is RTFM ???


I'll let anacedent answer that one !!
Re: can we call an sql script from the code of a stored procedure [message #173999 is a reply to message #173994] Thu, 25 May 2006 08:35 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

anyways is using EXECUTE IMMEDIATE the right option or i should use something else

the following options:
1.can a DLL which will call the script
2.can we call a batch file which will connect to the database username/pass and call the pkb file from there and create package body

pls suggest any other options and also how we could use them

Re: can we call an sql script from the code of a stored procedure [message #174003 is a reply to message #173999] Thu, 25 May 2006 08:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I'm not sure where you are having problems with this, but the code should look like:

Procedure proc_1
BEGIN
@C:\ec_data_file_pkg\mock_ec_data_file_pkg.pkb
end;

Now, I don't know what you have in C:\ec_data_file_pkg\mock_ec_data_file_pkg.pkb, but this is the method of accomplishing what you are looking for.
Re: can we call an sql script from the code of a stored procedure [message #174009 is a reply to message #173971] Thu, 25 May 2006 09:09 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

I tried to implement the following script:

create or replace Procedure proc_1
is
BEGIN
@C:\ec_data_file_pkg.pkb
end;

But it gives me complition errors:
Errors for PROCEDURE PROC_1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "@" when expecting one of the
following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe


It doesn't recognise the '@' in the procedure
i tried with ;(semicolon) after and before the call then too it gave me the error

Any other suggestions please-
Re: can we call an sql script from the code of a stored procedure [message #174011 is a reply to message #174009] Thu, 25 May 2006 09:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Well I'm running on UNIX, so I can't test a Windows path, but try to put the script itself in the directory you are running SQL*Plus from and just issue @ec_data_file_pkg.pkb. It is probably a matter of escaping the Windows path.
Re: can we call an sql script from the code of a stored procedure [message #174012 is a reply to message #174009] Thu, 25 May 2006 09:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

BEGIN
@C:\ec_data_file_pkg.pkb
end;

This will never work within pl/sql. Pl/sql cannot directly interact with Operating System. An sql file in OS means nothing to PL/SQL engine.
You need to use some Java Stored procedure or OCI calls and make your life more complex.
I fail to understand what the OP is trying to do here.
Quote:

I would like to compile a package body which is placed at
c:/package_1.pkb from the procedure proc_1

If it is compiled, it means, the code is inside the database.
All you have to do is, call the package within the pl/sql ( Not the sql file).
create or replace someprocure as
Begin
mypackage.myprocedure;
end;

If the OP is talking about a wrapped package,
you need to compile the wrapped sql in sql*plus and use it inside pl/sql.
Re: can we call an sql script from the code of a stored procedure [message #174013 is a reply to message #173971] Thu, 25 May 2006 09:32 Go to previous messageGo to next message
santosh_only
Messages: 8
Registered: May 2006
Location: MUMBAI
Junior Member

i tried this too putting the file in C:\ORACLE\ora10G\BIN directory but it doesn't help

PROCEDURE TEST
is
num number;
begin
@ec_data_file_pkg.pkb;
end;

it still gives me the same error:

(1): PLS-00103: Encountered the symbol "@" when expecting one of the following:
(1):
(1): begin case declare exit for goto if loop mod null pragma
(1): raise return select update while with <an identifier>
(1): <a double-quoted delimited-identifier> <a bind variable> <<
(1): close current delete fetch lock insert open rollback
(1): savepoint set sql execute commit forall merge pipe
(1): The symbol "<an identifier>" was substituted for "@" to continue.

Am i following the right syntax
Is there any other way by which we could achieve this ????

Re: can we call an sql script from the code of a stored procedure [message #174015 is a reply to message #174012] Thu, 25 May 2006 09:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Quote:

BEGIN
@C:\ec_data_file_pkg.pkb
end;
This will never work within pl/sql. Pl/sql cannot directly interact with Operating System. An sql file in OS means nothing to PL/SQL engine.
You need to use some Java Stored procedure or OCI calls and make your life more complex.


This does actually work. Give it a shot,

SQL>! cat /tmp/test.sql
dmbs_output.put_line('Test');

SQL>create or replace procedure test_proc
  2 is
  3 begin
  4 @/tmp/test.sql
  5 end;
  6 /

Procedure created.

SQL> select text from dba_source
  2 where name = 'TEST_PROC';

TEXT
----------------------------------------
create or replace procedure test_proc
is
begin
dmbs_output.put_line('Test');
end;

SQL> set serveroutput on
SQL> exec test_proc
Test

PL/SQL procedure successfully completed.

However, like you said
Quote:


If the OP is talking about a wrapped package,
you need to compile the wrapped sql in sql*plus and use it inside pl/sql.

Re: can we call an sql script from the code of a stored procedure [message #174016 is a reply to message #174013] Thu, 25 May 2006 10:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Issue the following:

SQL> host cd


and find what directory you are currently located. Put the file in this directory and then run the code I posted previously.
Re: can we call an sql script from the code of a stored procedure [message #174018 is a reply to message #174016] Thu, 25 May 2006 10:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Thanks to Brian, I stand corrected.
@OP
Try the execute immediate within the called sql file.
scott@9i > get a
  1* execute immediate('create table a (id number)');
scott@9i > get b
  1  declare
  2  a number;
  3  begin
  4  @a.sql;
  5* end;
scott@9i > @b

PL/SQL procedure successfully completed.

scott@9i > desc a
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
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 Go to previous messageGo to next message
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 #174437 is a reply to message #174422] Mon, 29 May 2006 04:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I can't find where it's documented, but it is a feature of SQL*Plus
Exactly~.
Thanks to Brian, got some enlightment. Smile
Re: can we call an sql script from the code of a stored procedure [message #676241 is a reply to message #174015] Wed, 22 May 2019 12:00 Go to previous messageGo to next message
kankanala_nag
Messages: 2
Registered: May 2019
Junior Member
Hi Team,
This is little urgent. 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.

See the below example, one is working and another one is NOT working.

create or replace procedure test_SP1 IS
l_file VARCHAR2(30000);
l_path VARCHAR2(1000);
BEGIN

--l_str := 'CREATE OR REPLACE PROCEDURE test_SP is
l_path := 'Filetransfer';
l_file := 'scriptname.sql';
@/Filetransfer/scriptname.sql; -- This is working good with hardcoded path and file name.
@/l_path/l_file; -- This is NOT working.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR=='||SQLERRM);
END;
/
Re: can we call an sql script from the code of a stored procedure [message #676242 is a reply to message #676241] Wed, 22 May 2019 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66448
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use UTL_FILE to read the file and execute the statement in it (name and directory of the file can then be passed to the procedure).

Apart from that, the following the worst error you can write in PL/SQL:
Quote:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR=='||SQLERRM);

Read WHEN OTHERS

Re: can we call an sql script from the code of a stored procedure [message #676243 is a reply to message #676241] Wed, 22 May 2019 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>@/l_path/l_file; -- This is NOT working
sqlplus treats line above EXACTLY as it does for the line that worked

In order to access a dynamic fully qualified pathname you need to (ab)use EXECUTE IMMEDIATE as documented in the Fine Manual

remove EXCEPTION handler & NEVER use again. Read below to learn why.
http://www.orafaq.com/wiki/WHEN_OTHERS



Re: can we call an sql script from the code of a stored procedure [message #676244 is a reply to message #676242] Wed, 22 May 2019 12:25 Go to previous messageGo to next message
kankanala_nag
Messages: 2
Registered: May 2019
Junior Member
The problem is there will be around half million records in the file. If I use UTL_FILE, then I have to read record by record and use 'execute immediate' which is auto commit.
It takes time. So I thought of executing the file by using @/path/filename.sql. Please let me know if my understanding is wrong. My main requirement is to reduce the execution time because my front end web based UI has the timeout limit of 3 mins. Please don't suggest to increase the limit at front end since that is last option Smile.
Re: can we call an sql script from the code of a stored procedure [message #676245 is a reply to message #676244] Wed, 22 May 2019 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66448
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
use 'execute immediate' which is auto commit.
This is wrong.

Re: can we call an sql script from the code of a stored procedure [message #676260 is a reply to message #676241] Thu, 23 May 2019 06:44 Go to previous messageGo to next message
EdStevens
Messages: 1081
Registered: September 2013
Senior Member
kankanala_nag wrote on Wed, 22 May 2019 12:00
Hi Team,
This is little urgent. 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.
<snip>
You should have started your own thread, instead of hijacking and reviving a thread that is 13 years old.
Also, on user-supported forums, there is no "urgent" or "ASAP".
Re: can we call an sql script from the code of a stored procedure [message #676270 is a reply to message #676260] Fri, 24 May 2019 02:51 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
Obvious question is why you're trying to do this from with in the DB at all. It's possible but it's generally easier to just have scripts/processes on the server to do it.
Re: can we call an sql script from the code of a stored procedure [message #676276 is a reply to message #676270] Fri, 24 May 2019 08:01 Go to previous messageGo to next message
EdStevens
Messages: 1081
Registered: September 2013
Senior Member
cookiemonster wrote on Fri, 24 May 2019 02:51
Obvious question is why you're trying to do this from with in the DB at all. It's possible but it's generally easier to just have scripts/processes on the server to do it.
This was running through my head as well, the entire time I was reading the original 13-year old posts as well as the recent hijacking of same. Why on earth would someone want to write a procedure just to invoke a script that creates another procedure/package?

The whole thing seems daft, to me. And at that I am also puzzled at the amount of energy others spent trying to help both OPs get their code to work instead of questioning the rationale.
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 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
kankanala_nag wrote on Wed, 22 May 2019 18:00
Hi 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

Previous Topic: How to get all child records from a table with multiple self referential integrity constraints? (merged 3)
Next Topic: json filter unusual behaviour
Goto Forum:
  


Current Time: Tue Jun 25 18:40:43 CDT 2019