Overview

Although ORACLE and PostgreSQL implement similar features there are important differences, ammituities in the translation process and of course each project is unique. Therefore, review and test your application carefully! To assist your reviews and tests thriae writes a report.

thriae focusses on most common and compatible features and cannot assure to be compatible to a particular version of ORACLE or PostgreSQL.

`You can use thriae in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. `

The heart of thriae is a translation service. Currently you use the thriae web app to translate interactively. If you start more complex migration projects you might use thriaes command line interface (CLI).

Translation basic, file format, comments

thriaes default file encoding is UTF-8 and the default linefeed is UNIX style '\n'. Tab characters may be converted to spaces and the default is four spaces which may be changed in configuration. Maximum input size is 200KB (204800 byte).

In principle thriae executes the following steps:

  • read configuration file,

  • parse your sourcecode into an abstract syntax tree (AST),

  • extract model information from AST and enrich configuration,

  • rearrange AST into a form suitable to PostgreSQL and record all changes,

  • render rearranged AST to PostgreSQL code and create the report.

Have a look at a simple example using this configuration:

defines: []
identifier: lowercase
linefeed: unix
schema: []
tabToSpace: 4

The SELECT statement translates PostgreSQL dialect. The only visible changes are quotes to the three identifiers.
As these trivial changes usually do not need further tests, they are colored green. Other changes cloured yellowor orangemay need some more attention. Redtext signals errors or incompatible sourcecode. If code is colored blue, manual action are required. When your sourcecode has syntax errors, the fragmet is colored io.thriae.report.doc.input.Del@37b6ce32.

SELECT
	PLAYER_ID
,	PLAYER_LAST_NAME
FROM
	DIM_PLAYER
;
SELECT
    player_id
,   player_last_name
FROM
    dim_player
;

thriae generates a report.

changes:
- color: green
  msg: id
  srcRange:
    start: {line: 2, pos: 1}
    stop: {line: 2, pos: 10}
  targetRange:
    start: {line: 2, pos: 4}
    stop: {line: 2, pos: 13}
- color: green
  msg: id
  srcRange:
    start: {line: 3, pos: 2}
    stop: {line: 3, pos: 18}
  targetRange:
    start: {line: 3, pos: 4}
    stop: {line: 3, pos: 20}
- color: green
  msg: id
  srcRange:
    start: {line: 5, pos: 1}
    stop: {line: 5, pos: 11}
  targetRange:
    start: {line: 5, pos: 4}
    stop: {line: 5, pos: 14}
syntaxerrors: 0
tokens: 14

If thriae has no translationrule for a particular AST, the original codefragment is rendered unchanged to PostgreSQL code. Said this, translated sourcecode may contain still incompatible fragments of ORACLE sourcecode. As thriae made no changes, these fragments may not appear in reports! Nevertheless, thriae aims to recognize incompatible fragments. Please help improve thriae and you are welcome to let us know about these issues.
Multiline and Singleline comments and whitespaces are parsed to the AST too. During rearrangement of AST comments may appear at unexpected positions or get lost in rare situations also minor formatting glitches may occur.

Explore Sample: Quoted 8 Spaces

The next example uses thriaes default configuration:

identifier: lowercase tabToSpace: 4 linefeed: unix

If the input is invalid ORACLE SQL thriae parse phase cancels and no translation happens. Instead invalid SQL is marked red.
Thriea cannot guarantee to detect 100% invalid statements and highlight may not match exact position.

SELECTx
PLAYER_ID
,	PLAYER_LAST_NAME
FROM
DIM_PLAYER
;
SELECTx
PLAYER_ID
,	PLAYER_LAST_NAME
FROM
DIM_PLAYER
;

Syntax error is shown in report:

Syntax error at 4:0

Explore sample: Syntax error .

As of version 0.2.2 thriae cannot translate `connect by`clauses, but instead it is able to detect these clauses and report not yet implemented features.

