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

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



   CURSOR c_schema IS

    select dschema from x.process_control where process_name = '....' order by schema;

      v_schema    process_control.db_schema%TYPE;




   OPEN c_schema;



      FETCH c_schema INTO v_schema;

       If v_schema <> '' then


       @connecting_schema v_schema;   ????????????   

     EXIT WHEN c_schema%NOTFOUND;



    CLOSE c_schema;


Open in new window

Question by:Neilami
  • 5
  • 3
  • 2
LVL 73

Expert Comment

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

      v_cur  SYS_REFCURSOR;
      OPEN v_cur FOR SELECT ..... <your query here>;


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

     for x in (select co1, col2, col3...  <your query here>) loop
     end loop;
LVL 29

Expert Comment

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.


Author Comment

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

Accepted Solution

sdstuber earned 250 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.  
LVL 73

Expert Comment

ID: 22714930
or, take the code out of your scripts and embed it within the anonymous pl/sql block.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

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

but anyways thx sdstuber
LVL 73

Expert Comment

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.

Author Comment

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!
LVL 73

Expert Comment

ID: 22717469
thank you!
LVL 29

Expert Comment

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


Open in new window


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 36
Updating a table from a temp table 4 30
1 FROM DUAL wont work with additional columns ?? 4 27
Extract string portion 2 16
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 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

26 Experts available now in Live!

Get 1:1 Help Now