Forum Stats

  • 3,757,803 Users
  • 2,251,268 Discussions
  • 7,869,920 Comments

Discussions

Report Wizard - Inconsistency causing ORA-01461 - bind a LONG (bug?)

Tim St.
Tim St. Member Posts: 25 Bronze Badge
edited Feb 1, 2012 4:27AM in APEX Discussions
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:+
Specs_*
Latest APEX 4.0.2.00.07
DB 11.1 and 11.2 confirmed same.

Process+*
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

Workaround+*
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.

Other Findings+*
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.

--Tim
Tagged:

Answers

This discussion has been closed.