473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

hi friend,

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


Regards
Rabindra
Feb 6 '08 #1
3 2442
debasisdas
8,127 Expert 4TB
let i am using varray in procedure .then how to call it.
can you kindly post the code for reference of our experts please.
Feb 6 '08 #2
amitpatel66
2,367 Expert 2GB
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?
Feb 6 '08 #3
hi friend,

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


Regards
Rabindra
try this.....................
Expand|Select|Wrap|Line Numbers
  1. CREATE TYPE BOOKOBJJ AS OBJECT(
  2.  TITLE VARCHAR2(40), AUTHER VARCHAR2(40),
  3.  CATALOG_NUMBER NUMBER(4) );
  4. /
  5.  
  6.  CREATE OR REPLACE TYPE BOOKLIST2 AS VARRAY(10) OF BOOKOBJ;
  7.  /
  8.  
  9. CREATE OR REPLACE PROCEDURE CHECKOUT(P_STUDENTID IN NUMBER,
  10. P_NEWBOOK IN BOOKOBJ) AS
  11. V_BOOKS BOOKLIST2;
  12. V_FOUND BOOLEAN := FALSE;
  13. V_BOOK V_BOOKOBJ;
  14. BEGIN
  15. -- FIRST GET THE CURRENT LIST OF BOOKS THIS STUDENT HAS CHECKED OUT.
  16. BEGIN
  17. SELECT BOOKS INTO V_BOOKS FROM CHECKED_OUT
  18. WHERE STUDENT_ID = P_STUDENTID;
  19. EXCEPTION 
  20. WHEN NO_DATA_FOUND THEN
  21. -- STUDENT HAS NO BOOKS CHECKED OUT
  22. V_BOOKS := BOOKLIST2(NULL);
  23. END;
  24. -- SEARCH THE LIST TO SEE IF THIS STUDENT ALREADY HAS THIS BOOK.
  25. FOR V_COUNTER IN 1..V_BOOKS.COUNT LOOP
  26. V_BOOK := V_BOOKS(V_COUNTER);
  27. IF V_BOOK.CATALOG_NUMBER = P_NEWBOOK.CATALOG_NUMBER THEN
  28. RAISE_APPLICATION_ERROR(-20001, 'BOOK IS ALREADY CHECKED OUT');
  29. END IF;
  30. END LOOP;
  31. -- MAKE SURE THERE IS STILL ROOM 
  32. IF V_BOOK.COUNT = V_BOOK.LIMIT THEN
  33. RAISE_APPLICATION_ERROR(-20002, 'CAN NOT CHECK OUT ANY MORE BOOK');
  34. END IF;
  35. -- CHECK OUT THE BOOK BY ADDING IT TO THE LIST.
  36. V_BOOK.EXTEND;
  37. V_BOOK(V_BOOK.COUNT) := P_NEWBOOK;
  38. -- AND PUT IT BACK IN THE DATABASE.
  39. UPDATE CHECKED_OUT 
  40. SET BOOKS = V_BOOK
  41. WHERE STUDENT_ID = P_STUDENTID;
  42. IF SQL%NOTFOUND THEN
  43. INSERT INTO CHECKED_OUT(STUDENT_ID,BOOKS)
  44. VALUES(P_STUDENTID,V_BOOK);
  45. END IF;
  46. END ;
  47. /
Feb 7 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Tomislav Petrovic | last post by:
I have following code on my page.... $ora_tns = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pirana)(PORT=1521)) )(CONNECT_DATA=(SID=pirana)(SERVER=DEDICATED)))"; $ora_username =...
0
by: PedroSilva | last post by:
I need to know how to get a collection (varray), basically it's an array of chars with size of 9999 positions, from oracle so I can use it as an array with php. There's very little documentation...
1
by: KVN Chary | last post by:
I wrote a small package and it successfully compiled. When I test the package it was giving an error. Please look into the below code. Can anyone written Varrays as input and output variables in...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
0
by: arvind | last post by:
Hello, We have been working on migrating oracle database to postgres for one of our client. We have a stored procedure in oracle which uses varray and I have to convert this stored procedure to...
1
by: deepdata | last post by:
Hi, I am trying to fetch data from db2 (express version) database by calling stored procedure. I have tried to use both cursor and for loop but still i am getting error. --======Start...
4
by: aaron80v | last post by:
Hi, Currently I am embarked on a project that requires me to define and pass array to stored procedure, and I found out that there is no direct way to do this. I wonder if a combination of UDF...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.