Table substitution with PRO*C Dynamic SQL

From: Joe Fulson-Woytek <joefw_at_eosdev1.gsfc.nasa.gov>
Date: 7 Jun 1993 19:52:30 GMT
Message-ID: <1v069u$jv1_at_skates.gsfc.nasa.gov>


I am trying my hand at dynamic sql using PRO*C. I was able to basically duplicate the example in the manual where I substitute a value into a where statement and do a select. But what I really want to do is substitute the table name in the select statement. The manual implies this is doable, but I was unable to find an example, so I struck out on my own, and struck out. I wrote the following code:

/**********************************************************************/
EXEC SQL INCLUDE sqlca.h;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR sqlstmt[80];
VARCHAR username[30];
VARCHAR table[30];
EXEC SQL END DECLARE SECTION;
main()
{

   table.len = sprintf ( table.arr, "PERSON" ) ;    EXEC SQL WHENEVER SQLERROR GOTO errexit; /* do the SQL CONNECT */

        :
   sqlstmt.len = sprintf

        ( sqlstmt.arr, "SELECT USERNAME FROM :v1 WHERE PERSON_ID = 430");    EXEC SQL PREPARE S FROM :sqlstmt;
   EXEC SQL DECLARE C CURSOR FOR S;
   EXEC SQL OPEN C USING :table;

   EXEC SQL FETCH C INTO :username;
/* rest of code */

        :
/**********************************************************************/

I get the error: ORA-00903: invalid table name which seems to indicate the substitution is not working. The straight sql statement: SELECT USERNAME FROM PERSON WHERE PERSON_ID = 430 works fine.

Any ideas/suggestions would be appreciated.

(Oracle V6 on SGI )

Joe Fulson-Woytek
Code 902/Goddard DAAC
NASA/Goddard Space Flight Center
joefw_at_eosdev1.gsfc.nasa.gov   Received on Mon Jun 07 1993 - 21:52:30 CEST

Original text of this message