passing parameters from PL/SQL to dynamic SQL statement

Could not find a way either in a package or otherwise to do a simple dynamic sql via an array.


declare
--l_deptnos varchar2(100) := 'I0157967';
l_rc sys_refcursor;
l_dept_rec fatckin%rowtype;

TYPE assoc_array_str_type2
IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

v5 assoc_array_str_type2;

begin

v5(1) := 'I0157967';
v5(2) := 'I0157928';

--open l_rc for 'Select fatckin_vend_full_name, sum(fatckin_net_amt) as amt from fatckin where fatckin_invh_code = (' || l_deptnos || ') group by fatckin_vend_full_name';

open l_rc for 'Select fatckin_vend_full_name, sum(fatckin_net_amt) as amt from fatckin where fatckin_invh_code in (' || v5() || ') group by fatckin_vend_full_name';

loop
fetch l_rc into l_dept_rec;
exit when l_rc%notfound;
dbms_output.put_line( l_dept_rec.fatckin_vend_full_name );
end loop;

close l_rc;

end;

Comments for passing parameters from PL/SQL to dynamic SQL statement

Average Rating starstarstarstarstar

Click here to add your own comments

May 12, 2016
Rating
starstarstarstarstar

by: Anonymous

The short answer is that you can't do that. The problem is that PL/SQL objects are stored in the PGA (Program Global Area) which is session specific whreas SQL uses the SGA (System Global Area) and you can't bin a whole PL/SQL array - only specific cells.

The good news though is that you can do this by using a SQL nested table type which is declared at the schema level and can be written-to and read-from in both PL/SQL and SQL.

Using Oracle's example HR schema we can declare a nested table type like so:-
create or replace type job_title_type as table of varchar2(35);

Having declared the type,in th ePL/SQL program we just need to declare a variable of that type to be able to use it. This first example shows one method of populating the nested table (we could also select into it from the database inclduing by use of BULK COLLECT


declare

rc sys_refcursor;
job_rec varchar2(35);

job_titles job_title_type;

begin

job_titles := job_title_type('Sales Manager','Stock Clerk');

open rc for select * from table(job_titles);

loop
fetch rc into job_rec;
exit when rc%notfound;
dbms_output.put_line( job_rec);
end loop;

close rc;

end;


This produces the output:
Sales Manager
Stock Clerk


This doesn't quite do what was asked though, so let's extend the example to match the original issue.

declare

rc sys_refcursor;
job_rec jobs%rowtype;

job_titles job_title_type;

begin

job_titles := job_title_type('Sales Manager','Stock Clerk');

open rc for select * from jobs where job_title in (select * from table(job_titles));

loop
fetch rc into job_rec;
exit when rc%notfound;
dbms_output.put_line( 'Job Id: '||job_rec.job_id||' Job Title: '||job_rec.job_title||' Min Sal: '||job_rec.min_salary||' Max Sal: '||job_rec.max_salary);
end loop;

close rc;

end;


This produces the following output:
Job Id: SA_MAN Job Title: Sales Manager Min Sal: 10000 Max Sal: 20080
Job Id: ST_CLERK Job Title: Stock Clerk Min Sal: 2008 Max Sal: 5000


For more details on ref cursors see http://www.asktheoracle.net/what-is-ref-cursor.html.

And here for more another example of using nested tables.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.