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: 20626
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)
);
- Hits: 10305
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: 14920
Page 1 of 3