[cx-oracle-users] ORA-01008: not all variables bound

[Tamás G. <gt...@gt...>]

Hi,

I've thought that dict params are for named variables, and the
placeholders can appear any order, anytime, any frequency. But with
the code below I get the following traceback:

Traceback (most recent call last):
  File "<stdin>", line 21, in <module>
cx_Oracle_dbg.DatabaseError: ORA-01008: not all variables bound



import cx_Oracle
import sys
cx = cx_Oracle.connect('$DSN')
cu = cx.cursor()
params = {'inp': [1, 2], 'out': cu.arrayvar(cx_Oracle.STRING, ['']*1000)}
#cu.execute("ALTER SESSION SET cursor_sharing='EXACT'")
cu.execute("""DECLARE
    TYPE in_tabTyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
        in_tab in_tabTyp := :inp;
        TYPE out_tabTyp IS TABLE OF VARCHAR2(1000) INDEX BY PLS_INTEGER;
        out_tab out_tabTyp;
        v_idx PLS_INTEGER;
BEGIN
        v_idx := in_tab.FIRST;
        WHILE v_idx IS NOT NULL LOOP
            SELECT SUBSTR(DUMP(in_tab(v_idx)), 1, 1000) INTO
out_tab(v_idx) FROM DUAL;
                v_idx := in_tab.NEXT(v_idx);
        END LOOP;
        :out := out_tab;
    DBMS_OUTPUT.PUT_LINE(:out.COUNT);
END;""", params)
out = params['out'].getvalue()
print repr(out)


I've tried setting the cursor_sharing option
(http://www.fuadarshad.com/2011/02/grid-control-targets-losing-config.html),
to no avail.

If I delete the one-before-last line
(DBMS_OUTPUT.PUT_LINE(:out.COUNT);) or change :out in it to out_tab,
then the script runs fine.

Any ideas?

Thanks in advance,
Tamás Gulácsi