SQL & PL/SQL :: Stored Procedure - Character To Number Conversion Error
Mar 14, 2011
I have written a stored procedure that has started returning the error:
Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')
Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"
I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:
IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......
Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).
View 8 Replies
ADVERTISEMENT
Aug 4, 2011
I got a string in the form 1+2+4.If we write select 1+2+4 from dual;then we get o/p as 7.but the same thing iam trying to do in a bit of pl/sql program by passing the string 1+2+4 value to a number variable as below.
COUNT_TASK := TO_NUMBER(TASK6_STATUS);
TASK6_STATUS value is 1+2+4 (this thing i got by replacing the string and lots of stuff) but i need the result after adding these 3 numbers in the string. and i declare COUNT_TASK as NUMBER;and i am very well aware that it gives me the error ORA-06502: PL/SQL: numeric or value error: character to number conversion error
how to add these numbers in my program to get the result 7.
View 6 Replies
View Related
Jun 20, 2013
1 error has occurred ORA-06502: PL/SQL: numeric or value error: character to number conversion error
I get the above error when I try to compare the below dates in a Pl/sql process in APEX environment.
Is there a work around for it? or (to_char(V_SERVFROM,'mm/dd/yyyy')) != (to_char(:P29_SERVFROM,'mm/dd/yyyy'))
View 4 Replies
View Related
Apr 23, 2013
How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
View 8 Replies
View Related
May 31, 2011
I have one stored proc with three in parameters as
number, varchar and table of number
what statement I need to write in pl/sql to execute it ...
execute getdetails(1,'xyz', ????????????)
View 5 Replies
View Related
Jun 14, 2012
I've a table with fields:
create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,
[Code]....
View 7 Replies
View Related
Feb 17, 2010
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies
View Related
Apr 16, 2010
I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7
The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"
and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"
The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.
create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....
View 26 Replies
View Related
Jan 6, 2011
I have this stored procedure that does implements some business logic and then sends an email.
I got the below error when it tried to send the email
Quote:Returned values:
ERR_CODE =-29279,
ERR_MESSAGE = "MSS DATABASE INCONSISTENCYOTHERS EXCEPTION in XCOM_X080_SUBMITORDER - SQL -29279 SQLERRM: ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO""
How can I handle this error using exception and in case sending email fails instead of abnormal exit ?
View 2 Replies
View Related
Nov 18, 2010
Procedure: CREATE OR REPLACE procedure test (a number, b varchar2) is
begin
dbms_output.put_line(a ||'->'||b);
end;
Anyonymous Block:
begin
exec test(1,'m');
end;
/
When i run this i am getting this error
ORA-06550: line 2, column 7:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following
[code]...
View 5 Replies
View Related
Feb 1, 2010
I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.
The Store procedure definition looks like :
CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,
[code].....
Type definitions
TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;
My C code looks like :
typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];
[code].....
The way I invoke the stored procedure:
char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,
[code].....
OCIStmtExecute() fails with the above error.
View 3 Replies
View Related
Oct 2, 2008
I have a update statement that works fine when its run in SQL window but when i try it as part of a Stored Procedure its giving me the Insufficient privileges error on that table.
If i dont have privilege to update that table wont it not let me update while doing it outside the stored procedure as well?
View 4 Replies
View Related
Oct 19, 2011
I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:
SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;
[code]....
The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.
View 4 Replies
View Related
Sep 13, 2010
we have to migrate data from 11.1.0.6.0 to 11.2.0.1.0
Version : 11.1.0.6.0 character set : WE8MSWIN1252
Version : 11.2.0.1.0 character set : AL32UTF8
Any hints with which we can go.
View 3 Replies
View Related
Jan 29, 2013
There are two application servers we have, one is windows based with 10g and linux based 11gr2. Our main login form throwing error message in linux server 'ora-12703 this character set conversion is not supported'. The same coding form in 10g running without any problem. Both application servers accessing the same oracle db server 11gr2.
View 3 Replies
View Related
Nov 3, 2010
I have requirement to cleanup special character of field. The data which is retrieved from the field and written to text file.
So Here
Ã- should be replaced by i
HÃ-re - I need value Hire(actual value) from the filed.
Ã- this has written into file instead of i
In database i have the following
NLS_NCHAR_CHARACTERSET - AL16UTF16
NLS_CHARACTERSET - UTF8
I tried this
select convert('HÃ-re','UTF8') from dual
------------------------
Output: H??re
View 6 Replies
View Related
Apr 4, 2011
got this Errormessage "ORA-12713: Character data loss in NCHAR/CHAR conversion" during a rman backupjob! How can I fixed it?
View 3 Replies
View Related
Oct 21, 2011
I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting
For example i/p asdfasfsdS251432dasdasd o/p should be S251432
View 10 Replies
View Related
Dec 24, 2012
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
View 6 Replies
View Related
Jan 4, 2013
I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).
I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"
I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)
View 1 Replies
View Related
Oct 27, 2013
In one of the table, dates are stored in character format. I want to find the ID where start_date is greater than stop date have converted the char to date by using to_date function but still I am getting error.
with t as(select 122 as ID, 'JAN-12' as start_date, 'MAR-13' stop_date from dual unionselect 123 as ID, 'MAR-12' as start_date, 'FEB-12' stop_date from dual unionselect 124 as ID, 'MAR-12' as start_date, 'JAN-12' stop_date from dual unionselect 125 as ID, '01-MAR-1949' as start_date, '13-APR-1947' stop_date from dual unionselect 126 as ID, '01-MAR-1959' as start_date, '13-APR-1970' stop_date from dual unionselect 127 as ID, '11-MAR-1950' as start_date, '13-JAN-1951' stop_date from dual unionselect 128 as ID, '21-JUL-1969' as start_date, '20-JUL-1969' stop_date from dual)select * from tWHERE to_date(start_date) > to_date(stop_date)
View 9 Replies
View Related
Aug 11, 2010
I have coluum value like 00000000399. i need to convert the float like i mentioned
00000000399 should be loaded as 3.99
00000001197 should be loaded as 11.97
00000000003 should be loaded as 0.03
wat option i try to change the value
View 1 Replies
View Related
May 14, 2011
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);
Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b)
Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
View 7 Replies
View Related
May 6, 2010
I want to change a table datatype from date to number where already existing data should get convert.any possibility of doing where i tried like this but no get changing. Even as Julian format is working a bit i want the data to come as GMT format
Scenarios where i tried are like this.
ALTER TABLE Contacts ADD ALERT_DATE1 NUMBER(20,0)
/
UPDATE Contacts SET ALERT_DATE1 = TO_NUMBER(ALERT_DATE)
/
ALTER TABLE Contacts DROP COLUMN ALERT_DATE
/
ALTER TABLE Contacts RENAME COLUMN ALERT_DATE1 TO ALERT_DATE
/
but second statement failing.
Julian fomat like
SELECT sysdate,
TO_CHAR(sysdate, 'J'),
TO_DATE(TO_CHAR(sysdate, 'J'),'J')
FROM dual;
View 10 Replies
View Related
Sep 18, 2009
When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.
Is there any way to solve it?
View 3 Replies
View Related
Sep 23, 2010
Oracle database version is : 10G.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
get this issue resolved.
View 7 Replies
View Related
Oct 1, 2013
Can we read .pkb file or any conversion procedure
View 2 Replies
View Related
Jun 17, 2013
In my Project, there are many queries have join conditions between Number and Vrahchar2. Will it give any wrong results or Invalid Number exception even the varchar2 column always contains the number data?
Example:
Select * from Table_t1 t1, table_t2 t2
where t1.num_col = t2.var_col
-- Here var_col always hold only numaric data.
View 1 Replies
View Related
Dec 1, 2011
A column in a table contains the Numeric and Character. I Require the Numeric Value in that Column, if the Value is Character then null value is require to display.
View 4 Replies
View Related
Jul 30, 2010
the basic salary has data type character.when i write
select basic_salary from table_name
it shows output.but when i need annul for that basic salary(basic_salary*12) it shows error. invalid number.
View 6 Replies
View Related
