How to use varray in plsql

Question

Below code block is throwing error..

DECLARE
  TYPE dbadaptor IS VARRAY (9) OF VARCHAR2(20);
  min_date DATE;
  max_date DATE;
  db       dbadaptor := dbadaptor('wsf_ds_11765_c', 'wsf_ds_11750_c',
                                  'wsf_ds_11756_c', 'wsf_ds_11759_c',
                                  'wsf_ds_11706_c', 'wsf_ds_11709_c', 
                                  'wsf_ds_11751_c', 'wsf_ds_11708_c',
                                  'wsf_ds_11762_c');
BEGIN
  FOR i IN 1..9 LOOP
    SELECT
      from_sf_date(max(start_time)),
      from_sf_date(min(start_time))
    INTO max_date, min_date
    FROM db(i);
  END LOOP;
END;

I am getting an error at line 8 ” PL/SQL: ORA-00933: SQL command not properly ended ” and this is because of db(i)…. Can you please help me out

score 2 · Answer 1 · answered Jun 14 '12 at 20:43

There are a variety of problems with your code:

You can't access datatypes defined in PL/SQL in a SQL statement.
In order to access an array in the from clause, you need to use the table function.
You're trying to select start_time from your array, but you have no such column defined. The only column you have is a string with an undefined name (which would be referenced as column_value).

Below is an example that demonstrate the techniques referenced in problems 1 and 2 (I lack enough information to provide a solution for problem 3).

CREATE TYPE dbadaptor AS VARRAY(9) OF VARCHAR2(20);

DECLARE
   min_date DATE;
   max_date DATE;
   db       dbadaptor
               := dbadaptor('wsf_ds_11765_c',
                            'wsf_ds_11750_c',
                            'wsf_ds_11756_c',
                            'wsf_ds_11759_c',
                            'wsf_ds_11706_c',
                            'wsf_ds_11709_c',
                            'wsf_ds_11751_c',
                            'wsf_ds_11708_c',
                            'wsf_ds_11762_c');
BEGIN    
   FOR db_value IN (SELECT COLUMN_VALUE AS CV FROM TABLE(db)) LOOP
      DBMS_OUTPUT.put_line(db_value.CV);
   END LOOP;    
END;
/

However, this is an inefficient way to loop through an array. If that's all you're doing, you'd be better off just using a loop:

DECLARE
   type dbadaptor is VARRAY(9) OF VARCHAR2(20);
   min_date DATE;
   max_date DATE;
   db       dbadaptor
               := dbadaptor('wsf_ds_11765_c',
                            'wsf_ds_11750_c',
                            'wsf_ds_11756_c',
                            'wsf_ds_11759_c',
                            'wsf_ds_11706_c',
                            'wsf_ds_11709_c',
                            'wsf_ds_11751_c',
                            'wsf_ds_11708_c',
                            'wsf_ds_11762_c');
   i number;
BEGIN
   FOR i IN 1..9 LOOP
      DBMS_OUTPUT.put_line(db(i));
   END LOOP;
END;
/

How to use varray in plsql

1 Answers1