Re: Pro*C Prepared statements using CURRVAL/NEXTVAL

> I believe Pro*C still supports the AT :db_name syntax.
> Shouldn't that allow you to use static sql instead of dynamic sql?
>
> Using dynamic sql all the time will also severely limit scalability.
>
> Regards,
>
> Sybrand Bakker, Senior Oracle DBA

Thank you for the reply. However, let me provide a bit more background. We are porting Informix to Oracle. Informix allows multiple databases within one server, so we have myDB1 with some tables, myDB2 with the same tables, etc. all under one server. As we port, we are taking the database names and appending them to the Oracle table names, thus myDB1.tablexyz and myDB2.tablexyz all in one Oracle database. I need to create a dynamic query to allow change to the prefix of the table names. Also, why would a prepared statement always return 0, whereas the static "hardcoded" query return the correct result?

We have found that, at least in ESQLC, preparing all statements once provides for enhanced performance, so we have gone with that approach. Also, it allows for dynamic switching amongst apps and databases (legacy in Informix, table prefix names now in Oracle), etc. Static SQL does not support the dynamic nature we need to support our multi-company, multi-distributed data without alot of if-then-elses to support pseudo-dynamic static SQL. Received on Tue Sep 18 2001 - 11:25:40 CDT