ewang1205
asked on
ora-01461 cannot bind a LONG value only for insert into a long column
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
SQL> test
Name Null? Type
--------------------------
SQLS LONG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Not only that, but I think it has been depricated since 8i.
Although it is kind of funny...PeopleSoft (now owned by Oracle) still uses the LONG datatype.
ASKER
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) );
insert into test(sqls) values (substr(lv_sql_all,1,3888)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
What language do you plan on using to access the data (insert/update/delete)? What is th emaximum size of your data?
ASKER
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.