Pass string array to oracle stored procedure.

Hi there,

Am trying to pass a string array to an ORACLE 8i stored procedure, I have
tested the procedure on the server and know it works, PB7 is giving me any
error though when trying to save ;

'variable reference in database statement has unsupported data type'

My PB code is;

dec{0} ld_sys_ed_key
STRING lsa_inp_key[],lsa_inp_val[]

//Initialise
lsa_inp_key[1] = '1900000137'
lsa_inp_key[2] = '1900000139'
lsa_inp_val[1] = 'ORCLEVAL'
lsa_inp_val[2] = 'YES'

ld_sys_ed_key = 0123456789
DECLARE insert_into_system_inputs PROCEDURE FOR
cameng.sms.insert_into_system_inputs(
:ld_sys_ed_key,
:lsa_inp_key[],
:lsa_inp_val[]) USING SQLCA;
EXECUTE insert_into_system_inputs;
CLOSE insert_into_system_inputs;

The procedure uses a table of SCALARS defined as a type in a package i.e.

type strArray is table of VARCHAR2(255) index by binary_integer;

procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in in
strArray,
inp_val_in in strArray);

procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in in
strArray,
inp_val_in in strArray)
IS
sys_inp_key_in      NUMBER := 0;

  BEGIN
    /*Insert all sys_inputs values to system_inputs*/
    for i in 1 .. inp_key_in.count loop
        INSERT INTO CAMENG.SYSTEM_INPUTS VALUES (sys_inp_key_in,
inp_key_in(i), inp_val_in(i));
    end loop;
    EXCEPTION
        WHEN OTHERS then
          ROLLBACK;
  END insert_into_system_inputs;

Not sure if the DECLARE method will allow me to pass arrays of values to a
stored procedure, any ideas ?

Also very hard to find any info on Powerbilder -> ORACLE datat type mappings
has anyone got a reference for this info ?

Many thanks,
Mully


0
Niall
8/19/2005 1:50:18 PM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

3 Replies
1380 Views

Similar Articles

[PageSpeed] 27

The online help, in a section entitled "Supported features when using
Oracle stored procedures" indicates that the DECLARE method doesn't
support PL/SQL table types.  You have to use the RPCFUNC method to do
that.

On 19 Aug 2005 06:50:18 -0700, "Niall Mullan"
<nmullan@nortelnetworks.com> wrote:

>Hi there,
>
>Am trying to pass a string array to an ORACLE 8i stored procedure, I have
>tested the procedure on the server and know it works, PB7 is giving me any
>error though when trying to save ;
>
>'variable reference in database statement has unsupported data type'
>
>My PB code is;
>
>dec{0} ld_sys_ed_key
>STRING lsa_inp_key[],lsa_inp_val[]
>
>//Initialise
>lsa_inp_key[1] = '1900000137'
>lsa_inp_key[2] = '1900000139'
>lsa_inp_val[1] = 'ORCLEVAL'
>lsa_inp_val[2] = 'YES'
>
>ld_sys_ed_key = 0123456789
>DECLARE insert_into_system_inputs PROCEDURE FOR
>cameng.sms.insert_into_system_inputs(
>:ld_sys_ed_key,
>:lsa_inp_key[],
>:lsa_inp_val[]) USING SQLCA;
>EXECUTE insert_into_system_inputs;
>CLOSE insert_into_system_inputs;
>
>The procedure uses a table of SCALARS defined as a type in a package i.e.
>
>type strArray is table of VARCHAR2(255) index by binary_integer;
>
>procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in in
>strArray,
>inp_val_in in strArray);
>
>procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in in
>strArray,
>inp_val_in in strArray)
>IS
>sys_inp_key_in      NUMBER := 0;
>
>  BEGIN
>    /*Insert all sys_inputs values to system_inputs*/
>    for i in 1 .. inp_key_in.count loop
>        INSERT INTO CAMENG.SYSTEM_INPUTS VALUES (sys_inp_key_in,
>inp_key_in(i), inp_val_in(i));
>    end loop;
>    EXCEPTION
>        WHEN OTHERS then
>          ROLLBACK;
>  END insert_into_system_inputs;
>
>Not sure if the DECLARE method will allow me to pass arrays of values to a
>stored procedure, any ideas ?
>
>Also very hard to find any info on Powerbilder -> ORACLE datat type mappings
>has anyone got a reference for this info ?
>
>Many thanks,
>Mully
>

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

