Loading...
Wednesday, November 5, 2008

ORA-00997

ORA-00997
ORA-00997: TNS:no listener
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

More Information
LONG datatypes cannot be used for Insert with selects or CTAS (create table as select)

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
Source:http://www.orafaq.com/forum/t/55560/0/

Related Link:
http://www.orafaq.com/forum/t/55560/0/
http://www.lazydba.com/oracle/0__39635.html
http://www.experts-exchange.com/Database/Oracle/Product_Info/Q_23801529.html
http://www.experts-exchange.com/Databases/Oracle/Q_20920426.html




Tags: ,,,,

0 comments:

 
TOP