Solved

spool in oracle

Posted on 2006-07-05
27
4,258 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 47

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
 

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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now