https://www.sqlines.com/ 2026-04-23T23:27:52+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2024-12-16T13:00:15+00:00 https://www.sqlines.com/oracle-to-postgresql/call_proc?rev=1734354015&do=diff In Oracle, you can execute a stored procedure from another stored procedure just specifying its name and parameters. In PostgreSQL, you have to use the CALL statement. Oracle: -- Sample procedure with parameters CREATE OR REPLACE PROCEDURE sp1(param1 VARCHAR2, param2 VARCHAR2) IS var1 VARCHAR2(60); BEGIN var1 := param1 || ', ' || param2 || '!'; END; / -- Sample procedure without parameters CREATE OR REPLACE PROCEDURE sp2 IS var1 VARCHAR2(60); BEGIN v… text/html 2024-07-09T08:17:10+00:00 https://www.sqlines.com/oracle-to-postgresql/char?rev=1720513030&do=diff In Oracle, the CHAR(n) data type stores fixed-length, blank padded character strings with the maximum size of 2000 bytes (default) or characters. In PostgreSQL, you can use CHAR(n) that can store up to 10,485,760 characters. Oracle: -- Sample table CREATE TABLE specs ( note CHAR(30), data CHAR, -- CHAR(1) name CHAR(100 CHAR), -- size in characters item CHAR(50 BYTE) -- size in bytes ); text/html 2023-07-06T22:33:10+00:00 https://www.sqlines.com/oracle-to-postgresql/connect_by_prior?rev=1688682790&do=diff In Oracle you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. PostgreSQL allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality. Rows Generator One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows: text/html 2021-12-07T20:11:37+00:00 https://www.sqlines.com/oracle-to-postgresql/create_trigger?rev=1638907897&do=diff In Oracle, you can use the CREATE TRIGGER statement to define the trigger action as a PL/SQL program block. In PostgreSQL you have to use two statements CREATE FUNCTION ... RETURNS TRIGGER and CREATE TRIGGER ... EXECUTE FUNCTION. Oracle: -- Data CREATE TABLE data1 (c1 CHAR(1)); -- An audit table used in trigger CREATE TABLE data1_audit ( c1 CHAR(1), operation VARCHAR2(30), ts TIMESTAMP ); CREATE OR REPLACE TRIGGER data1_audit_tr AFTER INSERT ON data1 … text/html 2024-12-14T16:09:32+00:00 https://www.sqlines.com/oracle-to-postgresql/datetime_arithmetic?rev=1734192572&do=diff In Oracle, you can use the + and - operators with DATE (includes time) and TIMESTAMP values i.e. you can add or subtract the specified number of days and fractions (hours, minutes etc.) of the day. In PostgreSQL, you can use the + and - operators only for DATE (year, month and day only) values and only with integer numbers. You have to use INTERVAL expressions in other cases. text/html 2026-02-05T14:21:08+00:00 https://www.sqlines.com/oracle-to-postgresql/dbms_session?rev=1770301268&do=diff In Oracle, the DBMS_SESSION package can be used to set session-level information that can later be retrieved using the SYS_CONTEXT function. In PostgreSQL, session-level information for applications can be stored and accessed using session variables. text/html 2025-02-20T05:52:42+00:00 https://www.sqlines.com/oracle-to-postgresql/execute_immediate?rev=1740030762&do=diff In Oracle, you can execute a dynamic SQL using the EXECUTE IMMEDIATE statement. In PostgreSQL, you can use the EXECUTE statement. Oracle: BEGIN EXECUTE IMMEDIATE 'CREATE TABLE colors (name VARCHAR2(30), category VARCHAR2(30))'; END; / /* PL/SQL procedure successfully completed.*/ text/html 2021-09-03T10:28:07+00:00 https://www.sqlines.com/oracle-to-postgresql/extract_xml?rev=1630664887&do=diff In Oracle the EXTRACT function allows you to extract content from XML using the specified XPATH expression. In PostgreSQL use can use the XPATH function, but note that the order of parameters is different. Oracle: SELECT EXTRACT(xml_doc, '/document/employee/text()') FROM (SELECT XMLTYPE('<?xml version="1.0"?><document>' || XMLAGG(XMLTYPE('<employee>' || ename || '</employee>') order by ename) || '</document>') AS xml_doc FROM emp); # Result (single row):… text/html 2023-07-30T17:20:32+00:00 https://www.sqlines.com/oracle-to-postgresql/from_tz?rev=1690737632&do=diff In Oracle the FROM_TZ function allows you set the specified time zone for a timestamp value that does not have the timezone: Oracle: -- Set UTC time zone SELECT FROM_TZ(TIMESTAMP '2021-09-24 21:12:11', 'UTC') FROM dual; # 24-SEP-21 09.12.11.000000000 PM UTC -- Set Eastern time zone SELECT FROM_TZ(TIMESTAMP '2021-09-24 21:12:11', 'EST') FROM dual; # 24-SEP-21 09.12.11.000000000 PM EST text/html 2026-01-30T18:34:21+00:00 https://www.sqlines.com/oracle-to-postgresql/instr?rev=1769798061&do=diff In Oracle the INSTR function allows you to find the position of a substring within a string. It accepts 2, 3 or 4 parameters. Oracle's INSTR function also supports a negative starting position, meaning that it is counted backward from the end of the string and the search is performed backward from the resulting position. text/html 2023-07-05T15:15:55+00:00 https://www.sqlines.com/oracle-to-postgresql/json_table?rev=1688570155&do=diff In Oracle the JSON_TABLE function allows you to extract content from JSON using the specified JPATH expression and produce table rows and columns. In PostgreSQL use can use the LATERAL clause and JSON expressions. Consider the following sample table: text/html 2024-07-16T10:49:24+00:00 https://www.sqlines.com/oracle-to-postgresql/listagg?rev=1721126964&do=diff In Oracle you can use LISTAGG function to concatenate strings from multiple rows into a single row value. In PostgreSQL you can use STRING_AGG function. Consider a sample table: CREATE TABLE cities (name VARCHAR(30)); -- Insert sample rows INSERT INTO cities VALUES ('Seville'); INSERT INTO cities VALUES ('Warsaw'); INSERT INTO cities VALUES ('Boston'); text/html 2024-09-19T17:16:46+00:00 https://www.sqlines.com/oracle-to-postgresql/number?rev=1726766206&do=diff In Oracle, the NUMBER is the main data type to store all numeric values including integers, fixed and floating point numbers. In PostgreSQL, you can use SMALLINT, INT and BIGINT for integers, DECIMAL or NUMERIC for fixed point numbers, REAL and DOUBLE PRECISION for approximate floating point numbers. text/html 2021-09-24T15:31:27+00:00 https://www.sqlines.com/oracle-to-postgresql/pipelined_function?rev=1632497487&do=diff In Oracle you can have a pipelined table function that can return multiple records and can be used in the FROM clause: Oracle: -- Define the record type CREATE TYPE t_record AS OBJECT ( id NUMBER(7), name VARCHAR2(50) ); / -- Define the table type CREATE TYPE t_table IS TABLE OF t_record; / -- Pipelined function returning multiple rows CREATE OR REPLACE FUNCTION get_rows (p_rows IN NUMBER) RETURN t_table PIPELINED AS BEGIN FOR i IN 1 .. p_rows LOOP … text/html 2026-01-16T09:38:42+00:00 https://www.sqlines.com/oracle-to-postgresql/regexp_substr?rev=1768556322&do=diff In Oracle, the REGEXP_SUBSTR function returns the substring that matches the regular expression. In PostgreSQL, you can use an expression with a subquery, the REGEXP_MATCHES table-valued function, and the SUBSTR function. Oracle: -- Match a string until a comma and return the 2nd occurrence SELECT SYSDATE, 'Found: ' || REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, 2) FROM dual; /* 13-JAN-26 Found: bbb */ -- Match a string until a blank and return the 3rd occurrence SELECT SYSDA… text/html 2020-09-10T07:43:30+00:00 https://www.sqlines.com/oracle-to-postgresql/return_sys_refcursor?rev=1599723810&do=diff An Oracle stored procedure can return a cursor to the caller, for example: Oracle: -- Get list of employees for the specified department CREATE OR REPLACE PROCEDURE getEmployeesByDept ( p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR ) AS BEGIN OPEN p_recordset FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno ORDER BY ename; END getEmployeesByDept; / text/html 2024-12-03T13:21:14+00:00 https://www.sqlines.com/oracle-to-postgresql/rownum?rev=1733232074&do=diff In Oracle, the ROWNUM pseudo-column returns the order number of rows selected from a table. It is usually used to limit the number of rows returned by a query, but ROWNUM can be also used to generate row numbers. Note that ROWNUM is applied before ORDER BY. text/html 2026-02-02T08:17:27+00:00 https://www.sqlines.com/oracle-to-postgresql/soundex?rev=1770020247&do=diff In Oracle and PostgreSQL, the SOUNDEX function returns a phonetic representation of a specified string. Note that before you can use SOUNDEX in PostgreSQL, you need to enable the fuzzystrmatch extension. Oracle: SELECT SOUNDEX('Orange') FROM dual; /* O652 */ text/html 2023-03-16T17:40:52+00:00 https://www.sqlines.com/oracle-to-postgresql/sqlcode?rev=1678988452&do=diff In Oracle SQLCODE variable contains the error code for the last exception. In PostgreSQL you can use SQLSTATE variable. Note that error code for the same errors can be different. Oracle: CREATE TABLE t1 (c1 INT NOT NULL); BEGIN -- Try to insert NULL into non-nullable column INSERT INTO t1 VALUES (NULL); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error code is ' || SQLCODE); END; / # Error code is -1400 # PL/SQL procedure successfully completed. text/html 2023-03-16T17:42:25+00:00 https://www.sqlines.com/oracle-to-postgresql/sqlerrm?rev=1678988545&do=diff Both Oracle and PostgreSQL support SQLERRM variable that contains the error message for the last exception. Note that error messages for the same errors can be different. Oracle: CREATE TABLE t1 (c1 INT NOT NULL); BEGIN -- Try to insert NULL into non-nullable column INSERT INTO t1 VALUES (NULL); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error message is ' || SQLERRM); END; / # Error message is ORA-01400: cannot insert NULL into ("T1"."C1") # PL/SQL procedu… text/html 2026-03-12T07:19:01+00:00 https://www.sqlines.com/oracle-to-postgresql/string_concat?rev=1773299941&do=diff In Oracle, the || operator concatenates strings ignoring NULL values. In PostgreSQL, the || operator returns NULL if any operand is NULL. To avoid this, use the CONCAT function, which ignores NULL values, or use COALESCE to replace NULLs with empty strings. text/html 2023-02-24T20:02:01+00:00 https://www.sqlines.com/oracle-to-postgresql/string_int_campare?rev=1677268921&do=diff Both Oracle and PostgreSQL allows you to compare string and integer without explicit casting. But PostgreSQL requires the explicit casting if a built-in function is involved: Oracle: -- Implicit casting to compare string and integer SELECT 't' FROM dual WHERE '0' < 1; # t -- Implicit casting with function SUBSTR SELECT 't' FROM dual WHERE SUBSTR('0', 1, 1) < 1; # t -- Implicit casting with function CAST SELECT 't' FROM dual WHERE CAST('0' AS CHAR(1)) < 1; # t text/html 2026-02-05T11:48:53+00:00 https://www.sqlines.com/oracle-to-postgresql/sys_context?rev=1770292133&do=diff In Oracle, the SYS_CONTEXT function retrieves system or session-level information previously set by the user application using the DBMS_SESSION.SET_CONTEXT procedure. In PostgreSQL, system information is retrieved using dedicated built-in functions, while session-level information for applications can be stored and accessed using session variables. text/html 2024-12-16T11:48:03+00:00 https://www.sqlines.com/oracle-to-postgresql/systimestamp?rev=1734349683&do=diff In Oracle, the SYSTIMESTAMP function returns the current date and time with fractional seconds and with the time zone of the current database instance. In PostgreSQL you can use the CURRENT_TIMESTAMP or NOW functions that also include fractional seconds and time zone. text/html 2025-12-15T11:33:59+00:00 https://www.sqlines.com/oracle-to-postgresql/trunc_datetime?rev=1765798439&do=diff In Oracle, the TRUNC(datetime, 'unit') function truncates a datetime value to the specified unit (for example, setting the time to zero or setting the date to the first day of the month). By default, it truncates to the day. In PostgreSQL, you can use the DATE_TRUNC function, but note that order of parameters and unit values are different. text/html 2026-02-02T11:34:39+00:00 https://www.sqlines.com/oracle-to-postgresql/userenv?rev=1770032079&do=diff In Oracle, the USERENV function returns information about the current session. In PostgreSQL, session information must be retrieved using dedicated functions for each specific attribute. Oracle: -- Get the current session ID SELECT USERENV('SESSIONID') FROM dual; /* 797676 */ text/html 2024-09-01T08:01:52+00:00 https://www.sqlines.com/oracle-to-postgresql/varchar2?rev=1725177712&do=diff In Oracle, the VARCHAR2(n) data type stores variable-length character strings up to n bytes (default) or characters with the maximum of 4000 or 32767 bytes if MAX_STRING_SIZE initialization parameter is set to EXTENDED (not set by default). In PostgreSQL, you can use VARCHAR(n) that can store up to 1 GB (bytes). text/html 2021-09-03T09:27:05+00:00 https://www.sqlines.com/oracle-to-postgresql/xmlagg?rev=1630661225&do=diff In Oracle the XMLAGG is an aggregate function that allows you to built XML from individual elements. PostgreSQL also has the XMLAGG function, so no conversion is required. Oracle: -- Built XML from elements SELECT XMLAGG( XMLTYPE('<employee>' || ename || '</employee>') ORDER BY ename) FROM emp; # Result (single row): # <employee>ADAMS</employee><employee>ALLEN</employee>... text/html 2021-09-02T21:24:46+00:00 https://www.sqlines.com/oracle-to-postgresql/xmltype?rev=1630617886&do=diff In Oracle the XMLTYPE function allows you to built XML element from a string. In PostgreSQL you can use the XMLPARSE function. Oracle: -- Single XML element SELECT XMLTYPE('<employee>John</employee>') FROM dual; PostgreSQL: -- Single XML element SELECT XMLPARSE(CONTENT '<employee>John</employee>'); text/html 2024-06-26T16:22:18+00:00 https://www.sqlines.com/oracle-to-postgresql/zero_char_code_in_string?rev=1719418938&do=diff PostgreSQL does not allow you to insert the character with code zero (ASCII NUL) to CHAR, VARCHAR or TEXT columns regardless of the specific character set. Oracle does not have such limitation: Oracle: -- Sample table CREATE TABLE items ( c1 CHAR(10) ); INSERT INTO items VALUES ('a' || CHR(0) || 'b'); # 1 row created.