Dynamic SQL Question - Bind Variables

From: Justin Wigg <justin_at_icsmultimedia.com.au>
Date: Fri, 21 Feb 2003 17:26:01 +1100
Message-ID: <b34gti$1i09l0$1_at_ID-71863.news.dfncis.de>

[Quoted] Hi all. I have a question regarding dynamic SQL and in particular, the use of bind variables within it. (Running a SQL*Plus 8.1.7.0.0 client that connects to a 8.1.6.0.0 database running on Windows2000 Server.)

The basic questions I'm asking are:

[Quoted]
1. Can you dynamically construct a column name in a dynamic SQL statement using bind variables?
2. Can you pass the table name into a dynamic SQL statement via bind variables?

Here are some more details. I have a situation where I have a PL/SQL function that might need to perform a select on one of many columns from one of several tables, none of which will be known until execution time. There is a large series of IF statements based on parameters passed to the function which leads me to want to use bind variables for the SQL statement instead of concatenating values (which would mean I would have to constantly re-define the basic SQL structure.)

The way I am trying to do it is by having a static SQL statement with several bind variables within it to formulate the column name and table name. The column name format for the column name is static with a single digit being appended to the end, and the table name is being passed as a whole string.

However, I am suffering two problems. Firstly, the column name is not being accepted as valid by the EXECUTE IMMEDIATE statement. Secondly, despite the fact that I know that the table name being passed in is correct, I keep getting an "invalid table name" error. Details below... (Note that for the following test, I am trying to dynamically select the column "seg_id1" from the table "gl_grps_org". See further below for the basic table structure...)

The PL/SQL code I have written is of the following basic structure (however the real case is MUCH more complicated - I have simplified this greatly):

DECLARE

   v_sql       VARCHAR2(1000);
   v_id        NUMBER := 1;
   v_value     VARCHAR2(20);
   v_value2    VARCHAR2(20);
   v_result    NUMBER;

BEGIN

   v_value := TO_CHAR(v_id);
   v_value2 := 'gl_grps_org';
   v_sql := 'SELECT seg_id:1 FROM :2 WHERE category IS NOT NULL';

   EXECUTE IMMEDIATE v_sql INTO v_result USING v_value, v_value2;    DBMS_OUTPUT.PUT_LINE('The result is: '||v_result); END; The first problem is that the first bind variable returns a "ORA-00923: FROM keyword not found where expected" error. Is it not possible to dynamically construct a column name this way?

Once you remove this problem from the equation (by hardcoding the column name into the query), the second bind variable (for the table name) causes a "ORA-00903: invalid table name" error. Is it not possible to pass in the table name via a bind variable? I can't believe that would be the case.

The basic table structure for the table mentioned is as follows:

GL_GRPS_ORG Name Null? Type

---------------- -------- --------------
CATEGORY                  VARCHAR2(10)
SEG_ID1                   NUMBER

I would GREATLY appreciate any help the group could give me. Many thanks in advance...

-- 
SQL> select * from users  | Justin Wigg  -   Hobart, AUSTRALIA
     where clue > 0;      | http://www.icsmultimedia.com.au
     no rows selected     | Reply: justin_at_icsmultimedia.com.au

Received on Fri Feb 21 2003 - 07:26:01 CET