pl/sql nested table assignment issue in APEX

Question

All,

I've been fighting this issue for a little while, now, and it seems like there's something simple I'm missing, but I haven't been able to figure it out.

I'm trying to use a pl/sql nested table in APEX to collate and return data from disparate tables to a page view.

As a test, I created a nested table object and type:

CREATE OR REPLACE TYPE  "E_TEST" as object (
col1 varchar2(8),
col2 varchar2(16)
)
/

CREATE OR REPLACE TYPE  "E_TEST_TAB" as table of e_test
/

When I attempt to populate the nested table with this code:

declare
l_test e_test_tab := e_test_tab();
begin
l_test.extend;
l_test(l_test.last) := e_test_tab('testing','testing12345');
end

I get these errors:

ORA-06550: line 5, column 24:
PLS-00306: wrong number or types of arguments in call to 'E_TEST_TAB'
ORA-06550: line 5, column 24:
PLS-00306: wrong number or types of arguments in call to 'E_TEST_TAB'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

I've been searching for an answer for a couple of days, but everything I can see says I'm creating and populating the object correctly, and searches related to the PLS-00306 error don't return much specific to the nested table context. I'm new to pl/sql, so there's a high probability that I've missed something simple, but the folks I know who might be able to help me don't use a lot of collections, so they're stumped by this one, as well.

Thanks in advance for your assistance.

Justin

Maheswaran Ravisankar · Accepted Answer · 2014-04-10 12:41:33Z

3

declare
l_test e_test_tab := e_test_tab();
begin
l_test.extend;
l_test(l_test.last) := e_test('testing','testing12345');
end;
/

It has to be e_test() while assignment of individual record.
Only while initializing the nested collection we use e_test_tab()

answered Apr 10, 2014 at 12:41

Maheswaran Ravisankar

17.9k6 gold badges51 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juheimbu Over a year ago

Thank you! Now, I need to get this to work in my much more complex function, but I have a place to begin sorting it out.

Richard Pascual Over a year ago

I had similar problems for a few weeks too. Just keep track of the progression from the record type to the table type. It's really subtle but makes all the difference.

Collectives™ on Stack Overflow

pl/sql nested table assignment issue in APEX

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related