Initialize a strcture-> array?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Initialize a strcture-> array?

  1. #1
    Join Date
    Nov 2001
    Posts
    55
    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

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Nov 2001
    Posts
    55

    Extremely sorry - And new array problem

    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

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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;
    Cheers!
    OraKid.

  5. #5
    Join Date
    Nov 2001
    Posts
    55
    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

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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;
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width