Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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:+
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

Comments

447039
One more note to the message. when I restart the database service, the application works fine. When I get this error, I did a tnsping and tnsping didnt give any errors.

Thanks,
Sirisha
This is an issue your DBA needs to address, it is not due to your client application --

Here is a Metalink note I found - for further information you will need to contact support.

Article-ID: Note 240710.1
Title: Intermittent TNS-12516 or TNS-12519 Errors Connecting Via
Net
Symptom(s)
~~~~~~~~~~
Client connections may intermittently fail with either of the following errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Additionally, a TNS-12520 error may appear in the listener log.

TNS:listener could not find available handler for requested type of server

The output of the lsnrctl services command may show that the service handler
is in a "blocked" state.

e.g. '"DEDICATED" established:1 refused:0 state:blocked'

Change(s)
~~~~~~~~~~

None necessarily. Perhaps increase in load.


Cause
~~~~~~~

By way of instance registration, PMON is responsible for updating the listener
with information about a particular instance such as load and dispatcher
information. Maximum load for dedicated connections is determined by the
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE
information varies according to the workload of the instance. The maximum
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance
but does not immediately get information about connections that have terminated.
Only when PMON updates the listener via SERVICE_UPDATE is the listener
informed of current load. Since this can take as long as 10 minutes, there can be
a difference between the current instance load according to the listener
and the actual instance load.

When the listener believes the current number of connections has reached maximum
load, it may set the state of the service handler for an instance to "blocked"
and begin refusing incoming client connections with either of the following
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Additionally, a TNS-12520 error may appear in the listener log.

The output of lsnrctl service may show that the service handler is "blocked".

e.g. '"DEDICATED" established:1 refused:0 state:blocked'

Fix
~~~~

Increase the value for PROCESSES.
1 person found this helpful
475050
The problem is you opened to much connection.

public static void OpenConnection()
{
//open connection once in the beginning or if the connection is closed during session, reopeon it
if (conn == null || conn.State != ConnectionState.Open)
{
conn = new OracleConnection("Data Source=localhost/XE;Persist Security Info=True;Password=a;User ID=a;");
conn.Open();
}
}

call this function in the beginning of you asp.net code and don't call any close conncetion. The application cache everything, so for every time you load a new page, it open a new connection
516073
call this function in the beginning of you asp.net code and don't call any close conncetion
I'd definitely call close at the end of the page processing code if you're using ASP.NET. That will put the connection back in the pool. I wouldn't want to trust the Framework to do it for you because that may not happen until garbage collection, and by then the connection will likely be stale.

Eric
580881
I also get this error intermittently, however only when using Oracle.DataAccess.Client (ODP.Net) and never with System.Data.OracleClient. Is this really a DBA issue or is it a problem with the provider or maybe a missing setting in the connection string?
Madrid
It should be verified the number of concurrent conections to the database. If maximum DB processes parameter has been reached this is a common symptom.


~ Madrid.
618669
Hi there,
Years later, I'm getting this same error. I have an asp.net web application that does a lot of database insert, although I'm careful to close the connections after I'm done each time, I get the tns error ORA-12516: TNS:listener could not find available handler with matching protocol stack.

I'm not an Oracle DBA but I still need to fix this error on my own. I use Oracle xe 10g and Oracle 9i client and know enough oracle to develop applications.

Thanks.
cganesh-Oracle
Please post issue on the oracle xe discussion forum as this is a db issue.
675632
Thanks everyone here for sharing the root cause.

I checked in my database, the processes parameter is set to 200. And no. of connections to db were going above 190+.
After bouncing the database, the issue seems to be resolved.

regds,
Anjul
1 person found this helpful
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 29 2012
Added on Feb 11 2011
#apex, #apex-discussions, #error, #report
1 comment
884 views