?
Solved

spool in oracle

Posted on 2006-07-05
27
Medium Priority
?
4,284 Views
Last Modified: 2010-08-05
This is what I wanted to do….

 

I need to call a simple sql file from a procedure. (
 

Create or replace procedure call_sql as

Begin

For i in 1..100000

Loop

If mod(i,1000)=0 then

@@file1.sql

End if

End loop

End;

 

Inside file1, I have Sql plus commands to spool to file in OS.
Is it possible.If so then how

0
Comment
Question by:suhinrasheed
  • 9
  • 8
  • 5
  • +2
27 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 17041544
@@file.sql is SQL*Plus statement and can not be invoked
from PL/SQL block

BUT

you can reprogram the statements in @@file1.sql
if they are pure SQL and/or PL/SQL statements
in a procedure or function and invoke it

Begin

For i in 1..100000

Loop

If mod(i,1000)=0 then

file1(par1,...,parn);

End if

End loop

End;
0
 

Expert Comment

by:oogooglies
ID: 17041550
I'm pretty sure you can't call a file in that way from inside a PL/SQL block, perhaps if stored the script in file1.sql as a function/procedure you could then use DBMS_OUTPUT.put_line to output the data you need..... you will have to do the spooling from the location you are running the call_sql proc from.

Spool c:\example.sql

execute call_sql

spool off

0
 

Author Comment

by:suhinrasheed
ID: 17041555
no my sql file has some sqlplus commands inside it
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:suhinrasheed
ID: 17041569
What my sql file contains is a few  select statements and i want to spool the outputs of those statements into a file in my local pc.So the file1.sql contains a sqlplus spool command
0
 

Expert Comment

by:oogooglies
ID: 17041576
In that case why don't you create a small script to run your proc and then put the sqlplus enviroment/spooling commands commands in the top of that?

set lines 1000, pages 500, trimspool on, termout off

Spool c:\example.sql

execute call_sql

spool off
0
 

Expert Comment

by:oogooglies
ID: 17041589

if you re-program as mentioned above you will be able to use DBMS_OUTPUT.PUT_LINE to print results to the screen. so if you set the spool before calling the prog then all of the output will be spooled into your file....
0
 

Author Comment

by:suhinrasheed
ID: 17041616
i am not getting you.just can you send me a code snippet.Suppose these are the sql statements in my file1.sql

SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(*) FROM DEPT;

refer the procedure i send in the post and send me a code
0
 

Expert Comment

by:oogooglies
ID: 17041689

Create the procedure below and then create a new script with containing.....

set lines 1000, pages 500, trimspool on, termout off, serveroutput on

Spool c:\example.sql

execute call_sql;

spool off

CREATE OR REPLACE PROCEDURE call_sql
AS
   v_all_emp_count     NUMBER (10) := 0;
   v_all_dept_count    NUMBER (10) := 0;
   v_all_obs_count     NUMBER (10) := 0;
BEGIN
   FOR i IN 1 .. 100000 LOOP
      IF MOD (i, 1000) = 0
      THEN
         DBMS_OUTPUT.ENABLE;

         SELECT COUNT (*)
           INTO v_all_obs_count
           FROM all_objects;

         SELECT COUNT (*)
           INTO v_all_dept_count
           FROM dept;

         SELECT COUNT (*)
           INTO v_all_emp_count
           FROM emp;

         DBMS_OUTPUT.put_line ("All Objects Count");
         DBMS_OUTPUT.put_line ("--------------------");
         DBMS_OUTPUT.put_line (TO_CHAR (v_all_obs_count));
         DBMS_OUTPUT.put_line ("Dept Count");
         DBMS_OUTPUT.put_line ("--------------------");
         DBMS_OUTPUT.put_line (TO_CHAR (v_all_dept_count));
         DBMS_OUTPUT.put_line ("Emp Count");
         DBMS_OUTPUT.put_line ("--------------------");
         DBMS_OUTPUT.put_line (TO_CHAR (v_all_emp_count));
      END IF;
   END LOOP;
END;
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17041692
From PL/SQL this is not going to be as easy as spooling from sql*plus. In PLSQL use DBMS_OUTPUT.PUT_LINE procedure to output data to screen or spool to file. Or use UTL_FILE to write to a file in the server.
0
 

Expert Comment

by:oogooglies
ID: 17041703
Sorry in the proc replace the double quotes with single ones......
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17041889
DBMS_OUTPUT and UTIL_FILE ork on server side.
I do not thiink they will show the result on the client side.
Only spool command and selects in a SQL*Plus script can help you.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17041954
SQL> set serveroutput on
SQL> begin
dbms_output.put_line('Sample code);
end;
/
SQL>
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17043278
which OS you are working on?

as other people said, you can not mix up pl/sql and sqlplus script but you can possibly use batch file to implement this logic.
0
 

Author Comment

by:suhinrasheed
ID: 17048013
actonwang i am using windows in my local system and Oracle is in Unix.

Can you tell the method using batchfile
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048202
here is the script, replace "<user>/<pass>" by real values:

////////////////////////////////
@echo off

for /l %%i in (1,1,100000) do call :script %%i
goto end

:script
set p=%1
set /A mod=%p%%%1000
if %mod%==0 (
echo exit; | sqlplus <user>/<pass> @file1.sql
)
goto :EOF

:end
@echo on
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048205
isn't that simple? :)

acton
0
 

Author Comment

by:suhinrasheed
ID: 17048224
actonwang..i want to accept your answer but i didnt understand what yu hve written .

Can you just rewrite my procedure and send me back with yur modifications
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048238
I just gave you the whole script for your orignal request as:


Begin

For i in 1..100000

Loop

If mod(i,1000)=0 then

@@file1.sql

End if

End loop

End;
/
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048241
mine is a window batch file, save it to a batch file , say, spool.bat then you can execute it.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048242
@echo off

for /l %%i in (1,1,100000) do call :script %%i
goto end

:script
set p=%1
set /A mod=%p%%%1000
if %mod%==0 (
echo exit; | sqlplus <user>/<pass> @file1.sql
)
goto :EOF

:end
@echo on
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048249
some explanations:

for /l %%i in (1,1,100000) do call :script %%i  <-- loop  from 1 to 100000

set /A mod=%p%%%1000 <-- mod caculation

if %mod%==0 (                                            <-- if mod is 0, then invoke the sqlplus and execute the script file.
echo exit; | sqlplus <user>/<pass> @file1.sql <-- after script is executed, exit it and continue the loop
)
0
 

Author Comment

by:suhinrasheed
ID: 17048295
Can explain me the syntax

for for /l %%i in (1,1,100000)

set p=%1
set /A mod=%p%%%1000

i cant understand the code convention in yur batch file
0
 

Author Comment

by:suhinrasheed
ID: 17048303
Your method is good.But my basic questiion was not running a batch file..but to call a file from inside a procedure
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17048382
Open the file using UTL_FILE and read the SQL statement. Then run the SQL using "execute immediate" or "ref cursor" .
0
 

Author Comment

by:suhinrasheed
ID: 17048394
The problem with UTL_FILE is, the file will reside in oracle server. Since Oracle_server is a unix box, I need to ftp it everytime
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17050058
>>.But my basic questiion was not running a batch file

   if you can use batch file to implement the logic as your procedure does. Why not use it?
   I think that your main purpose is to implement the loop and be able to call script file. isn't it?
0
 
LVL 19

Accepted Solution

by:
actonwang earned 1500 total points
ID: 17050063
>>Inside file1, I have Sql plus commands to spool to file in OS.
    using my method, you can do spool as well.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question