Re: ProC

In article <3ks3ag$7r4_at_eccdb1.pms.ford.com> alal1_at_PROBLEM_WITH_INEWS_GATEWAY_FILE (Anuj Lal) writes:

> >I have a problem about how to pass a value (string) into a host variable
> >( :name_of_table and :name_of_attribute) in a SQL (listed below)
> >
> > EXEC SQL SELECT *
> > FROM :name_of_table
> > WHERE :name_of_attribute;
> >
> >
> >Any suggestions/comments will be very useful for myself !!
> >
> >Thanks in advanced,
> >
> >yhwang_at_fusion.site.gmu.edu
>
>
> EXEC SQL BEGIN DECLARE SECTION
> VARCHAR name_of_table[15];
> VARCHAR name_of_attribute[15];
> EXEC SQL END DECLARE SECTION;
>
> /* get table name and atrribute name & then strcpy it */
>
> strcpy(name_of_table.arr,arraywhereyoustoredtable);
> strcpy(name_of_attribute.arr,arraywhereyoustoredattribute);
> name_of_table.len = strlen(name_of_table.arr);
> name_of_attribute = strlen(name_of_attribute.arr);
>
> EXEC SQL SELECT *
> FROM :name_of_table
> where attributa = :name_of_attribute ;

This definitely will not work. Using a bind variable as table name is illegal in Pro*C. In fact, I am almost certain that it is illegal across all Oracle products. The better approach is to use dynamic SQL:     EXEC SQL BEGIN DECLARE SECTION;
    char sql_stmt[1000];
    EXEC SQL END DECLARE SECTION;     sprintf(sql_stmt, "SELECT %s FROM %s", attribute_name, name_of_table);     EXEC SQL PREPARE my_sql_stmt FROM :sql_stmt;     EXEC SQL EXECUTE/FETCH..... For more information, please refer to the dynamic SQL section of the Precompiler manuals.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel