oratcl and collections 
Author Message
 oratcl and collections

I can call an oracle function which returns a single number, but when
the function returns a collection I am getting an execute error.

I have found this issue mentioned once or twice without a soloution
presented, has anyone had luck in solving this?

The collection is simple:
sql>DESC NV_VC_VC_TABTYPE

NV_VC_VC_TABTYPE TABLE OF NV_VC_VC_TYPE
Name                                      Null?    Type
----------------------------------------- -------- --------------
NAME                                               VARCHAR2(2000)
VALUE                                              VARCHAR2(2000)

----------------------------------

#TCL snippet with function that returns a single value that works
#and a function that returns a collection and doesnt work

package require Oratcl

if [catch {oralogon _username_/_password_} handle] {
        puts "couldn't logon to Oracle : $handle"
        exit(0)

Quote:
}

if [catch {oraopen $handle} shandle] {
        puts "couldn't get statement handle : $shandle"
        exit(0)

Quote:
}

#single value function call
set plsql {begin :model_t := getstatename(2); end; }
oraparse $shandle $plsql
orabind $shandle :model_t ""
oraexec $shandle

# Happily, this works...
#print
if {[orafetch $shandle -dataarray params] == 0} {
  puts $params(:model_t)

Quote:
}

#function that returns a collection
set plsql {begin :model_t := getStateList(); end; }
oraparse $shandle $plsql
orabind $shandle :model_t ""

#
# THIS IS WHERE IT CRASHES!!!
#
oraexec $shandle

oraclose $shandle
oralogoff $handle

----------------------------------------------------------------------------------------------

Any advice? Should I just give up on the collection idea?

Regards,
James



Mon, 23 Jun 2008 07:54:44 GMT  
 oratcl and collections

Hi James,

I never really implemented collection types.  As of this time, Oratcl
will not handle any user defined types.  Your only real avenue here is
to use a little anonymous PL/SQL block to return the collection
elements as single colums.

I would consider adding this feature in the future.  I never really
thought about it til you asked.

-Todd



Wed, 16 Jul 2008 05:16:17 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Oratcl logon problems on SunOS with oratcl-2.3 and Oracle 7.2.3

2. Collection of collections, how to collect?

3. collection inside a collection: how to selectively collect?

4. oratcl versions

5. Announce:: Oratcl 4.4 now available on sourceforge.net

6. Oratcl Bind Question

7. Oratcl

8. oratcl arm-linux: ORACLE 9i Client Libs ??

9. Oratcl

10. Poll of Oratcl Users for feature enhancement.

11. Oratcl 4.3 html page is out of sync with downloaded zipfile for windows

12. Problem with oratcl with HP-UX 11i v1

 

 
Powered by phpBB® Forum Software