Skip to Main Content
  • Questions

  • Inserting data from long to varchar2

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Johny.

Asked: February 04, 2005 - 11:41 am UTC

Last updated: April 28, 2005 - 7:59 am UTC

Version: 9i release 2

Viewed 1000+ times

You Asked

Tom,


How do I insert a record from one table to another, which has inconsistent data types?


Here the source table got a colum having long data type and the target table is of varchar2 data type;

Is there any way to insert this record into the target table?


create table source_tab(tno number(2), ttext long);

insert into source_tab values(1, 'First test record');

insert into source_tab values(2, 'Second test record');

commit;


create table target_tab(tno number(2), ttext varchar2(3000));


insert into target_tab select * from source_tab;



insert into target_tab select * from source_tab
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Any advise much appreciated
Johny

and Tom said...

you will have to do something like this:


ops$tkyte@ORA9IR2> begin
2 for x in (select * from source_tab)
3 loop
4 insert into target_tab values X;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from target_tab;

TNO
----------
TTEXT
-------------------------------------------------------------------------------
1
First test record

2
Second test record


that'll work as long as the longs are short enough to actually fit.

converting a long to a clob is easy, if they exceed your varchar2 size, you just use to_lob() on the long.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle 9IAS report Services

syed nisar ahmed, February 06, 2005 - 5:48 am UTC

Hi all , I have a query hope can be answered from here ,
I have installed 9IAS appllication server and 9i Database on a windows 2000 server , which was working fine until now, two days back i had shut down the services of both IAs and Infra in the sequence and shut down the machine.
After restarting the machine the Reports services of IAS are not starting i tried to start manually but still its the same problem. I have checked the log which is pasted here
*** 2005/1/6 10:39:17 -- Reports Server is starting up
*** 2005/1/6 10:39:21 -- Reports Server started up engine rwURLEng-0
*** 2005/1/6 10:39:23 -- Reports Server started up engine rwEng-0
*** 2005/1/6 10:39:28 -- Shutting down engine rwEng-0
*** 2005/1/6 10:39:28 -- Shutting down engine rwURLEng-0
*** 2005/1/6 10:39:28 -- Server is shuting down
by which im not able to work for Reports and
please reply as soon as possible

Tom Kyte
February 07, 2005 - 3:49 am UTC

please contact support. I've never configured the reports engine.

ORA-00997: illegal use of LONG datatype (using a view)

Paul, April 28, 2005 - 4:37 am UTC

Tom - why do you get ORA-00997 when using CTAS (I take it that CTAS is acceptable) on
a view over a dblink but not on a table over a dblink?


orhousin@BUSOTEST> get dblink2
1 create database link p04
2 connect to charges
3 identified by charges
4* using 'p04'
orhousin@BUSOTEST> l
1 create database link p04
2 connect to charges
3 identified by charges
4* using 'p04'
orhousin@BUSOTEST> /

Database link created.

orhousin@BUSOTEST> create view v_property
2 as
3 select * from property@p04;

View created.

orhousin@BUSOTEST>

orhousin@BUSOTEST> desc v_property
Name Null? Type
----------------------------------------- -------- --------------