SELECT
        ID
    FROM
        OBJECT
    START WITH
        ID = RootID
    AND
        NR = ChildNr
    CONNECT BY PRIOR
        PARENT_NR = NR
    AND
        ID = RootID;
SELECT
        id
    FROM
        object
    START WITH
        ID = RootID
    AND
        NR = ChildNr
    CONNECT BY PRIOR
        PARENT_NR = NR
    AND
        ID = RootID;

Explore sample: Detect not yet implementetd or unsupported features .

Identifier

Identifiers in PostgreSQL have to be lowercase or quoted. As ORACLE identifiers are uppercase by default unquoted identifiers have to be converted to match PostgreSQL syntax.

thriae offers three options to convert identifiers:
- lowercase
- quoted
- lowercaseQuoted

CREATE TABLE "DMS_SAMPLE_DW"."DIM_PLAYER"
(
    "PLAYER_ID" NUMBER(38,10),
    "SPORT_TEAM_ID" NUMBER(38,10),
    "PLAYER_LAST_NAME" VARCHAR2(30 BYTE),
    "PLAYER_FIRST_NAME" VARCHAR2(30 BYTE),
    "PLAYER_FULL_NAME" VARCHAR2(30 BYTE)
)
CREATE TABLE dms_sample_dw.dim_player
(
    player_id numeric(38, 10),
    sport_team_id numeric(38, 10),
    player_last_name varchar(30),
    player_first_name varchar(30),
    player_full_name varchar(30)
)

Explore sample: Identifier

Datatypes

Datatypes

Native Datatypes

Choosing the datatype can have significant impact on storage and response time. PostgreSQL® supports native datatypes for numeric columns for best performance.

But if your application needs rounding behavior as in ORACLE you should carefully choose datatypes.

Type Mapping

BINARY_INTEGER

integer

BINARY_FLOAT

numeric

BINARY_DOUBLE

numeric

NVARCHAR2

varchar(p), or text when no precision is given

DEC

decimal

INTEGER

integer

INT

integer

NUMERIC

see NUMBER

SMALLINT

smallint

NUMBER

Number datatype has precision and scale.
By default thriae maps type NUMBER to native types.
NUMBER(4, 0) : smallint
NUMBER(9, 0) : integer
NUMBER(19, 0) : bigint
NUMBER(p, 0) : numeric(p)
NUMBER(p, s) : positive scale, max 6 digits: real
NUMBER(p, s) : positive scale, max 15 digits: double precision
NUMBER(p, s) : numeric(p, s)
NUMBER(*, s) : numeric(38, s)

DECIMAL

see NUMBER

DOUBLE PRECISION

double precision

FLOAT

double precision

REAL

real

NCHAR

char(p), or text when no precision is given

LONG RAW

bytea

CHAR

char(p), or text when no precision is given

CHARACTER

char(p), or text when no precision is given

VARCHAR2

varchar(p), or text when no precision is given

VARCHAR

varchar(p), or text when no precision is given

STRING

varchar(p), or text when no precision is given

RAW

bytea

XMLTYPE

xml

Thriase default type mapping is easily shown in create table statements.

