Oracle Tip: Comment SQL Code in SQL Developer

When working with PL/SQL procedures, you'll have to comment (and uncomment) frequently part of the code.
A quick way to do it with Oracle SQL Developer is selecting the block of code you want to comment and use the shortcut:

Ctrl + '/'(slash)

Alternatively, you can choose the option 'Toggle Line Comments' from the menu 'Source' (in version 4 and above of SQL Developer). In previous versions, use the menu 'Edit -> Source'.

To remove comments, just use the same command.

Oracle Tip: ORA-30657 External Table Error

I was trying to load a CSV file into an external table. I copied the definition of the table from the final target table, adding the external table parameters: 

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>'));

When I compiled the table, I got the following error. 

ORA-30657:  Operation not supported on external organized table  

Cause: User attempted on operation on an external table which is not supported.  

Action: Don't do that!

Quite hilarious... but what did I do wrong? The Oracle error message did not help me at all (and who could ever help?). I checked the directory. It was there and its name was typed correctly. File names were ok. So what was the issue? 

It was as easy as removing the NOT NULL clause on the field specification.  

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>')

Silly error, but it took me a while to find out what was wrong. Hope this helps. 

Tip: Reset Oracle Sequence

Yesterday I was working on an ODI interface, a pretty easy one, which was supposed to insert new rows  into a table using a <SEQUENCE>.NEXTVAL for populating the primary key.
The primary key was set NUMBER(4). The last value inserted had 17 as primary key. I was expecting new items to start from 18, but I was constantly getting: 

SQL Error: ORA-12899: value too large for column

After spending  a considerable amount of time for finding out which column was making my ODI interface fail, I have executed the following: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

which returned me: 124586!!!

Something has obviously gone wrong with that sequence and I needed to restore it to 17. So basically I have executed the following: 

ALTER SEQUENCE MY_SEQ INCREMENT BY -(124586-17)

and then execute: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

to restore MY_SEQ.CURRVAL to 17. 

Then you reset the correct increment by executing: 

  

 ALTER SEQUENCE MY_SEQ INCREMENT BY 1