aot2002

thanks http://us2.php.net/manual/en/function.oci-bind-by-name.php#72639 this seems to be not just isolated to me

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

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