Calling EDB Stored procedure(which is having output parameter) using EDB*plus from remote server

Question

EDB plus is being used for the purpose of calling Enterprise Database procedure (EDB SPL), the return value of the procedure is to be captured in a shell variable. Need syntax and details for the same. The database is on a remote machine.

I am able to call the EDB SPL (stored procedure) without mentioning it's output parameter using EDB * Plus from remote server. But not able to capture its output parameter's values into shell local variables.

--shell script code(OS is Linux, EDB version 11.3.10)
Proc_result=`edbplus -S $DB_USER/$DB_PASSWORD@$DB_SCHEMA 2>>EDB_ERROR_FILE <<END_OF_SQL
set feedback off
set pages 0
set flush off
set feedback on
exec schema1.procedure_1(input_param1, input_param2, input_param3);
exit;
END_OF_SQL`

--EDB procedure signature
schema1.procedure_1(input_param1 varchar2, 
                    input_param2 number,
            input_param3 varchar2,
                    output_param1 varchar2,
            output_param2 number);

I want to get value of "output_param1" and "output_param2" in the shell variable "Proc_result"

score 0 · Answer 1 · answered Oct 09 '19 at 18:50

Perhaps you need to use CALL instead of EXEC?

[root@ep11 bin]# DB_USER=enterprisedb
[root@ep11 bin]# DB_PASSWORD=abc123
[root@ep11 bin]# DB_SCHEMA="127.0.0.1:5432/edb"
[root@ep11 bin]# Proc_result=`edbplus -S $DB_USER/$DB_PASSWORD@$DB_SCHEMA 2>>EDB_ERROR_FILE <<END_OF_SQL
> set feedback off
> set pages 0
> set flush off
> set feedback on
> exec schema1.procedure_1('foo',1,'bar');
> exit;
> END_OF_SQL`
[root@ep11 bin]# echo $Proc_result;
EDB-SPL Procedure successfully completed.
[root@ep11 bin]# Proc_result=`edbplus -S $DB_USER/$DB_PASSWORD@$DB_SCHEMA 2>>EDB_ERROR_FILE <<END_OF_SQL
set feedback off
set pages 0
set flush off
set feedback on
call schema1.procedure_1('foo',1,'bar');
exit;
END_OF_SQL`
[root@ep11 bin]# echo $Proc_result;
02c6e1417aae6f6719a772fe7ea2cfac 1 CALL completed.
[root@ep11 bin]# psql -c "call schema1.procedure_1('foo',1,'bar')"
          output_param1           | output_param2 
----------------------------------+---------------
 02c6e1417aae6f6719a772fe7ea2cfac |             1
(1 row)

Calling EDB Stored procedure(which is having output parameter) using EDB*plus from remote server

1 Answers1