OraFAQ Forum: SQL & PL/SQL » ORA-00997: illegal use of LONG datatype during insert into as select command

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00997: illegal use of LONG datatype during insert into as select command (Oracle 11g, windows)

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

ORA-00997: illegal use of LONG datatype during insert into as select command [message #558510]

Sat, 23 June 2012 02:45

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

we are facing an issue during the insert into as select statement when insert/select long datatype value.

Current Structure of tables are as follows

CREATE TABLE "MONETA"."TBL_BILLING_SUCCESS" 
   (	"ANI" VARCHAR2(12 BYTE), 
	"TOTAL_AMOUNT" VARCHAR2(10 BYTE), 
	"DEDUCTED_AMOUNT" VARCHAR2(10 BYTE), 
	"USER_BALANCE" VARCHAR2(10 BYTE), 
	"ISPREPAID" VARCHAR2(12 BYTE), 
	"DATETIME" TIMESTAMP (6), 
	"RECORDSTATUS" VARCHAR2(3 BYTE), 
	"ERRORDESC" VARCHAR2(1024 BYTE), 
	"CIRCLEID" VARCHAR2(10 BYTE), 
	"TYPE_EVENT" VARCHAR2(10 BYTE), 
	"IS_EMM" VARCHAR2(6 BYTE), 
	"MOD" VARCHAR2(20 BYTE), 
	"PROCESS_DATETIME" TIMESTAMP (6), 
	"SRC" VARCHAR2(20 BYTE), 
	"NOOFATTEMPT" NUMBER, 
	"NEXT_BILLED_DATE" TIMESTAMP (6), 
	"SERVICENAME" VARCHAR2(20 BYTE), 
	"NEXT_PROCESS_DATE" TIMESTAMP (6), 
	"SYSRESPONSE" VARCHAR2(20 BYTE), 
	"ROWNO" LONG
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "MONETA" ;


 CREATE TABLE "MONETA"."TBL_BILLING_SUCCESS_NEW" 
   (	"ANI" VARCHAR2(12 BYTE), 
	"TOTAL_AMOUNT" VARCHAR2(10 BYTE), 
	"DEDUCTED_AMOUNT" VARCHAR2(10 BYTE), 
	"USER_BALANCE" VARCHAR2(10 BYTE), 
	"ISPREPAID" VARCHAR2(12 BYTE), 
	"DATETIME" TIMESTAMP (6), 
	"RECORDSTATUS" VARCHAR2(3 BYTE), 
	"ERRORDESC" VARCHAR2(1024 BYTE), 
	"CIRCLEID" VARCHAR2(10 BYTE), 
	"TYPE_EVENT" VARCHAR2(10 BYTE), 
	"IS_EMM" VARCHAR2(6 BYTE), 
	"MOD" VARCHAR2(20 BYTE), 
	"PROCESS_DATETIME" TIMESTAMP (6), 
	"SRC" VARCHAR2(20 BYTE), 
	"NOOFATTEMPT" NUMBER, 
	"NEXT_BILLED_DATE" TIMESTAMP (6), 
	"SERVICENAME" VARCHAR2(20 BYTE), 
	"NEXT_PROCESS_DATE" TIMESTAMP (6), 
	"SYSRESPONSE" VARCHAR2(20 BYTE), 
	"ROWNO" LONG
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "MONETA" ;

When I am trying to insert record from TBL_BILLING_SUCCESS to TBL_BILLING_SUCCESS_NEW tables, its throwing below error.

INSERT INTO TBL_BILLING_SUCCESS_NEW SELECT * FROM TBL_BILLING_SUCCESS WHERE TRUNC(process_DATETIME) >=TRUNC(SYSDATE-10);
INSERT INTO TBL_BILLING_SUCCESS_NEW SELECT * FROM TBL_BILLING_SUCCESS WHERE TRUNC(process_DATETIME) >=TRUNC(SYSDATE-10)
                                           *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Kindly suggest what we can do to resolve this .

Thanks in Advance.

Pradeep

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558512 is a reply to message #558510]

Sat, 23 June 2012 02:55

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Kindly suggest what we can do to resolve this .

Do NOT use LONG but CLOB, LONG is obsolete since 15 years (yes, 15!).
No, you can't do it, definitively. You have to do it via an external program (or PL/SQL if all LONG are less than 32K).

Regards
Michel

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558513 is a reply to message #558512]

Sat, 23 June 2012 03:15

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michal,

Thanks for reply.

Can you please tel me that external program pl/sql code regarding which you are telling.

Pradeep

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558514 is a reply to message #558513]

Sat, 23 June 2012 03:40

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michal,

Also tell me that this table "Tbl_billing_success" having 22748501 Records.

So How much time it should take to complete.

Regards

Pradeep

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558515 is a reply to message #558514]

Sat, 23 June 2012 04:47

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Use APPEND hint ,try...

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558522 is a reply to message #558515]

Sat, 23 June 2012 08:53

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> INSERT /*+ APPEND */ INTO TBL_BILLING_SUCCESS_NEW 
  2  SELECT * FROM TBL_BILLING_SUCCESS WHERE TRUNC(process_DATETIME) >=TRUNC(SYSDATE-10);
SELECT * FROM TBL_BILLING_SUCCESS WHERE TRUNC(process_DATETIME) >=TRUNC(SYSDATE-10)
       *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

It does not work.
Why do you post such stupid stuff?
Try them YOURSELF before posting.

Regards
Michel

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558523 is a reply to message #558513]

Sat, 23 June 2012 08:55

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

pradies wrote on Sat, 23 June 2012 10:15

Hi Michal,

Thanks for reply.

Can you please tel me that external program pl/sql code regarding which you are telling.
Pradeep

I didn't say an external program pl/sql code.
But an external program (any language but PL/SQL) or a PL/SQL one IF the LONG is less than 3éK.

Regards
Michel

[Updated on: Sat, 23 June 2012 08:55]

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558530 is a reply to message #558523]

Sat, 23 June 2012 10:09

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

Quote:

You have to Quote:
do it
via an external program (or PL/SQL

I was not aware of there of this code. that's why I asked you about that.

Quote:

Why do you post such stupid stuff?
Try them YOURSELF before posting.

I posted this stupid stuff because I was facing this problem. and only you knows that this is a STUPID STUFF. If I knows that then I will never post this into your forums.

I tried this at self after that only I posted this into the forum for help only. Everyone is in this forum due to some issues/problems.

Rest I got the solution of the problem.

Pradeep

Report message to a moderator

Re: ORA-00997: illegal use of LONG datatype during insert into as select command [message #558545 is a reply to message #558530]

Sat, 23 June 2012 12:45

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The "stupid stuff" was not for you (see to whom I answered) it was for muralikri that is used to post silly things.
Sorry you took it for you. I don't think your question is silly, it is a question many that receive old databases and applications have to manage (and, by the way, was answered many times in the last 15 years).

Quote:

Rest I got the solution of the problem

Which was?

Regards
Michel

Report message to a moderator

Previous Topic:	Help SQL [merged 2 by jd]
Next Topic:	UTL_FILE.FGETATTR can't access files on a network disk after DB server was restarted

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 18 11:09:08 CDT 2025