We help IT Professionals succeed at work.

ora-01461 cannot bind a LONG value only for insert into a long column

ewang1205
ewang1205 asked
on
4,105 Views
Last Modified: 2008-01-09
I got error:  ora-01461 cannot bind a LONG value only for insert into a long column when do the following INSERT:   insert into test(sqls) values (lv_sql_all) .  Here is my table which SQLS is defined as LONG.  So what is wrong?  Thanks.

SQL> test
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 SQLS                                               LONG
Comment
Watch Question

senior developer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Are you sure that you want to use a "LONG" datatype in Oracle?  That has been supported in Oracle at least since Oracle7, but it has significant limitations.  The newer BLOB and CLOB datatypes may give you more options.
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Not only that, but I think it has been depricated since 8i.
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Although it is kind of funny...PeopleSoft (now owned by Oracle) still uses the LONG datatype.

Author

Commented:
I got the same error even if I use substr(lv_sql_all,1,3888).  I also change the column to clob and still got the same problem.  Please help.  Thanks.

insert into test(sqls) values (substr(lv_sql_all,1,3888));
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I am open to any datatype as long as it works.  Right now, it is not working.  The test table is for testing purpose so I can change.  Which datatype will eliminate my problem?  Thanks.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
actually varchar2 could be 4000 bytes in 8i and 9i as well.  One of the restrictions of LONG fields is, you can't put them through a function.

What language do you plan on using to access the data (insert/update/delete)?  What is th emaximum size of your data?

Author

Commented:
I found an entirely different workaround not using long.  No need to post my answer because it is not applicable.  I will split the points.  Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.