I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end…
undefine numero
set serveroutput on
accept numero prompt 'Type # between 100 and 999: '
declare
i number:=1;
a char(25);
b char(1);
c varchar2(10);
d number;
begin
c := №
d := length(c);
b := substr(c, i, 1);
while i <= d loop
if b = '1' then
a:= a||'one ';
end if;
i := i+1;
end loop;
dbms_output.put_line('The number is '||a);
end;
/
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 - "PL/SQL: numeric or value error%s"
FIXED by changing how I declared the variable «a» to:
a varchar2(2000);
*Notice that here, the significant change is to use VARCHAR2 instead of CHAR (not the bigger length). According to @user272735 ‘s answer, that’s the key.
Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.
ORA-06502 Cause
The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things:
- A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
- A non-numeric value is being assigned to a numeric variable.
- A value of NULL is being assigned to a variable which has a NOT NULL constraint.
Let’s take a look at the solutions for each of these causes.
The solution for this error will depend on the cause.
Let’s see an example of each of the three causes mentioned above.
Solution 1: Value Larger than Variable (Number Precision Too Large)
In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.
Let’s create this procedure which declares and then sets a variable:
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(3);
BEGIN
testNumber := 4321;
END;If we compile it, it compiles with no errors.
Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;We get an error:
Error starting at line : 8 in command - EXEC TestLargeNumber Error report - ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
The error we’ve gotten is “ORA-06502: PL/SQL: numeric or value error: number precision too large”. It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.
This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we’re setting it to a few lines later is 4 digit long (4321).
So, the value is too large for the variable.
To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).
In our example , we can change the size of the variable.
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(4);
BEGIN
testNumber := 4321;
END;Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;PL/SQL procedure successfully completed.
The procedure runs successfully. We don’t get any output (because we didn’t code any in), but there are no errors.
Read more on the Oracle data types here.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Solution 2: Non-Numeric Value
Another way to find and resolve this error is by ensuring you’re not setting a numeric variable to a non-numeric value.
For example, take a look at this function.
CREATE OR REPLACE PROCEDURE TestNonNumeric
AS
testNumber NUMBER(4);
BEGIN
testNumber := 'Yes';
END;Procedure TESTNONNUMERIC compiled
The procedure compiles successfully. Now, let’s fun the function.
EXEC TestNonNumeric;Error starting at line : 8 in command - EXEC TestNonNumeric Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
The error we get is “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”.
This happens because our variable testNumber is set to a NUMBER, but a few lines later, we’re setting it to a string value which cannot be converted to a number
To resolve this error:
- Ensure the value coming in is a number and not a string.
- Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
- Convert your string to the ASCII code that represents the string using the ASCII function.
- Change the data type of your variable (but check that your code is getting the right value first).
The solution you use will depend on your requirements.
Solution 3: NOT NULL Variable
This error can appear if you try to set a NULL value to a NOT NULL variable.
Let’s take a look at this code here:
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
testNumber := nullValue;
END;Procedure TESTNONNULL compiled
Now, the reason we’re using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it’s probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.
Let’s run this function now.
Error starting at line : 9 in command - EXEC TestNonNull Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.TESTNONNULL", line 6 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
We get the ORA-06502 error.
This error message doesn’t give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.
To be sure, we can output some text in our demo when it is null.
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
IF (nullValue IS NULL) THEN
dbms_output.put_line('Value is null!');
ELSE
testNumber := nullValue;
END IF;
END;Now let’s call the procedure.
EXEC TestNonNull;Value is null!
The output shows the text message, indicating the value is null.
ORA-06502 character string buffer too small
This version of the error can occur if you set a character variable to a value larger than what it can hold.
When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.
For example:
DECLARE
charValue VARCHAR2(5);
BEGIN
charValue := 'ABCDEF';
END;If I compile this code, I get an error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
This happens because the variable is 5 characters long, and I’m setting it to a value which is 6 characters long.
You could also get this error when using CHAR data types.
DECLARE
charValue CHAR(5);
BEGIN
charValue := 'A';
charValue := charValue || 'B';
END;ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 5
This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.
When you try to concatenate a value of B to it, the resulting value is ‘A B’, which is 6 characters.
To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.
ORA-06502: pl/sql: numeric or value error: null index table key value
Sometimes you might get this error message with the ORA-06502 error:
ORA-06502: pl/sql: numeric or value error: null index table key value
This means that either:
- Your index variable is not getting initialized, or
- Your index variable is getting set to NULL somewhere in the code.
Check your code to see that neither of these two situations are happening.
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
You might also get this specific error message:
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.
For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.
You may also get this error because some data types in PL/SQL have different lengths in SQL.
To resolve this, declare your variables as the same type as the SQL table:
type t_yourcol is table of yourtable.yourcol%TYPE;So, that’s how you resolve the ORA-06502 error.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Applies to:
______________________________________________________________________________
PL/SQL — Version 7.3.4.5 and later
Information in this document applies to any platform.
***Checked for relevance on 23-Nov-2015***
Goal:
_______________________________________________________________________________
The
purpose of this document is to provide guidance in debugging ORA-6502 errors.
It is intended for programmers and DBAs attempting to determine the cause of
this error in their application. It is written in a PL/SQL context, but
the concept applies to other programmatic interfaces such as Oracle Reports and
Forms, JDBC, OCI, and Precompilers.
Solution: _______________________________________________________________________________
How to Determine the Cause
of ORA-6502 Errors
The error description is very generic, but we attempt to further define its
meaning and some potential causes in this article. We also show you how
to debug and further narrow down the cause of this error.
This is not the type of error for which you can contact Oracle Support and
obtain an immediate solution. The causes are unique to every customer’s
application and you are in a better position to debug this than we. In
the majority of cases, you will find that the problem lies within your
application code, data source, or table definitions.
The error message is:
06502, 00000, «PL/SQL: numeric or value error%s»
// *Cause:
// *Action:
The first step when troubleshooting this problem is to identify each offending
SQL statement and determine which application or program unit generates this
statement. Oracle provides an event tracing facility that can be used to
identify these offending SQL statements. Modify your init.ora file by
setting the following event and restart your database.
event=»6502 trace name errorstack level 12″
When you rerun your application, the ora-6502 will be captured. By
examining this trace file, you are then able to identify the offending SQL
statement, and with your knowledge of your application, you can relate this
statement to the exact block of code within PL/SQL (or other programmatic interface)
used to generate this statement.
Some observations to make at this point include:
o Is the same SQL statement always being executed when this error occurs?
o Do other SQL statements raise this error?
o Is the error intermittent or consistently reproducible?
Once you isolate the offending SQL statement, you are ready to set SQL tracing
to capture the bind values. This can be done at the instance level or at
the session level.
SQL> alter system set timed_statistics = true;
SQL> alter system set sql_trace=true;
If you narrow this down to one isolated program unit, it may be more
appropriate to set this at the session level within the PL/SQL block.
create or replace
begin
execute_immediate(‘alter session set timed_statistics = true’);
execute_immediate(‘alter session set sql_trace = true’);
…
— execute your PLSQL block
— execute the target SQL statement
end;
After the program finishes, review the raw SQL trace file. The raw trace
file shows you the SQL statement plus the value of bind variables being bound
to the placeholders in the statement.
Locate your SQL statement. The following is an extract of a trace
file. Pay attention to the SQL statement, cursor number, bind number, and
bind value. In this example, the cursor number is 19 and is associated
with parse number 19 and bind number 19. You can clearly see the value
being bound to the place holder is ‘MIKE’.
==================================================
PARSING IN CURSOR #19 …
SELECT ENAME FROM EMP WHERE ENAME = :1 …
END OF STATEMENT
PARSE #19
BINDS #19
BIND 0:
VALUE=MIKE
EXEC
WAIT
WAIT
===================================================
To summarize our progress thus far, we determined the SQL statement in question
and the value being bound when the error occurs.
Review your PL/SQL code for invalid datatype assignments. These could
come in the form of an incorrect size or type assignment. Compare the
size of the PL/SQL variable you declared for this placeholder to the size of
thevalue you have assigned it.
If the size of the string is larger than the size you have declared for the
variable, then increase the size of the variable to accommodate larger values.
If you assigned an incompatible type, then you must recode using a compatible
type.
The obvious example of this
is assigning a number to a varchar2.
declare
x varchar2(10);
y number;
begin
y := ‘mike’; — valid ora-6502
x := ‘abcdefghikl’ — valid ora-6502
…
end;
Next, compare your defined column sizes to the defined variable sizes that are
part of your query. If the size of the value being fetched is larger than
your defined variable size, then either make your variable size larger or
decrease the size of your column.
declare
v_ename varchar2(3); — too small
begin
select ename into v_ename from emp where ename=’MILLER’;
end;
If your error cannot be isolated to a SQL statement but seems to occur at
package initialization, then review your initialization code to see if you have
a mismatch between the declared variable size or type and what is being
assigned.
Review all areas of code where functions or procedures are called and
parameters are passed. Ensure that you declared the right amount of space
and the appropriate type.
If you use any built-in SQL character or number conversion functions, then be
careful about assigning spaces to a number.
DECLARE
my_number NUMBER(10);
BEGIN
my_number := ‘ ‘;
END;
/
DECLARE
*
ERROR at line 1:
ORA-6502: PL/SQL: numeric or value error: character to number conversion
error
Another potential cause for the error can be due to size of the values returned
from the functions or the size of the variable passed as OUT
parameters. So we need to carefully analyze such cases and check for
proper sizes of the placeholders of returned values from functions.
For example:
Case 1 : Return Value larger than the receiving buffer
create or replace function ret return varchar2
as
begin
return ‘asdf’;
end;
declare
x varchar2(3);
begin
x:=ret;
end;
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4Case 2 : OUT parameters not large enough
create or replace PROCEDURE foo( bar OUT VARCHAR2 ) IS
BEGIN
SELECT ‘abcd’ INTO bar FROM dual;
END;
declare
x varchar2(3);
begin
foo(x);
end;
Other Causes:
If you have a case where the same data set bound to the same SQL statement
reproduces an intermittent ora-6502 error, then you may need to contact Oracle
Support. Provide the tracing you have done so far.
References:
_____________________________________________________________________________________________
Oracle [ID 139548.1]
ORA-06502: PL/SQL: numeric or value error error occurs when the not null variable is assigned a null value or when the assigned numeric value is greater than the allowed numeric datatype value or when a non-numeric value is assigned to a numeric variable. The numeric not null variable should be assigned a value that is within the maximum specified limit. If a non-numeric character, such as a number, should not be assigned to a numeric datatype. Otherwise, the error ORA-06502: PL/SQL: numeric or value error will be thrown
If a numeric variable is declared as not null and a null value is assigned to it, the null value cannot be assigned to a not null variable. The error message ORA-06502: PL/SQL: numeric or value error will be shown. In another case, if the value of a numeric variable is greater than the maximum specified numeric value, the value cannot be stored in the variable. The error message ORA-06502: PL/SQL: numeric or value error will be shown. If a non-numeric variable is assigned a numeric datatype, the non-numeric variable can not be assigned. The error ORA-06502: PL/SQL: numeric or value error will be thrown.
Problem
In the example below, there are two numeric variables empid and managerid. The empid is declared as the numeric not null variable. It is assigned with 1 as default. If the empid is assigned with managerid that contains null, the empid can not store a null value. The error ORA-06502: PL/SQL: numeric or value error will be thrown.
declare
empid numeric(4) not null := 1;
managerid numeric(4);
begin
empid := managerid;
end;Oracle Error
The below oracle error is thrown if a numeric not null variable is assigned with a null value. The following error will be shown when you run the PL/SQL code in oracle.
declare
empid numeric(4) not null := 1;
managerid numeric(4);
begin
empid := managerid;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 - "PL/SQL: numeric or value error%s"Cause
An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action
Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
Solution 1
The variable assigned with a null value should be changed to assign with a value. If a value is assigned with a not null numeric variable, the error will be resolved.
declare
empid numeric(4) not null := 1;
managerid numeric(4);
begin
managerid :=2;
empid := managerid;
end;Output
PL/SQL procedure successfully completed.Solution 2
The null value can not be assigned to a not null numeric variable. If the null value is allowed to store in the variable, the variable should be declared as numeric null data type. This will resolved the null value assignment in the variable.
declare
empid numeric(4);
managerid numeric(4);
begin
empid := managerid;
end;Output
PL/SQL procedure successfully completed.Solution 3
If the value could not be predicted and very unlikely the null value is inserted, then you can not change the data type or validate the value. The easiest approach is to handle the error.
declare
empid numeric(4) not null := 1;
managerid numeric(4);
begin
empid := managerid;
exception
WHEN OTHERS THEN
empid :=0;
end;Output
PL/SQL procedure successfully completed.I have a below Package which is giving error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Please let me know what is going wrong here.
CREATE OR REPLACE PACKAGE BODY PKG_H
IS
PROCEDURE PROC_SUBMIT_H
(
Pout_Rqst_Id OUT NVARCHAR2,
Pout_err_cd OUT VARCHAR2,
Pout_err_msg OUT VARCHAR2,
Pin_Rqst_Type_Id IN NUMBER,
Pin_Attachment IN NVARCHAR2,
Pin_Brand_Id IN NVARCHAR2,
Pin_Prop_Id IN NVARCHAR2,
-- Pin_Htl_Stat_Rqst_Typ_ID IN NUMBER,
Pin_Orcl_Acct_Num IN NVARCHAR2, -- NUMBER, /* Changed on 22.09.2011,as stated by FIS Team */
Pin_ORCL_User_Name IN NVARCHAR2,
Pin_Rstn_Id IN NUMBER,
Pin_Rstn_Name IN NVARCHAR2,
Pin_Rstn_Start_Date IN DATE,
Pin_Rstn_End_Date IN DATE,
-- Pin_Change_Type_Ind IN NVARCHAR2,
Pin_Trans_Time_Orcl IN TIMESTAMP,
Pin_Fis_Acct_Stat_Prsnt_Id IN NUMBER,
Pin_Fis_Acct_Future_Stat IN NUMBER,
Pin_Auto_Ind IN NVARCHAR2,
-- Pin_Stat_Change_Resn_ID IN NUMBER, /* changed due to ETL requirement as on 17.09.2011 */
Pin_Stat_Change_Resn_Desc IN NVARCHAR2, /* changed due to ETL requirement as on 17.09.2011 */
Pin_Brand_Dot_Com_Ind IN NVARCHAR2,
-- Pin_Expdt_Ind IN NVARCHAR2,
-- Pin_Expdt_Dt IN DATE,
Pin_Rqstr_Id IN NVARCHAR2,
Pin_Impn_Id IN NUMBER,
-- Pin_Agent_Id IN NVARCHAR2, /* Changed as on 22.09.2011 */
-- Pin_Agent_Name IN NVARCHAR2, /* Changed as on 22.09.2011 */
Pin_File_Name IN NVARCHAR2,
Pin_Prov_Date IN DATE
-- Pin_Rqst_Stat_ID IN NUMBER
-- Pin_Prov_Time IN DATE
)
IS
-- lv_err_cd VARCHAR2(10);
-- lv_err_msg VARCHAR2(4000);
Ln_Cnt NUMBER;
DUP_VAL EXCEPTION;
lv_rqst_id NVARCHAR2(20);
Ln_rqst_stat_id_it NUMBER;
Ln_rqst_stat_id_Q NUMBER;
Ln_rqst_category_id NUMBER;
Ln_Stat_Change_Resn_Id NUMBER;
-- Ln_Htl_Stat_Rqst_Typ_Id NUMBER;
lt_data_01 STRINGTABLETYPE := STRINGTABLETYPE();
lt_data_02 STRINGTABLETYPE := STRINGTABLETYPE();
BEGIN
SELECT fnc_gen_request_id
INTO Lv_rqst_id
FROM dual;
SELECT rqst_stat_id
INTO Ln_rqst_stat_id_it
FROM rqst_stat_mst
WHERE rqst_stat_desc = 'In Transmit';
SELECT rqst_stat_id
INTO Ln_rqst_stat_id_Q
FROM rqst_stat_mst
WHERE rqst_stat_desc = 'Pending';
SELECT COUNT(1)
INTO Ln_Cnt
FROM HOTEL_STAT_RQST
WHERE FILE_NAME=Pin_File_Name;
IF Ln_Cnt >0 then
RAISE DUP_VAL;
END IF;
IF Pin_Stat_Change_Resn_Desc IS NOT NULL THEN
SELECT STAT_CHANGE_RESN_ID
INTO Ln_Stat_Change_Resn_Id
FROM STAT_CHANGE_RESN_MST
WHERE UPPER(STAT_CHANGE_RESN_DESC)=UPPER(TRIM(Pin_Stat_Change_Resn_Desc));
END IF;
DELETE
FROM HOTEL_STAT_RQST
WHERE RQST_ID=lv_rqst_id;
INSERT INTO HOTEL_STAT_RQST
(RQST_ID
,RQST_TYPE_ID
,RQST_STAT_ID
,BRAND_ID
,PROPERTY_ID
,STAT_CHANGE_RESN_ID
-- ,HOTEL_STAT_RQST_TYPE_ID
,ORCL_ACCT_NUM
,ORCL_USER_NAME
,TRANS_TIME_ORCL
,FIS_ACCOUNT_STATUS_PRESENT_ID
,FIS_ACCT_FUTURE_STAT
,RSTCTN_ID
,RSTCTN_NAME
,RSTCTN_STRT_DT
,RSTCTN_END_DT
-- ,RSTCTN_PREV_STRT_DT /* SCHEMA CHANGED */
-- ,RSTCTN_PREV_END_DT /* SCHEMA CHANGED */
-- ,PREV_RSTN_ID /* SCHEMA CHANGED */
,AUTO_IND
-- ,CHANGE_TYPE_IND
,BRAND_DOT_COM_IND
,RQSTR_ID
,IMPN_ID
,EXPDT_IND
,EXPDT_DT
-- ,PROVSN_STAT /* SCHEMA CHANGED */
-- ,PROVSN_TIME /* SCHEMA CHANGED */
,CREATED_ON
,UPDATED_BY
,UPDATED_ON
,FILE_NAME
,PROV_DATE
)
VALUES
(
lv_rqst_id
,Pin_Rqst_Type_Id
,Ln_rqst_stat_id_it
,Pin_Brand_Id
,Pin_Prop_Id
,Ln_Stat_Change_Resn_Id /* changed due to ETL requirement as on 17.09.2011 */
-- ,Pin_Htl_Stat_Rqst_Typ_ID
,Pin_Orcl_Acct_Num
,Pin_ORCL_User_Name
,Pin_Trans_Time_Orcl
,Pin_Fis_Acct_Stat_Prsnt_Id
,Pin_Fis_Acct_Future_Stat
,Pin_Rstn_Id
,Pin_Rstn_Name
,Pin_Rstn_Start_Date
,Pin_Rstn_End_Date
-- ,NULL /* SCHEMA CHANGED */
-- ,NULL /* SCHEMA CHANGED */
-- ,NULL /* SCHEMA CHANGED */
,Pin_Auto_Ind
-- ,Pin_Change_Type_Ind
,Pin_Brand_Dot_Com_Ind
,Pin_Rqstr_Id
,Pin_Impn_Id
,NULL
,NULL
-- ,NULL /* SCHEMA CHANGED */
-- ,Pin_Prov_Time /* SCHEMA CHANGED */
,SYSDATE
,Pin_Rqstr_Id
,SYSDATE
,Pin_File_Name
,Pin_Prov_Date
);
IF Pin_Attachment IS NOT NULL THEN
DELETE
FROM attach_ref
WHERE rqst_id=lv_rqst_id;
SELECT CAST(SPLIT(Pin_Attachment,'|') AS STRINGTABLETYPE) INTO lt_data_01 FROM DUAL;
FOR i_outer IN 1..lt_data_01.COUNT LOOP
SELECT CAST(SPLIT(lt_data_01(i_outer),'~')AS STRINGTABLETYPE) INTO lt_data_02 FROM DUAL;
INSERT INTO attach_ref
(
rqst_id,
attach_id, -- SEQUENCE
attach_ind,
attach_file_name,
file_path,
ord_num
)
VALUES(
lv_rqst_id,
attach_id_seq.NEXTVAL, -- SEQUENCE
'REQUESTOR',
lt_data_02(1),
lt_data_02(2),
i_outer
);
END LOOP;
END IF;
DELETE FROM rqst_queue WHERE rqst_id=lv_rqst_id;
INSERT INTO rqst_queue
(
rqst_id,
prnt_rqst_id,
queu_start_time,
queu_end_time,
agnt_id,
agnt_name,
property_id,
src_sys,
tgt_sys,
queu_stat_ind
)
VALUES
(
lv_rqst_id,
NULL,
SYSDATE,
NULL,
NULL,
NULL,
Pin_Prop_Id,
'RQT',
'SFDC',
Ln_rqst_stat_id_Q
);
IF Pin_Rqst_Type_Id IS NOT NULL THEN
SELECT rqst_category_id
INTO ln_rqst_category_id
FROM rqst_type_mst
WHERE rqst_type_id = Pin_Rqst_Type_Id;
END IF;
DELETE
FROM rqst_sumry
WHERE rqst_id = lv_rqst_id;
INSERT INTO rqst_sumry
(
rqst_id,
rqst_type_id,
prnt_rqst_id,
brand_id,
property_id,
expdt_ind,
expdt_dt,
rqstr_id,
rqst_stat_id,
compln_dt,
estm_compln_time,
rqst_category_id,
submission_dt
)
VALUES
(
lv_rqst_id,
Pin_Rqst_Type_Id,
NULL,
Pin_Brand_Id,
Pin_Prop_Id,
NULL,
NULL,
Pin_Rqstr_Id,
Ln_rqst_stat_id_it,
NULL,
NULL,
ln_rqst_category_id,
SYSDATE
);
COMMIT;
Pout_Rqst_Id := lv_rqst_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
Pout_err_cd := SQLCODE;
Pout_err_msg := 'STAT_CHANGE_RESN_MISMATCH';
WHEN DUP_VAL THEN
Pout_err_cd := SQLCODE;
Pout_err_msg := 'DUPLICATE';
WHEN OTHERS THEN
ROLLBACK;
Pout_err_cd := SQLCODE;
Pout_err_msg := SUBSTR(SQLERRM, 1 , 4000);
END PROC_SUBMIT_H;
END PKG_H;
/