Home » SQL & PL/SQL » SQL & PL/SQL » Defining and initializing a VARRAY in PL/SQL for insert into a table (Oracle server 9.2.0.7.0)
Defining and initializing a VARRAY in PL/SQL for insert into a table [message #343829] Thu, 28 August 2008 05:24 Go to next message
carsten.jorgensen
Messages: 18
Registered: May 2005
Junior Member
Hi,

I have created a table containing a VARRAY and I'm now trying to insert rows into the table via PL/SQL code.

The varray of the table is defined like this:

CREATE OR REPLACE TYPE BKG_CL_TYPE AS OBJECT
(Bkg_cl Char(1)
,Bkg_cl_rank Number(2)
,Od_fare Number(9,0)
,Buyup Number
,Buyup_adj Number
)
/

CREATE OR REPLACE TYPE BKG_CL_ARRAY_TYPE AS VARRAY(26)
OF BKG_CL_TYPE
/

In the PL/SQL code I have defined:
w_BKG_CL_ARRAY BKG_CL_ARRAY_TYPE;

I initialize the entire array and create the first element:

w_BKG_CL_ARRAY := BKG_CL_ARRAY_TYPE();
w_BKG_CL_ARRAY.EXTEND;

That seems to be working fine.

But then I need to initialize each field of the 1st element of the VARRAY - I have tried with this syntax:
w_BKG_CL_ARRAY.BKG_CL (1) := 'A';

This gives me the error:
PLS-00302 Component 'BKG_CL' must be declared.

Can anybody pls. tell me what I doing wrong or show me a correct example ?

Re: Defining and initializing a VARRAY in PL/SQL for insert into a table [message #343875 is a reply to message #343829] Thu, 28 August 2008 06:52 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
try:
w_BKG_CL_ARRAY(1).BKG_CL := 'A'; 
Re: Defining and initializing a VARRAY in PL/SQL for insert into a table [message #343876 is a reply to message #343829] Thu, 28 August 2008 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Defining and initializing a VARRAY in PL/SQL for insert into a table [message #344198 is a reply to message #343829] Fri, 29 August 2008 01:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE TYPE BKG_CL_TYPE AS OBJECT
  2  (Bkg_cl Char(1)
  3  ,Bkg_cl_rank Number(2)
  4  ,Od_fare Number(9,0)
  5  ,Buyup Number
  6  ,Buyup_adj Number
  7  );
  8  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BKG_CL_ARRAY_TYPE AS VARRAY(26)
  2  OF BKG_CL_TYPE;
  3  /

Type created.

SCOTT@orcl_11g> DECLARE
  2    w_BKG_CL_ARRAY BKG_CL_ARRAY_TYPE;
  3  BEGIN
  4    w_BKG_CL_ARRAY := BKG_CL_ARRAY_TYPE();
  5    w_BKG_CL_ARRAY.EXTEND;
  6    w_BKG_CL_ARRAY(1) := BKG_CL_TYPE(NULL, NULL, NULL, NULL, NULL);
  7    w_BKG_CL_ARRAY(1).BKG_CL := 'A';
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: Defining and initializing a VARRAY in PL/SQL for insert into a table [message #344341 is a reply to message #343829] Fri, 29 August 2008 07:56 Go to previous message
carsten.jorgensen
Messages: 18
Registered: May 2005
Junior Member
Thank you very much.
It works perfectly now.
Previous Topic: Stored Procedure help
Next Topic: Looping through dba_objects to alter table
Goto Forum:
  


Current Time: Fri Dec 02 16:36:16 CST 2016

Total time taken to generate the page: 0.13295 seconds