Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT AND TABLE JOINS.. please help!!
BULK COLLECT AND TABLE JOINS.. please help!! [message #19840] Fri, 12 April 2002 08:35 Go to next message
Kalpa
Messages: 21
Registered: February 2002
Junior Member
When i try to use the FULLY QUALIFIED TABLE NAMES, THE PROCEDURE WILL BE CREATED SUCCESSFULLY BUT DOES NOT WHEN I TRY TO EXECUTE IT, IT GIVES ME ERROR.

1 create or replace procedure test_bulk_collect
2 IS
3 TYPE tab_state IS TABLE OF tbl_servicestateterritoryref.servicestate%TYPE;
4 TYPE tab_count IS TABLE OF INTEGER;
5 TYPE tab_amount IS TABLE OF NUMBER(11,2);
6 TYPE tab_date IS TABLE OF DATE;
7 v_state tab_state;
8 v_count tab_count;
9 v_amount tab_amount;
10 v_date tab_date;
11 BEGIN
12 select to_char(b.deposittime,'DD-MON-YYYY'),
13 a.servicestate,
14 count(*),
15 TO_CHAR(sum(b.totalamount/100),'L999G999G999G999D99MI')
16 bulk collect into
17 v_date,v_state,v_count,v_amount
18 from tbl_servicestateterritoryref a, tbl_claimshistory b
19 where
20 b.deposittime >= to_timestamp('01-FEB-2002','DD-MON-YYYY')
21 and b.deposittime < to_timestamp('01-FEB-2002','DD-MON-YYYY') + 1
22 and a.servicestate = b.servicestate
23 and a.servicestate = 'OK'
24 and b.billcode = '0'
25 group by b.deposittime,a.servicestate
26 order by b.deposittime,a.servicestate asc;
27 for i IN 1..v_state.COUNT LOOP
28 DBMS_OUTPUT.PUT_LINE(v_date(i));
29 DBMS_OUTPUT.PUT_LINE(v_state(i));
30 DBMS_OUTPUT.PUT_LINE(v_count(i));
31 DBMS_OUTPUT.PUT_LINE(v_amount(i));
32 END LOOP;
33* end test_bulk_collect;
SQL> /

Procedure created.

SQL> EXECUTE test_bulk_collect();
BEGIN test_bulk_collect(); END;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "D1.TEST_BULK_COLLECT", line 12
ORA-06512: at line 1

aND THEN WHEN I DON'T USE FULLY QUALIFIED NAMES, IT GIVES ME ERROR SAYING INVALID COLUMN NAME. LIKE BELOW:

1 create or replace procedure test_bulk_collect
2 IS
3 TYPE tab_state IS TABLE OF tbl_servicestateterritoryref.servicestate%TYPE;
4 TYPE tab_count IS TABLE OF INTEGER;
5 TYPE tab_amount IS TABLE OF NUMBER(11,2);
6 TYPE tab_date IS TABLE OF DATE;
7 v_state tab_state;
8 v_count tab_count;
9 v_amount tab_amount;
10 v_date tab_date;
11 BEGIN
12 select to_char(tbl_claimshistory.deposittime,'DD-MON-YYYY'),
13 tbl_servicestateterritoryref.servicestate,
14 count(*),
15 TO_CHAR(sum(tbl_claimshistory.totalamount/100),'L999G999G999G999D99MI')
16 bulk collect into
17 v_date,v_state,v_count,v_amount
18 from tbl_servicestateterritoryref a, tbl_claimshistory b
19 where
20 tbl_claimshistory.deposittime >= to_timestamp('01-FEB-2002','DD-MON-YYYY')
21 and tbl_claimshistory.deposittime < to_timestamp('01-FEB-2002','DD-MON-YYYY') + 1
22 and tbl_servicestateterritoryref.servicestate = tbl_claimshistory.servicestate
23 and tbl_servicestateterritoryref.servicestate = 'OK'
24 and tbl_claimshistory.billcode = '0'
25 group by tbl_claimshistory.deposittime,tbl_servicestateterritoryref.servicestate
26 order by tbl_claimshistory.deposittime,tbl_servicestateterritoryref.servicestate asc;
27 for i IN 1..v_state.COUNT LOOP
28 DBMS_OUTPUT.PUT_LINE(v_date(i));
29 DBMS_OUTPUT.PUT_LINE(v_state(i));
30 DBMS_OUTPUT.PUT_LINE(v_count(i));
31 DBMS_OUTPUT.PUT_LINE(v_amount(i));
32 END LOOP;
33* end test_bulk_collect;
SQL> /

Warning: Procedure created with compilation errors.

SQL> sho erro
Errors for PROCEDURE TEST_BULK_COLLECT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/1 PL/SQL: SQL Statement ignored
26/75 PL/SQL: ORA-00904: invalid column name

Report message to a moderator

Re: BULK COLLECT AND TABLE JOINS.. please help!! [message #19843 is a reply to message #19840] Fri, 12 April 2002 10:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
/* as per your code line 12 says */
12 select to_char(tbl_claimshistory.deposittime,'DD-MON-YYYY'),
/*line 26 says */
26 order by tbl_claimshistory.deposittime,tbl_servicestateterritoryref.servicestate asc
/* possible error is */
u have refered as to_char(tbl_claimshistory.deposittime,'DD-MON-YYYY') at line 12
but just as tbl_claimshistory.deposittime in line 26 for ordering!!.
so create a pseudocolum and refer it in ur order by.
something like this
--------------------------------------------
line 12 is now
---------------------------
select to_char(tbl_claimshistory.deposittime,'DD-MON-YYYY') dummie,
----------------
line 26 is
---------------
26 order by dummie ,tbl_servicestateterritoryref.servicestate asc;
-----------------------------------------------------
similarly try this in other columns also.
and let me know!

Report message to a moderator

Re: BULK COLLECT AND TABLE JOINS.. please help!! [message #19845 is a reply to message #19843] Fri, 12 April 2002 10:16 Go to previous messageGo to next message
Kalpa
Messages: 21
Registered: February 2002
Junior Member
I tried the way you told me, still gives me the same error.

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure test_bulk_collect
2 IS
3 TYPE tab_state IS TABLE OF tbl_servicestateterritoryref.servicestate%TYPE;
4 TYPE tab_count IS TABLE OF INTEGER;
5 TYPE tab_amount IS TABLE OF NUMBER(11,2);
6 TYPE tab_date IS TABLE OF DATE;
7 v_state tab_state;
8 v_count tab_count;
9 v_amount tab_amount;
10 v_date tab_date;
11 BEGIN
12 select to_char(tbl_claimshistory.deposittime,'DD-MON-YYYY') as dummiedate,
13 tbl_servicestateterritoryref.servicestate as dummiestate,
14 count(*),
15 TO_CHAR(sum(tbl_claimshistory.totalamount/100),'L999G999G999G999D99MI')
16 bulk collect into
17 v_date,v_state,v_count,v_amount
18 from tbl_servicestateterritoryref a, tbl_claimshistory b
19 where
20 dummiedate >= to_timestamp('01-FEB-2002','DD-MON-YYYY')
21 and dummiedate < to_timestamp('01-FEB-2002','DD-MON-YYYY') + 1
22 and dummiestate = tbl_claimshistory.servicestate
23 and dummiestate = 'OK'
24 and tbl_claimshistory.billcode = '0'
25 group by dummiedate,dummiestate
26 order by dummiedate,dummiestate asc;
27 for i IN 1..v_state.COUNT LOOP
28 DBMS_OUTPUT.PUT_LINE(v_date(i));
29 DBMS_OUTPUT.PUT_LINE(v_state(i));
30 DBMS_OUTPUT.PUT_LINE(v_count(i));
31 DBMS_OUTPUT.PUT_LINE(v_amount(i));
32 END LOOP;
33* end test_bulk_collect;
SQL> /

Warning: Procedure created with compilation errors.

SQL> sho erro
Errors for PROCEDURE TEST_BULK_COLLECT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/1 PL/SQL: SQL Statement ignored
25/27 PL/SQL: ORA-00904: invalid column name
SQL>

Report message to a moderator

Re: BULK COLLECT AND TABLE JOINS.. It finally worked:) [message #19846 is a reply to message #19843] Fri, 12 April 2002 13:24 Go to previous messageGo to next message
Kalpa
Messages: 21
Registered: February 2002
Junior Member
create or replace procedure test_bulk_collect
IS
TYPE tab_state IS TABLE OF tbl_servicestateterritoryref.servicestate%TYPE;
TYPE tab_count IS TABLE OF NUMBER(15);
TYPE tab_amount IS TABLE OF VARCHAR2(35);
TYPE tab_date IS TABLE OF VARCHAR2(35);
v_state tab_state;
v_count tab_count;
v_amount tab_amount;
v_date tab_date;
BEGIN
select to_char(b.deposittime,'DD-MON-YYYY') as dummiedate,
a.servicestate as state,
count(*) as clmcnt,
TO_CHAR(sum(b.totalamount/100),'L999G999G999G999D99MI') as clmamt
bulk collect into
v_date,v_state,v_count,v_amount
from tbl_servicestateterritoryref a, tbl_claimshistory b
where
b.deposittime >= to_timestamp('01-FEB-2002','DD-MON-YYYY')
and b.deposittime < to_timestamp('01-FEB-2002','DD-MON-YYYY') + 1
and b.servicestate = a.servicestate
and b.servicestate = 'OK'
and b.billcode = '0'
group by to_char(b.deposittime,'DD-MON-YYYY'),a.servicestate
order by to_char(b.deposittime,'DD-MON-YYYY'),a.servicestate;
for i IN 1..v_state.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_date(i));
DBMS_OUTPUT.PUT_LINE(v_state(i));
DBMS_OUTPUT.PUT_LINE(v_count(i));
DBMS_OUTPUT.PUT_LINE(v_amount(i));
END LOOP;
end test_bulk_collect;
/

Report message to a moderator

Re: BULK COLLECT AND TABLE JOINS.. please help!! [message #19851 is a reply to message #19843] Sat, 13 April 2002 13:57 Go to previous message
Su
Messages: 154
Registered: April 2002
Senior Member
Just have a look again at your code in the 16th line of your query.
15 TO_CHAR(sum(tbl_claimshistory.totalamount/100),'L999G999G999G999D99MI')
16 bulk collect into

There is a blank in between 'bulk' and 'collect' in the 16th line. Since they will be taken as two names and there is no comma immeidately after the TO_CHAR expression, the first 'bulk' will be taken as an alias for

TO_CHAR(sum(tbl_claimshistory.totalamount/100),'L999G999G999G999D99MI')

(since there is no comma after this expression)
and the second 'collect' will be considered a column you want select from the table where it might not be an existant column. Check the right name of it and correct the code. May be that could be the reason.
And again, dont rely on the line numbers it gives in the errors list :)

Good luck :)

Report message to a moderator

Previous Topic: %datatype
Next Topic: indexes-any limit?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 05:53:28 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.