spool in oracle

@@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;

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

no my sql file has some sqlplus commands inside it

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

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

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

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

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;

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.

Sorry in the proc replace the double quotes with single ones......

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.

SQL> set serveroutput on
SQL> begin
dbms_output.put_line('Sample code);
end;
/
SQL>

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.

actonwang i am using windows in my local system and Oracle is in Unix.

Can you tell the method using batchfile

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

isn't that simple? :)

acton

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

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;
/

mine is a window batch file, save it to a batch file , say, spool.bat then you can execute it.

@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

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
)

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

Your method is good.But my basic questiion was not running a batch file..but to call a file from inside a procedure

Open the file using UTL_FILE and read the SQL statement. Then run the SQL using "execute immediate" or "ref cursor" .

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

>>.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?