Skip to Main Content
  • Questions

  • Dynamically pass function's parameters using select statement in shell script

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Learner.

Asked: June 28, 2019 - 4:31 pm UTC

Last updated: July 01, 2019 - 9:46 am UTC

Version: 11

Viewed 1000+ times

You Asked

I have a shell script which calls an oracle function and generates extract. I was able to create this when I manually pass the parameter, but I would like to pass dynamically based on the SQL statement. (Need to pass the max value of a column from table)


sqlplus -s user/passw@schema_name << EOF
set markup csv on
set numwidth 20
SET FEEDBACK OFF
spool /mydir/test_ext/test.dat;
var c refcursor
exec :c := TEST_FUNCTION( 'PARAM1', 'PARAM2' )
print c
spool off;
EOF
cp /mydir/test_ext/test.dat /Newdir/test_ext/test.dat


I want pass the parameters PARAM1 and PARAM2 using a select statements.
Ex:
PARAM1 --> select max(col45) into PARAM1 from table1;
PARAM2 --> select max(col23) into PARAM2 from table2;
I would like to pass these parameters dynamically inside this shell script. Can you help with ideas?

and Chris said...

Just turn your function call to an anonymous block. And do whatever initialization you need to for the parameters there:

create table t (
  c1 int, c2 int
);

insert into t values ( 1, 1 );
insert into t values ( 1, 2 );
insert into t values ( 2, 2 );
commit;

create or replace function test_function ( 
  p int 
) 
  return sys_refcursor as
  cur sys_refcursor;
begin

  open cur for 
    select * from t
    where  c1 = p
    and    c2 = p;
    
  return cur;
end test_function;
/

var c refcursor
declare
  p int;
begin 
  select max ( c1 )
  into   p
  from   t;
  
  :c := test_function( p );
end;
/
print c

        C1         C2
---------- ----------
         2          2

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Learner DN, July 02, 2019 - 4:06 pm UTC

Thanks Chris, it worked...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library