Jump to content

stored proc oracle php ORAERROR on DATE


Recommended Posts

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

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.