Build Dynamic SQl in Oracle PL/SQL using Nested Tables

Question

Below is part of pl/sql code i am working on. I would like to generate dyamic sql which looks into the values in i_emp Nested Table and return employee names as result into CharArray. The below code is not working, need help in fixing the code

This is just sample code and should be done using dynamic sql

TYPE NestArray IS TABLE OF VARCHAR2(50);
TYPE CharArray IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;

PROCEDURE SAMPLE(i_emp IN NestArray)

v_emp_name CharArray;

v_sql := 'SELECT emp_name FROM emp ';
v_sql  := v_sql || ' WHERE empid IN (SELECT column_value FROM TABLE('||i_emp||'))';

EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_emp_name;

end;

Why does this need to be dynamic? Maybe if you explain how your data is configured we can provide you an even better solution. Right now there is no reason for this query to be dynamic. — Jorge Campos, Mar 07 '16 at 11:34
my code contains some conditions based on which sql will be generated similar to below IF i_emp_flg = 'Y' THEN v_sql := v_sql || 'AND emp_address = '''||i_emp_address||''''; ELSIF i_emp_flg = 'N' THEN v_sql := v_sql || 'AND emp_address IS NULL '; — Harish, Mar 07 '16 at 11:40
1. You can't access package defined types inside SQL. You must define them globally or at schema level 2. The SQL in your procedure doesn't know the content of the parameter `i_emp`. The Query compiler sees only `... WHERE empid IN (SELECT column_value FROM TABLE('NestArray'))...`. This is the typename, not the parameter neither the values — Recoil, Mar 07 '16 at 11:59
Possible duplicate of [How to load a large number of strings to match with oracle database?](http://stackoverflow.com/questions/34699223/how-to-load-a-large-number-of-strings-to-match-with-oracle-database) — MT0, Mar 07 '16 at 12:37

score 0 · Answer 1 · answered Mar 07 '16 at 12:02

Please see below snippet. It will describe the solution. Hope it helps

--Create schema level type object 

CREATE OR REPLACE TYPE NestArray IS TABLE OF VARCHAR2(50);

CREATE OR REPLACE PROCEDURE EMP_BULK(p_empni IN NestArray)
AS
TYPE CharArray
IS
  TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
  v_emp_name CharArray;
--  emp_id NestArray:=NestArray(1,2,3);
--  v_sql LONG;
BEGIN
  SELECT ename BULK COLLECT
  INTO v_emp_name
  FROM emp
  WHERE EMPno IN
    (SELECT COLUMN_VALUE FROM TABLE(p_empni)
    );
END;

EXEC EMP_BULK(NestArray(1,2,3));


----------------------------OUTPUT-------------------------------------------

PL/SQL procedure successfully completed.

score 0 · Answer 2 · answered Mar 07 '16 at 12:35

You do not need dynamic SQL and the nested select can be replaced with the MEMBER operator. You can do it like this:

CREATE TYPE NestArray IS TABLE OF VARCHAR2(50);
CREATE TYPE CharArray IS TABLE OF VARCHAR2(255);


CREATE OR REPLACE PROCEDURE SAMPLE(
 i_emp   IN  NestArray,
 o_names OUT Chararray
)
IS
BEGIN
  SELECT emp_name
  BULK COLLECT INTO o_names
  FROM   emp
  WHERE  empid MEMBER OF i_emp;
END;
/

Build Dynamic SQl in Oracle PL/SQL using Nested Tables

2 Answers2