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.

 -- Splitting test strings --
WITH qry AS (
Select 'a,b,c' As tf From Dual
Union Select 'd,e,f' As tf From Dual 
Union Select 'g,h' As tf From Dual 
Union Select 'i' As tf From Dual
) 
SELECT regexp_substr(tf, '[^\,]+', 1, rn) Transposed_Field 
FROM qry 
cross join 
(
Select rownum rn from ( 
select max(regexp_count(tf, ',')) + 1 mx from qry) connect by level <= mx 
) 
Where regexp_substr(tf, '[^\,]+', 1, rn) is not null
Order By Transposed_Field;

The resultset of above query will look like the following picture.

Details

Parent Category: Oracle Database

Last Updated: 01 January 2018

Read more: Split and Transpose Strings

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

Details

Parent Category: Oracle Database

Last Updated: 01 January 2018

Read more: Find a tables foreign keys

After missing to add a foreign key to a table, I ended up having to correct my mistake in many places in my PL SQL code.

Afterwards I thought: "Who created this table without this important constraint?! ... :0( Uups! It was me."

If something similar happens to you, below you'll find an SQL to add a foreign key to a table.

'References parent_table_name(parent_field)' gives the parent table, while 'Foreign Key(field_name)' is the field from the child table, which will be referenced.

If you receive an error message saying that the alter table constraint could not be validated because the table has child records, one quick fix is to delete all records in child and parent table and then executing the alter table statement again.

/* Drop a table constraint */
ALTER TABLE sod_cat_app_cat
drop constraint
fk_sod_category_app; /* Name of a constraint */

/*Add a Table Constraints for foreign key*/
Alter Table sod_cat_app_cat Modify (
	Constraint fk_sod_category_app 
	Foreign Key(sod_category_app_id)
	References sod_category_app(ID)
);

Details

Parent Category: Oracle Database

Last Updated: 24 June 2014