aot2002 Posted May 21, 2008 Share Posted May 21, 2008 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(); ?> Quote Link to comment https://forums.phpfreaks.com/topic/106556-stored-proc-oracle-php-oraerror-on-date/ Share on other sites More sharing options...
aot2002 Posted May 21, 2008 Author Share Posted May 21, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/106556-stored-proc-oracle-php-oraerror-on-date/#findComment-546245 Share on other sites More sharing options...
btherl Posted May 21, 2008 Share Posted May 21, 2008 There's an oracle sub-forum here. I'll request that this topic be moved there.. Quote Link to comment https://forums.phpfreaks.com/topic/106556-stored-proc-oracle-php-oraerror-on-date/#findComment-546282 Share on other sites More sharing options...
aot2002 Posted May 21, 2008 Author Share Posted May 21, 2008 thanks http://us2.php.net/manual/en/function.oci-bind-by-name.php#72639 this seems to be not just isolated to me Quote Link to comment https://forums.phpfreaks.com/topic/106556-stored-proc-oracle-php-oraerror-on-date/#findComment-546538 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.