Bolte IT Consulting
Oracle SQL
This category will hold examples for Oracle SQL.
If you want to remove line breaks or other characters from Oracle fields, the following article might help.
One scenario is for example export of data into text files.
SQL
The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').
The Oracle function 'translate' is applied on the whole String and might show better performance than regular expressions depending on the complexity of your regex.
Enjoy.
- Hits: 22341
Splitting Strings is a trivial task. Using SQL unfortunately it is not so easy to do. Recently I had to split long concatenated strings from one field in an Oracle database.
Luckily I was not the only one facing this challenge and found helpful information on the internet.
Below an example that did the trick for me.
The resultset of above query will look like the following picture.
- Hits: 15780
I had to rename a column in a table in an oracle database.
SQL
Unfortunately the oracle documentation was not working for me and I had to play around a bit and search the internet until I found the following syntax to be working.
alter table tableName rename column oldName to newName;
Some things to watch out for.
You have to use simple column names.
The oracle documentation listed as first result in a google search provided the following example, which did not work for me.
RENAME COLUMN EMPLOYEE.MANAGER TO SUPERVISOR
I am puzzled as to why the above syntax did not work for me while the alter table statement worked fine, since I was able to create the table in the first place
It looks as if I just misinterpreted the oracle documentation and the rename statement always has to be combined with an alter table statement.
References
Oracle documentation
https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljrenamecolumnstatement.html
Another resource using a syntax, which worked for me.
http://www.dba-oracle.com/t_rename_column_table.htm
- Hits: 10874
Recently I had to create some tables in Oracle and fill them with data from other tables for testing.
It is quite easy to create and modify tables, however it can be a pain digging through undocumented dependencies, table constraints which describe parent child relationships between tables.
In order to find foreign keys for a table, adjust below query by replacing 'table_name' with the name of a table of interest and execute.
Synonym 'a' is holding the child table details and synonym 'uc' is holding the parent table details.
Enjoy.
SQL
- Hits: 10138
If you have to know the current database version, you can get this information using the following SQL.
SQL
- Hits: 9010