CREATE TABLE NATIVE_TYPES
(
    COL01 BINARY_INTEGER,
    COL02 BINARY_FLOAT,
    COL03 BINARY_DOUBLE,
    COL04 NVARCHAR2(20),
    COL05 NVARCHAR2,
    COL06 DEC,
    COL07 INTEGER,
    COL08 INT,
    COL09 NUMERIC,
    COL10 SMALLINT,
    COL11 NUMBER,
    COL12 NUMBER(4, 0),
    COL13 NUMBER(9, 0),
    COL14 NUMBER(19, 0),
    COL15 NUMBER(7, 0) ,
    COL16 NUMBER(6, 2),
    COL17 NUMBER(16, 2),
    COL18 NUMBER(4, 2),
    COL19 NUMBER(*, 2),
    COL20 DECIMAL,
    COL21 DOUBLE PRECISION,
    COL22 FLOAT,
    COL23 NCHAR,
    COL24 NCHAR(3),
    COL25 LONG RAW,
    COL26 CHAR,
    COL27 CHAR(8),
    COL28 CHARACTER,
    COL29 VARCHAR2,
    COL30 VARCHAR,
    COL31 STRING,
    COL32 RAW,
    COL33 DATE,
    COL34 ROWID,
    COL35 UROWID,
    COL36 XMLTYPE
)
CREATE TABLE native_types
(
    col01 integer,
    col02 double precision,
    col03 double precision,
    col04 varchar(20),
    col05 text,
    col06 decimal,
    col07 integer,
    col08 integer,
    col09 numeric,
    col10 smallint,
    col11 numeric,
    col12 smallint,
    col13 integer,
    col14 bigint,
    col15 integer ,
    col16 double precision,
    col17 numeric(16, 2),
    col18 real,
    col19 numeric(38, 2),
    col20 numeric,
    col21 double precision,
    col22 double precision,
    col23 char,
    col24 char,
    col25 text,
    col26 text,
    col27 char(8),
    col28 text,
    col29 text,
    col30 text,
    col31 text,
    col32 bytea,
    col33 timestamp(0),
    col34 oid,
    col35 oid,
    col36 xml
)

Explore sample: Native Datatypes

You may overwrite default mapping in the configuration file.

Userdefined Datatypes

Translating user defined datatype reuses the mapping of native data types.

rem a simple user type
CREATE TYPE MY_POINT AS OBJECT (
    x DATE
,   y DATE
,   action CHAR(1)
);
/

rem a list of points
CREATE TYPE MY_POINT_LIST
IS TABLE of MY_POINT;
/

CREATE TYPE my_arry_type IS
    VARRAY(10) of VARCHAR2(15);
/

rem a table using usertypes
create Table USER_TYPES (
    COL000 MY_POINT,
    COL001 MY_POINT_LIST,
    COL002 MY_ARRAY_TYPE
)
NESTED TABLE COL001 STORE AS MY_POINTS
;
/
-- a simple user type
CREATE TYPE my_point AS (
    x timestamp(0)
,   y timestamp(0)
,   action char(1)
);


-- a list of points
CREATE TYPE my_point_list
  AS (my_point_list my_point[]);


CREATE TYPE my_arry_type
     AS (my_arry_type varchar(15)[10]);


-- a table using usertypes
create Table user_types (
    col000 my_point,
    col001 my_point_list,
    col002 my_array_type
)
/* NESTED TABLE COL001 STORE AS MY_POINTS */
;

Explore sample: User Datatypes

Tables

DML for tables translate with following features:

CREATE Table with columns

Simple Create Table with colums only. Datatypes are mapped as described above.

CREATE TABLE COUNTRY (
    id NUMBER(3),
    name VARCHAR2(12)
);
CREATE TABLE country (
    id smallint,
    name varchar(12)
);

Explore sample: Create Table, columns only

CREATE Table with columns and one primary key column

Simple Create Table with columns and one column as primary key.

CREATE TABLE COUNTRY (
    id NUMBER(3) PRIMARY KEY,
    name VARCHAR2(12)
);
CREATE TABLE country (
    id smallint PRIMARY KEY,
    name varchar(12)
);

Explore sample: Create Table, with pk column

Create Table with two pk columns inline

Simple Create Table with colums and two columns as primary key inline.

CREATE TABLE COUNTRY (
    country_id NUMBER(3),
    continent_id NUMBER(1),
    name VARCHAR2(12),
    PRIMARY KEY (continent_id, country_id)
);
CREATE TABLE country (
    country_id smallint,
    continent_id smallint,
    name varchar(12),
    PRIMARY KEY (continent_id, country_id)
);

Explore sample: Create Table, with two pk columns inline

CREATE Table with alter table add constraint

Simple Create Table and Alter Table with two columns as primary key. In difference to the examples above we specify a constraint name.

