Tags: bind, business, column, database, inspecting, lengthy, mysql, oracle, procedure, row, scans, sql, stored, string, table, variables, wich, written

Bind variables in PL/SQL read column name as string

On Database » Oracle

3,115 words with 3 Comments; publish: Fri, 08 Feb 2008 19:14:00 GMT; (25062.50, « »)

I have written a stored procedure wich scans through a table, row by row, then inspecting each row column by column. (A lengthy business.)

The outer loop picks up the rowid so I can use it in the inner loop. The inner loop looks something like this:

FOR nIndx IN 1 .. LastCol LOOP

EXECUTE IMMEDIATE 'select vsize( :ColName ) FROM MyTable WHERE rowid = :TheRowid'

INTO nSizeHolder

USING IN szColName, IN MyRowId;

.

. (Now I process szColName

.

END LOOP;

(szColName is, in fact, the contents of a PL/SQL table, referenced by nIndx.) As stated, if the column name is 'SPTYPE' (for example), the dynamic query will always return 6 (i.e. LENGTH( 'SPTYPE' ). If I change the EXECUTE IMMEDIATE to:

'select vsize ( ' || szColName || ' ) FROM ...'

it correctly gives a varying value, depending on the contents of the column SPTYPE. (I'm using SPTYPE as an example: it happens on all columns.)

So, while I can get it to work, failing to use the bind variables properly will hammer the Shared Pool.

I re-cast the code in DBMS_SQL equivalent and had exactly the same problem. (This is more drastic: DBMS_SQL could be more efficient because I could do most of the dynamic work once per row, outside the inner query; however, I can't because of this bind problem.)

I must be missing a trick somewhere. (The asnwer will probably be RTFM!) Anyone any ideas?

All Comments

Leave a comment...

  • 3 Comments
    • Hi,

      This Cannot be done.. U Cannot use bind variables for Column Names and Tablenames.

      Reason:

      During the Parse Stage We need to know what Table and Columns u'r Trying to get and if they are there or not and u have proper access rights to fetch the same.

      So u cannot parse the Stmt usiing Bind Variables bcos We will not what column u'r trying to use there.

      U Need to Use The concate Operator to add these values into the Select String.

      HTH

      regards,

      Ganesh R

      #1; Thu, 21 Feb 2008 21:51:00 GMT
    • hi

      i had the same problem i solve this problem as i transform the SELECT query into dinamic string with out USING statment and declaring the INTO variable as global in the package specification and referencing the INTO variable with in dinamic string as NAME_OF_PACKAGE.NAME_OF_VARIABLE and using concatenations to insert the values of COL_NAME and ROW_ID in dinamic query :

      FOR nIndx IN 1 .. LastCol LOOP

      EXECUTE IMMEDIATE 'SELECT vsize( '||szColName||' )'||

      ' INTO PACKAGE_NAME.nSizeHolder '||

      ' FROM MyTable WHERE rowid = '||''''||TheRowId||''''||

      .

      . (Now I process szColName

      .

      END LOOP;

      I hope this whil be helpful for you.

      #2; Thu, 21 Feb 2008 21:52:00 GMT
    • Thanks for the replies.

      Yes, I could get the concatenation but I was trying to keep the hit on the Shared Pool (and performance) down.

      I was affraid the answer would be "You can't do it!" - I was just hoping I was missing something. Incidentally, you _can_ use a bind variable for the rowid, which makes it a bit more efficient.

      Yhanks again.

      #3; Thu, 21 Feb 2008 21:53:00 GMT