CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Alkey29
Alkey29

Reputation: 199

Using an out parameter from an execute immediate statement that calls a procedure

I have a procedure that has an execute immediate statement. Within that execute immediate statement calls a procedure that has two out parameters. I'm having trouble trying to obtain those out parameters to be used in the procedure calling the execute immediate statement.

execute_stmt := 'DECLARE pi_error_log_rec error_log%ROWTYPE; p_plan_id NUMBER; begin SCHEMA.PACKAGE_NAME.' ||
                PROCEDURE || '(' ||
                p_audit_log_id || ', ' ||
                V_PARAMETER1 || ', ' || '''' ||
                V_PARAMETER2 || '''' ||
                ', p_plan_id, pi_error_log_rec); end;';
execute immediate execute_stmt;

if(pi_error_log_rec.error_text IS NOT NULL) THEN
  --do stuff;
ELSE
  --do stuff;

The out parameters are p_plan_id and pi_error_log_rec, the remaining are IN parameters. So I need to obtain those two outputs for use inside the procedure calling this execute immediate. But it's not setting the output in the variable. Am I missing a step or should I use a different approach in using out parameters?

Upvotes: 3

Views: 13789

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Try this one:

execute_stmt := 'Begin SCHEMA.PACKAGE_NAME.PROCEDURE(:p_audit_log_id, :V_PARAMETER1, :V_PARAMETER2); END;';
execute immediate execute_stmt USING IN p_audit_log_id, OUT pi_error_log_rec, OUT p_plan_id;

Since Oracle 12c you can also fetch PL/SQL datatypes (like RECORDS, BOOLEAN) into dynamic SQL. In earlier versions you where limited to SQL datatypes only (like NUMBER, VARCHAR2, etc.)

Upvotes: 9

Related Questions