Reputation: 8724
I am having troubling figure out error on following code
DECLARE
CR_QUERY INTEGER;
CR_HANDLE INTEGER;
V_COL1 VARCHAR2(40);
V_COL2 VARCHAR2(18);
N_ACTION NUMBER(3);
T_CREATE_TS TIMESTAMP(6);
V_COL3 VARCHAR2(60);
BEGIN
CR_QUERY := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (CR_QUERY, I_V_QUERY, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (CR_QUERY, 1, V_COL1, 40);
DBMS_SQL.DEFINE_COLUMN (CR_QUERY, 2, V_COL2, 18);
DBMS_SQL.DEFINE_COLUMN (CR_QUERY, 3, N_ACTION, 3);
DBMS_SQL.DEFINE_COLUMN (CR_QUERY, 4, T_CREATE_TS, 6);
DBMS_SQL.DEFINE_COLUMN (CR_QUERY, 5, V_COL3, 60);
CR_HANDLE := DBMS_SQL.EXECUTE (CR_QUERY);
LOOP
BEGIN
EXIT WHEN DBMS_SQL.FETCH_ROWS (CR_HANDLE) = 0;
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 1, V_COL1, N_COLERR, I_ACTUAL_LEN);
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 2, V_COL2, N_COLERR, I_ACTUAL_LEN);
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 3, N_ACTION, N_COLERR, I_ACTUAL_LEN);
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 4, T_CREATE_TS, N_COLERR, I_ACTUAL_LEN);
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 5, V_COL3, N_COLERR, I_ACTUAL_LEN);
the error is Error(41,5): PLS-00307: too many declarations of 'COLUMN_VALUE' match this call.
can someone suggest whats going ?
Upvotes: 3
Views: 2117
Reputation: 5830
The issue is with:
DBMS_SQL.COLUMN_VALUE (CR_QUERY, 4, T_CREATE_TS, N_COLERR, I_ACTUAL_LEN);
If you define the variable T_CREATE_TS as a DATE it should work fine, but COLUMN_VALUE doesn't have a declaration where VALUE is of type TIMESTAMP (at least not in 10.2), so it throws the error.
Upvotes: 3