Oracle Database - EXECUTE IMMEDIATE inside dbms_parallel_execute

Question

I've faced the following task: to parallel some process on Oracle 12c database. As always, I've started making this using dbms_parallel_execute package. Created a new task using dbms_parallel_execute.run_task procedure and passed my anonymous block with stored procedure call as sql_stmt parameter.

l_sql := 'BEGIN my_procedure(:start_id, :end_id); END;';
dbms_parallel_execute.run_task
(
    task_name => l_task_name,
    sql_stmt => l_sql,
    language_flag => dbms_sql.native,
    parallel_level => l_parallel_level
);

This stored procedure calls a couple of other procedures.

As a result, my "parent" procedure fails with the following exception:

PLS-00103: Encountered the symbol "." when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
The symbol "<an identifier>" was substituted for "." to continue.

As I found out, this exception throws because of EXECUTE IMMEDIATE call inside on of the procedures:

l_sql := 'BEGIN ' || some_another_pkg.schema_name || '.some_pkg.some_procedure(:1, :2); END;'

EXECUTE IMMEDIATE l_sql 
USING l_param_1, l_param_2l

Where schema_name is a global variable of VARCHAR2(30) inside some_another_pkg package.

When I changed it to a forward procedure call, exception disappeared. But I can't replace this EXECUTE IMMEDIATE on a target environment.

Are there any workarounds to fix this issue without replacing EXECUTE IMMEDIATE call?

Thanks in advance.

Please print out the exact value of l_sql before calling execute immediate on it and post a minimal reproducible example thanks. — OldProgrammer
Not enough info to really help solve this, but from what you have presented, do you have a semi-colon at the end of your EXECUTE IMMEDIATE statement? — TenG
@OldProgrammer, my bad, added some changes into my question. l_sql variable concatenates using a global variable in another package. — ahgpoug
@TenG, yes, this a part of existing code and it works fine, but fails if it runs inside parallel execution. — ahgpoug

ahgpoug ahgpoug · Accepted Answer · 2019-11-23T23:20:24

The problem, as it was mentioned in comments, was in some_another_pkg.schema_name code. In my case this value was NULL.

Oracle Database - EXECUTE IMMEDIATE inside dbms_parallel_execute

1 Answers