OraFAQ Forum: Server Administration » ORA-00997: illegal use of LONG datatype

Home » RDBMS Server » Server Administration » ORA-00997: illegal use of LONG datatype

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00997: illegal use of LONG datatype [message #148601]

Fri, 25 November 2005 04:47

raddisonlee
Messages: 29
Registered: March 2005

Junior Member

HI ,EVERYBOY !
I WANT TO USE THAT COMMAND "CREATE TABLE AS SELECT * FROM XX@SYBASE"

NOTE: @SYBASE IS A REMOTE SERVER FROM SYBASE 11.5

IS SURE THAT: DESC XX@SYBASE CAN WORK .

BUT TABLE XX WITH LONG DATATYPE COLUMN. SO IT OCCUR THE ERROR:

ORA-00997: illegal use of LONG datatype

MY ENV: WINDOWS2K/ORACLE9I

HELP !!!!! THANKS!

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #148652 is a reply to message #148601]

Fri, 25 November 2005 15:45

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi,

As per oracle documentation
===============================================================
ORA-00997: illegal use of LONG datatype

Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

Action: Remove the LONG value from the function or clause
===============================================================

Regards
Always Friend Sunilkumar

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #148700 is a reply to message #148601]

Sat, 26 November 2005 13:21

dbaxchangedba
Messages: 26
Registered: November 2005

Junior Member

LONG datatypes cannot be used for Insert with selects or CTAS (create table as select). There is a workaround for it and you can find it here:

http://www.dbaxchange.com/3_4_longdatatypes.htm

Hope this helps!

http://www.dbaxchange.com
(One stop site for practical solutions to oracle issues, oracle findings, Book recommendations, International job search site recommendations and much more....)

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #148838 is a reply to message #148601]

Mon, 28 November 2005 09:24

raddisonlee
Messages: 29
Registered: March 2005

Junior Member

sorry ,i cannot view that web ,
anyway ,i just want to migrate tables and it's data from sybase 11.5 to oracle9i .
otherwise , it is other way to achieve create the same table structure as exist table from sybase ??? how can i overcome the changes of datatype between the tow ???? thanks

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #148883 is a reply to message #148838]

Mon, 28 November 2005 18:00

dbaxchangedba
Messages: 26
Registered: November 2005

Junior Member

-- There is an oracle migration utility software available but I'm not sure if it will allow you to migrate from sybase. I've used it to migrate from SQL*server. Check it out at oracle's technet site.

-- The weblink that I had sent to you earlier basically explained the usage of COPY command which can be used to copy long datatype columns. Here what it said:

The ORA-00997 error is due to a know restriction where you cannot do an insert with select or Create table as select involving tables with LONG datatypes. We can however do the data transfer using the COPY command. Here's an example:

-- You can set long to any large value as long as it is larger than the max length of any -- long column in the table. Exception is that sqlplus commands cannot handle a long
-- greater than 64k. The long column will always be truncated at 64k.
set long 2000000000
-- commit after one batch of recordS
set copycommit 1
-- 100 records per batch
set arraysize 100

-- copy is a SQL*Plus command and needs the continuation character "-" for multiple lines.

SQL> copy from REMOTE_USER/REMOTE_USER_PASSWORD@remotedb -
insert testtab using select * from testtab

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #148933 is a reply to message #148601]

Tue, 29 November 2005 02:38

raddisonlee
Messages: 29
Registered: March 2005

Junior Member

appreciate your reply !!!!

and it seems as if i will fail to achive the migrate by the copy command between two different databases !!!

me do it :
COPY FROM sa/yuanzu@test -insert a using select * from F_ORGAN@TEST;

result as errors:

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db> : database string, e.g., scott/tiger@d:chicago-mktg
<opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

note :desc F_ORGAN@TEST work !!
what can i do ?????

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #150475 is a reply to message #148933]

Thu, 08 December 2005 01:25

dbaxchangedba
Messages: 26
Registered: November 2005

Junior Member

Not sure if you got this to work but it looks like your syntax for the COPY command needs a few changes. It needs to be like this:

SQL>COPY FROM sa/yuanzu@test insert a using select * from F_ORGAN

I'm assuming that the table that you are inserting in to is called "a" and the remote table that you are selecting from is F_ORGAN and "test" is actually an entry in your tnsnames.ora file that points to the remote database?

Please be aware that COPY does not use database links but instead performs a login in to the destination database and runs the query.

http://www.dbaxchange.com

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #150499 is a reply to message #150475]

Thu, 08 December 2005 03:33

raddisonlee
Messages: 29
Registered: March 2005

Junior Member

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> desc F_ORGAN@test
Name Null? Type
----------------------------------------- -------- ---------------------

ORGAN_ID NOT NULL CHAR(40)
ORGAN_NAME CHAR(60)
ORGAN_TYPE CHAR(40)
ORGAN_GRADE CHAR(10)
ORGAN_HIGH CHAR(40)
ORGAN_LEADER CHAR(10)
ORGAN_OFFICE CHAR(80)
ORGAN_TEL CHAR(20)
ORGAN_DESC CHAR(100)
ORGAN_IFHIGH CHAR(1)
ORGAN_BGNDATE DATE
ORGAN_ENDDATE DATE
ORGAN_BGNFILE CHAR(80)
ORGAN_ENDFILE CHAR(80)
ORGAN_REGION VARCHAR2(40)
ORGAN_RESPCNTR CHAR(1)

SQL> COPY FROM sa/yuanzu@test insert a using select * from F_ORGAN

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-12154: TNS:could not resolve service name
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

above list when i run that sql again.
i think that @test is a transperant gateway. not login actually .

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #150625 is a reply to message #150499]

Thu, 08 December 2005 14:09

dbaxchangedba
Messages: 26
Registered: November 2005

Junior Member

You can maybe try and use the transparent gateway database link by having COPY login to the oracle database and querying remotely. Try this :

COPY FROM <oracle username>/<oracle Password>@oracledbname insert a using select * from F_ORGAN@test

Use your oracle login, password and the oracle database name in the FROM clause above (should be the owner of table a). Also, make sure that the database name or alias that you use as part of @oracledbname is included within the tnsnames.ora file.

On a side note, COPY is a single line command within SQL*PLUS and so type the above command without carriage returns but if you do want to put in carriage returns then use - at the end of the line. For example:

COPY FROM <oracle username>/<oracle Password>@oracledbname -insert a using select * from F_ORGAN@test

http://www.dbaxchange.com

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #203582 is a reply to message #148601]

Wed, 15 November 2006 10:29

cleopard
Messages: 10
Registered: June 2005

Junior Member

I'm having trouble getting the syntax right using the COPY option to get around the LONG problem. Here's the SQL statement as I was running it in PL/SQL Developer:

insert into l_format_table
(
select * from l_format_table@otherdb.com
where format_id = 122
)

At the SQL*Plus command line, I tried:

copy from user/pass@otherdb.com insert into l_format_table select * from l_format_table where format_id = 122

but it gave me SP2-0498: missing parenthetical column list or USING keyword, whether I used parens or not.

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #203588 is a reply to message #203582]

Wed, 15 November 2006 12:04

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

The sqlplus copy command is pretty easy -- you don't mention what "without any luck" means but if you can:

- sqlplus user/password into the local database AND
- sqlplus user/password@REMOTE into the remote database...

You can sqlplus copy the data from T1( x, y, z ) (located on REMOTE) into
T2(x,y,z) (local) using:

SQL> copy from user/password@REMOTE insert t2(x,y,z) using select x,y,z from t1;

If you have a specific error you are getting with the COPY command -- please add
that ( a cut and paste of your attempt and the error would be great )

Another option is

o export the table from REMOTE using exp.
o import the table into local using imp.

Another option is to use a database link and simply:

insert into t2 select * from t1@remote_db_link;

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype [message #203598 is a reply to message #203588]

Wed, 15 November 2006 13:30

cleopard
Messages: 10
Registered: June 2005

Junior Member

SQL> copy from user/pass@otherdb.com insert into local_table -
> using select * from remote_table where format_id = 122
SP2-0498: missing parenthetical column list or USING keyword

Seems like no matter what syntax I tried, I'd get that same error, parens or no parens, even though I'm using the 'using' clause.

Report message to a moderator

Previous Topic:	SAN FAILOVER TAKES 110 SECONDS, WHAT IS THE IMPACT FOR AN INSTANCE?
Next Topic:	dataguard monitoring

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 18:17:25 CDT 2025