okbob/orafce_sql

PostgreSQL extension that provides a API of Oracle's package dbms_sql

DBMS_SQL

This is implementation of Oracle's API of package DBMS_SQL

It doesn't ensure full compatibility, but should to decrease a work necessary for successful migration.

Functionality

This extension implements subset of Oracle's dbms_sql interface. The goal of this extension is not a compatibility with Oracle, it is designed to reduce some work related migration Oracle's applications to Postgres. Some basic bulk DML functionality is supported:

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
  a := ARRAY[1, 2, 3, 4, 5];
  b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
  ca := ARRAY[3.14, 2.22, 3.8, 4];

  call dbms_sql.bind_array(c, 'a', a, 2, 3);
  call dbms_sql.bind_array(c, 'b', b, 3, 4);
  call dbms_sql.bind_array(c, 'c', ca);
  raise notice 'inserted rows %d', dbms_sql.execute(c);
end;
$$;


do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
  call dbms_sql.define_array(c, 1, a, 10, 1);
  call dbms_sql.define_array(c, 2, b, 10, 1);
  call dbms_sql.define_array(c, 3, ca, 10, 1);

  perform dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0
  loop
    call dbms_sql.column_value(c, 1, a);
    call dbms_sql.column_value(c, 2, b);
    call dbms_sql.column_value(c, 3, ca);
    raise notice 'a = %', a;
    raise notice 'b = %', b;
    raise notice 'c = %', ca;
  end loop;
  call dbms_sql.close_cursor(c);
end;
$$;

There is function dbms_sql.describe_columns_f, that is like procedure dbms_sql.describe_columns. Attention, the type ids are related to PostgreSQL type system. The values are not converted to Oracle's numbers

do $$
declare
  c int;
  r record;
  d dbms_sql.desc_rec;
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  r := dbms_sql.describe_columns(c);
  raise notice '%', r.col_cnt;

  foreach d in array r.desc_t
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

do $$
declare
  c int;
  n int;
  d dbms_sql.desc_rec;
  da dbms_sql.desc_rec[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  call dbms_sql.describe_columns(c, n, da);
  raise notice '%', n;

  foreach d in array da
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

Dependency

When you plan to use dbms_sql extension together with Orafce, then you have to remove line with CREATE DOMAIN varchar2 AS text; statement from install sql script.

ToDo

orafce_sql's Issues

CREATE EXTENSION dbms_sql is giving error

I am getting the below error while running the command CREATE EXTENSION IF NOT EXISTS dbms_sql;

ERROR: type "varchar2" already exists
SQL state: 42710

Note: I followed the steps in installation document.

ERROR: type "varchar2" already exists

Hi Pavel,

This morning I tried to install the dbms_sql extension and I'm facing the well know error in $subject. It is documented in the extension SQL file:

/*
 * temp solution, at the end varchar2 from orafce will be used
 */
CREATE DOMAIN varchar2 AS text; -- should be removed, if you use Orafce

My question is what is the interest in keeping the varchar2 data type in this extension instead of using varchar or text? I mean if the user use the varchar2 data type it must probably already have the orafce extension installed and in the orafce extension there is implicit cast to varchar and varchar2.

Best regards

Oracle [desc_tab] and [describe_columns] are not provided in this extension package

Hi ，

I'm using orafce_sql extension, it is found that [desc_tab] and [describe_columns] are not provided in this expansion package.
Is there any reason? I want to migrate these two functions from Oracle to Postgres. What should I do?

I look forward to your reply！
salix