Field in data file exceeds maximum length – error with SQL* Loader

During the last week, while loading the data into the table from csv file, some of the records are getting failed with the following error:

Record 25: Rejected – Error on table “SCOTT”.”EMPLOYEE”, column DESCRIPTION.
Field in data file exceeds maximum length

While verifying the control file of the sql loader, i got to know that its default behavior of the sql loader.

By default, when you mention “columnname char” without the size in the control file, sql loader will load maximum of 255 characters long to that particular column, even your column size is more than 255 characters in the table. In this situation, if we want to load the column data more than 255 characters data, explicitly we need to mention the size in the control file within the datatype.

Earlier in my SQL Loader control file, description column mentioned as follows:

description char

After the error, it is modified as:

description char(500)

it will allow description field upto 500 characters long.

In this way, if you want to load more than 255 characters, just explicitly mention the size in the data type field of the SQL Loader control file.

-Saandy

ORA-00942 Table or View Does Not Exist

Today i came across something interesting related object privileges.

In one of the development environment, developer is getting an error “ORA-00942:Table or View Does Not Exist” while creating a package. I’m surprised because the user has all the privileges on the objects which he is referring in the package.

Cross checked all things in dba_tab_privs, dba_role_privs  etc., but unfortunately no luck :-( – every where it is showing the user has select privileges on those objects he is referring.

Finally i got to know the reason for the error:

“Roles are disabled when stored procedures or packages are executed.

A user executing a procedure or package can perform actions against objects (select a table, select a view, create a table, create a view, create a trigger). When the necessary privileges are granted to this user indirectly via a role, the result is ORA-00942 or ORA-01933 or ORA-01031 and ORA-06512 or PLS-00201 and ORA-06550.

These actions are successful when the necessary privileges are granted directly to the user.

This means that the user executing the procedure or package should be granted  the privileges required to perform the actions against objects directly.

A user cannot acquire a privilege via a role if he needs that privilege when executing a stored procedure or function or package. If the user issues the same statements in SQL, it works as the user can use the  privileges granted via a role. “

Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus [ID 168168.1]

Happy reading….. thanks

-Saandy