-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|