Home » SQL & PL/SQL » SQL & PL/SQL » Error when using Collections / Records
Error when using Collections / Records [message #260531] Mon, 20 August 2007 04:31 Go to next message
yarrman
Messages: 5
Registered: May 2006
Junior Member
Hi All,

I am trying to run the following statement and I am getting the error
ORA-06550: line 39, column 55:
PLS-00302: component 'FIRST' must be declared
ORA-06550: line 39, column 37:
PL/SQL: Statement ignored


I tried delaration TABLE type for each variables and its working, but it consumes more code length.My aim to migrate from Cursor to Collections which I am trying first time. Could anyone pl help me in solving this error.


DECLARE
v_batch_num_addr_per NUMBER := 88888;
v_seq_row_id NUMBER;

TYPE coll_type_rec_addr IS RECORD ( v_AP_ADDR IN_EIM_ADDR_PER.AP_ADDR%TYPE,
v_AP_ADDR_NAME IN_EIM_ADDR_PER.AP_ADDR_NAME%TYPE
);
coll_var_rec_addr coll_type_rec_addr;

BEGIN

SELECT
DISTINCT(AP_ADDR),
AP_ADDR_NAME
BULK COLLECT INTO
coll_var_rec_addr
FROM
IN_EIM_ADDR_PER;

FORALL indx IN coll_var_rec_addr.FIRST.. coll_var_rec_addr.LAST

INSERT INTO SIEBEL.EIM_ADDR_PER
(
ROW_ID,
IF_ROW_STAT,
IF_ROW_BATCH_NUM,
AP_ADDR,
AP_ADDR_NAME
)
VALUES
(
seq_dm_row_id.NEXTVAL,
'FOR_TEST',
v_batch_num_addr_per,
coll_var_rec_addr.v_AP_ADDR(indx),
coll_var_rec_addr.v_AP_ADDR_NAME(indx)
);
END;

Thanks,
Yar
Re: Error when using Collections / Records [message #260535 is a reply to message #260531] Mon, 20 August 2007 04:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You've defined a RECORD type (coll_var_rec_addr), not a TABLE type. Records don't have FIRST or LAST attributes.

MHE
Re: Error when using Collections / Records [message #260536 is a reply to message #260531] Mon, 20 August 2007 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Which one is line 39?
Which ones are columns 55 and 37?
Copy and paste your screen with line numbers.

Regards
Michel
Re: Error when using Collections / Records [message #260538 is a reply to message #260531] Mon, 20 August 2007 04:47 Go to previous messageGo to next message
yarrman
Messages: 5
Registered: May 2006
Junior Member
Thanks for the reply.
Initiall I tried declaring the type as RECORD and assign it to TABLE TYPE and created variable for the table type like below.

TYPE RECORD declaration...
TYPE coll_tbl_rec_addr IS TABLE OF coll_type_rec_addr
INDEX BY PLS_INTEGER;
coll_var_tbl_addr coll_tbl_rec_addr;
and tried the following insert statement,

FORALL indx IN coll_var_tbl_addr.FIRST.. coll_var_tbl_addr.LAST
INSERT INTO SIEBEL.EIM_ADDR_PER
(
AP_ADDR
)
VALUES
(
coll_var_tbl_addr.v_AP_ADDR(indx)
);

But this shows gives me the error - PLS-00302: component 'V_AP_ADDR' must be declared
Could you please advise me on this.

Thanks,
Yar
Re: Error when using Collections / Records [message #260541 is a reply to message #260535] Mon, 20 August 2007 04:48 Go to previous messageGo to next message
yarrman
Messages: 5
Registered: May 2006
Junior Member
Sure, from next time I will follow the instructions.Thanks.
Re: Error when using Collections / Records [message #260545 is a reply to message #260541] Mon, 20 August 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sure, from next time I will follow the instruction

Quote:
Could you please advise me on this

Next time is come.
Follow the requirements.

Regards
Michel
Re: Error when using Collections / Records [message #260566 is a reply to message #260545] Mon, 20 August 2007 05:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel has a good point: use code tags to improve readability:
[code] and [/code] around code snippets does the trick:
DECLARE
   TYPE coll_type_rec_addr IS RECORD (
      v_ap_addr        in_eim_addr_per.ap_addr%TYPE
    , v_ap_addr_name   in_eim_addr_per.ap_addr_name%TYPE
   );

   TYPE coll_tbl_rec_addr IS TABLE OF coll_type_rec_addr
      INDEX BY PLS_INTEGER;

   coll_var_tbl_addr   coll_tbl_rec_addr;
BEGIN
   SELECT DISTINCT (ap_addr)
                 , ap_addr_name
   BULK COLLECT INTO coll_var_tbl_addr
   FROM            in_eim_addr_per;

   FORALL indx IN coll_var_tbl_addr.FIRST .. coll_var_tbl_addr.LAST
      INSERT INTO siebel.eim_addr_per
                  (ap_addr
                  )
      VALUES      (coll_var_tbl_addr (indx).v_ap_addr
                  );
END;


Have a look at the difference in code. Can you spot my modification?

MHE
Re: Error when using Collections / Records [message #260572 is a reply to message #260566] Mon, 20 August 2007 06:01 Go to previous messageGo to next message
yarrman
Messages: 5
Registered: May 2006
Junior Member
Thanks Maaher, the code is working now.

I agree that I should use code tags. Since this is the first time I am posting a thread I just added like that and replyed him that I will follow.
But I hate this man, who is Arrogant.


Thanks again.
Re: Error when using Collections / Records [message #260574 is a reply to message #260572] Mon, 20 August 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First time you post and you already hate me.
Woo, youre going to suffer.
Anyway, any place you will go you will have to follow the rules.

Thanks the A (in capital), I think I will like you.

Regards
Michel
Re: Error when using Collections / Records [message #260581 is a reply to message #260574] Mon, 20 August 2007 06:32 Go to previous messageGo to next message
yarrman
Messages: 5
Registered: May 2006
Junior Member
Hey Michel,

I found one thing about you here after refering some of your replyes for other posts. You always used to give some link and preach something irrelevent to the query. Is this how you increased your post numbers, SILLY FELLOW.

Anyone can ask to post properly that's good but the way you rule is unfair.

Follow others like Maaherto post and to adivse. If you are not then you are not fit to be a Senior Member.



[Updated on: Mon, 20 August 2007 06:33]

Report message to a moderator

icon9.gif  Re: Error when using Collections / Records [message #260586 is a reply to message #260581] Mon, 20 August 2007 06:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Look,
Michel - as a moderator - has the right and privilege to point people to the posting guidelines. You were lucky that I saw your code mistake immediately. It's very hard to read unformatted code. We get tons of posts like this every day. For regular members here this can get very frustrating. It's a pity that you feel personally offended in your first thread. I'm sure that this is a standard reply from Michel without the intention to attack anyone. Wink
Why didn't you just follow his advice?

And that's the sad end of this thread.

http://www.orafaq.com/forum/fa/448/0/

closed.

MHE

PS: no flaming. None. That's final.
Re: Error when using Collections / Records [message #260592 is a reply to message #260586] Mon, 20 August 2007 07:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not even a little bit of flaming?
Previous Topic: Using recyclebin parameter
Next Topic: How to fetch data frm "long" data type column
Goto Forum:
  


Current Time: Thu Dec 08 16:15:55 CST 2016

Total time taken to generate the page: 0.11824 seconds