CODEWITHSUNDEEP
sqloracle11g
timmy
timmy

Reputation: 1758

Migrate Existing Data Into VARRAY Types

I have the following VARRAY type defined in my db schema:

create or replace type STRING_VECTOR is varray(10) of varchar2(50);

I also have the following Employee database table

first name| last name| salary
'John'    | 'Doe'    | 10.00

Note that the salary column is a NUMBER(38,14).

What I would like to do is add a fourth column that is of my STRING_VECTOR type. I would like to populate my string vector with String values for each record like so:

first name| last name| salary| data
'John'    | 'Doe'    | 10.00 | ['John', 'Doe', '10.00']

Could someone give me some pointers as to whether PL/SQL can be written to achieve this?

Upvotes: 0

Views: 47

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

This is not hard at all, take a look:

CREATE TABLE my_emp_test (
  first_name VARCHAR2(20),
  last_name VARCHAR2(20),
  salary NUMBER(38, 14)
);

INSERT INTO my_emp_test VALUES ('John', 'Doe', 10.00);

COMMIT;

CREATE OR REPLACE TYPE string_vector IS VARRAY(10) OF VARCHAR2(50);

ALTER TABLE my_emp_test ADD data string_vector;

UPDATE my_emp_test
  SET data = string_vector(first_name, last_name, TO_CHAR(salary))
;

COMMIT;

SELECT * FROM my_emp_test;

FIRST_NAME           LAST_NAME                SALARY DATA                               
-------------------- -------------------- ---------- ------------------------------------
John                 Doe                          10 HR.STRING_VECTOR('John','Doe','10')

You wrote that you wanted PL/SQL, but it can be done in pure SQL as you can see, is that going to work for you?

Upvotes: 1

Related Questions