stored proc oracle php ORAERROR on DATE

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aot2002
Forum Newbie
Posts: 3
Joined: Tue May 20, 2008 8:13 pm

stored proc oracle php ORAERROR on DATE

Post by 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(:p_user, :p_station_id, :p_formal_name, :p_dba_name, :p_station_type, :p_mac_id, :p_federal_id, :p_owner_name, :p_owner_type, :p_rmv_region, :P_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(:p_user, :p_station_id, :p_formal_name, :p_dba_name, :p_station_type, :p_mac_id, :p_federal_id, :p_owner_name, :p_owner_type, :p_rmv_region, :P_RECORD_UPDATE_TS); END;

But yet i cannot seem to get this properly to execute?????


heres the store proc too

Code: Select all

 
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;
 
 

Code: Select all

 
<?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 .= " :P_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, ":P_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
Forum Newbie
Posts: 3
Joined: Tue May 20, 2008 8:13 pm

Re: stored proc oracle php ORAERROR on DATE

Post by 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
aot2002
Forum Newbie
Posts: 3
Joined: Tue May 20, 2008 8:13 pm

Re: stored proc oracle php ORAERROR on DATE

Post by aot2002 »

looks like this seems to be not just my issue

http://us2.php.net/manual/en/function.o ... .php#72639

although its still not working that way either
Post Reply