Hi,
I'm trying to build dynamic SQL string, in which I'm using Nested Table as one of the tables. Following is an excerpt:
Step-1: CREATE TYPE sample_rec AS OBJECT (field_id NUMBER, field_name VARCHAR2(100));
Step-2: CREATE TYPE coll_of_sample_rec IS TABLE OF sample_rec;
Step-3: PLSQL Block:
v_my_table_coll coll_of_sample_rec;
-----
g_sql_str :=
'SELECT a.field_id, a.field_name, b.hex_value
FROM TABLE(my_table_coll) a, mapping_values b
WHERE a.field_id = b.map_field_id';
----
Step-4: 1st I tried using the simple EXECUTE IMMEDIATE method like-
EXECUTE IMMEDIATE g_sql_str BULK COLLECT INTO v_some_other_coll;
[Above query fails with ERROR message, "MY_TABLE_COLL" is not a valid identifier"]
Step-5: 2nd time I tried use the bind method with EXECUTE IMMEDIATE method like-
-----
g_sql_str :=
'SELECT a.field_id, a.field_name, b.hex_value
FROM TABLE(:my_table_coll) a, mapping_values b
WHERE a.field_id = b.map_field_id';
----
EXECUTE IMMEDIATE g_sql_str BULK COLLECT INTO v_some_other_coll using my_table_coll;
[Above query fails with ERROR message, "incorrect datatype ....."]
Step-6: 3rd time I made use of REFCURSOR like-
v_refcur sys_refcursor;
-----
g_sql_str :=
'SELECT a.field_id, a.field_name, b.hex_value
FROM TABLE(my_table_coll) a, mapping_values b
WHERE a.field_id = b.map_field_id';
----
OPEN v_refcur FOR g_sql_str;
FETCH ....
[Above query fails with ERROR message, "not implemented...."]
So is there a way that I can make use of the NESTED TABLE in a dynamic SQL; or do I have to wait for an Oracle feature enhancement?
Hello,
As long as you are using only nested table types defined in the database (using CREATE TYPE),
there is no problem to pass those dynamically to SQL, either using EXECUTE IMMEDIATE or OPEN FOR ....
You should only take care of the variable types and pass a variable of the correct data type in the USING clause,
like in the following:
DECLARE
v_my_table_coll coll_of_sample_rec;
g_sql_str VARCHAR2(32000);
TYPE out_rec_t is RECORD ( field_id NUMBER, field_name VARCHAR2(100), hex_value VARCHAR2(100) );
TYPE out_rec_tab_t is TABLE OF out_rec_t ;
v_some_other_coll out_rec_tab_t ;
BEGIN
g_sql_str :=
'SELECT a.field_id, a.field_name, b.hex_value
FROM TABLE(:my_table_coll) a, mapping_values b
WHERE a.field_id = b.map_field_id';
-- populate the bind variable
v_my_table_coll := coll_of_sample_rec ( sample_rec(1,'A'), sample_rec(2,'B') );
-- pass the bind variable to the dynamic SQL
EXECUTE IMMEDIATE g_sql_str
BULK COLLECT INTO v_some_other_coll USING v_my_table_coll;
DBMS_OUTPUT.put_line ('Count of rows selected='||v_some_other_coll.COUNT);
END;
/
That is, ":my_table_coll" is just a bind variable NAME, which has no significance by itself, it is only a placeholder,
and only its position in the SQL statement matters, if you have several bind variables.
Instead, what you use in the USING clause should be a variable name of the proper data type,
like "v_my_table_coll" above, which you should populate BEFORE executing the statement.
The rules for OPEN ... USING ... are similar to the above.
The "feature enhancement" that you are talking about probably refers to the fact that in Oracle12c
you can also bind collection types that are defined in a package specification, in addition to those defined
in the database (at schema level).
Hope that this will help.