Toad World® Forums

Toad 10.1.0.7 - SB - error on Data tab on some tables: ORA-02005: implicit (-1)


#1

Toad 10.1.0.7 - SB - error on Data tab on some tables: ORA-02005: implicit (-1) length not valid for this bind or define datatype.

Work around: in Toad Options - Schema Browser / Data Tab - uncheck: enable value lookup for foriegn key constraints.

SQLMonitor output:

Timestamp: 09:30:25.015
declare
type rslt_tbl4k is table of varchar2(4000) index by binary_integer;
type rslt_tbl is table of varchar2(30) index by binary_integer;
rsltRC rslt_tbl4k;
rsltLC rslt_tbl;
rsltCN rslt_tbl;
rsltRN rslt_tbl;
rsltRO rslt_tbl;
rsltRT rslt_tbl;
RC_size number;
LC_size number;
CN_size number;
RN_size number;
RO_size number;
RT_size number;

cursor c_master is
SELECT CONSTRAINT_NAME, R_CONSTRAINT_NAME, R_OWNER
FROM DBA_CONSTRAINTS
WHERE OWNER = :o
AND TABLE_NAME = :tn
AND CONSTRAINT_TYPE = ‘R’;
c_master_var c_master%rowtype;

cursor c_master_cols(cname varchar2) is
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE owner = :o
AND table_name = :tn
AND constraint_name = cname
ORDER BY POSITION;
c_master_cols_var c_master_cols%rowtype;

cursor c_ref_table(rcown varchar2, rcname varchar2) is
SELECT table_name
FROM DBA_CONSTRAINTS
WHERE owner = rcown
AND constraint_name = rcname
AND constraint_type IN (‘P’, ‘U’);
c_ref_table_var c_ref_table%rowtype;

cursor c_ref_cols(rcown varchar2, rtname varchar2, rcname varchar2) is
SELECT COLUMN_NAME
FROM DBA_CONS_COLUMNS
WHERE owner = rcown
AND table_name = rtname
AND constraint_name = rcname
ORDER BY POSITION;
c_ref_cols_var c_ref_cols%rowtype;

rtable varchar2(30);

begin
RC_size := 0;
LC_size := 0;
CN_size := 0;
RN_size := 0;
RO_size := 0;
RT_size := 0;

for c_master_var in c_master loop
CN_size := CN_size + 1;
rsltCN(CN_size) := c_master_var.constraint_name;
RN_size := RN_size + 1;
rsltRN(RN_size) := c_master_var.r_constraint_name;
RO_size := RO_size + 1;
rsltRO(RO_size) := c_master_var.r_owner;

for c_ref_table_var in c_ref_table(c_master_var.r_owner,
c_master_var.r_constraint_name) loop rTable := c_ref_table_var.table_name;
RT_size := RT_size + 1;
rsltRT(RT_size) := rTable;
end loop;

for c_master_cols_var in c_master_cols(c_master_var.constraint_name) loop
  LC_size := LC_size + 1;
  rsltLC(LC_size) := c_master_cols_var.column_name;
end loop;
LC_size := LC_size + 1;
rsltLC(LC_size) := '-';

for c_ref_cols_var in c_ref_cols(c_master_var.r_owner, RTable,
c_master_var.r_constraint_name) loop RC_size := RC_size + 1;
rsltRC(RC_size) := c_ref_cols_var.column_name;
end loop;
RC_size := RC_size + 1;
rsltRC(RC_size) := ‘-’;

end loop;
:outRCTbl := rsltRC;
:outLCTbl := rsltLC;
:outCNTbl := rsltCN;
:outRNTbl := rsltRN;
:outROTbl := rsltRO;
:outRTTbl := rsltRT;
end;
o = ‘R’
t = ‘A’
Runtime error occurred: 2005 (O)


#2

Hi Ian,

Will you send me the table DDL? I’ll probably need DDL for the FK’s
and the other tables too.

I assume this error happens when you are NOT running SQL Monitor, but if it came
up while you were running SQL Monitor, try shutting down SQL Monitor and then
see if the problem still happens in Toad. You can use Database -> Spool SQL in
Toad 10.

-John