In Defense of Top Posting
http://alpage.ath.cx/toppost/toppost.htm

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org
0
Bruce
8/19/2005 2:08:06 PM
Thanks,

I read this but I am not sure where I declare my functions for this. One
article I read said to make them  inside the transaction user object, our
transaction object is SQCLA but it is not declared in its own option or
anything just in the additional properties of the application ??

Any ideas ?



"Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
wrote in message news:dspbg15p3hodobfcffen04fc81umobn650@4ax.com...
> The online help, in a section entitled "Supported features when using
> Oracle stored procedures" indicates that the DECLARE method doesn't
> support PL/SQL table types.  You have to use the RPCFUNC method to do
> that.
>
> On 19 Aug 2005 06:50:18 -0700, "Niall Mullan"
> <nmullan@nortelnetworks.com> wrote:
>
> >Hi there,
> >
> >Am trying to pass a string array to an ORACLE 8i stored procedure, I have
> >tested the procedure on the server and know it works, PB7 is giving me
any
> >error though when trying to save ;
> >
> >'variable reference in database statement has unsupported data type'
> >
> >My PB code is;
> >
> >dec{0} ld_sys_ed_key
> >STRING lsa_inp_key[],lsa_inp_val[]
> >
> >//Initialise
> >lsa_inp_key[1] = '1900000137'
> >lsa_inp_key[2] = '1900000139'
> >lsa_inp_val[1] = 'ORCLEVAL'
> >lsa_inp_val[2] = 'YES'
> >
> >ld_sys_ed_key = 0123456789
> >DECLARE insert_into_system_inputs PROCEDURE FOR
> >cameng.sms.insert_into_system_inputs(
> >:ld_sys_ed_key,
> >:lsa_inp_key[],
> >:lsa_inp_val[]) USING SQLCA;
> >EXECUTE insert_into_system_inputs;
> >CLOSE insert_into_system_inputs;
> >
> >The procedure uses a table of SCALARS defined as a type in a package i.e.
> >
> >type strArray is table of VARCHAR2(255) index by binary_integer;
> >
> >procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in
in
> >strArray,
> >inp_val_in in strArray);
> >
> >procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in
in
> >strArray,
> >inp_val_in in strArray)
> >IS
> >sys_inp_key_in      NUMBER := 0;
> >
> >  BEGIN
> >    /*Insert all sys_inputs values to system_inputs*/
> >    for i in 1 .. inp_key_in.count loop
> >        INSERT INTO CAMENG.SYSTEM_INPUTS VALUES (sys_inp_key_in,
> >inp_key_in(i), inp_val_in(i));
> >    end loop;
> >    EXCEPTION
> >        WHEN OTHERS then
> >          ROLLBACK;
> >  END insert_into_system_inputs;
> >
> >Not sure if the DECLARE method will allow me to pass arrays of values to
a
> >stored procedure, any ideas ?
> >
> >Also very hard to find any info on Powerbilder -> ORACLE datat type
mappings
> >has anyone got a reference for this info ?
> >
> >Many thanks,
> >Mully
> >
>
> Bruce Armstrong [TeamSybase]
> http://www.teamsybase.com
>
> In Defense of Top Posting
> http://alpage.ath.cx/toppost/toppost.htm
>
> Preach the gospel at all times. If necessary, use words. - Francis of
Assisi
> http://www.needhim.org


0
Niall
8/19/2005 2:48:17 PM
Found in help how to inherit from transaction and use stored procedures
under the new transaction objects.

Cheers again,
Mully


