let i am using varray in procedure .then how to call it.

[PANIGRAHI]

hi friend,

let i am using varray in procedure .then how to call it.


Regards
Rabindra

[debasisdas]

let i am using varray in procedure .then how to call it.

can you kindly post the code for reference of our experts please.

[amitpatel66]

hi friend,

let i am using varray in procedure .then how to call it.


Regards
Rabindra

Please provide more detail about what is your procedure doing.
Are you using VARRAY as input/output parameter for the procedure?

[subashsavji]

hi friend,

let i am using varray in procedure .then how to call it.


Regards
Rabindra

try this........... ..........
[code=oracle]
CREATE TYPE BOOKOBJJ AS OBJECT(
TITLE VARCHAR2(40), AUTHER VARCHAR2(40),
CATALOG_NUMBER NUMBER(4) );
/

CREATE OR REPLACE TYPE BOOKLIST2 AS VARRAY(10) OF BOOKOBJ;
/

CREATE OR REPLACE PROCEDURE CHECKOUT(P_STUD ENTID IN NUMBER,
P_NEWBOOK IN BOOKOBJ) AS
V_BOOKS BOOKLIST2;
V_FOUND BOOLEAN := FALSE;
V_BOOK V_BOOKOBJ;
BEGIN
-- FIRST GET THE CURRENT LIST OF BOOKS THIS STUDENT HAS CHECKED OUT.
BEGIN
SELECT BOOKS INTO V_BOOKS FROM CHECKED_OUT
WHERE STUDENT_ID = P_STUDENTID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- STUDENT HAS NO BOOKS CHECKED OUT
V_BOOKS := BOOKLIST2(NULL) ;
END;
-- SEARCH THE LIST TO SEE IF THIS STUDENT ALREADY HAS THIS BOOK.
FOR V_COUNTER IN 1..V_BOOKS.COUN T LOOP
V_BOOK := V_BOOKS(V_COUNT ER);
IF V_BOOK.CATALOG_ NUMBER = P_NEWBOOK.CATAL OG_NUMBER THEN
RAISE_APPLICATI ON_ERROR(-20001, 'BOOK IS ALREADY CHECKED OUT');
END IF;
END LOOP;
-- MAKE SURE THERE IS STILL ROOM
IF V_BOOK.COUNT = V_BOOK.LIMIT THEN
RAISE_APPLICATI ON_ERROR(-20002, 'CAN NOT CHECK OUT ANY MORE BOOK');
END IF;
-- CHECK OUT THE BOOK BY ADDING IT TO THE LIST.
V_BOOK.EXTEND;
V_BOOK(V_BOOK.C OUNT) := P_NEWBOOK;
-- AND PUT IT BACK IN THE DATABASE.
UPDATE CHECKED_OUT
SET BOOKS = V_BOOK
WHERE STUDENT_ID = P_STUDENTID;
IF SQL%NOTFOUND THEN
INSERT INTO CHECKED_OUT(STU DENT_ID,BOOKS)
VALUES(P_STUDEN TID,V_BOOK);
END IF;
END ;
/[/code]