Bind variables in PL/SQL blocks in oraperl

From: Guy Harrison <gharriso_at_werple.apana.org.au>
Date: 19 Jan 1994 13:19:10 +1100
Message-ID: <2hi5au$b2h_at_werple.apana.org.au>

I've been trying to write some oraperl utilities which call ORACLE stored procedures. I need to get values back from the stored procedure either by function return values or by using "IN OUT" parameters. Unfortunately, oraperl (2.4) as distributed doesn't seem to support this. If I go:

  local($np_stmt)=
    ("begin

        pratt_pr.new_pratt(null,null,:1,null,null,null,null);
      end; ");

  $np_csr=&ora_open($lda,$np_stmt)
||&abend("Open np_stmt:$ora_errstr\n$np_stmt\n");
 $rc=&ora_bind($np_csr,$opt_d)

||&abend("Bind np_stmt:$ora_errstr");

I get

ORA-01006: bind variable does not exist

if i use :name rather than :1, I get

ORA-01008: not all variables bound

Looking at the OCI manual and the orafns.c file it seems the problem is:

        oraperl uses the obndrn function which binds using numeric
        placeholders (:1,:2,:3).

        PL/SQL blocks do not recognize numbered placeholders - you need
        to use the obndrv (or obndra) functions.

So it looks like what I need to do is write a special ora_bind that uses named placeholders. However, as I am not much of a C programmer, the idea does not thrill me!

Has anyone run into this problem? Perhaps found (or coded) a solution?

Is there a version of oraperl which has true ORACLE7 support in the wings?

Also, I need access to the long message text as given by the PRO*C glm function. Otherwise, I can't see the text returned by raise_application_error;

Any help would be greatly appreciated!

Guy

---

Guy Harrison                        gharriso_at_werple.apana.org.au
ORACLE  Database Consultant         gharriso_at_vitgwms1.telecom.com.au

Synchronous Software P/L
Melbourne Australia Received on Wed Jan 19 1994 - 03:19:10 CET