Oracle

Define a variable in Oracle #

DEFINE formatt = 'FM9999999999999999999990.00'

select
    to_char(10000, '&formatt') as amount
from DUAL;

When you have dynamic, let’s say 3, number of decimals:

select
    TO_CHAR(10000 / power(10, 3),(('999999999999990.'|| LTRIM((Power(10, 3)), 1)))) as net_amount,
from DUAL;

Format output in Oracle when you have very long columns #

SQL>  SET linesize 5000
SQL>  SET tab off
SQL>  select * from table_with_many_long_columns;

Another possible way of doing it:

SET WRAP OFF
SET PAGESIZE 0

Select from a list of static data #

This must work in any ANSI compatible database:

WITH my_table as (
    select 'one', 'a' from dual
    union all
    select 'two', 'b' from dual
)
select * from my_table;