Oracle SQL

This category will hold examples for Oracle SQL.

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

Oracle is able to join tables using the LIKE opertaor.

SQL

Select * 
From
a_Table
Left Join
b_Table
On
a_Table.a_Field LIKE '%' || b_Table.b_Field || '%';

The LIKE oprator works in combination with wildcards ('%').

The above SQL will work fine for text based data types like varchar. I've never tested it for other data types.

Then on the contrary: Would it make sence to join other types than text based types using LIKE? Most likely not.

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