Client Tools :: Update Failed ORA-01722 - Invalid Number
May 18, 2011
Enviroment is Embarcadero RAD Studio XE. Work with DataSnap WebBroker Application. Its Server methods ancestor is TDSServerModule.
I've got SQLConnection (dbx), SQLDataSet, DataSetProvider on server side and SQLConnection, DSProviderConnection, ClientDataSet plus DataSource, DBGrid, DBNavigator on client side.
SQLConnection on server side uses Oracle driver - dbxora.dll. DB: Oracle 11g.
SQLConnection on client side uses Datasnap driver.
SQLDataSet has DbxCommandType set to Dbx.SQL, CommandText: "Select * from Table1".
All fields except indexed one have their pfInWhere set to false.
updateMode of DataSetProvaider is set to upWhereKeyOnly.
All is well up to point where ApplayUpdates is fired. It does nothing. HandleReconcileError shows ORA-01722: invalid number.
Oracle explains: ORA-01722:invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. But all updated fields are of character strings type. There is no need for conversion. I suppose the automatically created SQL has some extra checking. But I cannot see those SQLs. I guess must be a way of controlling those SQLs thru params, but don't know which. May be comparison old value - new value of index column is depending on this conversion?
INSERT INTO t_category m( m.service_id, m.customer_id) SELECT u.service_id, (SELECT p.add_data FROM t_add p WHERE p.service_id=u.service_id AND p.add_type='CUSTOMER_ID') FROM t_iservice u
I got this error: ORA-01722: invalid number. The problem is m.customer_id has data type NUMBER, p.add_data is VARCHAR.
I have a problem when executing the statement below querying a specific ID# in view
select * from VW_MML_LTR_MSTR where LTR_ID = 26 order by CLNT_CDE ,LTR_GRP, ltr_purp_id, LTR_CDE
I checked the data type acquired by the view and the table involved and confirmed that it was of number type.Weird thing for me is that I changed the where statement as
where LTR_ID LIKE 26 where LTR_ID != 25
Is this on the database settings? What should be done here?
select ROUND( ( SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', 2, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,1)- 2) + SUBSTR(' m1058,1672|1090,1672|1090,1716|1058,1716 x e', instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1, instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e',',',1,3) - (instr(' m1058,1672|1090,1672|1090,1716|1058,1716 x e','|',1,2)+1)) )/ 2) AS x from dual;
I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.
The log file has the following errors:
KUP-04021: field formatting error for field DEPTNO KUP-04023: field start is after end of record KUP-04101: record 1 rejected in file /usr/tmpclie.csv error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv ORA-01722: invalid number
ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle
I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.
CREATE OR REPLACE PROCEDURE test IS CURSOR cusers IS SELECT user_name, user_date FROM users; uname users.user_name%TYPE; udate users.user_date%TYPE; BEGIN OPEN cusers;
[code].....
When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement
Compilation of procedure is successful.
SQL code for creating the USERS table is here:
CREATE TABLE "USERS" ("USER_ID" NUMBER(10,0) NOT NULL ENABLE, "USER_NAME" VARCHAR2(50) NOT NULL ENABLE, "USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, "USER_PASS" VARCHAR2(50) NOT NULL ENABLE, "USER_DATE" DATE NOT NULL ENABLE, CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE )
I'm placing this here since the only tool I have is TOAD to hack database. I have discovered a bad record with the system but as the date field is '0000' and the values are all hieroglyphics which I can't place in UPDATE statement. Only fields able to determine are that it's a journal entry. I tried the blow code only to be forced out of TOAD via ORA-03113: end-of-file on communication channel error is a generic error. How can I update the bad date fields with such limited access? Is the ROWID acceptable?
Update ft_os set ost_upd_dt = '06-dec-2010' where ost_tran_code = 'JEN' and ost_doc_ref = 'IS'
Now i have a problem which i am facing for the first time. The problem is:
I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered. SQL> create table test1 2 ( 3 startno number(25), 4 endno number(25) 5 );
in the following program i am submitting a request and sending concurrent program log file (.REQ file)as an attachment but getting following error
ORACLE error 29277 in FDPSTP Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation ORA-06512: at "SYS.UTL_SMTP", line 44 ORA-06512: at "SYS.UTL_SMTP", line 150 ORA-06512: at "SYS.UTL_SMTP", line 383 ORA-06512: at "SYS.UTL_SMTP", line 399
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com # SID is sting2 # SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server. STING2 = (DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.") jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method" jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works) jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors) (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.
I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.
I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
I usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line (run->cmd, then enter username and password).
The best command line tools available. What is generally used command line tools.
The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. for Unix AIX 5
I install the ODBC Driver Manager, Basic Instant Client, ODBC Instant Client packages (libsqora.so, odbc_update_ini.sh).
The error:
[oracle@d4 oracle] $ isql OracleODBC-10g user passtemp -v [01000][unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_10_2/libsqora.so' : file not found [ISQL]ERROR: Could not SQLConnect
