Initialize a strcture-> array?

[Rajkartha]

create or replace TYPE assay_attribute_struct AS object(
ASSAY_ID NUMBER(10),
attribute_no NUMBER(3),
Attribute_id NUMBER(10),
parent_attribute_no NUMBER(3),
Attribute_type VARCHAR2(20),
DISPLAY_UNIT VARCHAR2(20),
display_order NUMBER(3),
Update_values_from_index NUMBER(1),
Creation_date DATE,
parent_display_order NUMBER(3));

TYPE vAryAssay_attribute IS VARRAY(500) OF assay_attribute_struct;

I have defined vAryAssay_attribute like this in sqlplus.
declare

name aimads_pack.vAryAssay_attribute := aimads_pack.vAryAssay_attribute(assay_attribute_struct(NULL,NULL,41,NULL,'Defining-Fixed',NULL,NULL,1,NULL,NULL),assay_attribute_struct(NULL,NULL,41,NULL,'Defining-Fixed',NULL,NULL,1,NULL,NULL));

is this correct?

ThanX
Raj

[TimHall]

Come on guy! This is still the same varray thread. Don't start a new thread every time you have a slightly different question on the same topic. Add it to the existing thread.

Looks a bit odd to me. Are you using the constructor twice?

I'd like to see the full code. I looks like you've defined the types OK, but then you are trying to use the types directly. These are only definitions of types. You have to create an instance of the type by defining a variable of that type:


my_varray name aimads_pack.vAryAssay_attribute;

The use:

my_varray := aimads_pack.vAryAssay_attribute(assay_attribute_struct(NULL,NULL,41,NULL,'Defining- Fixed',NULL,NULL,1,NULL,NULL),assay_attribute_struct(NULL,NULL,41,NULL,'Defining-Fixed',NULL,NULL,1,NULL,NULL));

If I'm understanding you correctly this should be fine.

Once again, try to progress the existing thread rather than starting a new one all the time.

[Rajkartha]

TimHall,

In the future I will surely use the same thread. I never knew that I can continue using the same thread. ThanX a lot for help . . . and I mean it.


Here is a new problem I am facing.

I am receiving an array as IN paramter. Can I use this array in a dynamic SQL stmt created in the procedure using USING clause?. If YES please help me with a snippet code

Somthing like this

TYPE vAryAssayId IS VARRAY(1000) OF NUMBER(10);

create or repalce PROCEDURE GetMatch ( InAry IN vAryAssayId) IS
begin
sql_stmt := 'select * from TableA WHERE xyz = :aa';
open abc for sql_stmt USING InAry;
END GetMatch;

Once again Sorry for making too many threads. TimHall ThanX a lot for the eye opener.

Raj

[balajiyes]

try this,

create or repalce PROCEDURE GetMatch ( InAry IN vAryAssayId) IS
begin
sql_stmt := 'select * from TableA WHERE xyz = :aa';
open abc for sql_stmt USING IN InAry;
END GetMatch;

[Rajkartha]

balajiyes,

This is what I did,

CREATE OR REPLACE PACKAGE arytst_pack AS
TYPE vAryAssayId IS VARRAY(5) OF varchar2(20);
TYPE testabc IS REF CURSOR;
PROCEDURE GetMatch ( InAry IN vAryAssayId,abc out testabc);
END arytst_pack;
/
CREATE OR REPLACE PACKAGE BODY arytst_pack AS
PROCEDURE GetMatch ( InAry IN vAryAssayId,abc out testabc) is
sql_stmt varchar2(2000);
begin
sql_stmt := 'select * from TBGroupPrivileges WHERE groupid = :aa';
open abc for sql_stmt USING IN InAry;
END GetMatch;

END arytst_pack;

and coming out with compilation error,

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: Statement ignored
6/32 PLS-00457: expressions have to be of SQL types

Warning: Package Body created with compilation errors.

ThanX
Raj

[balajiyes]

hi,

sql_stmt := 'BEGIN select * from TBGroupPrivileges WHERE groupid = :aa; END;';
open abc for sql_stmt USING IN InAry;





this is just an example:
DECLARE
TYPE typ_empno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
TYPE typ_ref is REF CURSOR;
lv_cur_dynamic typ_ref;
lv_typ_empno typ_empno;
lv_qry_str varchar2(30000) := 'SELECT empno FROM emp';
BEGIN
OPEN lv_cur_dynamic FOR lv_qry_str ;
LOOP
FETCH lv_cur_dynamic BULK COLLECT INTO lv_typ_empno LIMIT 500;
FORALL i IN 1..lv_typ_empno.count
INSERT INTO dummy VALUES (lv_typ_empno(i));
EXIT WHEN lv_cur_dynamic%NOTFOUND;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('err >>> '||SQLERRM);
END;