CREATE TABLE COUNTRY (
    country_id NUMBER(3),
    continent_id NUMBER(1),
    name VARCHAR2(12)
);

ALTER TABLE COUNTRY ADD
CONSTRAINT PK_COUNTRY PRIMARY KEY (
    continent_id
,   country_id
)
;
CREATE TABLE country (
    country_id smallint,
    continent_id smallint,
    name varchar(12)
);

ALTER TABLE country ADD
CONSTRAINT pk_country PRIMARY KEY (
    continent_id
,   country_id
)
;

Explore sample: Create Table with Alter Table Add Constraint

CREATE Table with Create Index And Alter Table Add Constraint using Index

Simple Create Table, Create Index and Alter Table with two columns indexed and primary key. Note, PostgreSQL will rename index and constraint to the same name and prompt a message like:

NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "idx_country" to "pk_country"
CREATE TABLE COUNTRY (
    country_id NUMBER(3),
    continent_id NUMBER(1),
    name VARCHAR2(12)
);

CREATE UNIQUE INDEX IDX_COUNTRY ON
COUNTRY (
    continent_id
,   country_id
)
;

ALTER TABLE COUNTRY ADD
    CONSTRAINT PK_COUNTRY PRIMARY KEY 
    USING INDEX IDX_COUNTRY
;
CREATE TABLE country (
    country_id smallint,
    continent_id smallint,
    name varchar(12)
);

CREATE UNIQUE INDEX idx_country ON
country (
    continent_id
,   country_id
)
;

ALTER TABLE country ADD
    CONSTRAINT pk_country PRIMARY KEY
;

Explore sample: CREATE Table with Create Index And Alter Table Add Constraint Using Index

CREATE Table with Create Indes And Alter Table Add Constraint causing name-clash

As seen in the example above, relation-names in PostgreSQL are unique per table. The following script will cause a name-clash, without proper handling in thriae.
Thriea recognize the suitable index created before and switches to USING INDEX syntax.

Again, PostgreSQL will rename the index automatically to: pk_country!

CREATE TABLE country (
    country_id smallint,
    continent_id smallint,
    name varchar(12)
);

CREATE UNIQUE INDEX pk_country ON
country (
    continent_id
,   country_id
)
;

ALTER TABLE country ADD
CONSTRAINT pk_country PRIMARY KEY (
    continent_id
,   country_id
)
;
CREATE TABLE country (
    country_id smallint,
    continent_id smallint,
    name varchar(12)
);

CREATE UNIQUE INDEX pk_country ON
country (
    continent_id
,   country_id
)
;

ALTER TABLE country ADD PRIMARY KEY USING INDEX pk_country
;

Explore sample: CREATE Table with Create Indes And Alter Table Add Constraint causing name-clash

Create Table and unique constraints

Create unique indexes is quite similar.

CREATE TABLE COUNTRY (
    country_id NUMBER(3) UNIQUE,
    continent_id NUMBER(1),
    name VARCHAR2(12),
    UNIQUE (continent_id, country_id)
);

ALTER TABLE COUNTRY ADD
CONSTRAINT UK_NAME UNIQUE (
    name
);
CREATE TABLE country (
    country_id smallint UNIQUE,
    continent_id smallint,
    name varchar(12),
    UNIQUE (continent_id, country_id)
);

ALTER TABLE country ADD
CONSTRAINT uk_name UNIQUE (
    name
);

Explore sample: Create Table and unique constraints

NULL and NOT NULL Constraints

Create a Table with NULL constraints.

CREATE TABLE COUNTRY (
    country_id NUMBER(3) NOT NULL,
    continent_id NUMBER(1),
    name VARCHAR2(12) NULL
);
CREATE TABLE country (
    country_id smallint NOT NULL,
    continent_id smallint,
    name varchar(12) NULL
);

Explore sample: Create Table with Alter Table Add Constraint

Check Constraints

Create Table with complex CHECK constraint.

