asked on

Execute Immediate

Hi,
I have a package within which I am trying to execute plsql function that returns a return cd using execute immediate statement. The plsql function are being read from a table and execute in a loop. I am getting an invalid statement. Can you tell me what am i doing wrong. See the snippet of the code below;

Cursor c1 is
select procedure_name,severity_level,message,module_name
from auto_rate_tbl_validation_list
where risk_state_Cd = pStateCd
and rating_plan = pRatingPlan
and promotion_level = pPromotionLevel;

for aa in c1
loop
begin
dbms_output.put_line(aa.procedure_name);
vSql1 := 'RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')';
dbms_output.put_line(vSql1);
execute immediate (vSql1) into pReturnCd ;
if pReturnCd != 0 then
vTable := aa.module_name;
vDesc := aa.message;
vSeverity := aa.severity_level;
PIPE ROW(o_Error_Row(vTable,vDesc,vSeverity));
vErrCd := 1;
end If;
end;
end loop

alligatorsql

Hello

Call the function as an anonymous block. That means, that you have to put the word "begin" before and "end;" after the call.
Here is the new code:

vSql1 := 'BEGIN; RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')'||'; END;';
dbms_output.put_line(vSql1);
execute immediate (vSql1) into pReturnCd ;

Note: At the end of the function, there must be a ;
example

vSQL1 := 'BEGIN func(); END;'

Hope that helps ?

Best regards
Manfred Peter

TheHartford

ASKER

Peter,
I tried that. It did not work. I had removed the commented code from the snippet that I posted earlier. Is there any other way?

-- vSql1 := 'Begin RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||');'
-- || ' End;';

Thanks
Sri

Danielzt

what did you get in the following command?

dbms_output.put_line(vSql1); -- check here, the command is your want.
execute immediate (vSql1) into pReturnCd ;

pratikroy

Well, your problem is that you are trying to execute a function, whereas the method that you are using, is used to dynamically call an SQL statement.

If you dont want to try anything else, you can do the following :

vSql1 := 'SELECT RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||') FROM DUAL';

the variable vSql1 would have an SQL statement like "SELECT RATE_TABLE_VALIDATION_PKG_SRI.yourfunc(pStateCd,pRatingPlan,pVersion) from Dual"

This becomes a valid SQL statement, and you should be able to get the desired output without changing anything else :)

ASKER CERTIFIED SOLUTION

pratikroy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pratikroy

@AlligatorSQL :

Just wanted to point out that you were partially incorrect, w.r.t the function call within a PLSQL Block. You said that the variable can be set to :

vSql1 := 'BEGIN; RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')'||'; END;';

1. You dont need a ';' after BEGIN
2. I dont think you can execute the function in the PLSQL block the way you have suggested. I believe that it is only applicable for procedure calls.

Like :
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;

Please let me know, if you think there is a way to execute the function dynamically (the way you have suggested). A small example, the way I have given above might help in understanding.

Thanks.

sdortch

I have tried ran into the same problem. I need to run a function from EXECUTE IMMEDIATE however, my function has DML statements inside and I get ORA-14551.

Is there any other solution for this problem?

Thanks in advance

pratikroy

@@sdortch : Please post a new question for this.