Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Collections
PLSQL Collections [message #240086] Thu, 24 May 2007 01:50 Go to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Hi
When and in which scenario PLSQL Tables,VARRAYS are used, can anyone in this forum highlight this.
Thanks and Regards
Sibghat
Re: PLSQL Collections [message #240097 is a reply to message #240086] Thu, 24 May 2007 02:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read this article
Re: PLSQL Collections [message #240150 is a reply to message #240086] Thu, 24 May 2007 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And of course PL/SQL User's Guide and Reference, Chapter 5 Using PL/SQL Collections and Records

Regards
Michel
Re: PLSQL Collections [message #240170 is a reply to message #240150] Thu, 24 May 2007 05:22 Go to previous messageGo to next message
shivkumarora
Messages: 3
Registered: May 2007
Junior Member
Hai
This is my prcodeure

create or replace procedure sp_update_tbl(empid IN varchar2,empname IN varchar2)
AS
BEGIN

UPDATE employee set name=empname where emp_code=empid ;
END sp_get_tbl;

The above procedure is working fine...

Instead of passing parameters empid,empname as varchar. i need to get it from hashtable.
So how to pass hashtable and get values from hashtable in procedure.
Pls send a sample procedure

Thanks
Re: PLSQL Collections [message #240171 is a reply to message #240170] Thu, 24 May 2007 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a hashtable?

Regards
Michel
Re: PLSQL Collections [message #240173 is a reply to message #240171] Thu, 24 May 2007 05:29 Go to previous messageGo to next message
shivkumarora
Messages: 3
Registered: May 2007
Junior Member
Hi Michale

Hashtable contains key and value pair.
I have to get values in hashtable based on the key in hashtable.
How to do this in stored procedure.

Thanks for ur reply
Re: PLSQL Collections [message #240520 is a reply to message #240086] Fri, 25 May 2007 02:35 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Oracle doesn't have a native "hashtable" datatype, but it does have associative arrays which are indexed by the "key".

See here.

Regards
Re: PLSQL Collections [message #242497 is a reply to message #240520] Sat, 02 June 2007 19:00 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
mchadder wrote on Fri, 25 May 2007 08:35
Oracle doesn't have a native "hashtable" datatype, but it does have associative arrays which are indexed by the "key".

See here.


I'm also puzzled about the requirement to pass a "hashtable", presumably some sort of collection type. I'm guessing one of the two SQL collection types (Nested Table or Varray) is more likely to be compatible with the client developer toolset than the PL/SQL-only Associative Array, but who knows?

IMHO though, the article linked to makes the same mistake as the Oracle documentation, in that it claims to offer developers guidelines for choosing the right type, but then assumes you are interested in storing the collection as a column of a database table (trust me, you aren't) and mixes storage considerations ("preserving element order", the "STORE AS" clause) with PL/SQL programming ("can be indexed by non-integer").

A Varray (which you will probably never need) is just a functionally crippled version of a nested table, with a LIMIT clause.

This came up here: http://forums.oracle.com/forums/thread.jspa?messageID=1846802
Previous Topic: USER_TAB_COLUMNS and Other Schemas
Next Topic: Query Help!!!!!!!!!!!!!!
Goto Forum:
  


Current Time: Sun Dec 04 06:55:34 CST 2016

Total time taken to generate the page: 0.04609 seconds