PL/SQL Dynamic SQL USING clause

Question

I am working with an Oracle 11g database, release 11.2.0.3.0 - 64 bit production

I have several defined packages, procedures, functions and data types. After numerous intermediate calculations largely done using collections, arrays and other data structure, I ultimately need to create a database table dynamically to output my final results. For the purpose of this question, I have the following:

TYPE ids_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

benefit_ids ids_t;

--Lots of other code which successfully populates benefit_ids. 
--benefit_ids has several million rows, and is used successfully as 
  the input to the following function:

FUNCTION find_max_ids(in_ids in ids_t)
RETURN ids_t
IS
    str_sql varchar2(200);
    return_ids ids_t;
BEGIN
    str_sql := 'SELECT max(b.benefit_id)
                FROM TABLE(:1) a
                JOIN benefits b ON b.benefit_id = a.column_value
                GROUP BY b.benefit_id';

    EXECUTE IMMEDIATE str_sql BULK COLLECT INTO return_ids USING in_ids;

    RETURN return_ids;
END;

The above works fine and clearly demonstrates that it is possible to pass an array as a parameter to a dynamic sql function or procedure.

However, when I try using EXECUTE IMMEDIATE and USING to create a database table as my final output I run into problems:

PROCEDURE create_output_table(in_ids in ids_t, in_tbl_nme in varchar2)
AUTHID CURRENT_USER
IS
   str_sql := 'CREATE TABLE Final_Results AS (
                 SELECT a.client_id, a.benefit_id
                 FROM ' || in_tbl_nme || ' a
                 LEFT JOIN TABLE(:1) b on b.column_value = a.benefit_id
                 WHERE b.column_value is NOT NULL)';

   EXECUTE IMMEDIATE str_sql USING IN in_ids;

END;

Rather unhelpfully the only error message I receive back is ORA-00933: SQL command not properly ended. However, I can't see anything wrong with the syntax per se, though I suspect the problem is with how I am applying the EXECUTE IMMEDIATE in this instance.

Any advice would be gratefully received.

I cant see you have declared str_sql and also BEGIN keyword is missing. Try to incorporate these things your code will work. — Avrajit Roy
– Avrajit Roy, Commented Apr 8, 2016 at 14:27

Alex Poole · Accepted Answer · 2016-04-08 14:38:10Z

5

The code you've shown doesn't get ORA-00933, but it still isn't valid:

create type ids_t is table of number
/
create table test_table (client_id number, benefit_id number)
/
insert into test_table values (1, 1)
/

declare
  str_sql varchar2(4000);
  in_tbl_nme varchar2(30) := 'TEST_TABLE';
  in_ids ids_t := ids_t(1, 2, 3);
begin
   str_sql := 'CREATE TABLE Final_Results AS (
                 SELECT a.client_id, a.benefit_id
                 FROM ' || in_tbl_nme || ' a
                 LEFT JOIN TABLE(:1) b on b.column_value = a.benefit_id
                 WHERE b.column_value is NOT NULL)';

   EXECUTE IMMEDIATE str_sql USING IN in_ids;
end;
/

Error report -
ORA-22905: cannot access rows from a non-nested table item

That error doesn't look right; lets cast it to see if it's happier, even though it shouldn't be necessary:

declare
  str_sql varchar2(4000);
  in_tbl_nme varchar2(30) := 'TEST_TABLE';
  in_ids ids_t := ids_t(1, 2, 3);
begin
   str_sql := 'CREATE TABLE Final_Results AS (
                 SELECT a.client_id, a.benefit_id
                 FROM ' || in_tbl_nme || ' a
                 LEFT JOIN TABLE(CAST(:1 AS ids_t)) b on b.column_value = a.benefit_id
                 WHERE b.column_value is NOT NULL)';

   EXECUTE IMMEDIATE str_sql USING IN in_ids;
end;
/

Error report -
ORA-01027: bind variables not allowed for data definition operations

That error is described in this article.

So you need to create and populate the table in two steps:

declare
  str_sql varchar2(4000);
  in_tbl_nme varchar2(30) := 'TEST_TABLE';
  in_ids ids_t := ids_t(1, 2, 3);
begin
   str_sql := 'CREATE TABLE Final_Results AS
                 SELECT a.client_id, a.benefit_id
                 FROM ' || in_tbl_nme || ' a
                 WHERE 1=0'; -- or anything that always evaluates to false

   EXECUTE IMMEDIATE str_sql;

   str_sql := 'INSERT INTO Final_Results (client_id, benefit_id)
                 SELECT a.client_id, a.benefit_id
                 FROM ' || in_tbl_nme || ' a
                 LEFT JOIN TABLE(CAST(:1 AS ids_t)) b on b.column_value = a.benefit_id
                 WHERE b.column_value is NOT NULL';

   EXECUTE IMMEDIATE str_sql USING IN in_ids;
end;
/

PL/SQL procedure successfully completed.

select * from final_results;

 CLIENT_ID BENEFIT_ID
---------- ----------
         1          1

Creating a table on the fly isn't generally a good idea; aside from schema management and maintainability considerations, you have to be sure that only one session is calling the procedure and that the table doesn't already exist. If you have a process that does this work, uses the results and then drops the table then you still have to be sure it cannot be run simultaneously, and can be restarted if it fails part way through.

If all the work is done in the same session then you could create a (permanent) global temporary table instead, as a one-off schema set-up task. The insert to populate it would still have to be dynamic as in_table_nme isn't known, but it would be a bit of an improvement. (I'm not sure why your query in find_max_ids is dynamic though, unless you're also creating benefits dynamically). Or depending on the amount of data involved, you could use another collection type, instead of a table.

The data in a GTT is only visible to that session, and is destroyed when it ends. If that isn't appropriate then a normal table could be created once, which would be better than creating/dropping it dynamically. You still need to prevent multiple sessions running the process simultaneous in that case though, as they might not see the data they expect.

edited Apr 8, 2016 at 14:38

answered Apr 8, 2016 at 14:26

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2948208 Over a year ago

Thanks for your analysis and comments. Re the ORA-00933 error, it turned out there was another unknown error in my original code which caused that. I didn't reproduce that error when I posted my question. When I corrected my original code, you were correct in that the real error was ORA-01027: bind variables not allowed. The article you referenced explains the situation - thanks. I've taken your suggested code and modified it accordingly for my own purposes and it works fine. I note the comments re creating tables on the fly, and generally I agree but it's not an issue for me here.

StewS2 Over a year ago

I agree with Alex that it's probably a bad idea to create these tables on the fly. Maybe it'd be better to have a single table with a column for the source table name (with the values from your in_ids list), and the other two columns?

bjan Over a year ago

dynamic queries can have varying number of columns to be inserted or updated so can we have a dynamic list of bind values to be used with using clause, for example EXECUTE IMMEDIATE str_sql USING [a dynamic binded list of values];?

Collectives™ on Stack Overflow

PL/SQL Dynamic SQL USING clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related