CREATE TABLE COUNTRY (
    COUNTRY_ID NUMBER(3) ,
    CONTINENT_ID NUMBER(1),
    NAME VARCHAR2(12) ,
    CONSTRAINT CK_ID_NAME
    CHECK (
        (COUNTRY_ID IS NOT NULL AND CONTINENT_ID IS NOT NULL)
    AND
        (SUBSTR(NAME, 0, 1) = 'A')
    )
);
CREATE TABLE country (
    country_id smallint ,
    continent_id smallint,
    name varchar(12) ,
    CONSTRAINT ck_id_name
    CHECK (
        (country_id IS NOT NULL AND continent_id IS NOT NULL)
    AND
        (SUBSTR(name, 0, 1) = 'A')
    )
);

Explore sample: Check Constraints

List-Partitioning

TODO

CREATE Table with a virtual column

As PostgreSQL does not implement virutal columns thriae translates to stored column. The default type for generated columns is text, but you can define another type in modelconfig file.

CREATE TABLE COUNTRY  (

    id NUMBER(3)
,    name VARCHAR2(12)
,    upper_case
        GENERATED ALWAYS AS upper(name) VIRTUAL
)
CREATE TABLE country  (

    id smallint
,    name varchar(12)
,    upper_case text
        GENERATED ALWAYS AS upper(name) STORED
)

Explore sample: Create Table with virtual column

Trigger

DML for triggers.

ALTER-Trigger statements require table definition, as PostgreSQL knows ALTER-Table statements only, no ALTER-Trigger.

Before Insert Trigger

Triggers are transpiled to trigger and assigned trigger-function.

CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
	 CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
	CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
	CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
	CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

CREATE OR REPLACE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  select 1 from dual;
END secure_employees;
/

