We learned in a previous tip that we can ENABLE ORACLE’S COMPATIBILITY FUNCTIONS AND PACKAGES IN YSQL.
That’s awesome! But what are the Oracle functions that are added?
Find out with this query:
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
AND e.extname = 'orafce'
ORDER BY 1, 3;
Example (of DATE related functions):
yugabyte=# SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
yugabyte-# FROM pg_catalog.pg_extension AS e
yugabyte-# INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
yugabyte-# INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
yugabyte-# INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
yugabyte-# INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
yugabyte-# WHERE d.deptype = 'e'
yugabyte-# AND e.extname = 'orafce'
yugabyte-# AND p.proname ILIKE '%DATE%'
yugabyte-# ORDER BY 1, 3;
extname | extschema | proname | proschema
---------+-----------+---------------------+------------
orafce | public | sysdate | oracle
orafce | public | to_date | pg_catalog
orafce | public | to_date | oracle
orafce | public | to_date | oracle
orafce | public | unpack_message_date | dbms_pipe
(5 rows)
Have Fun!