"Niall Mullan" <nmullan@nortelnetworks.com> wrote in message
news:4305f131@forums-1-dub...
> Thanks,
>
> I read this but I am not sure where I declare my functions for this. One
> article I read said to make them  inside the transaction user object, our
> transaction object is SQCLA but it is not declared in its own option or
> anything just in the additional properties of the application ??
>
> Any ideas ?
>
>
>
> "Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
> wrote in message news:dspbg15p3hodobfcffen04fc81umobn650@4ax.com...
> > The online help, in a section entitled "Supported features when using
> > Oracle stored procedures" indicates that the DECLARE method doesn't
> > support PL/SQL table types.  You have to use the RPCFUNC method to do
> > that.
> >
> > On 19 Aug 2005 06:50:18 -0700, "Niall Mullan"
> > <nmullan@nortelnetworks.com> wrote:
> >
> > >Hi there,
> > >
> > >Am trying to pass a string array to an ORACLE 8i stored procedure, I
have
> > >tested the procedure on the server and know it works, PB7 is giving me
> any
> > >error though when trying to save ;
> > >
> > >'variable reference in database statement has unsupported data type'
> > >
> > >My PB code is;
> > >
> > >dec{0} ld_sys_ed_key
> > >STRING lsa_inp_key[],lsa_inp_val[]
> > >
> > >//Initialise
> > >lsa_inp_key[1] = '1900000137'
> > >lsa_inp_key[2] = '1900000139'
> > >lsa_inp_val[1] = 'ORCLEVAL'
> > >lsa_inp_val[2] = 'YES'
> > >
> > >ld_sys_ed_key = 0123456789
> > >DECLARE insert_into_system_inputs PROCEDURE FOR
> > >cameng.sms.insert_into_system_inputs(
> > >:ld_sys_ed_key,
> > >:lsa_inp_key[],
> > >:lsa_inp_val[]) USING SQLCA;
> > >EXECUTE insert_into_system_inputs;
> > >CLOSE insert_into_system_inputs;
> > >
> > >The procedure uses a table of SCALARS defined as a type in a package
i.e.
> > >
> > >type strArray is table of VARCHAR2(255) index by binary_integer;
> > >
> > >procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in
> in
> > >strArray,
> > >inp_val_in in strArray);
> > >
> > >procedure insert_into_system_inputs(sys_ed_key_in in NUMBER, inp_key_in
> in
> > >strArray,
> > >inp_val_in in strArray)
> > >IS
> > >sys_inp_key_in      NUMBER := 0;
> > >
> > >  BEGIN
> > >    /*Insert all sys_inputs values to system_inputs*/
> > >    for i in 1 .. inp_key_in.count loop
> > >        INSERT INTO CAMENG.SYSTEM_INPUTS VALUES (sys_inp_key_in,
> > >inp_key_in(i), inp_val_in(i));
> > >    end loop;
> > >    EXCEPTION
> > >        WHEN OTHERS then
> > >          ROLLBACK;
> > >  END insert_into_system_inputs;
> > >
> > >Not sure if the DECLARE method will allow me to pass arrays of values
to
> a
> > >stored procedure, any ideas ?
> > >
> > >Also very hard to find any info on Powerbilder -> ORACLE datat type
> mappings
> > >has anyone got a reference for this info ?
> > >
> > >Many thanks,
> > >Mully
> > >
> >
> > Bruce Armstrong [TeamSybase]
> > http://www.teamsybase.com
> >
> > In Defense of Top Posting
> > http://alpage.ath.cx/toppost/toppost.htm
> >
> > Preach the gospel at all times. If necessary, use words. - Francis of
> Assisi
> > http://www.needhim.org
>
>


0
Niall
8/19/2005 3:54:18 PM
Reply:

Similar Artilces:

Passing Array to Oracle Stored Procedure
I have requirement where in I need to pass a array from Powerbuilder to Oracle Stored procedure and also I want to know how to access the returned array from the Oracle stored procedure. Any help in this regard is appreciated. Thanks -Sanjay ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp Make it a ref in the RPCFUNC declaration. It will be an INOUT argument with a TABLE OF type in the procedure or function. You will need to pre-fill the array [to maximum length if it is type string] *before* the call. On return the values wil...

