Solved

Is it possible to pass parameters to the called sql script from sqlplus script?...

Posted on 2008-10-14
10
261 Views
Last Modified: 2013-12-07
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?
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;

Open in new window

0
Comment
Question by:Neilami
  • 5
  • 3
  • 2
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
there are two standard ways to do this.


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.col2,x.col3);
     end loop;
end
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

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.


0
 

Author Comment

by:Neilami
Comment Utility
so, somehow, still not clear answer....if i don't want to create any procedure , is it impossible? If yes, how exactly...?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
no, it is not possible to use pl/sql to call sqlplus scripts.

you could instead,  use shell scripting to iterate through values and call sqlplus with those parameters.  
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
or, take the code out of your scripts and embed it within the anonymous pl/sql block.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Neilami
Comment Utility
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...
http://www.experts-exchange.com/Database/Oracle/10.x/Q_23804747.html

but anyways thx sdstuber
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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.
0
 

Author Comment

by:Neilami
Comment Utility
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!
 
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
thank you!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

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

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 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

10 Experts available now in Live!

Get 1:1 Help Now