UPRN NOT NULL NUMBER(7)
PROP_NUM NUMBER(5)
PROP_SUB_NUM VARCHAR2(5)
PROP_NAME VARCHAR2(35)
STREET_NAME NOT NULL VARCHAR2(35)
ADDR_LINE2 VARCHAR2(35)
ADDR_LINE3 VARCHAR2(35)
POST_CODE VARCHAR2(8)
PROPERTY_CLASS NOT NULL VARCHAR2(1)
PROPERTY_TYPE_CODE NOT NULL VARCHAR2(1)
PROP_SIZE NUMBER(2)
FLOOR_LEVEL NUMBER(2)
DWELLING_TYPE_CODE VARCHAR2(4)
DWELLING_SUBTYPE_CODE VARCHAR2(4)
MANAGEMENT_AREA_CODE NOT NULL VARCHAR2(5)
REPAIRS_AREA_CODE VARCHAR2(5)
REPAIRS_SYSTEM_AREA VARCHAR2(20)
HEATING_FUEL_CODE VARCHAR2(4)
FURNITURE_CODE VARCHAR2(4)
DWELLING_CHAR_CODE VARCHAR2(4)
LOCALITY_CODE VARCHAR2(4)
HEATING_TYPE_CODE VARCHAR2(4)
FUTURE_USE_CODE VARCHAR2(4)
STATE_OF_REPAIRS_CODE VARCHAR2(4)
PROVISIONAL_TERM_FLAG VARCHAR2(1)
PROVISIONAL_TERM_DATE DATE
NEXT_ACCOUNT_STATUS VARCHAR2(1)
WARD_CODE VARCHAR2(4)
ESTATE_CODE VARCHAR2(4)
REPAIRS_SYSTEM_PROP_REF VARCHAR2(24)
SHED VARCHAR2(1)
PURPOSE_BUILT VARCHAR2(1)
BUILD_DATE DATE
CONVERTED VARCHAR2(1)
CONVERSION_DATE DATE
IMPROVED VARCHAR2(1)
IMPROVMENT_DATE DATE
EXTERNAL_RENT_REF VARCHAR2(24)
CURRENT_TENANT_BALANCE NUMBER(7,2)
BALANCE_DATE DATE
NET_DEBIT_AMOUNT NUMBER(5,2)
NET_DEBIT_DATE DATE
RENT_SYS_PROP_REF VARCHAR2(24)
WHEELCHAIR VARCHAR2(1)
SHELTERED VARCHAR2(1)
LIFT VARCHAR2(1)
PARKING_SPACE VARCHAR2(1)
GARAGE VARCHAR2(1)
VOID_SYSTEM_ACC_NUM VARCHAR2(24)
ACQUIRING_LEGISLATION VARCHAR2(4)
COMMUNAL_HEATING VARCHAR2(1)
INDIVIDUAL_HEATING VARCHAR2(1)
HEATING_PERCENT NUMBER(5,2)
EQUITY_SHARING_PERCENT NUMBER(5,2)
RESOURCE_TYPE_CODE VARCHAR2(4)
FLOORS_IN_BLOCK NUMBER(2)
NUM_OF_FLOORS NUMBER(2)
NUM_OF_ROOMS NUMBER(2)
NUM_OF_BEDROOMS VARCHAR2(2)
NUM_OF_BEDSPACES VARCHAR2(2)
NUM_OF_LIVING_ROOMS VARCHAR2(2)
NUM_OF_KITCHENS VARCHAR2(2)
NUM_OF_BATHROOMS VARCHAR2(2)
NUM_OF_TOILETS VARCHAR2(2)
NUM_DOUBLES NUMBER(2)
NUM_SINGLES NUMBER(2)
ESTABLISHMENT_NAME VARCHAR2(40)
ESTABLISHMENT_TEL_NUM VARCHAR2(20)
CONTACT_NAME VARCHAR2(40)
CONTACT_ADDR1 VARCHAR2(35)
CONTACT_ADDR2 VARCHAR2(35)
CONTACT_ADDR3 VARCHAR2(35)
CONTACT_TEL_NUM VARCHAR2(20)
DATE_OF_MOST_RECENT_INSPECT DATE
FIRE_CERTIFICATE_NUM VARCHAR2(20)
DSS_OFFICE_CODE VARCHAR2(4)
DSS_OFFICE_NAME VARCHAR2(40)
ESTIMATED_LIFE VARCHAR2(20)
LEASEHOLD VARCHAR2(1)
LEASE_EXPIRY_DATE DATE
INVOICE_FREQUENCY VARCHAR2(20)
CREDITOR_REF_IDENTIFIER VARCHAR2(13)
SCHEME1 VARCHAR2(5)
SCHEME2 VARCHAR2(5)
WL_CODE_FOR_QUOTA VARCHAR2(3)
VAT_REGISTERED VARCHAR2(1)
OWNER_CODE VARCHAR2(5)
HIGH_SEQ_INDIC_FILLED NUMBER(4)
DATE_NOTEPAD_CREATED DATE
DATE_NOTEPAD_UPDATED DATE
ADDRESS_KEY VARCHAR2(12)
PERMISSIBLE_OCCUPANTS NUMBER(3,1)
OS_REFERENCE VARCHAR2(12)
COUNCIL_TAX_REFERENCE VARCHAR2(24)
COUNCIL_TAX_BAND VARCHAR2(2)
CONTRACT_AREA_CODE VARCHAR2(5)
NOTES LONG
ADVERTISED VARCHAR2(1)
ADVERTISED_DATE DATE
DATE_ADV_FLAG_CHANGED DATE
OSAPR VARCHAR2(18)
ESTATE_AREA_CODE VARCHAR2(5)
PROVISIONAL_MAILING_ADD1 VARCHAR2(35)
PROVISIONAL_MAILING_ADD2 VARCHAR2(35)
PROVISIONAL_MAILING_ADD3 VARCHAR2(35)
PROVISIONAL_MAILING_ADD4 VARCHAR2(35)
PROVISIONAL_MAILING_ADD5 VARCHAR2(35)
PROVISIONAL_MAILING_POSTCODE VARCHAR2(8)
PROVISIONAL_MAILING_ADDR_RSN VARCHAR2(4)
LOT_NUMBER VARCHAR2(5)
ACTIVE_WITHIN_CBL VARCHAR2(1)
LLPG_UPRN NUMBER(15)

orhousin@BUSOTEST> create table x
2 as
3 select uprn from v_property;
select uprn from v_property
*
ERROR at line 3:
ORA-00997: illegal use of LONG datatype

orhousin@BUSOTEST> create table x
2 as
3 select uprn from property@p04;

Table created.

orhousin@BUSOTEST>

Tom Kyte
April 28, 2005 - 7:59 am UTC

the view is expanded out in the dictionary to be "select c1, c2, c3, ... "

so that is

create table x as select uprn from (select c1,c2,c3,.... from property@p04);

the sql apparently references the long. but when you do the specific select, it does not -- so the create table as select is happy.

(we could have used a two column example here....)

ORA-00997

A reader, April 28, 2005 - 6:13 am UTC

Tom - I'm using Oracle version 8.1.0.7

Two row example

Paul, April 28, 2005 - 8:10 am UTC

Tom - as soon as I submitted the review I realised I shouldn't have put all that junk in and that I'd missed out the Oracle version number. Sorry about that!

Two column example!

Paul, April 28, 2005 - 8:14 am UTC

... it's been a long day.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here