How to know if a procedure has successfully executed using dynamic sql in PL/SQL?

Question

I am trying to get an OUT parameter calling a procedure dynamically using bind parameters. The procedure has some IN parameters and an OUT parameter. The IN parameters are fetched from a single column containing all the parameters separated with comma. I am adding the bind parameter after that. But its giving an error. How to resolve this issue? I am returning 'S' for successful execution of the procedure being called and 'R' for unsuccessful execution. I need this returning data to process some further coding.

CREATE OR REPLACE PROCEDURE PROC_TEST (P_SL_NO VARCHAR2,
                             P_DATA VARCHAR2, /*P_DATA = A,B,C,D (COMES FROM A COLUMN IN A TABLE)*/
                             P_MSG OUT NUMBER)
IS

V_PROC VARCHAR2(250) := 'PKG_PROC.PROC_INTER@DBLINK';--PROCEDURE TO BE CALLED DYNAMICALLY
V_VAR2 VARCHAR2(4000);
V_BEGIN VARCHAR2(10) := 'BEGIN ';
V_END VARCHAR2(10) := ' END';
PARAM_MSG CHAR(1);

BEGIN

V_VAR2 := V_BEGIN||V_PROC||'('||P_DATA||','||':X'||')'||';'||V_END||';'; --:X IS AN OUT PARAMETER HERE RETURNS 'S' FOR SUCCESS OR 'R' IF UNSUCCESSFUL

EXECUTE IMMEDIATE V_VAR2 USING OUT PARAM_MSG;

DBMS_OUTPUT.PUT_LINE(PARAM_MSG);

P_MSG := 1;

EXCEPTION WHEN OTHERS THEN
P_MSG := 0;

END;
/

"Giving an error" is not very descriptive. Show us what error you're getting. A `when others` exception handler that doesn't even log the actual exception that was generated is a pretty poor practice and is going to be hiding the details you likely need to debug the problem. — Justin Cave, Jul 14 '22 at 17:52

score 0 · Answer 1 · answered Jul 15 '22 at 00:51

You are constructing pl/sql block without single quotes where they are needed. Your V_VAR2 variable with given values returns something like:

BEGIN PKG_PROC.PROC_INTER@DBLINK(A,B,C,D,:X);END;;

So, you need to insert single quotes with parameter values to get the executable pl/sql block. I suggest you to declare a variable for single quote with default value of 4 single quotes (which in the code wil be represented as one) and change your code for V_VAR2 to look like this:

Declare
--  ... ... ...
sq  VARCHAR2(1)  := '''';
BEGIN
-- ... ... ...
-- ... ... ...
    V_VAR2 :=  V_BEGIN  || Chr(10) || Chr(9) || V_PROC || '(' || sq || SubStr(P_DATA, 1, 2) || sq || SubStr(P_DATA,3, 2) || sq || SubStr(P_DATA, 5, 2) || sq || SubStr(P_DATA, 7, 1) || sq || ', :X);' || Chr(10) || V_END;
- ... ... ...
-- ... ... ...
END;
--  Result for V_VAR2 should be ok. now 
--  BEGIN 
--      PKG_PROC.PROC_INTER@DBLINK('A,'B,'C,'D', :X);
--  END;

I don't knowwhat that procedure does afterwards but the error is probabli caused by EXEC IMMEDIATE with bad pl/sql code. Regards...

I have figured out the error. V_VAR2 is already read as a string variable as per my code. My code is alright. The issue was with the called procedure PKG_PROC.PROC_INTER@DBLINK. I made the procedure into PRAGMA AUTONOMOUS_TRANSACTION. This has solved the issue. — Doodledim, Nov 03 '22 at 07:45

How to know if a procedure has successfully executed using dynamic sql in PL/SQL?

1 Answers1