Collection Help

[ExecMan]

Hi,

I have a multi-level collection, indexed by VARCHAR2. After I fill it
I try and traverse it and print out data and I'm getting an error.
Can anyone see maybe what I am doing wrong?? Here is my code.

The actual error is referring to the first FOR index1 loop.

Thanks in advance!


TYPE order_rec IS RECORD (
order_date DATE,
order_cnt NUMBER,
order_amt NUMBER);

TYPE order_tab IS TABLE OF order_rec INDEX BY VARCHAR2(30);

TYPE registration_rec IS RECORD (
date_registered DATE,
total_registered NUMBER,
order_data ORDER_TAB);

TYPE registration_tab IS TABLE OF registration_rec INDEX BY
VARCHAR2(30);

v_order_tab REGISTRATION_TAB;

BEGIN
.
.

FOR index1 IN v_order_tab.FIRST .. v_order_tab.LAST LOOP
v_string1 := TO_CHAR(v_order_tab(index1).date_registered,'Month
YYYY') || ',' || v_order_tab(index1).total_registered;
v_string2 := NULL;
FOR index2 IN v_order_tab(index1).order_data.FIRST ..
v_order_tab(index1).order_data.LAST LOOP
v_string2 := v_string2 ||
TO_CHAR(v_order_tab(index1).order_data(index2).order_date,'Month') ||
',' ||

v_order_tab(index1).order_data(index2).order_cnt || ',' ||

v_order_tab(index1).order_data(index2).order_amt;
END LOOP;

UTL_FILE.PUT_LINE(v_file_id,v_string1 || ',' || v_string2);
END LOOP;


ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number
conversion error
ORA-06512: at line 80

[dombrooks]

The FOR LOOP with FIRST and LAST a) only works for index-by integers and b) is not great for sparse collections.

A WHILE loop that iterates through the collection using .NEXT suits all scenarios.

e.g. something like:

DECLARE
...
idx VARCHAR2(30);
...
BEGIN
idx := v_order_tab.FIRST;
WHILE (idx IS NOT NULL)
LOOP
...
idx := v_order_tab.NEXT(idx);
END LOOP;
...
END;

[onedbguru]

Dom - WRONG!!

Actual the error is pretty self explanatory The OP is trying to concatenate a number to a string (VARCHAR2). This is NOT java or PHP.

this is the problem:
...|| ',' || v_order_tab(index1).total_registered;
total_registered is defined as a NUMBER data type.

[Mladen Gogala]

On Wed, 14 Mar 2012 18:16:36 -0700, onedbguru wrote:

>
> Dom - WRONG!!
>
> Actual the error is pretty self explanatory The OP is trying to
> concatenate a number to a string (VARCHAR2). This is NOT java or PHP.
>
> this is the problem:
> ...|| ',' || v_order_tab(index1).total_registered;
> total_registered is defined as a NUMBER data type.

That has nothing to do with the problem. You can concatenate number to
string nicely, it will get converted to string:

1 declare
2 v_year number:=2012;
3 v_election varchar2(64):= 'The next election year is:';
4 v_sentence varchar2(128):=v_election||v_year;
5 begin
6 dbms_output.put_line(v_sentence);
7* end;
SQL> /
The next election year is:2012

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

I defined VARCHAR2 variable v_sentence as a concatenation of number to
string. It worked just fine. I agree with Dominic.


--
http://mgogala.byethost5.com

[dombrooks]

I admit I fired off my reply without even checking it, not having access to a db at the time, but I stand by it given there's no standalone test case and no indication of which is line 80.

SQL> DECLARE
2 TYPE t1 IS TABLE OF NUMBER INDEX BY VARCHAR2(1);
3 v1 t1;
4 BEGIN
5 v1('A') := 1;
6 FOR i IN v1.FIRST .. v1.LAST
7 LOOP
8 DBMS_OUTPUT.PUT_LINE(v1(i));
9 END LOOP;
10 END;
11 /
DECLARE
*

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 6


SQL> DECLARE
2 TYPE t1 IS TABLE OF NUMBER INDEX BY VARCHAR2(1);
3 v1 t1;
4 idx VARCHAR2(30);
5 BEGIN
6 v1('A') := 1;
7 idx := v1.FIRST;
8 WHILE (idx IS NOT NULL)
9 LOOP
10 DBMS_OUTPUT.PUT_LINE(v1(idx));
11 idx := v1.NEXT(idx);
12 END LOOP;
13 END;
14 /
1

PL/SQL procedure successfully completed.

SQL>