I 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;
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?