[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-10-14
10
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22711473
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
ID: 22711773

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
ID: 22714884
so, somehow, still not clear answer....if i don't want to create any procedure , is it impossible? If yes, how exactly...?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 74

Accepted Solution

by:
sdstuber earned 750 total points
ID: 22714916
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 74

Expert Comment

by:sdstuber
ID: 22714930
or, take the code out of your scripts and embed it within the anonymous pl/sql block.
0
 

Author Comment

by:Neilami
ID: 22715033
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 74

Expert Comment

by:sdstuber
ID: 22715214
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
ID: 22715407
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 74

Expert Comment

by:sdstuber
ID: 22717469
thank you!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22724259

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

649 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