Home » SQL & PL/SQL » SQL & PL/SQL » Regarding error : Subscript beyond count
Regarding error : Subscript beyond count [message #251751] Mon, 16 July 2007 09:22 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
PROCEDURE up_value_set_blk(p_upload_phase IN VARCHAR2 DEFAULT NULL,       
                           p_upload_mode IN VARCHAR2,
                           p_custom_mode IN VARCHAR2 DEFAULT NULL) is
                cursor cur_flex_load is select * from value_set_stg;
                type fnd_flex_set_type is table of fnd_flex_value_sets%rowtype;
                type value_set_stg_type is table of value_set_stg%rowtype;
                value_set_t value_set_stg_type;
                fnd_flex_t fnd_flex_set_type := fnd_flex_set_type();
 BEGIN
        open cur_flex_load;
        fetch cur_flex_load bulk collect into value_set_t;
        FOR i in value_set_t.first..value_set_t.last LOOP
             IF (get_vst_set(p_flex_value_set_name => value_set_t(i).flex_value_set_name, 
                         x_vst_set => fnd_flex_t(i))) THEN      
                              NULL;
             END IF;  
         END LOOP;
 END;         

exec up_value_set_blk('BEGIN','NLS','FORCE');
BEGIN up_value_set_blk('BEGIN','NLS','FORCE'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "APPS.FND_FLEX_LOADER_APIS_BLK"
ORA-06512: at line 1
       


The error occurs coz of this stmt

get_vst_set(p_flex_value_set_name => value_set_t(i).flex_value_set_name, x_vst_set => fnd_flex_t(i))

Let me know what should be done ?
Re: Regarding error : Subscript beyond count [message #251753 is a reply to message #251751] Mon, 16 July 2007 09:52 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
get_vst_set(p_flex_value_set_name => value_set_t(i).flex_value_set_name,
            x_vst_set => fnd_flex_t(i))

You should not reference to fnd_flex_t(i) as this set is empty.
As I do not know get_vst_set function, I cannot suggest how to fix it properly.
Re: Regarding error : Subscript beyond count [message #251860 is a reply to message #251751] Tue, 17 July 2007 00:24 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member

desc fnd_flex_value_sets
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 FLEX_VALUE_SET_ID                         NOT NULL NUMBER(10)
 FLEX_VALUE_SET_NAME                       NOT NULL VARCHAR2(60)
 LAST_UPDATE_DATE                          NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER(15)
 CREATION_DATE                             NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER(15)
 LAST_UPDATE_LOGIN                         NOT NULL NUMBER(15)

FUNCTION get_vst_set(p_flex_value_set_name IN VARCHAR2,
                     x_vst_set OUT nocopy vst_set_type)
  RETURN BOOLEAN IS                                                             
     l_func_name VARCHAR2(80);                              
BEGIN                               
   SELECT * INTO x_vst_set FROM fnd_flex_value_sets WHERE flex_value_set_name = p_flex_value_set_name;
   RETURN TRUE;      
EXCEPTION 
   WHEN no_data_found THEN
      RETURN FALSE;                                           
END get_vst_set; 




This is what get_vst_set does .
Let me know what should be done
Re: Regarding error : Subscript beyond count [message #251867 is a reply to message #251860] Tue, 17 July 2007 00:47 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Does this work?


PROCEDURE up_value_set_blk(p_upload_phase IN VARCHAR2 DEFAULT NULL,       
                           p_upload_mode IN VARCHAR2,
                           p_custom_mode IN VARCHAR2 DEFAULT NULL) is
                cursor cur_flex_load is select * from value_set_stg;
                type fnd_flex_set_type is table of fnd_flex_value_sets%rowtype;
                type value_set_stg_type is table of value_set_stg%rowtype;
                value_set_t value_set_stg_type;
fnd_flex_t fnd_flex_set_type := fnd_flex_set_type();
 BEGIN
        open cur_flex_load;
        fetch cur_flex_load bulk collect into value_set_t;
        FOR i in value_set_t.first..value_set_t.last LOOP

                               fnd_flex_t.extend;

             IF (get_vst_set(p_flex_value_set_name => value_set_t(i).flex_value_set_name, 
                         x_vst_set => fnd_flex_t(i))) THEN      
                              NULL;
             END IF;  
         END LOOP;
 END;         






Re: Regarding error : Subscript beyond count [message #251949 is a reply to message #251867] Tue, 17 July 2007 04:13 Go to previous message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Thanks ...that works
Previous Topic: [PACKAGE PL/SQL] - Creation d'un curseur...
Next Topic: query problem
Goto Forum:
  


Current Time: Mon Dec 05 02:33:14 CST 2016

Total time taken to generate the page: 0.08949 seconds