Home » SQL & PL/SQL » SQL & PL/SQL » execute a sql file within sql? (Oracle 9i)
execute a sql file within sql? [message #326231] Tue, 10 June 2008 14:14 Go to next message
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 #326235 is a reply to message #326231] Tue, 10 June 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@
@@
start

Regards
Michel
Re: execute a sql file within sql? [message #326236 is a reply to message #326231] Tue, 10 June 2008 14:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #326512 is a reply to message #326510] Wed, 11 June 2008 17:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.orafaq.com/forum/t/63829/45693/
Re: execute a sql file within sql? [message #326514 is a reply to message #326231] Wed, 11 June 2008 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL<>SQL<>SQL*Plus
All 3 of the above are separate, distinct, & DIFFERENT from each other.
What is valid in 1, may not be valid in either of the others.

When all else fails you should Read The Fine Reference Manuals for each which can be found at http://tahiti.oracle.com
Re: execute a sql file within sql? [message #326777 is a reply to message #326514] Thu, 12 June 2008 10:05 Go to previous messageGo to next message
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 #326778 is a reply to message #326231] Thu, 12 June 2008 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>PLS-00103: Encountered the symbol "@" when expecting one of the following:
Because
@filename.sql
is NOT valid in PL/SQL

Are you unwilling or incapable of Reading The Fine Manual?
Re: execute a sql file within sql? [message #326786 is a reply to message #326778] Thu, 12 June 2008 10:29 Go to previous messageGo to next message
Paul0112
Messages: 7
Registered: March 2008
Junior Member
So your saying is that there is no way for a sql to execute another sql via parameter wise.
Re: execute a sql file within sql? [message #326790 is a reply to message #326786] Thu, 12 June 2008 10:37 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Paul0112 wrote on Thu, 12 June 2008 08:29
So your saying is that there is no way for a sql to execute another sql via parameter wise.

This statement shows PROFOUND lack of understanding about Oracle, PL/SQL, & SQL.
You really, really should go read the Concepts Manual before proceeding.

What is "a sql"?
What is "via parameter wise"?

PL/SQL is a programming language which runs deeps inside the database engine.
It has limited ability to interact with anything at the Operating System level.

You really, really should go read the Concepts Manual before proceeding.

You really, really should go read the Concepts Manual before proceeding.

You really, really should go read the Concepts Manual before proceeding.
Re: execute a sql file within sql? [message #326793 is a reply to message #326790] Thu, 12 June 2008 10:44 Go to previous messageGo to next message
Paul0112
Messages: 7
Registered: March 2008
Junior Member
I guest the concepts of Object programming and re-usability of a program is not in your mind.

Thank you for your posting.
Re: execute a sql file within sql? [message #326795 is a reply to message #326231] Thu, 12 June 2008 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Object Oriented programming & RDBMS are orthoginal paradigms
& have nothing to do with your lack of understanding how to program in PL/SQL.

[Updated on: Thu, 12 June 2008 10:52] by Moderator

Report message to a moderator

Re: execute a sql file within sql? [message #326796 is a reply to message #326795] Thu, 12 June 2008 11:00 Go to previous messageGo to next message
Paul0112
Messages: 7
Registered: March 2008
Junior Member
Yes, I'm lack of understanding of how to program in PL/SQL. But I'm opened to new ideas and not close minded.

[Updated on: Thu, 12 June 2008 11:01]

Report message to a moderator

Re: execute a sql file within sql? [message #326797 is a reply to message #326796] Thu, 12 June 2008 11:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Separating items and then ordering
Next Topic: Problem with GROUP BY
Goto Forum:
  


Current Time: Mon Dec 05 21:35:36 CST 2016

Total time taken to generate the page: 0.06221 seconds