Re: CLOBs in Dynamic Sql

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sat, 17 Feb 2001 18:16:02 -0800
Message-ID: <3A8F3061.2B80B9B7@exesolutions.com>

> Has anyone been able to get CLOBs working (in select list) using Dynamic Sql
> method 4 in Pro*C? Either Oracle or Ansi dynamic sql will work.

Yes.

> I have had no luck. The Oracle documentation said to use ANSI dynamic sql
> instead of Oracle dynamic sql because it supported LOBs. However, all its
> examples convert everything to VARCHAR which results in an inconsitent
> datatype error when you try to select a CLOB.

You can not run CLOBs directly in dynamic SQL. What you need to do is look at the DBMS_SQL and DBMS_LOB packages. If the CLOB is smaller than 32K you can use the DBMS_LOB.SUBSTR to move it to a LONG and then execute using Native Dynamic SQL. If it is larger than 32K then look at the PARSE method in DBMS_SQL where you can create a table (really an array) of 256byte VARCHARs and then execute the table.

Daniel A. Morgan Received on Sat Feb 17 2001 - 20:16:02 CST