Re: Dynamic SQL Question - Bind Variables

justin_at_icsmultimedia.com.au said...
> 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:
>
> 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...
>
You might want to search http://asktom.oracle.com for your answer. You have to use the dbms_sql package to use bind variables for column names. There are lots of examples at his site to do what you want ... at least most of it.

-- 
/Karsten
DBA > retired > DBA