Calling an array receiving procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Calling an array receiving procedure

  1. #1
    Join Date
    Nov 2001
    Posts
    55
    How can I call this procedure from sql*plus worksheet (GUI) or sqlplus,

    Please help ME. . . . .

    TYPE vAryAttribute_name IS VARRAY(100) OF VARCHAR2(40);

    create or replace PROCEDURE SearchAssay( InAryAttribute_name IN vAryAttribute_name) IS
    sql_stmt VARCHAR2(4000);
    sql_stmtatt VARCHAR2(1000);
    idx SMALLINT;

    BEGIN

    FOR idx IN 1 .. InAryAttribute_name.count
    LOOP
    IF InAryAttribute_name(idx) IS NULL THEN
    EXIT;
    ELSE
    IF (idx = 1) THEN
    sql_stmtAtt := '(select distinct assay_id from assay_attribute where attribute_id = (select attribute_id from attribute where attribute_name = ''' || InAryAttribute_name(idx) ||'''))';
    ELSE
    sql_stmtAtt := '(select distinct assay_id from assay_attribute where assay_id IN '|| rtrim(sql_stmtatt) ||' AND attribute_id = (select attribute_id from attribute where attribute_name = ''' || InAryAttribute_name(idx) || '''))';
    END IF;
    END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(sql_stmtAtt);
    END SearchAssay;

  2. #2
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I think you can do it in the following way, you can pass values in the bracket also to send any value for the array.

    declare
    name varyattribute_name:=varyattribute_name();
    begin
    searchassay(name);
    end;
    or
    declare
    name varyattribute_name:=varyattribute_name('1','2');
    begin
    searchassay(name);
    end;

    KN

  3. #3
    Join Date
    Nov 2001
    Posts
    55
    Knarayan U R outstanding

    ThanX a lot

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