ORA-1006 bind variable doesn't exists while using dbms_sql.bind_variable function

As a workaround, you could avoid using dynamic SQL. Instead of coding the asterisk ("*") in the SELECT list, specify the column names you need returned. And include the argument variables in the SQL text; Oracle does automatic binds for you.

CREATE OR REPLACE PROCEDURE ... 
IS
  CURSOR lcsr_txn IS
  SELECT t.cntAcct
       , t.custAcct
       , t.cardNo
       , t.Code
    FROM txn t
   WHERE t.cntAcct  = IN_cntAcct
     AND t.custAcct = IN_custAcct
     AND t.cardNo   = IN_cardNo
     AND t.Code     = IN_Code
  ;
BEGIN
  FOR lrec IN lcsr_txn LOOP
    -- code to process rows goes here e.g.
    DBMS_OUTPUT.PUT_LINE("cntAcct="||lrec.cntAcct);
  END LOOP;
END;

ORIGINAL ANSWER

That's a head scratcher. There's nothing obviously wrong in this code. (Given that the procedure works most of the time, I'm going to assume that the datatypes on the parameters are specified, and that these are all IN parameters.)

It's conceivable that the parse works, you get a cursor handle back, but the cursor is getting invalidated before all of the calls to bind_variable complete. But I would think that would be an exception other that ORA-1006.

If txnHdl is somehow getting wonked up and pointed to a different cursor, I could see an ORA-1006 exception being raised, when the bind_variable procedure gets called on the wrong cursor. (But that would be a serious bug in the Oracle code, not in your procedure. But I've never encountered that error, never had to check for something like this in any of MySQL.

To debug this, since it happens so rarely, I would open a case with Oracle support, and see if its possible to collect a trace/dump whenever exception ORA-1006 is raised. (I've never done a SET EVENT for that one before; I don't even know if that's possible or supported, that may be a "client side" exception, but in a PL/SQL block executing on the server, I would think it would be available. But I would do this, especially on a production database server, under the guidance of Oracle support.)

Q:

How sure are you that it's the bind_variable call in the procedure that is throwing the exception. Is that ORA-1006 exception wrapped inside an ORA-06502 exception? (I would expect to be. If it's not, then it may actually be a JDBC session that is binding variables to the procedure which is raising the exception.)

I'd be tempted to create a local procedure variable for each of the input parameters to the procedure, and then assign the input parameters to the local procedure variables. I'd also be tempted to put in an EXCEPTION handler, so I could audit the issue:

CREATE OR REPLACE PROCEDURE bindFailure
( In_cntAcct  NUMBER
, In_custAcct NUMBER
, In_CardNo   VARCHAR2
, In_Code     VARCHAR2  
) AS
  txnHdl INTEGER;
  xn_cntacct   txn.cntAcct%TYPE;
  xn_custacct  txn.custAcct%TYPE;
  xn_cardno    txn.cardNo%TYPE;
  xn_code      txn.Code%TYPE;
  bind_variable_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT (bind_variable_does_not_exist, 1006);
BEGIN
  xn_cntAcct  := IN_CntAcct;
  xn_custAcct := IN_CustAcct;
  xn_cardno   := IN_CardNo;
  xn_code     := IN_Code;

  txnHdl := dbms_sql.open_cursor;
  sqlStmt := 'select * from txn t'
             || ' where t.cntAcct  = :IN_CNTACCT '
             || ' and   t.custAcct = :IN_CUSTACCT '
             || ' and   t.cardNo   = :IN_CARDNO '
             || ' and   t.Code     = :IN_CODE ';

  dbms_sql.parse(txnHdl, sqlStmt, DBMS_SQL.NATIVE);
  dbms_sql.bind_variable(txnHdl, ':IN_CNTACCT'  , xn_cntacct);
  dbms_sql.bind_variable(txnHdl, ':IN_CUSTACCT' , xn_custacct);


EXCEPTION 
  WHEN bind_variable_does_not_exist THEN
    -- auditing here
    RAISE;
  WHEN OTHERS THEN
    RAISE;
END;