Passing array to a stored procedure

Passing array to a stored procedure

Postby danyinfo » Thu 02 Jun 2011 09:14

Hello.
I want to pass an array to a stored procedure.
I have a type like: CREATE TYPE table_number AS TABLE OF number;
and i want to pass this array into procedure p$test(param$array table_number).
How can i do this?


Thank you.
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby AlexP » Thu 02 Jun 2011 11:45

Hello,

Unfortunately, our dbExpress driver for Oracle does not support object types as parameters. This restriction is connected with the specificity of the Borland dbExpress implementation. You can find more detailed information in the ../Devart/Dbx/Oracle/Readme.html fiile, the Known Problems section.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Oracle dotConnect Professional

Postby danyinfo » Mon 06 Jun 2011 07:59

Hello,
The Professional edition has this option?
Thank you.
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby AlexP » Mon 06 Jun 2011 10:24

Hello,

There is no Professional edition for dbExperss driver for Oracle. Only Standard Edition and Standard Edition with Source Code are available.
But as I have written earlier, you will not be able to use object types as procedure parameters in any version, because it's a dbExpress technology restriction.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Help

Postby danyinfo » Mon 06 Jun 2011 11:21

Hello again.
Thanks for the promptness with which you answered me.
Is there any possibility to use arrays with any of your product. Any solution?
I saw an older post about working with array and i wonder if it works in my case: http://www.devart.com/forums/viewtopic. ... ight=array

Thank you.
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby AlexP » Mon 06 Jun 2011 12:39

Hello,

You can work with Oracle object types with the help of our ODAC components (http://www.devart.com/odac/). In the OCI mode, all Oracle types and features are supported, the latest version Oracle 11 inclusive.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Postby costa » Tue 04 Mar 2014 01:23

Are table types such as "...type table of number" still not supported as parameters (in the dbexpress driver)?

If they are supported, could you please provide a sample? What data type should be used to populate the parameter?

Thank you
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: Passing array to a stored procedure

Postby costa » Tue 04 Mar 2014 02:21

I read the source code and I don't think it is supported. I basically looked at TCRSQLCommand.setParamDescValue. It doesn't handle the dtTable type parameters.

But how hard can it be to add support for TABLE types? The TOCIMetaData.GetProcedureParameters returns the type of table for Table type parameters. Is there anything else needed?

I would like to use this thread to request support for this type of parameters.

Thanks
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: Passing array to a stored procedure

Postby AlexP » Wed 05 Mar 2014 12:21

This is a restriction of the dbExpress technology, therefore we can't support this functionality
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Postby costa » Wed 12 Mar 2014 00:16

Alex, could you please be more specific, what is exactly that the dbexpress technology is restricting or it doesn't support?

Thank you
costa
 
Posts: 30
Joined: Thu 27 Aug 2009 20:30

Re: Passing array to a stored procedure

Postby AlexP » Thu 13 Mar 2014 10:56

Since dbExpress is designed for work with various databases, there is no implementation of specific data types for different databases in it. For example, in our DAC products, for support for such specific types, there are implemented separate classes for each type, and the is no such feature in dbExpress. Therefore you can work with common basic types only in dbExpress.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Postby barbados » Thu 14 Aug 2014 00:33

Does the dbExpress driver support Oracle temporary tables? Just an idea for a workaround for the PL/SQL stored subprogram parameter limitation - insert (any number of) records into a temporary table (transaction scoped data) and then call a stored PL/SQL subprogram (within the same transaction) to use the data instead of getting it through parameter passing.

Thanks.

barbados
barbados
 
Posts: 2
Joined: Mon 24 Feb 2014 19:02

Re: Passing array to a stored procedure

Postby AlexP » Thu 14 Aug 2014 09:56

Yes, dbExpress driver for Oracle supports the work with temp tables.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for Oracle