"Subroutines" in SQLPLUS 1

[MCubitt]

I have a large SQL script which repeats a process many times for different values, i.e.:

EXEC :uUserID := :uUserID01
runsql .... where userid=:uUserID
EXEC :uUserID := :uUserID02
runsql .... where userid=:uUserID
EXEC :uUserID := :uUserID03
runsql .... where userid=:uUserID

etc

Ideally I would like to have:

runprocedure1(user1)
runprocedure1(user2)
runprocedure1(user3)


But i do not want to store anything in the DB, just run the SQL standalone. Can this be done?





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[slaing]

Some PL/SQL sounds the obvious choice - using a loop to process the users.

You don't need to store the PL/SQL in the database, just write it as an anonymous block that can be put into your .sql script file

For example :

declare
procedure do_processing(p_value in varchar) is
begin
insert into test_table (username) values (p_value);
end;
begin
for i in 1..10
loop
do_processing('user'||to_char(i));
end loop;
commit;
end;

 

[MCubitt]

I tried the following, in SQL:

declare
procedure do_processing(p_value in varchar) is
begin
select p_value from dual;
end;

begin
for i in 0..9
loop
do_processing('user'||to_char(i));
end loop;
end;
/

but get:
select p_value from dual;
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement



Any ideas why?



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Slaing,

Sorry, I meant to thank you for your response but got carried away with my reply.

regards

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[slaing]

No problem !

SELECTS in PL/SQL need to be worded as

SELECT column INTO variable FROM...


eg :

set serveroutput on
declare
dTemp DATE;
begin
select sysdate into dTemp from dual;
dbms_output.put_line(dTemp);
end;
/

HTH

Steve

 

[MCubitt]

Right, thanks... So now I have this:

declare
procedure do_processing(p_value in varchar) is
tempvar char(30);
begin
select p_value into tempvar from dual;
end;

begin
spool abc.txt;
for i in 0..9
loop
do_processing('user'||to_char(i));
end loop;
spool off;
end;

/


I need to accumulate the results into one spool file, you see.

However, now I get this:
spool abc.txt;
*
ERROR at line 10:
ORA-06550: line 10, column 7:
PLS-00103: Encountered the symbol "ABC" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "ABC" to continue.
ORA-06550: line 15, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.

I'm getting lost in this!!!


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[slaing]

spool is a SQL*Plus command, not a PL/SQL one

try

spool abc.txt
begin
...
end;
/
spool off

 

[MCubitt]

Oh Gawd.. the SQL*Plus vs PL/SQL conundrum!

Thanks for the idea but moving the spool did not work. So it seems I need SQL*Plus after all.

Sorry I did not make that clear to start with.
Does this mean no procedures? No subroutines?

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Martin,

As you are finding, SQL*Plus does not have a native LOOP-ing capability as does PL/SQL. You can, however, simulate looping in SQL*Plus by causing your script to recursively call itself.

For example, if you have a script named "Martin.sql", at the end of Martin.sql, you could say, "@Martin". At the point you are done, you just <ctrl-C> out of the script. (There are other "control" alternatives that would be "cleaner" than <ctrl-C>-ing out of the loop. Let us know if you would like to see control alternatives.)

SQL*Plus has a maximum of 20 recursive calls. So, if you wish to execute Martin.sql recursively more than 20 times in one session, then I suggest you use a PL/SQL looping mechanism. A non-database-control mechanism in PL/SQL would be to place your "p_value" control values into a flat file and read the flat file to loop through your logic until the flat file is empty. If you need instructions on how to do this (using Oracle PL/SQL's "utl_file" flat-file-processing package) please post your need here.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:18 (06Sep04) UTC (aka "GMT" and "Zulu"), 11:18 (06Sep04) Mountain Time)

 

[MCubitt]

Santa,

Thanks for the tip.. in fact, it is not the looping that is the problem for me, but the use of subroutines.

I will quite happily manually pass the parameters rather than looping to create an effective one:

spool copyto.csv

select '"Rec","Action","UserID","Description"' from dual;

EXEC :uCopyFromUserID := :uCopyFromUserID00
EXEC :uCopyToUserID := :uCopyToUserID00
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID

EXEC :uCopyFromUserID := :uCopyFromUserID01
EXEC :uCopyToUserID := :uCopyToUserID01
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID

EXEC :uCopyFromUserID := :uCopyFromUserID02
EXEC :uCopyToUserID := :uCopyToUserID02
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID

EXEC :uCopyFromUserID := :uCopyFromUserID03
EXEC :uCopyToUserID := :uCopyToUserID03
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID

etc

spool off;

exit;

Would become

procedure defined as...
begin
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID
end


spool copyto.csv

select '"Rec","Action","UserID","Description"' from dual;

p_copy(:uCopyFromUserID00, :uCopyToUserID00)

p_copy(:uCopyFromUserID01, :uCopyToUserID01)

p_copy(:uCopyFromUserID02, :uCopyToUserID02)

p_copy(:uCopyFromUserID03, :uCopyToUserID03)

etc.

spool off;

exit;

Excuse pidgeon SQL




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[stefanhei]

Hi.
You can "implement subroutines" by using a seperate SQL*Plus-scripts for the part you call often. I for example use this technique for different spool-settings (pagesize,...)
So there would be 2 SQL*Plus-scripts:
1) call_copy.sql

spool copyto.csv
select '"Rec","Action","UserID","Description"' from dual;
@do_copy.sql :uCopyFromUserID00 :uCopyFromUserID00
@do_copy.sql :uCopyFromUserID01 :uCopyFromUserID01
...
spool off
exit

@ is similar to the SQL*Plus start command, but faster to write ;-)
2) do_copy.sql

EXEC :uCopyFromUserID := &1
EXEC :uCopyToUserID := &2
Select ',,' || :uCopyToUserID || ', value from table where user=:uCopyFromUserID;

&1 is the first parameter you pass to your do_copy-script, &2 the second...

Stefan

 

[MCubitt]

That's a jolly good, yet blatantly simple, idea!
No wonder I missed it!

Thanks Stefan.


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Ok, getting verrry close. However, using:
@makecopyp1.sql :uCopyFromUserID00 :uCopyToUserID00

seems to provide the arguements as :uCopyFromUserID00 :uCopyToUserID00 not as their values.

Am I doing it wrong?!



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Oops, i sorted it. Stefan - I should have followed your code more closely.

I had my incoming arguments with ('&1') not just &1!

Thanks again


There's no need for sarcastic replies, we've not all been this sad for that long!