Today, when I was using BULK COLLECT to fetch some rows to the collection, I got the following error:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

I checked my code and didn’t find anything special that can cause this error. After investigating a while, I found that the row which has length more than 2000 bytes causes that error. I checked my collection and saw that this row was defined as – dbms_sql.varchar2_table

What do you think, how it was defined in the package level (DBMS_SQL)? I was thinking it was defined as “VARCHAR2(4000)“, but it was “VARCHAR2(2000)“.

type Varchar2_Table is table of varchar2(2000) index by binary_integer;

So I declared new variable, changed the declaration of my collection as follows and it worked:

type Varchar4000_Table is table of varchar2(4000) index by binary_integer;

TYPE trec  IS RECORD (

…..

…..

column Varchar4000_Table;

)

SQL> create user usr1 identified by usr1;
User created.
SQL> grant dba to usr1;
Grant succeeded.
SQL> create user usr2 identified by usr2;
User created.
SQL> grant dba to usr2;
Grant succeeded.
SQL> conn usr1/usr1
Connected.
SQL> create table tbl_usr1 (id number);
Table created.
SQL> conn usr2/usr2
Connected.
SQL> create table tbl_usr2 (id number);
Table created.
SQL> insert into tbl_usr2 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table tbl_usr2 add primary key(id);
Table altered.
SQL> create public synonym tbl_usr2 for tbl_usr2;
Synonym created.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> conn usr1/usr1
Connected.
SQL> select * from tbl_usr2;
        ID
----------
         1
SQL> alter table tbl_usr1 add foreign key (id) references tbl_usr2(id);
alter table tbl_usr1 add foreign key (id) references tbl_usr2(id)
                                                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tbl_usr2;
        ID
----------
         1

Ops… Although I can query the table, while creating foreign key and referencing to that table, I get “ORA-00942: table or view does not exist” error. The reason is that the user hasn’t REFERENCES privilege which should be granted

GRANT REFERENCES is a privilege required by a user on a table so that this user can create new tables referencing such tables in foreign keys where he/she would otherwise be restricted.

SQL> conn usr2/usr2
Connected.
SQL> grant references on tbl_usr2 to usr1;
Grant succeeded.
SQL> conn usr1/usr1
Connected.
SQL> alter table tbl_usr1 add foreign key (id) references tbl_usr2(id);
Table altered.
SQL>

Hi All. Welcome to my Oracle Blog. In this blog, I’ll try to add new solutions for some problems and provide briefly examples. Moreover, I’m planning periodically to analyze some administration features of Oracle database