1 Reply Latest reply: Apr 15, 2011 11:03 PM by 854578 RSS

    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