Array ORA-06533: Subscript beyond count [message #666736] |
Wed, 22 November 2017 04:32 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I am using array.count property to get the number of records and fill next value. I need to ask why P_try in the below example gives "ORA-06533: Subscript beyond count" error. What is the correct way?:
create or replace TYPE VR_TEXT IS TABLE OF VARCHAR2(100) ;
CREATE OR REPLACE PACKAGE test_pkg as
PROCEDURE P_try(
CUR_WARNING OUT SYS_REFCURSOR
);
END test_pkg;
cREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE P_try(
CUR_WARNING OUT SYS_REFCURSOR
)
AS VR_WARNING VR_TEXT;
BEGIN
VR_WARNING:= VR_TEXT(10);
VR_WARNING(VR_WARNING.count+1):= 'war1';
VR_WARNING(VR_WARNING.count+1):= 'war2';
VR_WARNING(VR_WARNING.count+1):= 'war3';
OPEN CUR_WARNING FOR SELECT column_value as warninig FROM TABLE(CAST(VR_WARNING AS VR_TEXT));
END P_TRY;
END test_pkg;
Thanks,
Ferro
[Updated on: Wed, 22 November 2017 04:37] Report message to a moderator
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666746 is a reply to message #666736] |
Wed, 22 November 2017 08:32 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
ORA-06533: Subscript beyond count
*Cause: An in-limit subscript was greater than the count of a varray
or too large for a nested table.
*Action: Check the program logic and explicitly extend if necessary.
SQL> create or replace TYPE VR_TEXT IS TABLE OF VARCHAR2(100) ;
2 /
Type created.
SQL> CREATE OR REPLACE PACKAGE test_pkg as
2 PROCEDURE P_try(
3 CUR_WARNING OUT SYS_REFCURSOR
4 );
5 END test_pkg;
6 /
Package created.
SQL> cREATE OR REPLACE PACKAGE BODY test_pkg AS
2 PROCEDURE P_try(
3 CUR_WARNING OUT SYS_REFCURSOR
4 )
5 AS VR_WARNING VR_TEXT;
6 BEGIN
7 VR_WARNING:= VR_TEXT(10);
8 dbms_output.put_line('>>>>>>>>>>>>>>> count is '||VR_WARNING.count||
9 ', first is '||VR_WARNING.first||', last is '||VR_WARNING.last||
10 ', value is '||VR_WARNING(1));
11 VR_WARNING(VR_WARNING.count+1):= 'war1';
12 VR_WARNING(VR_WARNING.count+1):= 'war2';
13 VR_WARNING(VR_WARNING.count+1):= 'war3';
14
15 OPEN CUR_WARNING FOR SELECT column_value as warninig FROM TABLE(CAST(VR_WARNING AS VR_TEXT));
16 END P_TRY;
17 END test_pkg;
18 /
Package body created.
SQL> set serveroutput on
SQL> var c refcursor
SQL> exec test_pkg.p_try(:c)
>>>>>>>>>>>>>>> count is 1, first is 1, last is 1, value is 10
BEGIN test_pkg.p_try(:c); END;
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "MICHEL.TEST_PKG", line 11
ORA-06512: at line 1
[Updated on: Wed, 22 November 2017 11:08] Report message to a moderator
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666749 is a reply to message #666746] |
Thu, 23 November 2017 00:07 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear Michel,
Thanks for the feedback, I edited the below lines and it works, but I have the following questions:
1- I tried to extend once before adding the warnings but it also failed as count does not give the number of full records, it give the total number of records! Why is that? is there a way to get the actual count of filled records (in other words, is count ever different than last)?
VR_WARNING(VR_WARNING.count):= 'war1'; vr_warning.extend ();
VR_WARNING(VR_WARNING.count):= 'war2'; vr_warning.extend ();
VR_WARNING(VR_WARNING.count):= 'war3';
Thanks,
Ferro
[Updated on: Thu, 23 November 2017 01:47] Report message to a moderator
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666750 is a reply to message #666749] |
Thu, 23 November 2017 02:24 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> cREATE OR REPLACE PACKAGE BODY test_pkg AS
2 PROCEDURE P_try(
3 CUR_WARNING OUT SYS_REFCURSOR
4 )
5 AS VR_WARNING VR_TEXT;
6 BEGIN
7 VR_WARNING:= VR_TEXT();
8 VR_WARNING.EXTEND(3);
9 dbms_output.put_line('>>>>>>>>>>>>>>> count is '||VR_WARNING.count||
10 ', first is '||VR_WARNING.first||', last is '||VR_WARNING.last);
11 for i in VR_WARNING.first..VR_WARNING.last
12 loop
13 if VR_WARNING.exists(i) then
14 dbms_output.put_line('VR_WARNING('||i||') exists and value is "'||VR_WARNING(i)||'"');
15 else
16 dbms_output.put_line('VR_WARNING('||i||') does not exist');
17 end if;
18 end loop;
19 /*
20 VR_WARNING(VR_WARNING.count+1):= 'war1';
21 VR_WARNING(VR_WARNING.count+1):= 'war2';
22 VR_WARNING(VR_WARNING.count+1):= 'war3';
23
24 OPEN CUR_WARNING FOR SELECT column_value as warninig FROM TABLE(CAST(VR_WARNING AS VR_TEXT));
25 */
26 END P_TRY;
27 END test_pkg;
28 /
Package body created.
SQL> exec test_pkg.p_try(:c)
>>>>>>>>>>>>>>> count is 3, first is 1, last is 3
VR_WARNING(1) exists and value is ""
VR_WARNING(2) exists and value is ""
VR_WARNING(3) exists and value is ""
PL/SQL procedure successfully completed.
When you extend, Oracle creates the elements and set them to NULL.
SQL> cREATE OR REPLACE PACKAGE BODY test_pkg AS
2 PROCEDURE P_try(
3 CUR_WARNING OUT SYS_REFCURSOR
4 )
5 AS VR_WARNING VR_TEXT;
6 BEGIN
7 VR_WARNING:= VR_TEXT();
8 VR_WARNING.EXTEND(3);
9 VR_WARNING.DELETE(1,3);
10 dbms_output.put_line('>>>>>>>>>>>>>>> count is '||VR_WARNING.count||
11 ', first is '||VR_WARNING.first||', last is '||VR_WARNING.last);
12 VR_WARNING(VR_WARNING.count+1):= 'war1';
13 VR_WARNING(VR_WARNING.count+1):= 'war2';
14 VR_WARNING(VR_WARNING.count+1):= 'war3';
15 dbms_output.new_line;
16 dbms_output.put_line('>>>>>>>>>>>>>>> count is '||VR_WARNING.count||
17 ', first is '||VR_WARNING.first||', last is '||VR_WARNING.last);
18 for i in VR_WARNING.first..VR_WARNING.last
19 loop
20 if VR_WARNING.exists(i) then
21 dbms_output.put_line('VR_WARNING('||i||') exists and value is "'||VR_WARNING(i)||'"');
22 else
23 dbms_output.put_line('VR_WARNING('||i||') does not exist');
24 end if;
25 end loop;
26 /*
27 OPEN CUR_WARNING FOR SELECT column_value as warninig FROM TABLE(CAST(VR_WARNING AS VR_TEXT));
28 */
29 END P_TRY;
30 END test_pkg;
31 /
Package body created.
SQL> exec test_pkg.p_try(:c)
>>>>>>>>>>>>>>> count is 0, first is , last is
>>>>>>>>>>>>>>> count is 3, first is 1, last is 3
VR_WARNING(1) exists and value is "war1"
VR_WARNING(2) exists and value is "war2"
VR_WARNING(3) exists and value is "war3"
PL/SQL procedure successfully completed.
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666752 is a reply to message #666750] |
Thu, 23 November 2017 03:33 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Thanks a lot @Michel,
Actually this is related to my previous post http://www.orafaq.com/forum/t/204107/ where I was trying to figure out the best way to pass a group of warnings from PLS/SQL to external caller (in my case .net app).
The way this SP is written is the way I thought is best, it will be great if you can give me your feedback.
In the real SP I use Boolean functions and then:
IF NOT MISCILANIOUS.F_CHEK_xyz(TO_NUMBER(ARRAY_INPARAMS(1))) THEN
VR_WARNING.EXTEND(); VR_WARNING(VR_WARNING.LAST):= 'war1';
END IF;
before I finally create the output warning cursor for the caller
OPEN CUR_WARNING FOR SELECT column_value as warning FROM TABLE(CAST(VR_WARNING AS VR_TEXT));
Thanks again,
Ferro
[Updated on: Thu, 23 November 2017 03:38] Report message to a moderator
|
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666789 is a reply to message #666753] |
Sat, 25 November 2017 22:04 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
@BalckSwan
Those are not warnings that can/should be corrected. Those are business-boundary warning messages that application user should take into account while completing a legal operation from business point of view. For example, warning the user that a record with the same amount and beneficiary to the one being entered was found under the same account. Or warning the user that the loan being edited is due in 3-months. The user might be already aware of this and does nothing, or can be alerted and may choose a different treatment.
It would be great if I can benefit from your opinion about this approach of communicating warnings between PL/SQL and external caller.
Thanks,
Ferro
[Updated on: Sat, 25 November 2017 22:09] Report message to a moderator
|
|
|
|
Re: Array ORA-06533: Subscript beyond count [message #666793 is a reply to message #666792] |
Sat, 25 November 2017 23:01 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
@BalckSwan
Thanks for your reply. I got your point and you have all the right not to understand my exact purpose as the word warning can be interpreted differently.
I really would like to benefit from your knowledge, so in other words, consider the need of sending a group of text statements that are not in the database but are rather deduced from 1 or more select statements. Each select statement checks a database query and accordingly decides to send a certain text statement or not. Example: checking the database with a loan number that the user is trying to edit if it is due in 3-month nor not and send a text saying "loan being edited is due in 3-months" if it does. So the text that will be sent is not in the database however a database check is done to see if it should be sent or not among a group of other similar checks all happen when user is editing a loan.
To do this, I figured out that:
- Input: A variable array is the best type to be used inside the SP and can be filled with every check to see if text is going to be sent or not and decide to add this text to the array. I thought of a single varchar variable but this will not allow the caller to call them or show them as separate statements.
- Output: A ref cursor should be the output after casting the object.
- Routine: A SP is better than a function because its easier for the caller to execute it and get the output
Thanks,
Ferro
[Updated on: Sat, 25 November 2017 23:04] Report message to a moderator
|
|
|