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: ora-00997,ora-error,oracle error,oracle code,ora error code