Redwood - declare array and load from sql select - how?

[Former Member]

Scenario - PL/SQL script to be run via batch in Redwood. Need to read JCS_SCRIPTS and load APPLICATION_NAME and NAME to an array. Have googled extensively for how to do this - most preferred way seems to be to declare type of object and then declary varray with that.

However, when I declare...

CREATE TYPE meal_t AS OBJECT (

number_served INTEGER

, meal_type VARCHAR2 ( 100 )

, food_served food_t

);

I get message from Redwood that... "object not supported in this context.". And this is needed so that can declare the varray.

CREATE TYPE meals_vat

IS VARRAY ( 3 ) OF meal_t;

Therefore, given the sql... SELECT APPLICATION_NAME, NAME FROM SYSJCS WHERE TYPE = '3';

How do I declare an array in a Redwood PL/SQL script and application_name and name to it?

[Former Member]

Here you go.

First define type in oracle

CREATE OR REPLACE

TYPE meal_t AS OBJECT (

number_served INTEGER

, meal_type VARCHAR2 ( 100 )

, food_served VARCHAR2 ( 100 )

);

CREATE OR REPLACE

TYPE meal_p IS TABLE OF meal_t;

Now in your oracle script in declare section use

v_array meal_p;

You can simply fetch the date from you sql now in this v_array object.

- Bhushan