SQL & PL/SQL :: Stored Procedure Function - Numeric Or Value Error
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
ADVERTISEMENT
Oct 28, 2010
I am getting the below error in a Stored Package.
SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "GENEVA_ADMIN.NCOTE", line 3
ORA-06512: at line 2
I must be trying to do a select or put some other value into a variable that is too small. The Odd thing is that when I run the stored procedure again (as long as I don't exit from sqlplus)...
SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
PL/SQL procedure successfully completed.
why the process is successful the second (or third, etc) call from the sqlplus but not the first. If I exit sqlplus and then start it again, once more I get the error again.
View 6 Replies
View Related
Jun 9, 2010
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
View 3 Replies
View Related
Jan 12, 2010
I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.
The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..
I want to know what is the difference betn stored procedure and function?
From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).
View 2 Replies
View Related
Feb 13, 2012
I am familiar with WRAP command.It is used in command prompt.
C:> wrap iname=filename.sql
and this thing will convert filename.plb file.
but now I want to wrap my stored Procedure or function.how can i do this thing?it is saved in my disk?
View 10 Replies
View Related
Apr 30, 2013
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies
View Related
Feb 24, 2011
I have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
View 2 Replies
View Related
Apr 9, 2013
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
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
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
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
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
View Related
Sep 16, 2011
I have error ORA-00904: "USERNAME" : identificateur non valide. What is the problem ?
My command impdp :
impdp datapump/password@%ORACLE_SID% DIRECTORY=datapump schemas=gcom INCLUDE=PROCEDURE remap_tablespace=gpao_indx:indx remap_tablespace=gpao_data:data DUMPFILE=%ORA_DUMPFILE% LOGFILE=Imp_%annee%%mois%%jour%%hh%%min%%sec%_%ORACLE_SID%.log
The result :
;;;
Import: Release 10.2.0.4.0 - Production on Jeudi, 15 Septembre, 2011 17:47:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connecté à : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Table maître "DATAPUMP"."SYS_IMPORT_SCHEMA_29" chargée/déchargée avec succès
[Code]....
View 2 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
Jul 29, 2011
I have a requirement in which I have to append LOBs and I have to insert the LOB into a table column. I am facing problem when the data exceeds certain limit. (please note that my program logic is given below, not the exact program)
DECLARE
final_html CLOB;
int_html CLOB;
v_str VARCHAR2(32767);
i number:=0;
BEGIN
dbms_lob.createtemporary(lob_loc => int_html, CACHE => TRUE, dur => dbms_lob.CALL);
dbms_lob.createtemporary(lob_loc => final_html, CACHE => TRUE, dur => dbms_lob.CALL);
DBMS_LOB.OPEN(int_html, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(final_html, DBMS_LOB.LOB_READWRITE);
dbms_output.ENABLE(1000000);
[code]....
when the looping is done for lesser value say for e.g FOR i IN 1..10 loop, the program works fine, but when the looping is done for more values I am getting "-6502-ORA-06502: PL/SQL: numeric or value error" error message.
View 1 Replies
View Related
Dec 18, 2012
create or replace package pckname is
Procedure VALIDATE_USER(p out credentials.pc%TYPE,
u in credentials.uc%TYPE,
c in credentials.sc%TYPE);
end pckname;
I m getting error at line 1 and line 8:
View 1 Replies
View Related
Oct 31, 2010
SQL> ed
Wrote file afiedt.buf
1 begin
2 for i in 3159572060..3159572274 loop
3 insert into recmsisdn(msisdn)
4 values(i);
5 end loop;
6* end;
7 /
begin
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2
View 4 Replies
View Related
Jul 25, 2012
I have an error with this request :
SELECT SEQ_ENTITE.nextval, ENT_CODE_ENT, ENT_GPL_ID, TO_NUMBER('2012')+1,
pkg_etl_fdx.f_getseqexe('LPG_TYE', 'TYE_CODE_CON', ENT_CODE_TEN, 'TYE_SEQ', TO_NUMBER('2012')),
ENT_LIB_ENT, ENT_SEUIL, 'O', 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, ENT_CODE_UNI, ENT_CODE_MERE, ENT_IND_FIN, 0,
0, 0, ENT_NIVEAU, ENT_CODE_TEN, CHAR_TO_LABEL('EXERCICE', 'PUB:EXE'||TO_NUMBER('2012')+1||':'), OLS_GRP
FROM ENTITE
WHERE ENT_OLS_EXE = TO_NUMBER('2012');How to fix ORA-01722 PL/SQL: numeric or value error string ?
The description of the table Entite :
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)
[code]....
View 3 Replies
View Related
Oct 21, 2011
When i try to use the PIPELINED returned function in the for loop, I am getting the below error
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
View 2 Replies
View Related
Sep 4, 2012
What is the first term in the Fibonacci sequence to contain 1000 digits?
DECLARE
f_num INTEGER;
n_num INTEGER;
h_num INTEGER;
[code]...
why I am getting this error?? Is there any problem in my code??
View -1 Replies
View Related
Sep 23, 2012
SQL> declare
2 TYPE id_collection is TABLE of number(6);
3 TYPE ename_collection is TABLE of varchar2(20);
4 id ID_COLLECTION;
5 ename ENAME_COLLECTION;
6 cursor c is select empid,name from Nemp;
7 begin
8 open c;
9 loop
[code]....
Here sub_Nemp is my new table in which i have to insert the values from Nemp old table.Both tables are same like below:-
SQL> create table sub_Nemp(empid number(6),name varchar2(20));
I'm unable to find this error...
View 7 Replies
View Related
Sep 29, 2010
I use stored function in where condition but it gives wrong output but output of stored function is use in where condition it gives correct output. i don't know how to use it.
Scripts:
select get_emp_mgr_no('MHR,ERT,TYU') FROM DUAL;
OUTPUT:
'MHR','ERT','TYU'
SELECT count(1)
FROM BANK_details
where notre in ('MHR','ERT','TYU');
count:
12345
select count(1)
from bank_details
where notre in '('||get_emp_mgr_no('MHR,ERT,TYU')||')';
count
0
It shows wrong output. how to use stored function in where condition?
View 3 Replies
View Related
Dec 7, 2012
How many types of pl/sql value or numeric errors are there and when occurs.Like as follows
1.ORA-06502: PL/SQL: numeric or value error: character string buffer too small
2.ORA-06502: PL/SQL: numeric or value error: number precision too large
View 8 Replies
View Related
May 24, 2011
when I want to create a table.When I run my procedure I received :
PL/SQL: numeric or value error: character string buffer too small
create or replace PROCEDURE p_create_tmp_tables (p_result OUT NUMBER ) IS
string_sql varchar2(1000);
result NUMBER;
BEGIN
string_sql := 'CREATE TABLE TMP_CATEGORIES (CODE_CATEGORY NUMBER(6,0), NAME_CATEGORY VARCHAR2(25 BYTE))';
execute immediate string_sql;
[code]...
View 10 Replies
View Related
Aug 12, 2013
so im running this script
DECLARE
STQ_ID NUMBER;
MESSAGE VARCHAR2(500);
[Code].....
and getting this error ORA-06502: PL/SQL: numeric or value error: NULL index table key value
the code where it is inserting and getting out of it values is:
IF vt_Prs_Code_Ipr.COUNT <> 0 THEN
IF vt_Ist_Code_Ipr(1) IS NOT NULL THEN
IF vr_data.insurance_kind = 'B' THEN
[Code]....
Im running script at PL/SQL Developer and using debugger, i see that it's inserting dates but at some point between point 5.3.2.1.1 till 5.3.2.1.5 it returns no dates :/ basically it needs to stop at 5.3.2.1.2 and in form need return date 02.02.1996, but it returns 01.01.1996 + gives that ORA-06502: PL/SQL: numeric or value error: NULL index table key value
edit: vt_SnpStartDate(v_Idx)is 01.01.1996
vt_SnpEndDate(v_Idx) is 31.12.1996
vt_prd_tbl(v_Idx2).Start_Date is 01.01.1996
vt_prd_tbl(v_Idx2).End_Date is 01.02.1996
at p.5.3.2.1.3 it shows that there is no date in vt_prd_tbl(v_Idx2).Start_Date and is returning ora error, why?
View 10 Replies
View Related
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
May 6, 2008
I am trying to execute this
begin
-- Initialize owa with an empty array, just so we can use htp.
owa.init_cgi_env( param_val=>owa.cgi_var_name );
-- Add a header to avoid dealing with header-related code in htp.
htp.prn('Content-Type:text/plain');
htp.print('');
[code]....
The error I am getting is
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HTP", line 1550
ORA-06512: at line 32
what i feel is that oracle Database UTF8 or AL32UTF8 characterset does not support htp package properly. When i run the code on another characterset, it runs errorfree except in UTF8 or AL32UTF8.
View 7 Replies
View Related
Mar 14, 2013
I have made a report with sql injection. there is no problem with the report. but when i run from the form with following string it gives error.
:global.where_clause := 'where request_type = 5 and request_status in (3, 4, 5) and list_approval_date is null';
And no error is there if i passed the string as
:global.where_clause := 'where request_status in (3, 4, 5) and list_approval_date is null';
REQUEST_TYPE is a database column and it is of number type.
View 9 Replies
View Related
