Ora-06502: Oracle scripts and errors

When we are programming and we get an oracle error, we can capture this error in an exception block. Within this block, we could use the variable sqlerrm in order to know its description.

Here it is an example:

declare
tmp varchar2(10);
begin
tmp:=’aaaaaaaaaaaaaa’;
exception
when others then
dbms_output.put_line(sqlcode||’–>’||sqlerrm);
end;

In this example, we have forced an ora-06502 error and we have captured it with the clause “when others”. We obtain the code with the internal variable sqlcode and we can obtain its description using the internal variable sqlerrm.

I hope you enjoyed it.

To create a new table in our database, we use the CREATE TABLE statement. It’s very similar to the sentence in oder database systems. But in Oracle we can specify some Oracle specific features such as the storage or the tablespace.

Here it is an example of use of the CREATE TABLE statement.

CREATE TABLE AYUDA
(
ID NUMBER(10) NOT NULL,
FIELD1 NUMBER(10),
FIELD2 VARCHAR2(250 BYTE)
)
TABLESPACE myTablespace
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;

We can revoke priviliges on tables and functions/procedures using REVOKE. Let’s see a couple of example to better understand it.

We can revoke privileges for common DML statements (select, insert, update and delete). We use sentences like this:

REVOKE select, insert ON mytable FROM myuser;

We can also revoke privileges for some DDL statements. If we want to let an user modify the structure of a table, we use a sentence like this:

REVOKE alter ON mytable FROM myuser;

Another use of the REVOKE sentence is to remove permission to execute procedures and functions.

REVOKE execute ON myfunction FROM myuser;

We can grant priviliges on tables and functions/procedures using GRANT. Let’s see a couple of example to better understand it.

We can grant privileges for common DML statements (select, insert, update and delete). We use sentences like this:

GRANT select, insert ON mytable TO myuser;

We can also grant privileges for some DDL statements. If we want to let an user modify the structure of a table, we use a sentence like this:

GRANT alter ON mytable TO myuser;

Another use of the GRANT sentence is to give permission to execute procedures and functions.

GRANT execute ON myfunction TO myuser;

This error message is very tricky. Here, you can learn how to solve it.

1. The main cause that produces this message is that you are assigning a value NULL to a variable that does not admit it.

DECLARE
N1 number(2) NOT NULL :=20;
N2 number(2);
BEGIN
N1 := N2;
END;

2. Another possible cause is that you are assigning a number to a variable and its size is to small to hold that value.

DECLARE
N NUMBER(2);
BEGIN
N:= 100;
END;

3. Sometimes, you get that error message when you concatenate two strings and the result is assigned to a variable too small.

DECLARE
C1 varchar2(10) ;
C2 varchar2(7);
BEGIN
C1:=’This is an’;
C2:=’ error.’
C1 :=C1||C2 ;
END;