stored proc oracle php ORAERROR on DATE

[aot2002]

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();
            
?>

[aot2002]

ok i managed to find out no to_timestamp is allowed in ocibindbyname and also the order of the param's is important !

 

does anyone know if the order of param's can be dynamic in the ocibindbyname

[btherl]

There's an oracle sub-forum here.

 

I'll request that this topic be moved there..

[aot2002]

thanks

 

http://us2.php.net/manual/en/function.oci-bind-by-name.php#72639

this seems to be not just isolated to me