How to return IN OUT and OUT parameters from executing procedures on a remote Oracle database

This article shows how to create procedures in PostgreSQL with functions similar to CAPTURE in Oracle.

Procedures, unlike functions , do not return anything.

In Oracle, if a capture is required, a way to pass them from the PLSQL context to SQL is required. Or alternatively, they can be printed to std out using dbms_output in PLSQL.

Let us create a test procedure on the Oracle side.

This procedure must:

• Carry out DML such as insert update or delete
• Have the three types of parameters : IN , IN OUT , OUT

Here is the proposed DDL: the IN OUT param takes a number modifies and apply some formatting The OUT param will capture a successfull insert and commit on a table called dropme (hence the load_dopme name for the procedure).

CREATE OR REPLACE PROCEDURE "LOAD_DROPME" 
(p1 IN NUMBER,
p_phone_no IN OUT VARCHAR2,
p_status OUT VARCHAR2)
AS
BEGIN
INSERT INTO dropme VALUES(p1,p_phone_no); 
p_phone_no := '(' || SUBSTR (p_phone_no,1,3) ||
') ' || SUBSTR (p_phone_no,4,3) ||
'-' || SUBSTR (p_phone_no,7);

COMMIT;
p_status := 'OK';
EXCEPTION
WHEN OTHERS THEN a
NULL;
p_status := 'fail';
END;

In sqlplus, Oracle has a VARIABLE command , which can be used as shown below, to capture the IN OUT and OUT parameters from its client.

The variable command will create bind variables used in the context switch.

See example below:

sqlplus -s scott/tiger@//192.168.0.56:1521/orcl <<EOF
set serverout on
set line 3000
column status format a20
column inout_param format a20 

set autoprint on

variable status varchar2(20);
variable inout_param varchar2(20) = '8006330575'; -- must be assigned now

exec load_dropme(1,:inout_param,:status);

EOF

This will produce

PL/SQL procedure successfully completed.


STATUS
OK


INOUT_PARAM
(800) 633-0575

To capture this values in EPAS the following workaround is required :

-- Create a dblink_ora as usual 
select dblink_ora_connect('conn1', '192.168.0.56' ,'orcl', 'SCOTT','tiger',1521); 

-- In 18c + , you can use PRIVATE temp tables, we are limited by 12c to a global temp

select dblink_ora_exec('conn1',$$ create global temporary table ora$ptt_my_temp_table (inout_param VARCHAR2(20), status VARCHAR2(20)) on commit delete rows $$) ;

-- execute the procedure , capture the in out params in variables: inout_param and status, and insert them into the temp table 
select dblink_ora_exec('conn1',
$$ declare 
inout_param varchar2(20) := '8006330575' ;
status VARCHAR(10);
begin
load_dropme(10,inout_param ,status); 
insert into ora$ptt_my_temp_table values(inout_param, status);
end ;
$$);

select * from dblink_ora_record('conn1', 'select inout_param,status from ora$ptt_my_temp_table ') AS foo(inout_param text, status text);

-- this will produce 
-- 
-- inout_param |status|
-- (800) 633-0575|OK |
-- 
-- 

-- drop the temp table

select dblink_ora_exec('conn1',$$ drop table ora$ptt_my_temp_table $$);


select dblink_ora_disconnect('conn1'); 

There are other options of course, such as create wrappers functions on the Oracle side.

This however, will require using (if an insert/update/delete) is used in the procedure , an autonomous transaction.

This is never recommended but for very limited uses (E.g. logging).