Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL -- help needed
On Wed, 21 Jan 1998 18:41:31 -0500, "Michael Will de Laforcade" <mwill_at_dris.com> wrote:
>I have a situation where I need to dynamically create tables with 800 +
>columns from within PL/Sql.
>
>I am running into the varchar2 limit of 400 chars max.
>
>I tried submitting the create statement in chunks via multiple calls to
>dbms_sql.parse, but get errors.
>
>Is there a way to get this to work??
>
>Thanks in advance for all suggestions.
>
> Michael..
>
>
>
>
>
You do not mention your database version.
In any case, if you are using Oracle7.x you are limited to 254 columns/table so 800 won't work.
If you are using 7.3 of Oracle, then there are 2 dbms_sql.parse calls. One takes a single varchar, the other takes an array of varchars. the array of varchars lets you break the statement up into a whole bunch of smaller strings that will be glued back together for execution -- this allows you to pass in very large (>32k) sql statements in one call. this was first available with 7.3.
If you are using 8.0 of Oracle then use the dbms_sql call with an array of varchar2 and you can create a table with upto 1,000 columns.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 22 1998 - 00:00:00 CST