Oracle TO_DATE( ) this afternoon

Just being asked to convert string "1/1/2020 0:00' to date field in Oracle, and come up with this:

select to_date('1/1/2020 0:00', 'dd/mm/yyyy hh24:mi') from dual

Handling table names with underscore ("_") in flask_sqlalchemy / Python

When you create table class using flask_sqlalchemy (the Flask extension for SQLAlchemy), it can maps the modal class to a proper table automatically. This is cool but if you have a table name with underscore (e.g. MY_SAMPLE_TABLE) and you name your model class with the underscore ('_') as usual, you will find SQLAlchemy will map your table name as something the following:

"MY__SAMPLE__TABLE"

Each single underscore (_) with becomes a double underscores (__). This is annoying and you might believe this is a bug, but in fact you can resolve this issues by naming your table class using the PEP8 naming convention, and named your class as:

class MySampleTable(db.Model):
     field_id = db.Column(db.Integer, primary_key = True)
     field_data = db.Column(db.String(200))

The the SQLAlchemy will map the MySampleTable class to table name as "MY_SAMPLE_TABLE" correctly.

For more information about PEP8, click here for details.

Column aggregation in Oracle

Suppose we have a table (TBL) in Oracle with columns (CATEGORY, ATTR). Consider the following:

CATEGORY	ATTR
1	A
1	B
1	C
2	D
2	E
2	F
3	X
3	Y
3	Z

We want to convert the table as follows:

CATEGORY	ATTR
1	A,B,C
2	D,E,F
3	X,Y,Z

This can be done via the SQL below:

select CATEGORY, 
       xmlagg(xmlelement(c, ATTR || ',')).extract('//text()') 
from TBL 
group by CATEGORY