Report Wizard - Inconsistency causing ORA-01461 - bind a LONG (bug?)
Tim St.Feb 11 2011 — edited Feb 1 2012
We have come across an inconsistency in the creation of APEX report region resulting in an error "ORA-01461: can bind a LONG value only for insert into a LONG column"
I want to share this to either get the attention of someone on the development team to help fix it, or to help someone who is running into the same issue and how to work around it.
Here are the details:+
Latest APEX 4.0.2.00.07
DB 11.1 and 11.2 confirmed same.
First - complex nasty query. Fact of life - sometimes it happens. Long with over 4K characters
Creating a STANDARD report (non interactive) - using the CREATE PAGE wizard or the CREATE REGION wizard, after entering in the SQL, it will throw a simple error page (wwv_flow.accept) showing the error ORA-01461: can bind a LONG value only for insert into a LONG column
Run the same wizard with select * from dual to get the page and region created.
Once it is created - edit the region - and place the long nasty query in the region source. It will then take and work as expected.
Originally, we considered it was because of the LENGTH of the query. This proved to be un-true. We were able to run the wizard with a 20,000 character select statement without an issue. We are still investigating, but it is difficult to determine what is going on inside the wizard. The SQL is valid and performs well, even if it is complex.
Note: Because of placement of bind variables - it does not work well as a view.
The work around is good to know, but the behavior has been described as "buggy" by some of my developers.
If anyone is interested in more details, please let me know.