Re: Dynamic SQL doesn't do tables??

Eric_Karabin_at_msn.com (Eric Karabin) wrote:

>I am using Pro*C 2.1 with Dynamic SQL Method 3 because I want to be
>able to name the table in my program.
 

>ALL of the examples in the Pro*C book only use variables after the
>WHERE statement. How do you use variables after the FROM statement?
>The book states that the tables can be changed as well.

>Example:
 

> SELECT name, file_name, title FROM tablename WHERE UPPER(name) like :v1
 

>The above example works fine. I just replace the :v1 later.

>So how do I do the following?:
 

> SELECT name, file_name, title FROM :v1 WHERE UPPER(name) like 'A%'
 

>This always fails, telling me 'invalid table name'. I tried using
>char and VARCHAR variables for it but neither work.

>Eric

Database objects like column names, table names, etc cannot be bind variables. Think of a dynamic sql statement like a subroutine. The only thing you can change in a subroutine once it is compiled is the input to it-- not the code within it. A dynamically prepared sql statement is compiled into an access plan, changing the tablename would change the plan. Changing bind variables would not.

The following subroutine shows the correct sequence of events in a pro*c application to achieve what you want:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;

	VARCHAR		stmt[500];
	int			n;
	VARCHAR 	x[10];
EXEC SQL END DECLARE SECTION;
	char * 		tname;

	strcpy( x.arr, "A%" );
	x.len = strlen( x.arr );


	tname = "ALL_CATALOG";
	sprintf( stmt.arr, 
		"select count(*) from %s where table_name like :x", tname );
	stmt.len = strlen( stmt.arr );

	EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
	EXEC SQL PREPARE S FROM :stmt;
	EXEC SQL DECLARE C CURSOR FOR S;
	EXEC SQL OPEN C USING :x;
	EXEC SQL FETCH C INTO :n;
	printf( "Table %s has %d matching rows\n", tname, n );

	tname = "ALL_CONSTRAINTS";
	sprintf( stmt.arr, 
		"select count(*) from %s where table_name like :x", tname );
	stmt.len = strlen( stmt.arr );

	EXEC SQL PREPARE S FROM :stmt;
	EXEC SQL OPEN C USING :x;
	EXEC SQL FETCH C INTO :n;
	printf( "Table %s has %d matching rows\n", tname, n );

	EXEC SQL CLOSE C;

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Oct 13 1995 - 00:00:00 CET