Problems with Oracle VARRAY and PHP – PHP

  php

Q(Question):

I have following code on my page….

$ora_tns =
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP )(HOST=pirana)(PORT=1521))
)(CONNECT_DATA=(SID=pirana)(SERVER=DEDICATED)))";
$ora_username = "username";
$ora_password = "pwd";

$db = OCILogon($ora_username, $ora_password, $ora_tns);

$arr = OCINewCollection($db, ‘LOCAL_ID_ARRAY’);
$err = ocierror($db);
When I execute it on a simple page containing only this everything works
fine…..

However, when I put it on a larger page containing other stuff using other
oracle connections I get following error when trying to create collection:
"OCI-21522: attempted to use an invalid connection in OCI (object mode
only)"

What is the source of this error and how can I correct it….

LOCAL_ID_ARRAY is defined in Oracle as:
CREATE OR REPLACE TYPE local_id_array AS VARRAY(100) OF NUMBER(11);

Thank you in advance, Tomy.

A(Answer):

Forgot to mention:

Apache/1.3.29 (Win32) PHP/4.3.3
OCI8 Support enabled
Revision $Revision: 1.183.2.5 $

Tomy.

A(Answer):

[ Followup-To: comp.lang.php ]

Tomislav Petrovic wrote:

$db = OCILogon($ora_username, $ora_password, $ora_tns);

I have never used OCI*, but it pays to do error checking no matter what
you use.

Try showing ocierror() after every OCI* function you use.
$db = OCILogon($ora_username, $ora_password, $ora_tns) or die(ocierror());

–= my mail box only accepts =–
–= Content-Type: text/plain =–
–= Size below 10001 bytes =–

A(Answer):

Pedro Graca wrote:

Try showing ocierror() after every OCI* function you use.
$db = OCILogon($ora_username, $ora_password, $ora_tns) or
die(ocierror());

Thanks for the comment, I do check this but have omitted it for
simplicity…
And unfortunately my problem is not in OCILogon but OCINewCollection…

Tomy.

A(Answer):

Tomislav Petrovic wrote:

Thanks for the comment, I do check this but have omitted it for
simplicity…

Oh! ok, sorry
And unfortunately my problem is not in OCILogon but OCINewCollection…

I meant that ocierror() should be checked for all OCI functions.

Anyway, after googling for "attempted to use an invalid connection in
OCI (object mode only)" I got this page:

http://www.engin.umich.edu/caen/wls/…202/e19400.htm

which states

ORA-21522 attempted to use an invalid connection in OCI (object mode
only)

Cause: User attempted to use an invalid connection or a connection
that has been terminated in an OCI environment (object mode), or
user attempted to de-reference a REF obtained from a connection
which has been terminated.

Action: Ensure that the connection exists and is still valid.
Maybe the connection closed for some reason.
Can you do something like:

if (!connected()) {
reconnect();
}

before every OCINewCollection?

–= my mail box only accepts =–
–= Content-Type: text/plain =–
–= Size below 10001 bytes =–

A(Answer):

On Wed, 7 Jan 2004 13:08:35 +0100, "Tomislav Petrovic" <t.********@inet.hr>
wrote:

I have following code on my page….

$ora_tns =
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TC P)(HOST=pirana)(PORT=1521))
)(CONNECT_DATA=(SID=pirana)(SERVER=DEDICATED))) ";
$ora_username = "username";
$ora_password = "pwd";

$db = OCILogon($ora_username, $ora_password, $ora_tns);

$arr = OCINewCollection($db, ‘LOCAL_ID_ARRAY’);
$err = ocierror($db);
When I execute it on a simple page containing only this everything works
fine…..

However, when I put it on a larger page containing other stuff using other
oracle connections I get following error when trying to create collection:
"OCI-21522: attempted to use an invalid connection in OCI (object mode
only)"

What is the source of this error and how can I correct it….

LOCAL_ID_ARRAY is defined in Oracle as:
CREATE OR REPLACE TYPE local_id_array AS VARRAY(100) OF NUMBER(11);

Which version of Oracle?

Can you reduce it to a simple testcase?

The error implies the connection’s dropped; can you try a simple select after
the error to see if you get ORA-03114 not connected to Oracle?

If the connection has dropped, is there anything in the database alert log, or
a stack trace in your database’s cdump directory if the Oracle process crashed?


Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

A(Answer):

Andy Hassall wrote:

Which version of Oracle?
9.2….
Can you reduce it to a simple testcase?
No. That’s my problem….
When I do everything I did on a simple page one after another, it
works like a charm…
But when I separate it into class methods and invoke them one after another
it does not 🙁
The error implies the connection’s dropped; can you try a simple
select after the error to see if you get ORA-03114 not connected to
Oracle?

It is not dropped. Simple select or store procedure call after
works ok.

Tomy.

LEAVE A COMMENT