execute a sql file within sql? [message #326231] |
Tue, 10 June 2008 14:14  |
Paul0112
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
Does anyone know if their is a way you can execute a sql file within another sql file?
For example I want to make a general sql file that handles basic operations like file handling. Then the details or more specific task that is pass in via variable 'file.sql', which get executed via the basic file or 'basic.sql' or something around that line.
basic file
declare
...
&1 -- file name
&2 -- sql file to be executed.
begin
... -- do some basic operations
... -- hands it off to the more specific task...
-- execute this file.sql that get pass in.
end;
/
|
|
|
|
Re: execute a sql file within sql? [message #326236 is a reply to message #326231] |
Tue, 10 June 2008 14:31   |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
if you are just running DML statements, you can build up text variables, and run using execute immediate.
If you want to run sql*plus scripts from within oracle, I think you want to look up DBMS_SCHEDULER, and EXTERNAL JOBs. This will allow you to run sqlplus on the server host from a plsql block.
|
|
|
Re: execute a sql file within sql? [message #326510 is a reply to message #326236] |
Wed, 11 June 2008 16:53   |
Paul0112
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
Thank you Michel, that work fine by it self but if I put into a begin block it blow up.
I have an error: PLS-00103
---test.sql---
declare
fout UTL_FILE.file_type;
begin
fout := UTL_FILE.fopen('/tmp','testout.txt','w');
UTL_FILE.put_line(fout,'Hello World');
UTL_FILE.fclose (fout);
end;
/
----end test.sql ---
----Generic Test File ---
declare
fout UTL_FILE.file_type;
begin
@test.sql;
fout := UTL_FILE.fopen('/tmp','testout.txt','w');
UTL_FILE.put_line(fout,'Did this work?');
UTL_FILE.fclose (fout);
end;
-----
I also try using the execute immediate '@test.sql';
|
|
|
|
|
Re: execute a sql file within sql? [message #326777 is a reply to message #326514] |
Thu, 12 June 2008 10:05   |
Paul0112
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
Still no luck... Here what I'm trying to do in 'Toad' to create this...
----- test.sql ----
begin
DBMS_OUTPUT.put_line ('Hello World!');
end;
------end test.sql ---
------mytest.sql------
begin
@&1
end;
Passing in the '/tmp/test.sql' to that sql... Still getting an error:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
|
|
|
|
|
|
|
|
|
Re: execute a sql file within sql? [message #326797 is a reply to message #326796] |
Thu, 12 June 2008 11:12   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
But you don't seem to be open to the idea to reading the concept manual.
That would quickly lead to the understanding that calling SQL scripts from SQL scripts is a very BAD way to do obects and/or code re-use, and that functions, procedures and packages are a much better way.
|
|
|
Re: execute a sql file within sql? [message #326808 is a reply to message #326797] |
Thu, 12 June 2008 11:44   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Wow - this forum will be dead soon with all the unconstructive bashing going on.
http://www.orafaq.com/forum/t/63829/45693/#msg_174015
from SQLPLUS:
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.
|
|
|
Re: execute a sql file within sql? [message #326815 is a reply to message #326808] |
Thu, 12 June 2008 12:55   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
andrew again wrote on Thu, 12 June 2008 12:44 | Wow - this forum will be dead soon with all the unconstructive bashing going on.
http://www.orafaq.com/forum/t/63829/45693/#msg_174015
from SQLPLUS:
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.
|
Wait a minute. I'm confused. Was this to show code that does not work from another posting?
1. dbms_output is spelled wrong
2. TEST from dba_source would not show "create or replace"
[Updated on: Thu, 12 June 2008 13:01] Report message to a moderator
|
|
|
Re: execute a sql file within sql? [message #326820 is a reply to message #326815] |
Thu, 12 June 2008 13:57   |
Paul0112
Messages: 7 Registered: March 2008
|
Junior Member |
|
|
The finally output of the test_proc would look like this once Procedure created.
CREATE OR REPLACE procedure SA.test_proc
is
begin
begin
DBMS_OUTPUT.put_line ('test');
end;
end;
/
---------
So it's kinda defeat the purpose of this posting, because I wanted to know if I can past the 'test.sql' has a variable (&1)... Not during the creation of the of the procedure. Therefore the procedure can be call multiple times and can have different files/statement depending on what the user pass in.
Thank you for all that post a reply back to this message...
This posting can be deleted.
[Updated on: Thu, 12 June 2008 13:58] Report message to a moderator
|
|
|
Re: execute a sql file within sql? [message #326821 is a reply to message #326815] |
Thu, 12 June 2008 14:10   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Good catch - I didn't test the quoted example in the link.
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 12 11:44:27 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create or replace procedure test_proc
2 is
3 begin
4 @c:\test.sql
4 dbms_output.put_line('Test');
5 end;
6 /
Procedure created.
SQL> l
1 create or replace procedure test_proc
2 is
3 begin
4 dbms_output.put_line('Test');
5* end;
SQL> set serveroutput on
SQL> exec test_proc;
Test
PL/SQL procedure successfully completed.
SQL>
I've never needed the technique.
|
|
|
Re: execute a sql file within sql? [message #326895 is a reply to message #326820] |
Fri, 13 June 2008 00:31  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Paul0112 wrote on Thu, 12 June 2008 20:57 | So it's kinda defeat the purpose of this posting, because I wanted to know if I can past the 'test.sql' has a variable (&1)... Not during the creation of the of the procedure. Therefore the procedure can be call multiple times and can have different files/statement depending on what the user pass in.
This posting can be deleted.
|
We don't delete posts here, unless they are offending.
The reason the &1 will never work is that it is not a replacement variable for runtime, but it will be replaced by sqlplus at the time you enter the code. It is NOT a pl/sql thing, it is a sqlplus thing.
|
|
|