Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #83464 > unrolled thread

cx_Oracle, callfunc and varray

Back to article view | Back to comp.lang.python

Contents

  cx_Oracle, callfunc and varray Dom <dominic.giles@gmail.com> - 2015-01-09 11:24 -0800
    Re: cx_Oracle, callfunc and varray John Gordon <gordon@panix.com> - 2015-01-09 21:10 +0000
    Re: cx_Oracle, callfunc and varray Ian Kelly <ian.g.kelly@gmail.com> - 2015-01-10 00:30 -0700

#83464 — cx_Oracle, callfunc and varray

Hi

I'm trying to return a simple array of numbers from a package using cx_oracle (5.1.2). I believe this is possible. I've not been able to find anything that suggest it isn't

create or replace TYPE NUMARRAY
-- Simple VArray of numbers
is VARRAY(3) OF NUMBER;
/

create or replace PACKAGE SIMPLEPACKAGE
AS
  FUNCTION DoSomethingSimple(
      cust_id INTEGER)
    RETURN numarray;
  FUNCTION DoSomethingSimpler(
      cust_id INTEGER)
    RETURN INTEGER;
END SIMPLEPACKAGE;
/

create or replace PACKAGE BODY SIMPLEPACKAGE
AS
FUNCTION DOSOMETHINGSIMPLE(
    cust_id INTEGER)
  RETURN numarray
AS
  simple_array numarray := numarray();
BEGIN
  simple_array.extend;
  simple_array(1) := cust_id;
  simple_array.extend;
  simple_array(2) := cust_id;
  simple_array.extend;
  simple_array(3) := cust_id;
  RETURN SIMPLE_ARRAY;
END DOSOMETHINGSIMPLE;
FUNCTION DOSOMETHINGSIMPLER(
    cust_id INTEGER)
  RETURN INTEGER
AS
BEGIN
  RETURN cust_id;
END DOSOMETHINGSIMPLER;
END SIMPLEPACKAGE;
/

The python (2.7) is very simple

import cx_Oracle

if __name__ == '__main__':
     with cx_Oracle.connect('soe', 'soe', 'oracle12c2/soe') as connection:
        try:
            cursor = connection.cursor();
            ArrayType = cursor.arrayvar(cx_Oracle.NUMBER,3)
            NumberType = cursor.var(cx_Oracle.NUMBER)
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLER", NumberType, [99])
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLE", ArrayType, [99])
        except cx_Oracle.DatabaseError as dberror:
            print dberror
        finally:
            cursor.close()

The call to return works just fine. The call to return the function gives the error

ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Any ideas what I'm doing wrong?

Dom

[toc] | [next] | [standalone]

#83465

In <db700d22-f8a0-40bb-9e01-1a9a0caba1f1@googlegroups.com> Dom <dominic.giles@gmail.com> writes:

> create or replace PACKAGE SIMPLEPACKAGE
> AS
>   FUNCTION DoSomethingSimple(
>       cust_id INTEGER)
>     RETURN numarray;
>   FUNCTION DoSomethingSimpler(
>       cust_id INTEGER)
>     RETURN INTEGER;
> END SIMPLEPACKAGE;
> /

> Any ideas what I'm doing wrong?

Is RETURN INTEGER; allowed?

-- 
John Gordon         Imagine what it must be like for a real medical doctor to
gordon@panix.com    watch 'House', or a real serial killer to watch 'Dexter'.

[toc] | [prev] | [next] | [standalone]

#83492

On Fri, Jan 9, 2015 at 12:24 PM, Dom <dominic.giles@gmail.com> wrote:
> Hi
>
> I'm trying to return a simple array of numbers from a package using cx_oracle (5.1.2). I believe this is possible. I've not been able to find anything that suggest it isn't

I'm afraid I don't have an answer for you, but you would probably have
better luck asking on the cx-oracle-users mailing list:
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web