Passing array to a stored procedure

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Passing array to a stored procedure

Post by 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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by 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.

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Oracle dotConnect Professional

Post by danyinfo » Mon 06 Jun 2011 07:59

Hello,
The Professional edition has this option?
Thank you.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by 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.

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Help

Post by 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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by 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.

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

Re: Passing array to a stored procedure

Post by 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

Post by 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

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Post by AlexP » Wed 05 Mar 2014 12:21

This is a restriction of the dbExpress technology, therefore we can't support this functionality

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

Re: Passing array to a stored procedure

Post by 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

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Post by 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.

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

Re: Passing array to a stored procedure

Post by 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

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Passing array to a stored procedure

Post by AlexP » Thu 14 Aug 2014 09:56

Yes, dbExpress driver for Oracle supports the work with temp tables.

Post Reply