ALTER TRIGGER secure_employees DISABLE;
CREATE TABLE employees
    ( employee_id    integer
    , first_name     varchar(20)
    , last_name      varchar(25)
     CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          varchar(25)
    CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   varchar(20)
    , hire_date      timestamp(0)
    CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         varchar(10)
    CONSTRAINT     emp_job_nn  NOT NULL
    , salary         double precision
    , commission_pct real
    , manager_id     integer
    , department_id  smallint
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

CREATE OR REPLACE FUNCTION trfct_secure_employees() RETURNS trigger AS $BODY$

BEGIN

  select 1 /* from dual */;
RETURN NEW;
END $BODY$ LANGUAGE PLPGSQL;

CREATE OR REPLACE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
 EXECUTE PROCEDURE trfct_secure_employees();
;


ALTER TABLE employees DISABLE TRIGGER secure_employees;

Explore sample: Table DUAL

Select

Select

Table DUAL

Table DUAL does not exist in PostgreSQL. It should be removed form FROM clauses.

select 1 from dual;
select 1 /* from dual */;

Explore sample: Table DUAL

Outer joins with operator (+)

ORACLE has an outer join syntax with (+) operator which is not ANSI-SQL and is not supported by PostgreSQL. Nevertheless it can be rewritten to LEFT-OUTER-JOIN syntax. This requires table aliases inside select-statements. If table structure is available, thriae will introduce missing aliases on the fly.

CREATE TABLE table_a (
  me_id NUMBER(18) NOT NULL,
  a_value VARCHAR2
);

CREATE TABLE table_b (
  b_id NUMBER(8) NOT NULL,
  other_id NUMBER(2),
  b_value CHAR(200)
);

SELECT
  a_value
, b_value
FROM
  table_a
, table_b
WHERE
  me_id = other_id(+);
CREATE TABLE table_a (
  me_id bigint NOT NULL,
  a_value text
);

CREATE TABLE table_b (
  b_id integer NOT NULL,
  other_id smallint,
  b_value char(200)
);

SELECT
  thr1.a_value
, thr2.b_value
FROM
  table_a thr1
    LEFT JOIN  table_b thr2 ON
  thr1.me_id = thr2.other_id ;

Explore sample: Outer joins with operator (+)

PL/SQL

PL/SQL

CREATE FUNCTION

Transpile a function.

CREATE OR REPLACE FUNCTION FCT_1 (
    NAME IN VarChar2
) RETURN Number IS
    n number;
BEGIN
    select 1 into n from dual;
    return 1;
END;
/
CREATE OR REPLACE FUNCTION fct_1 (
    name IN text
) RETURNS numeric AS $body$ 
DECLARE

    n numeric;
BEGIN
    select 1 into n /* from dual */;
    return 1;
END; $body$
LANGUAGE PLPGSQL
;

Explore sample: CREATE FUNCTION

CREATE PROCEDURE

Transpile a procedure.

CREATE OR REPLACE PROCEDURE PROC_1 (
    NAME IN VarChar2
) AS
    n number;
BEGIN
    select 1 into n from dual;
END;
/
CREATE OR REPLACE PROCEDURE proc_1 (
    name IN text
) AS $body$ 
DECLARE

    n numeric;
BEGIN
    select 1 into n /* from dual */;
END; $body$
LANGUAGE PLPGSQL
;

Explore sample: CREATE PROCEDURE

CREATE PACKAGE

Transpile a package header and body.

CREATE OR REPLACE PACKAGE PKG_A IS

    FUNCTION Fct_1(
        NAME IN VarChar2
    ) RETURN Number;

    PROCEDURE Proc_1 (
        ID IN NUMBER
    );

END;
/

CREATE OR REPLACE PACKAGE BODY PKG_A IS

    FUNCTION Fct_1(
        NAME IN VarChar2
    ) RETURN Number IS
        sName VARCHAR2(100);
    BEGIN

        SELECT 'DUAL'
        INTO sName
        FROM DUAL;

        RETURN 0;
    END Fct_1;

    PROCEDURE Proc_1 (
        ID IN NUMBER)
    IS
        sName VARCHAR2(100);
    BEGIN

        SELECT 'DUAL'
        INTO sName
        FROM DUAL;
    END;
END;
/
/* CREATE OR REPLACE PACKAGE PKG_A IS

    FUNCTION Fct_1(
        NAME IN VarChar2
    ) RETURN Number;

    PROCEDURE Proc_1 (
        ID IN NUMBER
    );

END */;


CREATE SCHEMA IF NOT EXISTS  pkg_a;

    CREATE OR REPLACE FUNCTION  pkg_a.fct_1 (
        name IN text)  RETURNS numeric AS $body$ 
        DECLARE
        
        sname varchar(100);
    BEGIN

        SELECT 'DUAL'
        INTO sname
        /* FROM DUAL */;

        RETURN 0;
    END;
    $body$
    LANGUAGE PLPGSQL;

    CREATE OR REPLACE PROCEDURE  pkg_a.proc_1 (
        id IN numeric)
    AS $body$ 
    DECLARE
    
        sname varchar(100);
    BEGIN

        SELECT 'DUAL'
        INTO sname
        /* FROM DUAL */;
    END;
    $body$
    LANGUAGE PLPGSQL;;

Explore sample: CREATE PACKAGE BODY

Build-In Packages

Build-In Packages

DBMS_OUTPUT

Write messages to output.

BEGIN
	DBMS_OUTPUT.PUT('Fragment 1');
    BEGIN
        DBMS_OUTPUT.PUT('Fragment 2');
        DBMS_OUTPUT.PUT('Fragment 3');
        DBMS_OUTPUT.NEW_LINE;
        DBMS_OUTPUT.PUT_LINE('Some text');
    END;
	DBMS_OUTPUT.PUT('Fragment 4');
	DBMS_OUTPUT.NEW_LINE;
	DBMS_OUTPUT.PUT_LINE('another text');
END;
/
DO $$
DECLARE
  dbmsoutput varchar :='';
BEGIN
    dbmsoutput := concat(dbmsoutput, ('Fragment 1'));
    BEGIN
        dbmsoutput := concat(dbmsoutput, ('Fragment 2'));
        dbmsoutput := concat(dbmsoutput, ('Fragment 3'));
        RAISE NOTICE '%', dbmsoutput;
        dbmsoutput := '';
        RAISE NOTICE ('Some text');
    END;
    dbmsoutput := concat(dbmsoutput, ('Fragment 4'));
    RAISE NOTICE '%', dbmsoutput;
    dbmsoutput := '';
    RAISE NOTICE ('another text');
END$$;

Explore sample: Write messages to text buffer.

Admin

Administrative tasks

CREATE User

Simple Create User with password.

CREATE USER TEST IDENTIFIED BY Test_123
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;
CREATE ROLE test WITH LOGIN PASSWORD 'Test_123'
/* DEFAULT TABLESPACE TEST_DATA */
/* TEMPORARY TABLESPACE TEST_TEMP */;

Explore sample: Create user identified by password

Password identification is supported only, all other credentials are unsupported. All other option are unsupported too.

sqlplus

sqlplus commands

A simplified preprocessor

Thriea has a simplified preprocessor. The preprocessor processes DEFINE and UNDEFINE statements and replaces matching placeholder line by line. Values are recursivly evaluated and cycles will abort with an error message. As thriae has no SQL-connection to any database the preprocessor does not evaluate more complex tasks you might use in sqlplus.

The preprocessor is agnostic to ORACLE grammar and replaces all occurences in inputfiles. Lines with `DEFINE`or `UNDEFINE`statements are skiped in the result.

Changes processed by the preprocessor will not occur in the report and are not highlighted in the PGPLSQL file.

DEFINE _TAB_NAME=MY_TABLE
DEFINE _sUFFIX=_1
DEFINE _ID_COL_NAME=ID&_SUfFIX.
DEFINE _CPU_COUNT=4

DEFINE _TAB_COMMENT=save some data

-- create table &_TAB_NAME.
CREATE TABLE &_TAB_NAME. (
	&_ID_COL_NAME. NUMBER(9) NOT NULL
,	VALUE NVARCHAR2(100) NOT NULL
) INITRANS &_CPU_COUNT.
;
comment on table &_TAB_NAME. is '&_TAB_COMMENT. &_CPU_COUNT.';
comment on column &_TAB_NAME..&_ID_COL_NAME. is 'Id)';
comment on column &_TAB_NAME..VALUE is 'some text';

UNDEFINE _CPU_COUNT

DEFINE _TAB_NAME=ANOTHER_TABLE
DEFINE _SUFFIx=_2

CREATE TABLE &_TAB_NAME. (
	&_ID_COL_NAME. NUMBER(9) NOT NULL
,	VALUE NVARCHAR2(100) NOT NULL
)
;
comment on table &_TAB_NAME. is '&_TAB_COMMENT. &_CPU_COUNT.';
comment on column &_TAB_NAME..&_ID_COL_NAME. is 'OID)';
comment on column &_TAB_NAME..VALUE is 'some text';


-- create table MY_TABLE
CREATE TABLE my_table (
    id_1 integer NOT NULL
,   value varchar(100) NOT NULL
) /* INITRANS 4 */
;
comment on table my_table is 'save some data 4';
comment on column my_table.id_1 is 'Id)';
comment on column my_table.value is 'some text';



CREATE TABLE another_table (
    id_2 integer NOT NULL
,   value varchar(100) NOT NULL
)
;
comment on table another_table is 'save some data &_CPU_COUNT.';
comment on column another_table.id_2 is 'OID)';
comment on column another_table.value is 'some text';

Explore sample: A simplified preprocessor

Defined key-value pairs will be collected in the configuration for further transpiler calls. Keys are case-insensitive and handled in uppercase.

DBMS_OUTPUT.PUTLINE

Print text to console.

EXEC DBMS_OUTPUT.PUT_LINE('my message...');
exec DBMS_OUTPUT.NEW_LINE;
\echo 'my message...'
\echo

Explore sample: DBMS_OUTPUT.PUTLINE