Problems with Oracle VARRAY and PHP

[Tomislav Petrovic]

I have following code on my page....

$ora_tns =
"(DESCRIPTION=( ADDRESS_LIST=(A DDRESS=(PROTOCO L=TCP)(HOST=pir ana)(PORT=1521) )
)(CONNECT_DATA= (SID=pirana)(SE RVER=DEDICATED) ))";
$ora_username = "username";
$ora_password = "pwd";

$db = OCILogon($ora_u sername, $ora_password, $ora_tns);

$arr = OCINewCollectio n($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]

Re: Problems with Oracle VARRAY and PHP

Forgot to mention:

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

Tomy.

[Pedro Graca]

Re: Problems with Oracle VARRAY and PHP

[ Followup-To: comp.lang.php ]

Tomislav Petrovic wrote:[color=blue]
> $db = OCILogon($ora_u sername, $ora_password, $ora_tns);[/color]

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_u sername, $ora_password, $ora_tns) or die(ocierror()) ;
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Tomislav Petrovic]

Re: Problems with Oracle VARRAY and PHP

Pedro Graca wrote:[color=blue]
> Try showing ocierror() after every OCI* function you use.
>
>
> $db = OCILogon($ora_u sername, $ora_password, $ora_tns) or
> die(ocierror()) ;[/color]

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

Tomy.

[Pedro Graca]

Re: Problems with Oracle VARRAY and PHP

Tomislav Petrovic wrote:[color=blue]
> Thanks for the comment, I do check this but have omitted it for
> simplicity...[/color]

Oh! ok, sorry
[color=blue]
> And unfortunately my problem is not in OCILogon but OCINewCollectio n...[/color]

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/software/oracle/server.901/a90202/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 OCINewCollectio n?
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Andy Hassall]

Re: Problems with Oracle VARRAY and PHP

On Wed, 7 Jan 2004 13:08:35 +0100, "Tomislav Petrovic" <t.petrovic@ine t.hr>
wrote:
[color=blue]
>I have following code on my page....
>
>$ora_tns =
>"(DESCRIPTION= (ADDRESS_LIST=( ADDRESS=(PROTOC OL=TCP)(HOST=pi rana)(PORT=1521 ))
>)(CONNECT_DATA =(SID=pirana)(S ERVER=DEDICATED )))";
>$ora_usernam e = "username";
>$ora_passwor d = "pwd";
>
>$db = OCILogon($ora_u sername, $ora_password, $ora_tns);
>
>$arr = OCINewCollectio n($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_ARR AY is defined in Oracle as:
>CREATE OR REPLACE TYPE local_id_array AS VARRAY(100) OF NUMBER(11);[/color]


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 (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Tomislav Petrovic]

Re: Problems with Oracle VARRAY and PHP

Andy Hassall wrote:[color=blue]
> Which version of Oracle?[/color]
9.2....
[color=blue]
> Can you reduce it to a simple testcase?[/color]
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 :(
[color=blue]
> 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?[/color]
It is not dropped. Simple select or store procedure call after
works ok.

Tomy.