username OUTPUT IS
to_timestamp('17-MAY-08 06.39.44.6157 AM','DD-MON-RR HH.MI.SSXFF AM')
executing BEGIN username.mgmt_stations.update_info(_user, _station_id, _formal_name, _dba_name, _station_type, _mac_id, _federal_id, _owner_name, _owner_type, _rmv_region, _RECORD_UPDATE_TS); END;
Warning: ociexecute() [function.ociexecute]: ORA-01840: input value not long enough for date format ORA-06512: at line 1 in test.php on line 56
ORA-01840: input value not long enough for date format ORA-06512: at line 1BEGIN username.mgmt_stations.update_info(_user, _station_id, _formal_name, _dba_name, _station_type, _mac_id, _federal_id, _owner_name, _owner_type, _rmv_region, _RECORD_UPDATE_TS); END;
But yet i cannot seem to get this properly to execute?????
heres the store proc too
procedure update_info(
p_user varchar2,
p_station_id mavid.stations.station_id%TYPE,
p_RECORD_UPDATE_TS MAVID.STATIONS.RECORD_UPDATE_TS%TYPE,
p_formal_name mavid.stations.formal_name%TYPE,
p_dba_name mavid.stations.dba_name%TYPE,
p_station_type mavid.stations.STATION_TYPE%TYPE,
p_mac_id mavid.stations.MAC_ID%TYPE,
p_federal_id mavid.stations.FEDERAL_ID%TYPE,
p_owner_name mavid.stations.OWNER_NAME%TYPE,
p_owner_type mavid.stations.OWNER_TYPE%TYPE,
p_RMV_REGION mavid.stations.RMV_REGION%TYPE
) IS
v_SYSTEM_NAME mavid.stations.SYSTEM_NAME%TYPE;
v_TRANSACTION_TS TIMESTAMP;
NO_ROWS_UPDATED EXCEPTION;
BEGIN
SELECT VALUE
INTO v_SYSTEM_NAME
FROM V$PARAMETER
WHERE NAME = 'db_name';
v_TRANSACTION_TS := SYSTIMESTAMP;
update mavid.stations set
SYSTEM_NAME = v_SYSTEM_NAME,
RECORD_UPDATE_TS = v_TRANSACTION_TS,
UPDATED_BY = p_USER,
FORMAL_NAME = p_FORMAL_NAME,
DBA_NAME = p_DBA_NAME,
FEDERAL_ID = p_FEDERAL_ID,
OWNER_NAME = p_OWNER_NAME,
OWNER_TYPE = p_OWNER_TYPE,
MAC_ID = p_MAC_ID,
RMV_REGION = p_RMV_REGION,
STATION_TYPE = p_STATION_TYPE
where STATION_ID = p_STATION_ID
and RECORD_UPDATE_TS = p_RECORD_UPDATE_TS;
IF SQL%ROWCOUNT = 0 THEN
RAISE NO_ROWS_UPDATED;
END IF;
EXCEPTION
WHEN NO_ROWS_UPDATED THEN
RAISE_APPLICATION_ERROR(-20001,'No rows unpdated. Client record is stale or the record key does not exist');
COMMIT;
end update_info;
<?php
class test {
function run(){
$con = oci_connect ( "username", "****", "TESTDB" ) or die ( "ERROR" );
$stationinfo['p_user'] = "USER1"; //$this->getRequestParameter('p_user');
$stationinfo['p_station_id'] = "XYZ12345";
$stationinfo['p_formal_name'] = "TEST";
$stationinfo['p_dba_name'] = "TEST";
$stationinfo['p_station_type'] = "P";
$stationinfo['p_mac_id'] = "1";
$stationinfo['p_federal_id'] = "0";
$stationinfo['p_owner_name'] = "NONE";
$stationinfo['p_owner_type'] = "P";
$stationinfo['p_rmv_region'] = "1";
$sql = "select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT'";
$res = $this->oraselect($sql);
if (!$res) {
$row = oci_fetch_assoc($res);
$ts_format = $row['VALUE'];
} else {
$ts_format = "DD-MON-RR HH.MI.SSXFF AM";
}
//GET current table timestamp
$this->statement = $this->oraselect("SELECT RECORD_UPDATE_TS FROM STATIONS WHERE STATION_ID = 'XYZ12345'");
$row = array();
$row = $this->statement[0];
$time_stamp = " to_timestamp('".$row['RECORD_UPDATE_TS'][0]."','$ts_format') ";
echo $time_stamp."<br>";
foreach ( $stationinfo as $key => $value ) {
$bindinfo .= ":$key, ";
//:Bindname1, :Bindname2
}
//left over comma from above
$bindinfo .= " _RECORD_UPDATE_TS";
$statement = OCIParse ( $con, "BEGIN username.mgmt_stations.update_info($bindinfo); END;" );
foreach ( $stationinfo as $key => $value )
OCIBindByName ( $statement, ":$key", $value );
//RECORD UPDATE TS
OCIBindByName ( $statement, "_RECORD_UPDATE_TS", $time_stamp );
$result = OCIExecute ( $statement, OCI_DEFAULT );
if (! $results) {
$e = oci_error ( $statement ); // For oci_execute errors pass the statementhandle
echo ( $e ['message'] );
echo ( $e ['sqltext'] );
}
}
function oraselect($query, $db = "ORCL") {
//$con = Propel::getConnection($db);
$this->con = oci_connect ( "username", "****", "TESTDB" ) or die ( "ERROR" );
//clean query first
/*$set = false;
$parsed = array();
$pieces = explode("~",$qquery);
foreach ($pieces as $item) {
if ($set) {$item = $this->oraclean($item);}
$set = !$set;
array_push($parsed,$item);
}
$query = implode("",$parsed);
*/
// execute the query
$statement = oci_parse ( $this->con, $query ); //statement
$results = oci_execute ( $statement );
//$resource[0] = $rs->getResource(); //resource
//$resource[1] = $statement;
if (! $results) {
$e = oci_error ( $statement ); // For oci_execute errors pass the statementhandle
echo ( $e ['message'] );
echo ( $e ['sqltext'] );
//printf ( "\n%" . ($e ['offset'] + 1) . "s", "^" );
}
$nrows = oci_fetch_all ( $statement, $results_array );
//results of select into table
$statement_array [0] = $results_array;
$statement_array [1] = oci_num_fields ( $statement );
$statement_array [2] = $statement;
$statement_array [3] = $nrows;
/*echo "<pre>";
var_dump($statement_array);
*/
//echo ( "SELECT ORACLE: $query" );
return $statement_array;
}
}
$tmp = new test();
$tmp->run();
?>