Dynamic Sql (DBMS_SQL package) Question

From: Prabakar <prabakar_at_aol.com>
Date: 21 Jul 1998 11:12:45 GMT
Message-ID: <1998072111124501.HAA05911@ladder03.news.aol.com>

I have a procedure that uses dynamic sql. It compiles kay, byt when I run it it produces 2 error messages. THe procedure receives 4 parameters, two of them are names of the fields in a table and two other parameters are values for a column in the table. All 4 parameters are used in the WHERE clause. THe query does a self join(join involving the same table). I am pasting a part of the code where I think the problem occurs:

  DBMS_SQL.PARSE(source_cursor,

       'SELECT t1.sourcesysid, t2.sourcesysid, t1.z002legacyno,t2.z002legacyno,
        t1.materialgroup, t2.materialgroup FROM conmaterialbasic_pr t1,        
        conmaterialbasic_pr t2 
        WHERE t1.:y1=t2.:y2 AND t1.sourcesysid=:x1 and                         

t2.sourcesysid=:x2',DBMS_SQL.V7);

  DBMS_SQL.BIND_VARIABLE(source_cursor, ':x1', source1);        
  DBMS_SQL.BIND_VARIABLE(source_cursor, ':x2', source2);        
  DBMS_SQL.BIND_VARIABLE(source_cursor, ':y1', field1);        
  DBMS_SQL.BIND_VARIABLE(source_cursor, ':y2', field2);        

The error message I get are:
1. IF i remove < t1.:y1=t2.:y2> from the sql statement and substitute actual fieldname eg. t.GIN=t2.PCN it works fine, else it gives

      Invalid owner.tablename.column or table.column or column reference

2. The second error comes when I introduce bind variable at <t1.sourcesysid=:x1>
If I substitute actual value instead of :x1 it works fine or even if I remove this it owrks fine whereas with status quo it produces following error message.

     Bind variable not found

I would really appreciate any help on this.

Thank you.

Regards
Prabakar Received on Tue Jul 21 1998 - 06:12:45 CDT