Execute immediate procedurename returning data from a different schema

Toad for Oracle
1

I have a question about how to return data to one schema from a procdure that performs inserts on an another schema.

I am working on an application that uses one schema as a global entry point for all calls from Java to Oracle stored procedures. A procedure in the package of this global schema, uses the arguments passed in to format a character string to execute a second procedure that will execute on a specific client schema. Below is a diagram of the architecture:

Schema Architecture.jpeg

I am having issues returning OUT variables from the Procedure in the Client Schema to the Global Schema. I can successfully return data from a procedure while logged into a Client Schema. It is how the Global Schema is executing the Client Schema where I’m having issues. Below is a simplified version of the Procedure from the Global Schema and then the Procedure from the Client Schema:

GLOBAL SCHEMA

procedure P_UPDATE(
CLIENT_NUMBER in varchar2,
PKG_NAME in varchar2,
PROC_NAME in varchar2,
PROC_ARGS in ARG_ARRAY,
OUT_CURSOR OUT REFCUR_TYPE,
OUT_RETURN_CODE OUT INTEGER,
OUT_RETURN_MSG OUT VARCHAR2
) is
SCHEMA_NAME varchar2(10);
SQL_STMT varchar2(10000);
VAL_LIST varchar2(10000);
T_CURSOR REFCUR_TYPE := NULL; (this is a reference cursor)
T_RETURN_CODE integer := 0;
T_RETURN_MSG VARCHAR2(2000) := ‘’;
begin
SCHEMA_NAME := ‘CLIENT’ || CLIENT_NUMBER;

  -- format the schema.package.proc
  SQL_STMT := 'begin '||SCHEMA_NAME||'.'||PKG_NAME||'.'||PROC_NAME;

– format the procedure arguments()
… code to populate VAL_LIST with properly formatted arguments for procedure …
SQL_STMT := SQL_STMT||’(’||VAL_LIST||’,); end;’;

—SQL_STMT will contain ‘begin CLIENT01.PKG_DATA.P_INSERT_ACCOUNT(935,0,‘T001ABC’,); end;’

execute immediate SQL_STMT INTO T_CURSOR, T_RETURN_CODE, T_RETURN_MSG; {this is where its failing}

OUT_CURSOR := T_CURSOR;
OUT_RETURN_CODE := T_RETURN_CODE;
OUT_RETURN_MSG := T_RETURN_MSG;
end;

CLIENT SCHEMA

PROCEDURE P_INSERT_ACCOUNT(
A_ACCTID IN CLIENT_REVIEW_LOG.ACCOUNT_ID%TYPE,
A_REVID IN CLIENT_QUESTION_RESPONSES.CLIENT_REVIEW_ID%TYPE,
A_USER IN CLIENT_USER_INFORMATION.USER_NAME%TYPE,
O_CURSOR OUT TYPE_REFCUR_ADMN,
O_RETURN_CODE OUT INTEGER,
O_RETURN_MSG OUT VARCHAR2
)
IS
BEGIN
O_RETURN_CODE := 0;
O_RETURN_MSG := ‘Successful Completion’;

INSERT stuff INTO CLIENT_ACCOUNT;

OPEN O_CURSOR FOR
SELECT
ACCOUNT_NUMBER AS ACCOUNTNUMBER_OUT,
CAPACITY_CODE AS CAPCODE_OUT,
ADMINISTRATOR AS ADM_OUT,
PORTFOLIO_MANAGER AS PM_OUT,
TAX_PREPARER AS TAXP_OUT,
BRANCH_CODE AS BRANCH_OUT
FROM CLIENT_ACCOUNT
WHERE CLIENT_REVIEW_ID = A_REVID;
END;

I have received a battery of error messages while testing, all of which occur on the ‘execute immediate’ statement within the Global Schema. I am not sure how to dynamically load the OUT variables. Based on the documentation that I have read it does not seem that OUT variables require an initial value, yet PLSQL balks when I don’t specify some … and it balks when I do specify something. When I do specify a variable, the message indicates that it must be defined (but all variables are defined to the procedure).

Has anyone used an architecture like this and successfully returned data from a procedure that executes DML statements? If so, how did you format your ‘execute immediate’ statement? Or did you use a different approach?

Regards,

Tom

2

From the looks of it, you either want an array returned or piped row output. I would assume the array. What you're trying to do with the cursor seems a little funky.

