select statement not work in php with OCIEXEcute()

suchi_sood · 04-10-2006, 09:31 AM

<?php
putenv("ORACLE_SID=crbtdb");
putenv("ORACLE_HOME=/home/oracle/product/9.2.0.1.0");
putenv("TNS_ADMIN=/home/oracle/product/9.2.0.1.0/network/admin/");
$userora = "crbtclient";
$passwdora = "crbtclient";
$db="(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(HOST=leopard)(PORT=1521)
)
)
(CONNECT_DATA=(SERVICE_NAME=crbtdb))
)";
if($db_conn = OCILogon($userora,$passwdora,$db))
{
echo "Successfully connected to Oracle.\n";
}
else {
echo "Oracle Connect Error " . ocierror($db_conn);
}
$cmdstr = "select DN from ALLOWED_DNS like ";
$stmt = OCIParse($db_conn, $cmdstr);
OCIError($stmt);

$exec=OCIExecute($stmt);
OCIError($exec);
$nrows=ocifetchstatement($stmt, $results);
echo $nrows;
ocicommit($db_conn);
ocilogoff($db_conn);

gives an error
Successfully connected to Oracle.
Warning: OCIStmtExecute: Error while trying to retrieve text for error ORA-03106 in /var/www/html/CRBT/SUCHI/login.php on line 37

where as insert into table command works perfectly why..

using php4.1.2 with oracle 9i

if the datatype of the value is int then the select command works..
wheras in case of varchar2(20) ..it does not fetch the records..

graemef · 04-10-2006, 11:12 AM

You select doesn't look as if has been completed

Code:

select DN from ALLOWED_DNS like

like what?

suchi_sood · 04-10-2006, 11:25 PM

it is select dn frpm allowed_dns
writin mistake it cwas..
this query does work in sql promt..
yes i m able to be connected in oracle..
select statemrnt works on the table where data type is int..
if the data type is varchar2(10) then gives an error
OCIStmtExecute: Error while trying to retrieve text for error ORA-03117

paulsm4 · 04-11-2006, 01:34 AM

Look at your environment variables.

Don't trust PHP "putenv()" to set the variables correctly - set ORACLE_HOME. ORACLE_SID, NLS_LANG, etc at the *apache* level (not in your PHP script).

Here's another link (besides the other links I posted in your other thread):
http://php.benscom.com/manual/es/function.ocilogon.php

Good luck!

suchi_sood · 04-11-2006, 01:49 AM

set the enviornment variables in httpd.conf file as well..
but of no use..
cant find the correct ans...

SetEnv ORACLE_HOME /home/oracle/product/9.2.0.1.0
SetEnv ORA_NLS33 /home/oracle/product/9.2.0.1.0/ocommon/nls/admin/data
SetEnv NLS_LANG AMERICAN
SetEnv TWO_TASK /home/oracle/product/9.2.0.1.0/network/admin/tnsnames.ora
SetEnv LD_LIBRARY_PATH /home/oracle/product/9.2.0.1.0/lib:/lib:/usr/lib:/usr/local/lib
SetEnv ORACLE_SID crbtdb
SetEnv TNS_ADMIN /home/oracle/product/9.2.0.1.0/network/admin/tnsnames.ora
SetEnv NLS_CHARACTERSET "WE8DEC"
SetEnv NLS_NCHAR_CHARACTERSET "AL16UTF16"

smallville · 04-11-2006, 03:31 AM

i think you have to correct your sql query, as the oher user said, LIKE what?????

suchi_sood · 04-11-2006, 04:03 AM

plz tell what changes have to be made in sql area..
be atleast little precise.
any expert in php n oracle

graemef · 04-11-2006, 10:07 AM

Well initially I'd change

Code:

$cmdstr = "select DN from ALLOWED_DNS like ";
$stmt = OCIParse($db_conn, $cmdstr);

to

Code:

$cmdstr = "select DN from ALLOWED_DNS";
$stmt = OCIParse($db_conn, $cmdstr);

That at least looks like a valid sql statement to me.

suchi_sood · 04-11-2006, 11:22 PM

already made the correction ..
plz suggest the appropriate ans

graemef · 04-12-2006, 06:22 AM

Have you changed the httpd.conf as PaulSM suggested?