spool in oracle

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

suhinrasheedAsked:
Who is Participating?
 
actonwangConnect With a Mentor Commented:
>>Inside file1, I have Sql plus commands to spool to file in OS.
    using my method, you can do spool as well.

0
 
schwertnerCommented:
@@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
 
oogoogliesCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
suhinrasheedAuthor Commented:
no my sql file has some sqlplus commands inside it
0
 
suhinrasheedAuthor Commented:
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
 
oogoogliesCommented:
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
 
oogoogliesCommented:

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
 
suhinrasheedAuthor Commented:
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
 
oogoogliesCommented:

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
 
MohanKNairCommented:
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
 
oogoogliesCommented:
Sorry in the proc replace the double quotes with single ones......
0
 
schwertnerCommented:
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
 
MohanKNairCommented:
SQL> set serveroutput on
SQL> begin
dbms_output.put_line('Sample code);
end;
/
SQL>
0
 
actonwangCommented:
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
 
suhinrasheedAuthor Commented:
actonwang i am using windows in my local system and Oracle is in Unix.

Can you tell the method using batchfile
0
 
actonwangCommented:
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
 
actonwangCommented:
isn't that simple? :)

acton
0
 
suhinrasheedAuthor Commented:
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
 
actonwangCommented:
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
 
actonwangCommented:
mine is a window batch file, save it to a batch file , say, spool.bat then you can execute it.
0
 
actonwangCommented:
@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
 
actonwangCommented:
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
 
suhinrasheedAuthor Commented:
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
 
suhinrasheedAuthor Commented:
Your method is good.But my basic questiion was not running a batch file..but to call a file from inside a procedure
0
 
MohanKNairCommented:
Open the file using UTL_FILE and read the SQL statement. Then run the SQL using "execute immediate" or "ref cursor" .
0
 
suhinrasheedAuthor Commented:
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
 
actonwangCommented:
>>.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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.