DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Help With varray's

  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Help With varray's

    I am having som problems with the varray datatype. What I want to do is call the procedure that I have listed below, and insert values into the varray every time I call the procedure. I am running into problems with initializing the varray, and now I cant seem to insert more than one value into the varray even when I call it multiple times.

    Can comeone help?

    Thanks for the help guys. If you need more info I can provide, sorry I am new here.

    /* Varray creation script */
    CREATE OR REPLACE TYPE CODE_VARRAY_TYPE
    AS VARYING ARRAY (100) OF VARCHAR2(20)

    /* Procedure that I am trying to run */

    CREATE OR REPLACE PROCEDURE add_verbatim_code (
    v_project_id IN NUMBER,
    v_respondent_id IN VARCHAR2,
    v_q_tag IN VARCHAR2,
    v_new_code IN VARCHAR2) AS
    v_code_array CODE_VARRAY_TYPE;
    BEGIN

    SELECT code
    INTO v_code_array
    FROM verbatims
    WHERE project_id = v_project_id
    AND respondent_id = v_respondent_id
    AND q_Tag = v_q_tag;
    IF v_code_array IS NULL THEN
    v_code_array := CODE_VARRAY_TYPE(v_new_code);
    ELSE
    v_code_array.EXTEND(1);
    v_code_array := CODE_VARRAY_TYPE(v_new_code);
    END IF;

    for i in v_code_array.first..v_code_array.last
    loop
    dbms_output.put_line(v_code_array(i));
    end loop;

    UPDATE verbatims
    SET code = v_code_array
    WHERE project_id = v_project_id
    AND respondent_id = v_respondent_id
    AND q_Tag = v_q_tag;

    END;

  2. #2
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    You have to use "BULK COLLECT" in front of INTO like this:
    BULK COLLECT INTO v_code_array
    Also for a bulk insert you have to use FORALL starement.

    See details here:
    http://download-west.oracle.com/docs...0pck.htm#32025

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