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. 

ORA-02292: integrity constraint (.) violated - child record

I was writing an ODI procedure from creating a static dimension from scratch. The procedure looked like this:

begin
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT;
end

When I run it, I got the following error:

ORA-02292: integrity constraint (<OWNER>.<FK_CONSTRAINT>) violated - child record

The error means that I was trying to delete values that were used in a column that had a foreign key constraint on it.
To overcome this issue, without deleting any value in the referencing table, you first have to disable the constraint and then enable it again once you have finished populating the table.
So the procedure will look like this:


begin
execute immediate 'ALTER TABLE <DIM_TABLE> DISABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT; 
execute immediate 'ALTER TABLE <DIM_TABLE> ENABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
end