Solved

How to extend the PLSQL table collection

Posted on 2011-02-25
8
800 Views
Last Modified: 2012-06-21
Hi,

I have initialized PLSQL tabletype and i just assign the element to the tabletype instead of staring from the 1. My value is 2014829 for tabletype index. When i assign the value i am getting the below error "Subscribe beyond count". When i extend n the element it has entended till 2014829. But i am doing this in the loop. My second value will be 2014830. And again the index extended till 4029659.

How do i stop this? please help me..

Thanks
0
Comment
  • 4
  • 2
  • 2
8 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
please post your code
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility

v_mo_num := TO_NUMBER(REPLACE(v_buffer,v_job_no_prefix,NULL));

                        IF v_mo_exist_check.EXISTS(v_mo_num)
                        THEN
                            v_mo_num_check := FALSE;                            
                        ELSE
                            v_mo_num_check := TRUE;
                            v_mo_exist_check.EXTEND(v_mo_num);                            
                            v_mo_exist_check(v_mo_num)  := v_buffer;
                       END IF;

Open in new window

0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
I posted my code. Thius logic will be in the loop where the value of v_mo_num will be contains the different value.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
please post more of the code...

like the declaration for v_mo_exist_check
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
v_mo_exist_check is just the tabletype declartion "DBMS_SQL.varchar2a"

v_buffer is nothing but the value string. i am just getting only the number value to v_mo_num and passed the value to tabletype element.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
Why not just use a plsql table indexed by binary integer?

Then you don't have to worry about extend.
declare
		TYPE myType IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;
		myArr myType;
begin

	myArr(123) := 'Tom';
	myArr(999) := 'Dick';
	myArr(123456) := 'Harry';


	dbms_output.put_line('count: ' || myArr.count);

end;
/

Open in new window

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
dbms_sql.varchar2a  IS an associative array indexed by binary_integer


that's the problem.  Don't extend
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
GOOD
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now