Home » SQL & PL/SQL » SQL & PL/SQL » Array ORA-06533: Subscript beyond count (11.0.2.10)
Array ORA-06533: Subscript beyond count [message #666736] Wed, 22 November 2017 04:32 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
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 Go to previous messageGo to next message
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
VR_WARNING.EXTEND(3) 
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
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 Go to previous messageGo to next message
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 #666753 is a reply to message #666752] Thu, 23 November 2017 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I would have thought that warnings & errors should have been detected & corrected prior to deploying code into production.
Exactly what actions will be taken by application when warning occurs?
Re: Array ORA-06533: Subscript beyond count [message #666789 is a reply to message #666753] Sat, 25 November 2017 22:04 Go to previous messageGo to next message
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 #666792 is a reply to message #666789] Sat, 25 November 2017 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Words have meanings & meanings matter.

From your perspective you see them as "warnings"
From my perspective & that of the dumb database, they are merely text strings.

>I am using array.count property to get the number of records and fill next value.
RDBMS do not have records. They have tables with rows.


I don't understand how your business model maps into database objects.
I don't know if your "warnings" have a 1 to 1 relationship, 1 to many relationship, or many to 1 relationship with anything else in your model.
So I am not qualified to judge about your proposed approach because I am clueless about what problem you are trying to solve



Re: Array ORA-06533: Subscript beyond count [message #666793 is a reply to message #666792] Sat, 25 November 2017 23:01 Go to previous message
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

Previous Topic: Oracle Sub-query and AND clause behaviour
Next Topic: help convert mac address string to hex
Goto Forum:
  


Current Time: Wed Apr 17 19:11:39 CDT 2024