Dynamic sql - binding association arrays and binding a portion of the AND

854578

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;

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

854578

I am marking this as answered and moving to another forum area - as I think i posted this in the incorrect place - Thank You.