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;
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?
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?
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
I have a pkg with a procedure that uses dbms_sql to process a varchar2_table. Each record in the table is a delimited string, such as "Priority^2", or "Destination^7". The goal is to split that string on the hat and update a record in the panelfield_users table. It works perfectly if I replace the first substr/instr on :pColumn with a hardcoded number, so I know that the binding is working to that point. It is only when I try to get the latter half of the string that it chokes. I keep getting a "invalid number" error. The displayorder field is an integer field and all values will be 3 digits or less. If I pull the sql string out into an editor, it runs just lovely.
The pertinent code is: C := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(C,'update panelfield_users set displayorder = to_number(substr(:pColumn, instr(:pColumn, ''^'') + 1)) where userid = :pUID and fieldid = (select fieldid from panel_fields where upper(panelname) = upper(:pPanel) and upper(modulename) = upper(:pModule) and upper(field_displayname) = upper(substr(:pColumn, 1, instr(:pColumn, ''^'')-1)) )
REVNUMBER is a VARCHAR2. I've set it to increment by letter in alphabet to note the revision of a document, i.e. IR*, A, B, C... . This works beautifully.
VERNUMBER is a VARCHAR2. I've set it to increment by number but starts from null to 'IR' to 1, 2, 3...
Until now, I've been incrementing VERNUMBER without problem if, instead of IR as the first incremented value I leave the field null. The user has asked that IR display. I've tried using IR as the default but I still need to increment from there so I don't see it making a difference between that or dumping it as the first condition of the following. The problem is I get an invalid number error either way.
*'IR' = 'Initial Release'
SELECT CASE WHEN VERNUMBER IS NULL THEN 'IR' WHEN VERNUMBER = 'IR' THEN '1' ELSE REGEXP_REPLACE(VERNUMBER, '[0-9]+$', REGEXP_SUBSTR(UPPER(VERNUMBER), '[0-9]+$') + 1) END NEW_VERSION FROM DOC_INFO WHERE DOC_INFO_ID = :P3_DOC_INFO_ID
desc entite Nom NULL Type ------------- -------- ----------------- ENT_SEQ NOT NULL NUMBER(38) ENT_CODE_ENT NOT NULL VARCHAR2(6 CHAR) ENT_GPL_ID NOT NULL NUMBER(10) ENT_OLS_EXE NOT NULL NUMBER(38) ENT_TYE_SEQ NUMBER(38)
I have to ask a pretty basic question (my pl/sql skills need serious work). I need to validate that a string can be converted to a number, and if it can't be, substitute something else. This works:orcl> create or replace function jw_to_number(str varchar2) return number is
2 n number; 3 begin 4 n := to_number(str); 5 return n; 6 exception when others then return 0; 7 end; 8 /
Function created.
orcl> select jw_to_number('a') from dual; JW_TO_NUMBER('A') ----------------- 0 orcl>
but of course I don't want to use WHEN OTHERS. So I tried this:orcl> create or replace function jw_to_number(str varchar2) return number is
2 n number; 3 begin 4 n := to_number(str); 5 return n; 6 exception when INVALID_NUMBER then return 0; 7 end; 8 /
Function created.
orcl> select jw_to_number('a') from dual; select jw_to_number('a') from dual * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "SCOTT.JW_TO_NUMBER", line 4 orcl>I
thought this would replace the standard error handler with my own, but it doesn't. I tried redeclaring the pre-defined exception, same result.
I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.
select COUNTRY_CODE, STATE_CODE, count(*) from (select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE where DRIVING_LICENCE is not null and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT'))) group by COUNTRY_CODE, STATE_CODE
The output is okay...for example I get these results:
INTINT NZSI NZINT AUSTINT NZNSW NZ <null>
However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?
when I execute this I get an output of $123.50...I want to use other speacial characters like @,* instead of $...But I am getting invalid identifier error if I use any special character other than $.
PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER, in_SPLY_REORD_NO IN CHAR, in_OHB_QTY_CARTONS IN NUMBER, in_OHB_QTY_UNITS IN NUMBER, in_SPLY_TOT_OHB_QTY IN NUMBER, in_OHB_INPUT_CTNS_MIN IN NUMBER, in_OHB_INPUT_CTNS_MAX IN NUMBER, in_UNITS_PER_CARTON IN NUMBER, in_OHB_INPUT_UNIT_CONSTANT IN NUMBER, in_TOTAL_CARTONS IN NUMBER, out_ALLOW_OHB_INPUT_FLAG OUT CHAR, out_ERR_CODE OUT NUMBER, out_ERR_MESSAGE OUT VARCHAR2)
When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor
Here is the execution script DECLARE IN_CALL_ID NUMBER; IN_SPLY_REORD_NO VARCHAR2(32767); IN_OHB_QTY_CARTONS NUMBER; IN_OHB_QTY_UNITS NUMBER; IN_SPLY_TOT_OHB_QTY NUMBER; IN_OHB_INPUT_CTNS_MIN NUMBER; [code]...
There is no cursor used in the procedure. It just inserts records in the table for given input values.
The following query gives me "Invalid Identifier error: ORA-00904.
SELECT TMADMIN.pkg_twatch_invdb.Max_Trk_Date(tmadmin.sites.study_id,tmadmin.sites.site_id, 1000280)) "Qualified Date" FROM tmadmin.sites "tmadmin' is the Schema Name "pkg_twatch_invdb" is the package name "max_trk_date" is a function inside the package (returns date)
I am using the same format for other function, it doesn't give any errors.
i was written query for nth highest salary from emp table...i get error
1 select distinct (a.sal) from emp a where 2* &N = (select count (distinct(b.sal) from emp b where a.sal <= b.sal); 3 / Enter value for n: 2 old 2: &N = (select count (distinct(b.sal) from emp b where a.sal <= b.sal); new 2: 2 = (select count (distinct(b.sal) from emp b where a.sal <= b.sal); 2 = (select count (distinct(b.sal) from emp b where a.sal <= b.sal); * ERROR at line 2:ORA-00911: invalid character
I'm teaching myself to write stored procedures, working in TOAD 10.5 with Oracle 10g. I keep getting error ORA-00900: invalid SQL Statement. Here's the code, cut down to bare minimum sample size. I can't see where I'm doing anything wrong.
If I cut out the cursor (taking it down to just "Begin" and "End"), it does run, but I can't see anything wrong with the cursor.
CREATE OR REPLACE PROCEDURE IN_PROCESS_CASES_BOS IS V_HELLO VARCHAR2(10); CURSOR C_MAIN IS SELECT 'HELLO' FROM DUAL;
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action:
UPDATE XXX_CURR_EOM SET ID = (select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM where XXX_CURR_EOM.ID = CAPITALPLAN_id)
