Re: Parameterized CREATE TABLE in Oracle from VFP using ODBC????

A copy of this was sent to "New Delhi" <rajesh_at_simulate.com> (if that email address didn't require changing) On Wed, 2 Sep 1998 17:59:18 -0400, you wrote:

I don't know why its getting the ora-1036, it should be getting ora-1027

$ oerr ora 1027
01027, 00000, "bind variables not allowed for data definition operations"

// *Cause:  An attempt was made to use a bind variable in a SQL data definition
//          operation.
// *Action:  Such bind variables are not allowed.

I believe you are going to have to do this in 2 steps. If you issue:

create table test as select to_char( 0, '9999' ) as row1, d0055.* from d0055 WHERE 1 = 0 you'll get the table created and then execute

Insert into Test Select TO_CHAR(?m.Param , '9999') , d0055.* From d0055

To test that bind variables are not supported in DDL you can run the small script:

• eof ----------------------------------------------- variable n number; exec :n := 0; create table t as select to_char(:n,'9999'), d.* from dual d;
• eof -----------------------------------------------

in sqlplus -- it'll give you the ORA-1027.

>Hi,
>I am trying to send a query from Visual Foxpro to Oracle using ODBC that
>looks like follows:
>
> SQLEXEC(g_connect_handle , "create table test as Select TO_CHAR
> ?m.Param , '9999') as Row1 , d0055.* From d0055")
>
>
>So what i want to achieve is that Oracle create a table for me where one
> or more ) of the fields is parameterized.
>
>I keep getting an error :
>
>
> [S1000] [Oracle][ODBC][Ora]ORA-01036: illegal variable name/number
>(1036)
>
>
>I am tracing the calls made by ODBC and it fails on SQLEXECDIRECT :
>
>It shows me the query as follows
>"create table test as Select TO_CHAR(? , '9999') as Row1 , d0055.* From
>d0055"
>
>
>before this, it successfully makes a call to SQLBindParameter()
>
>To indicate the type of field to create, I have included the TO_CHAR
>function but Oracle does not seem to be intelligent enough. Does using a
>particular ODBC driver make a difference? I have tried with Oracle's own
>driver and Microsoft's . Both have the same issue
>
>
>
>The same thing works if I try to do it to an MS Access database by doing
>"Select LTrim ( m.Param) as Row1 , d0055.* From d0055 into Table test".
>Access creates a new table where Row1 is of type string with 255 field
>width.
>
>
>
>
>The following however seems to work "Insert into Test Select
>TO_CHAR(?m.Param , '9999') , d0055.* From d0055" with Oracle. So i have a
>feeling that somehow in "create table" query it HAS TO ABSOLUTELY kNOW THE
>TYPE OF EACH FIELD IN THE TABLE. But I would like it if i could create the
>table in the same step rather than first creating and then inserting.
>
>
>any help will be appreciated
>
>Thanks
>
>rajesh
>
>rajesh_at_simulate.com
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
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.