("<unnamed portal 9>") on calling procedures in EDBPOSTGRESQL

Question

I have a situation here, we are migrating from Oracle to EDB POSTGRES ADVANCED SERVER(ORACLE COMPATIBLE). I will share a sample procedure (the same method is used everywhere in our organization procedures).

CREATE OR REPLACE PROCEDURE get_emp(
OUT dataset refcursor,
jb character varying)
LANGUAGE 'edbspl'

AS $BODY$

STR VARCHAR2(32000) ;
BEGIN
STR := 'SELECT * FROM EMP WHERE JOB='''||JB||'''  ';

DBMS_OUTPUT.PUT_LINE ( STR ) ;
OPEN DATASET FOR STR ;

END GET_EMP$BODY$;

This procedure is compiled successfully. but when I call the procedure

SELECT GET_EMP('CLERK');

The result is like

"<unnamed portal 1>"

after this what I do is

begin;
SELECT GET_EMP('CLERK');
fetch all in "<unnamed portal 1>";

This time I am getting the desired output. Is there any way to get the records just by calling the procedure like follows

SELECT GET_EMP('CLERK');

Answer 1

Your procedure is vulnerable to SQL injection. Besides, why the mumbo-jumbo with refcursors if all you want is:

CREATE FUNCTION get_emp(jb text) RETURNS SETOF emp
   LANGUAGE sql AS
'SELECT * FROM emp WHERE job = jb';