This discussion is archived
7 Replies Latest reply: Oct 9, 2012 9:26 PM by ChakravarthyDBA RSS

How to declare a array in procedure

ChakravarthyDBA Newbie
Currently Being Moderated
PROCEDURE INORUPSHIFTPATTERN_1
(
pSHIFTPATTERNID IN VARCHAR2,
pSHIFTPATTERNNAME IN VARCHAR2,
pSTARTDAYOFTHEWEEK IN NUMBER,
pNOOFPATTERNDAYS IN NUMBER,
pISACTIVE IN VARCHAR2,
pCREATEDBY IN VARCHAR2,
pSHIFTID IN ARRATYPE,
pPATTERNDAY IN ARRATYPE,
CUR_OUT OUT SYS_REFCURSOR
)

this is my procedure how can I declare array type here, pSHIFTID and pPATTERNDAY are array types

TYPE ARRAYTYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

where I can write the above type in procedure and it is showing an error ARRAYTYPE must be declared please help
  • 1. Re: How to declare a array in procedure
    Fran Guru
    Currently Being Moderated
    please check:
    http://stackoverflow.com/questions/831188/how-to-create-a-stored-procedure-in-oracle-which-accepts-array-of-parameters
  • 2. Re: How to declare a array in procedure
    Purvesh K Guru
    Currently Being Moderated
    user9235224 wrote:
    PROCEDURE INORUPSHIFTPATTERN_1
    (
    pSHIFTPATTERNID IN VARCHAR2,
    pSHIFTPATTERNNAME IN VARCHAR2,
    pSTARTDAYOFTHEWEEK IN NUMBER,
    pNOOFPATTERNDAYS IN NUMBER,
    pISACTIVE IN VARCHAR2,
    pCREATEDBY IN VARCHAR2,
    pSHIFTID IN ARRATYPE,
    pPATTERNDAY IN ARRATYPE,
    CUR_OUT OUT SYS_REFCURSOR
    )

    this is my procedure how can I declare array type here, pSHIFTID and pPATTERNDAY are array types

    TYPE ARRAYTYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

    where I can write the above type in procedure and it is showing an error ARRAYTYPE must be declared please help
    It depends on where your procedure exists.

    If you procedure exists in a package the below will suffice:
    create or replace package my_package
    is
      type array_type is table of varchar2(4000) index by pls_integer;
      
      procedure my_proc(pram in array_type);
    end my_package;
    
    create or replace package body my_package
    is
      procedure my_proc(pram in array_type)
      as
      begin
        --Populate or process pram parameter accordingly
        dbms_output.put_line('Into proc with input as Array_Type');
      end my_proc;
    end my_package;
    If your procedure is Standalone procedure, then
    create or replace array_type is table of varchar2(4000);
    
    procedure my_proc(param in array_type)
    is
    
    begin
      dbms_output.put_line('Into Standalone proc to initialize and process the Table Type.');
    end my_proc;
  • 3. Re: How to declare a array in procedure
    ChakravarthyDBA Newbie
    Currently Being Moderated
    where can I insert this code in package

    DECLARE
    TYPE Str_Array IS VARRAY(4) OF VARCHAR2(50);
    v_array Str_Array;

    PROCEDURE PROCESS_ARRAY(v_str_array Str_Array)
    AS
    BEGIN
    FOR i IN v_str_array.first .. v_str_array.last LOOP
    DBMS_OUTPUT.PUT_LINE('Hello '||v_str_array(i));
    END LOOP;
    END;

    BEGIN

    v_array := Str_Array('John','Paul','Ringo','George');

    PROCESS_ARRAY(v_array);

    -- can also pass unbound Str_Array
    PROCESS_ARRAY(Str_Array('John','Paul','Ringo','George'));

    END;

    plz tell me
  • 4. Re: How to declare a array in procedure
    Purvesh K Guru
    Currently Being Moderated
    This way...

    create or replace type my_type is varray(5) of varchar2(2);
    TYPE my_type compiled
    
    create or replace procedure my_proc
    (
      v_type      in      my_type
    )
    is
    begin
      for i in 1..v_type.count loop
        dbms_output.put_line(v_type(i));
      end loop;
    end;
    PROCEDURE my_proc compiled
    
    set serveroutput on;
    declare
      arr_type      my_type;
    begin
      arr_type := my_type('AA', 'BB', 'CC', 'DD');
      dbms_output.put_line('Calling My_Proc');
      my_proc(arr_type);
      dbms_output.put_line('After Calling My_Proc');
      
      dbms_output.put_line('Calling My_Proc');
      my_proc(my_type('AA', 'BB', 'CC', 'DD'));
      dbms_output.put_line('After Calling My_Proc');
    end;
    
    anonymous block completed
    Calling My_Proc
    AA
    BB
    CC
    DD
    After Calling My_Proc
    Calling My_Proc
    AA
    BB
    CC
    DD
    After Calling My_Proc
  • 5. Re: How to declare a array in procedure
    Marco V. Expert
    Currently Being Moderated
    Have a look at this link
    http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42plsql-1653077.html
  • 6. Re: How to declare a array in procedure
    rp0428 Guru
    Currently Being Moderated
    >
    pSHIFTID IN ARRATYPE,
    pPATTERNDAY IN ARRATYPE,

    this is my procedure how can I declare array type here, pSHIFTID and pPATTERNDAY are array types

    TYPE ARRAYTYPE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

    where I can write the above type in procedure and it is showing an error ARRAYTYPE must be declared please help
    >
    The parameters 'pSHIFTID' and 'pPATTERNDAY' have 'ARRATYPE' which is mispelled; should be ARRAYTYPE.

    You can't declare the type IN the procedure that you use it in. You need to declare it in a package that the procedure is in. For a standalone procedure you can declare it as a SQL type.
  • 7. Re: How to declare a array in procedure
    ChakravarthyDBA Newbie
    Currently Being Moderated
    Thank you the problem is solved.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points