Passing an array of strings to a Stored Procedure
Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind] GO /****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[gv_sp_UpdatePOs] ( @IDList varchar(500), @ReorderLevel int, @ProductName nvarchar(30)   ) AS BEGIN SET NOCOUNT ON  EXEC('Update dbo.Products SET ReorderLevel = (' + @Reorde...

Passing array argument to Oracle stored procedure
I have PB 7.0.3 build 10047 and O84 driver running on Windows 2000 with Oracle 8i on a DEC Alpha. Is this the driver we should be using? If not what should we be using and where do I get it? I am getting an error message while passing an array argument to a stored procedure: Select error: ORA-06550: line 1, column 16: PLS-00306: wrong number or types of arguments in call to ...ORA-06550: line 1, column 16: PL/SQL statement ignored. And I do have SQLCA.dbparm ="pbdbms=0" It works fine if I pass a value that is not an array. Here is my pl/sql program: CREATE OR ...

pass array to store procedure in dbi oracle
------_=_NextPart_001_01C2C838.DC51E8C5 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi is it posible to send an array to a store procedure in dbi ( oracle ) ? thanks =20 =20 Ran Aker R&D Programmer tel: 972.9.892.1052 =20 DealTime=AE<?xml:namespace prefix =3D o /> Compare products, prices, and=20 stores at www.dealtime.com <http://www.dealtime.com/>=20 =20 ------_=_NextPart_001_01C2C838.DC51E8C5-- ...

Is it possible to pass an array to a stored procedure -oracle
Hi, I am new to stored procedure based datawindows. Pb 6.5,Oracle 7.3.3. Could anybody clarify me that can we the pass the data in a DW to a oracle stored procedure as a input param in the form of an array or PL/SQL tables or some other means? Thanx mega Support for PL/SQL table arguments is limited to RPC calls with arrays of atomic data types. Arrays of records are not supported. meghavathy <mathi_a@hotmail.com> wrote in message news:8#5esslb$GA.184@forums.sybase.com... > Hi, > I am new to stored procedure based datawindows. > Pb 6.5,Oracle 7.3.3. >...

(Fwd) Pass Arrays to Stored Procedures in Oracle
----- Forwarded message from "Grimes, Greg" <ggrimes@ercot.com> ----- Delivered-To: tim.bunce@pobox.com Subject: Pass Arrays to Stored Procedures in Oracle Date: Wed, 13 Aug 2003 14:28:38 -0500 From: "Grimes, Greg" <ggrimes@ercot.com> To: <Tim.Bunce@pobox.com>, "Steve Baldwin" <stbaldwin@multiservice.com> X-OriginalArrivalTime: 13 Aug 2003 19:28:38.0385 (UTC) FILETIME=[1883B610:01C361D1] Steve, I saw your posting concerning pipelined functions. How does the perl script benefit from this approach? I would assume the perl scri...

ora_type and passing array to an oracle stored procedure
------_=_NextPart_001_01C1897F.15FAE2A0 Content-Type: text/plain Hello, I have two questions, one which I think is probably simple. 1. (the simple one). Is there a list of all the ora_type codes somewhere (e.g. ORA_CLOB, 24) 2. How do I pass an array from perl to a PL/SQL stored procedure using DBI? I have the following types in my database: SQL> desc id_rec Name Null? Type ----------------------------------------- -------- ------------------------ ---- ID NUMBER SQL> des...

all arguments passed as string in stored procedure datawindow with Oracle?
Can someone please explain me this? I'm using PB8.0.3 build 9704 with Oracle 9.2. There is a datawindow based on a stored procedure with the following execute statement and retrieval arguments: procedure="1 execute proc1;0 p_kdnr = :an_kdnr, p_zeit = :adt_date, p_kond = 0, p_lg = 'N', p_atyp = :as_typ" arguments=(("as_typ", string), ("an_kdnr", number), ("adt_date", datetime), ("ac_wfak", number), ("as_wakz", string), ("as_name", string)) The stored procedure in Oracle looks like this: CREATE...

passing string array to a stored procedure : PB scrash occured
My PB configuration is PB 651 My OS is NT WS pack 5 My DB driver is Oracle 734 on my DB server, the OS is NT4 WS pack 5 and the Oracle version is 8.1.5 I tried to use the feature ' pass arguments array to a stored procedure' It works good for number arrays, even if some values are set to NULL, but with string array, if there is one or more NULL values into my array, there is a scrash into my application ( PB60.exe , ..... ) Do you know that problem ? is it bind with my PB version, with my oracle client version, with my server version ? Patrick, Check my answer to ...

Passing Arrays to Oracle 7.3 stored procedures as argument
From datawindow it's possible to pass array argument to SQL statement. Is it possible to pass array as argument to Oracle 7.3 stored procedure ? The easiest is proably passing a string that is not comma-delimited and then parse it out in your sp. -- Terry Dykstra (TeamSybase) Canadian Forest Oil Ltd. Sybase Developer's Network: http://www.sybase.com/sdn "Evgeny" <evgeny@cool.mb.ca> wrote in message news:3925305c.173883938@forums.sybase.com... > From datawindow it's possible to pass array argument to SQL statement. > Is it possible to pass a...

How can I pass a bi-dimensional array to an oracle stored procedure ?
Suppose that I have the following vb.net bi-dimensional array: Dim ds As DataSet = SomeDataSet() Dim num_cols As Integer num_cols = ds.Tables(0).Columns.Count - 1 Dim num_rows As Integer num_rows = ds.Tables(0).Rows.Count - 1 Dim zmatrix(num_rows, num_cols) As StringDim row As Integer = 0 For Each rw As DataRow In ds.Tables(0).Rows Dim col As IntegerFor col = 0 To num_cols - 1 zmatrix(row, col) = "" NextFor col = 0 To num_cols - 1 If Not IsDBNull(rw(col)) Then zmatrix(row, col) = rw(col).ToString() End If Next row = row + 1 NextHow can I pass that bi-dim...

Session Array and pass array in stored procedure
hi all 1. it is possible to use session array. 2. i want to store group of values in session.  3. finally i want pass this session in stored procedure. give me some source 1. it is possible to use session array. , yes you can create an array of sessions if you want 2. i want to store group of values in session. yes you can store collections,objects .....UDT 3. finally i want pass this session in stored procedure. give me some source....dont think you can do this, it kind of goes against best practices....usually your sql box should NOT be on your web server for performance a...

Passing array parm to Oracle stored procedure
begin 644 sample.sql M0U)%051%($]2(%)%4$Q!0T4@4$%#2T%'12!P:V=?<&QS<6Q?=&%B;&5?=&5S M=`T*25,-"B`@5%E012!E;7!?:61?=&)L($E3(%1!0DQ%($]&($Y534)%4B!) M3D1%6"!"62!"24Y!4EE?24Y414=%4CL-"B`@5%E012!M>5]R97-U;'1?<V5T M($E3(%)%1B!#55)33U([#0H@(%!23T-%1%5212!S<%]P;'-Q;%]T86)L95]T M97-T*&%R9U]E;7!?:60@24X@96UP7VED7W1B;"P@(&%R9U]E;7!?;F%M92!) M3B!/550@;7E?<F5S=6QT7W-E="D[#0I%3D0@<&MG7W!L<W%L7W1A8FQE7W1E M<W0[#0HO#0I#4D5!5$4@3U(@4D503$%#12!004-+04=%($)/1%D@<&MG7W!L M<W%L7W1A8FQE7W1E...

Passing array parm to Oracle stored procedure
I have PB 7.0.3 build 10047 and O84 driver running on Windows 2000 with Oracle 8i on a DEC Alpha. Is this the driver we should be using? If not what should we be using and where do I get it? I am getting an error message while passing an array parameter to a stored procedure: Select error: ORA-06550: line 1, column 16: PLS-00306: wrong number or types of arguments in call to ...ORA-06550: line 1, column 16: PL/SQL statement ignored. And I do have SQLCA.dbparm ="pbdbms=0" It works fine if I pass a value that is not an array. Thanks. I, for one, can't read atta...

Web resources about - Pass string array to oracle stored procedure. - sybase.powerbuilder.database

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

Metal Gear Online PS4 release trolled by ancient PSN update procedures
One of the chief problems I have with the PS4 is the way the storefront (and subsequently, the release of most games) works. Not only has the ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

resources last updated: 11/20/2015 2:26:17 PM