Dynamic sql - binding association arrays and binding a portion of the AND
854578Apr 12 2011 — edited Apr 16 2011I have two questions regarding dynamic sql using oracle 11g. My understanding is that using binding variables is both faster and helps prevent sql injection.
1st I want to pass in two assocation arrays - (these are just samples as my actual procedure is pretty large).
TYPE t_array_one IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE t_array_two IS TABLE OF VARCHAR2(75) INDEX BY BINARY_INTEGER;
PROCEDURE SomeProcedure(
p_array_one IN t_array_one,
p_array_two IN t_array_two
I can create the dynamic sql I need, looping through these arrays, however i do not know how many bind variables I have until run time - so I am unsure what to use for the using clause.
somerefcursor is a SYS_REFCURSOR, and sql_stmt is my dynamic sql variable, complete with bind variables.
Given the above what should be included in the using clause below?
OPEN somerefcur FOR sql_stmt USING ?????
2nd I am using a filter of sorts on the client side - this would mean a portion of the where clause is created by the user
so that they would choose a column_name, logical operator and a comparsion value.
An example would look like this, state = 'VA'.
A portion of my dynamic sql where clause would become sql_stmt := sql_stmt || 'AND : x'; -- additional space between colon and x as if I do not add this here - the display is showing as an emoticon.
The using clause for this bind variable should then become state='VA' to replace : x, however, nothing in any form or fashion I have tried seems to allow this.
Any suggestions, and or examples anywhere, that may help me resolve these issues?
Thank You,
Keith
Edited by: user10651723 on Apr 11, 2011 10:38 PM