Neilami
asked on
Is it possible to pass parameters to the called sql script from sqlplus script?...
Hi.
I can't find anywhere the answer so I hope you will help...
I run in sqlplus query with cursor. I need to pass each row from the cursor as parameter to the called script....Is it actually possible?
I can't find anywhere the answer so I hope you will help...
I run in sqlplus query with cursor. I need to pass each row from the cursor as parameter to the called script....Is it actually possible?
DECLARE
CURSOR c_schema IS
select dschema from x.process_control where process_name = '....' order by schema;
v_schema process_control.db_schema%TYPE;
BEGIN
OPEN c_schema;
LOOP
FETCH c_schema INTO v_schema;
If v_schema <> '' then
@connecting_schema v_schema; ????????????
EXIT WHEN c_schema%NOTFOUND;
END LOOP;
CLOSE c_schema;
END;
Also, unless the "@connecting_schema v_schema; ???????????? " is a valid set of PL/SQL statements, an SQL*Plus command would not recognized by PL/SQL. You would need to convert it to a stored procedure and use sdstuber's recommendation.
ASKER
so, somehow, still not clear answer....if i don't want to create any procedure , is it impossible? If yes, how exactly...?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or, take the code out of your scripts and embed it within the anonymous pl/sql block.
ASKER
yeah, the parameter was suppose to be the name of the schema, under which would the sql connect to database and run other file...and here we come to my question that nobody answer :)....again...
https://www.experts-exchange.com/questions/23804747/How-to-run-one-script-parallel-on-each-schema.html
but anyways thx sdstuber
https://www.experts-exchange.com/questions/23804747/How-to-run-one-script-parallel-on-each-schema.html
but anyways thx sdstuber
sorry, "it's not possible" is the answer.
pl/sql is simply not the tool for doing what you're asking.
if you used a procedure instead of scripts it could work, or if you use shell scripting it will work, or if you use external procedures or java stored procedures to invoke sqlplus it would work. But given what the constraints you requested, it's not possible.
pl/sql is simply not the tool for doing what you're asking.
if you used a procedure instead of scripts it could work, or if you use shell scripting it will work, or if you use external procedures or java stored procedures to invoke sqlplus it would work. But given what the constraints you requested, it's not possible.
ASKER
sorry, with that not answered question i meant the other question that i posted the link to....I already accept your "no,impossible" first time ;)....btw congratulation to marriage....Alles gute!
thank you!
-- OR --
You could try something like this:
-- ---------------------------------------------------------------------
-- Execute on all schema
set pages 0 feed off ver off term off
spo /tmp/schema_exec.sql
Select "@connecting_schema "||Dschema
From X.Process_Control
Where Process_Name = '....'
Order By Schema;
spo off
@/tmp/schema_exec.sql
first, open your cursor and pass it is as a reference cursor to your procedure which would then do the fetching and processing of each row itself.
that would look like this...
declare
v_cur SYS_REFCURSOR;
begin
OPEN v_cur FOR SELECT ..... <your query here>;
your_procedure(v_cur);
end;
second, simply fetch the values and pass them into your procedure. I recommend using the FOR cursor loop instead of while/end. That would look like this...
begin
for x in (select co1, col2, col3... <your query here>) loop
your_procedure(x.col1,x.co
end loop;
end