PL/SQL define and initialize multi field array or table from text

Question

So I've been searching for a way to do this and I just can't seem to find it. I have a list of 3 pieces of data tag, time, and type. there are multiple records (like 280+) in a text (csv) file.

In PL/SQL developer I have a command file, in which i want to declare an array, and fill it from the values in the text file, but since I can't put the file on the server, I can't use the file as an external data source. I need to copy/paste the values into the PL\SQL block file that I'm working with. Something like the following

declare
  type v_data is RECORD (tag number, time varchar2(12), type varchar2(15)) ;

  type v_data_table is table of v_data index by binary_integer;
  v_list v_data_table := v_data_table(v_data(1,'6:00 am','ONE'), v_data(240,'11:00 am', 'TWO'));

But i am getting an error that function v_data does not exist in the current scope when declaring the v_list variable. I've even tried moving the v_list initialize statement into the PL/SQL block, after the begin, like so;

 declare
   type v_data is RECORD (tag number, time varchar2(12), type varchar2(15)) ;

   type v_data_table is table of v_data index by binary_integer;
   v_list v_data_table ;
 begin
   v_list := v_data_table(v_data(1,'6:00 am','ONE'), v_data(240,'11:00 am', 'TWO'));

same error... any help is appreciated!

"since I can't put the file on the server, I can't use the file as an external data source"...that's false...assuming Oracle DBA account user is given permission... external directories can be network locations on other machines...I use this often — Ctznkane525
– Ctznkane525, Commented Feb 2, 2018 at 22:18
You have two issues. The first is that you are trying to construct a RECORD type as though it were an OBJECT type. If your type were an object, then you get a default constructor that does exactly what you are attempting. Second, you are using a table type indexed by binary_integer. Any table type with an index type declared cannot be built in bulk like this. You need to remove that piece of the declaration. — Chris Hep
– Chris Hep, Commented Feb 2, 2018 at 22:39
@HepC - thanks, can you expound a bit? Maybe move your explanation into an answer instead of a comment. Once I test this, I'll give you the solution point. — user3530692
– user3530692, Commented Feb 3, 2018 at 0:04

user3530692 · Accepted Answer · 2018-02-03 00:34:27Z

1

Well, Thanks to HepC and a little Google-fu, I got a script that worked.

set serveroutput on size 1000000
create type v_data AS OBJECT (tag number, time varchar2(12), type varchar2(15)) ;
/
declare
type v_data_table is table of v_data index by binary_integer;
v_list v_data_table := v_data_table(v_data(1,'6:00 am','ONE'), v_data(240,'11:00 am', 'TWO'));
begin
  for i in v_list.first..v_list.Last
  LOOP
    DBMS_OUTPUT.PUT_LINE('TAG: ' || to_char(v_list(i).tag));
    DBMS_OUTPUT.PUT_LINE('TAG: ' || v_list(i).time);   
    DBMS_OUTPUT.PUT_LINE('TAG: ' || v_list(i).type);
  END LOOP;
END;
/
drop type v_data;
/
set serveroutput off;
/

I was worried about creating the type on the server, since it's a production server, and this is a one-off script, I didn't want a stray type taking up precious resources.

answered Feb 3, 2018 at 0:34

user3530692

111 silver badge3 bronze badges

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

Comments

Ray DeBruyn · Accepted Answer · 2018-02-04 05:28:18Z

1

DECLARE

   type v_data is RECORD 
   ( tag number
   , time varchar2(12)
   , type varchar2(15));

   type v_data_table is table of v_data index by binary_integer;

   v_list v_data_table ;

   PROCEDURE add_new_data(new_tag number
                        , new_time varchar2
                        , new_type varchar2)
      IS
      BEGIN

         v_list(v_list.COUNT + 1).tag := new_tag;
         v_list(v_list.COUNT).time    := new_time;
         v_list(v_list.COUNT).type    := new_type;

      END add_new_data;

BEGIN

   add_new_data(1, '6:00 am', 'ONE');
   add_new_data(240, '11:00 am', 'TWO');

END;
/

answered Feb 4, 2018 at 5:28

Ray DeBruyn

411 silver badge7 bronze badges

2 Comments

user3530692 Over a year ago

I didn't try this, so I'm not sure if it works. but in your procedure you're adding the tag to count + 1 and the time and type to count. Is Count a dynamic property like that, or would you need to EXTEND the v_list first before adding to it. I tried declaring v_data as a record, and that worked, similar to this solution, you can see it in my solution on this post. Thanks for your attempt!!

Ray DeBruyn Over a year ago

no need to extend. count simply gives the number of element in the array so far. if the pl table has 10 rows, count + 1 returns 11, so count + 1 is saying add row at index 11. the 2 next lines count returns 11, so you're adding the values to the row at index 11.

Ray DeBruyn · Accepted Answer · 2018-02-02 22:56:24Z

0

DECLARE

   type v_data is RECORD 
   ( tag number
   , time varchar2(12)
   , type varchar2(15));

   type v_data_table is table of v_data index by binary_integer;

   v_list v_data_table ;

BEGIN

   v_list(v_list.COUNT + 1).tag := 1;
   v_list(v_list.COUNT).time    := '6:00 am';
   v_list(v_list.COUNT).type    := 'ONE';

   v_list(v_list.COUNT + 1).tag := 240;
   v_list(v_list.COUNT).time    := '11:00 am';
   v_list(v_list.COUNT).type    := 'TWO';

END;
/

answered Feb 2, 2018 at 22:56

Ray DeBruyn

411 silver badge7 bronze badges

1 Comment

user3530692 Over a year ago

Thanks Ray, but this is what I'm trying to avoid, all the timestaking declarations of v_list iterations, I have 280+ and I've already used NotePad++ to encase them in brackets and single quotes. I want to bulk declare the v_list variable in one statement then iterate through it. Close

Collectives™ on Stack Overflow

PL/SQL define and initialize multi field array or table from text

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related