martedì 11 ottobre 2016

Oracle Tip: executing SQL scripts in SQL Developer

One common problem with SQL Developer is executing huge SQL scripts (i.e. INSERT statements from the export of a big table).
Typically you will get the following error when trying to open the file: 

which is caused by an OutOfMemoryError of the Java Heap space.

One convenient way to execute .sql without having to open the script in the SQL Developer editor is to configure the default path for scripts.
Go to Tools -> Preferences->Database->Worksheet:

and select the path where you want to place the SQL scripts to execute.
Then, go to the SQL Developer editor and simply use the @ command to reference and execute the script.

lunedì 10 ottobre 2016

ODI Tip: Generating flat files with spaces in column names

I had a requirement of generating a .csv file with header out of a database table.
I had created the model as follows:
  • File Format: Delimited
  • Record Separator: Unix  
  • Field Separator: , (comma)
In the interface, I had placed the file as Target Datasource and used 'IKM SQL to File Append' with the following options:

Unfortunately, the header columns were truncated at the first space and adding double quotes did not help. The column header were all truncated after the first space found.
After investigating, I came to the conclusion that there is was no quick or easy way to deal with spaces. I guess the only way is creating a custom version of the IKM.
Therefore, since I have very little time, I have replaced the spaces with underscores and added a OdiOSCommand in the package to replace the underscores with spaces in the output file.

ODIOSCommand is a more flexible version of OSCommand and allows you to invoke a sed command to do the replacement in place (-i) of the underscores with spaces.

sed -i 's/_/ /g' <filename>