Problems with Oracle VARRAY and PHP

[Tomislav Petrovic]

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.

[Tomislav Petrovic]

Forgot to mention:

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

Tomy.

[Pedro Graca]

[ 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 =--

[Tomislav Petrovic]

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.

[Pedro Graca]

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 =--

[Andy Hassall]

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)

[Tomislav Petrovic]

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.