Oracle Database

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

Subcategories

This category will hold examples for Oracle SQL.

This category will hold Oracle SQL Developer specific articles.

A category holding articles for Oracles programming language PL SQL.