I've written some dynamic SQL myself and we have code that uses dynamic schema, packages, and database link names.

I'll review your code on a computer (I'm on my phone.)

Have you tried removing the cursor reference for your code to see if it passes with that? Also, can you copy and paste all of the errors you got?

On Nov 22, 2013, at 10:10 AM, "tom.westerhoff" bounce-tomwesterhoff@toadworld.com wrote:

execute immediate procedurename returning data from a different schema

Thread created by tom.westerhoff
I have a question about how to return data to one schema from a procdure that performs inserts on an another schema.

I am working on an application that uses one schema as a global entry point for all calls from Java to Oracle stored procedures. A procedure in the package of this global schema, uses the arguments passed in to format a character string to execute a second procedure that will execute on a specific client schema. Below is a diagram of the architecture:

I am having issues returning OUT variables from the Procedure in the Client Schema to the Global Schema. I can successfully return data from a procedure while logged into a Client Schema. It is how the Global Schema is executing the Client Schema where I'm having issues. Below is a simplified version of the Procedure from the Global Schema and then the Procedure from the Client Schema:

GLOBAL SCHEMA

procedure P_UPDATE(
CLIENT_NUMBER in varchar2,
PKG_NAME in varchar2,
PROC_NAME in varchar2,
PROC_ARGS in ARG_ARRAY,
OUT_CURSOR OUT REFCUR_TYPE,
OUT_RETURN_CODE OUT INTEGER,
OUT_RETURN_MSG OUT VARCHAR2
) is
SCHEMA_NAME varchar2(10);
SQL_STMT varchar2(10000);
VAL_LIST varchar2(10000);
T_CURSOR REFCUR_TYPE := NULL; (this is a reference cursor)
T_RETURN_CODE integer := 0;
T_RETURN_MSG VARCHAR2(2000) := '';
begin
SCHEMA_NAME := 'CLIENT' || CLIENT_NUMBER;

  -- format the schema.package.proc
  SQL_STMT := 'begin '||SCHEMA_NAME||'.'||PKG_NAME||'.'||PROC_NAME;

-- format the procedure arguments()
... code to populate VAL_LIST with properly formatted arguments for procedure ...
SQL_STMT := SQL_STMT||'('||VAL_LIST||',,,); end;';

3

I removed all but the INTEGER being returned from the procedure. Below are some of the Oracle errors that have generated:

Error Example #1.)

execute immediate SQL_STMT USING OUT T_INTEGER;

SQL_STMT =’begin CLIENT01.PKG_ACCESS.P_INSERT_ACCOUNT(935,0,‘T001ABC’,); end;’

ORA-06550: line 1, column 60:

PLS-00103: Encountered the symbol “)” when expecting one of the following:

( - + case mod new not null

continue avg count current exists max min prior wql stddev

sum variance execute forall merg time timestamp interval

date

pipe

Error Example #2.)

execute immediate SQL_STMT USING OUT T_INTEGER;

SQL_STMT =’begin CLIENT01.PKG_ACCESS.P_INSERT_ACCOUNT(935,0,‘T001ABC’,T_INTEGER); end;’

ORA-06550: line 1, column 60:

PLS-00201: identifier ‘T_INTEGER’ must be declared

Error Example #3.)

execute immediate SQL_STMT USING OUT T_INTEGER;

SQL_STMT =’begin CLIENT01.PKG_ACCESS.P_INSERT_ACCOUNT(935,0,‘T001ABC’,0); end;’

ORA-06550: line 1, column 60:

PLS-00363: expression ‘0’ cannot be used as an assignment target

Error Example #4.)

execute immediate SQL_STMT USING OUT T_INTEGER RETURNING INTO T_INTEGER;

SQL_STMT =’begin CLIENT01.PKG_ACCESS.P_INSERT_ACCOUNT(935,0,‘T001ABC’,:1); end;’

ORA-06547: RETURNING clause must be used with INSERT, UPDATE or DELETE statements

Somehow I need to tell the ‘execute immediate’ that the procedure is returning data (from a DML statement) and where to put that returning data.

Any advice is appreciated.

Thanks,

Tom

4

Thanks for the reply jpdominator. Actually what I want returned in one (or multiple) parms from the procedure call (P_INSERT_ACCOUNT()). What you are seeing are the pipe delimited input arguments that are passed into the global schema. Hope that make sense.

Tom