"[Oracle][ODBC][Ora]ORA-01008: not all variables bound" Error

[Harvey]

Hello:

I am trying to submit the following to Oracle from VBA code through a
conn.Execute(sql), where sql is:

DECLARE v_existing_reccount NUMBER;
BEGIN DBMS_APPLICATION_INFO.set_client_info('RODELASM');
SELECT COUNT(*) INTO :v_existing_reccount FROM
INDEXER_APPL_CODES_INFO_VW WHERE cde_id = 4146 AND appl_id =7130237 AND
End_Date is null or End_Date > (SELECT sysdate FROM dual);
If v_existing_reccount = 0) Then
INSERT INTO INDEXER_APPL_CODES_INFO_VW (CDE_ID,
APPL_ID, STATUS) VALUES (4146,7130237, 'ACTIVE');
END IF;
END;


this works fine in SQL*Plus, but I get the error when submitted through VBA
in Access2003.

I would appreciate any help/comments anyone might have.

thanks much
Harvey

 

[John W. Vinson]

Hello:

I am trying to submit the following to Oracle from VBA code through a
conn.Execute(sql), where sql is:

DECLARE v_existing_reccount NUMBER;
BEGIN DBMS_APPLICATION_INFO.set_client_info('RODELASM');
SELECT COUNT(*) INTO :v_existing_reccount FROM
INDEXER_APPL_CODES_INFO_VW WHERE cde_id = 4146 AND appl_id =7130237 AND
End_Date is null or End_Date > (SELECT sysdate FROM dual);
If v_existing_reccount = 0) Then
INSERT INTO INDEXER_APPL_CODES_INFO_VW (CDE_ID,
APPL_ID, STATUS) VALUES (4146,7130237, 'ACTIVE');
END IF;
END;


this works fine in SQL*Plus, but I get the error when submitted through VBA
in Access2003.

I would appreciate any help/comments anyone might have.

thanks much
Harvey

You'll need to use a Passthrough query for this, since this is valid PL/SQL
code but it souldn't be valid Access SQL. See the VBA Help for CreateQueryDef
and for ODBCDirect.