-- Generated by Ora2Pg, the Oracle database Schema converter, version 23.2.1
-- Copyright 2000-2022 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=localhost;sid=NORMATIV;port=1521


\set ON_ERROR_STOP ON



-- Oracle package 'CONSOLE' declaration, please edit to match PostgreSQL syntax.

DROP SCHEMA IF EXISTS console CASCADE;
CREATE SCHEMA IF NOT EXISTS console;
CREATE DOMAIN CONSOLE.tmessage AS varchar(1024);
CREATE TYPE console.tlogmessage AS (
LogLevel integer,
      Message  CONSOLE.TMessage
    
);


CREATE DOMAIN CONSOLE.text AS varchar(32767);





CREATE OR REPLACE FUNCTION console.__init__ () RETURNS VOID AS $body$
DECLARE

	gDirectionScreen constant integer := 0;
	gDirectionTable  constant integer := 1;
	gLogLevelOff     constant integer := 0;
	gLogLevelFatal   constant integer := 100;
	gLogLevelError   constant integer := 200;
	gLogLevelWarn    constant integer := 300;
	gLogLevelInfo    constant integer := 400;
	gLogLevelDebug   constant integer := 500;
	gLogLevelTrace   constant integer := 600;
	gLogLevelAll     constant integer := 128000;
	gDirection integer;
	gGroupIndentIndex integer;

BEGIN
  gDirection        := gDirectionScreen;
  gGroupIndentIndex := 0;
end;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON FUNCTION console.__init__ () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.clear () AS $body$
#package
BEGIN
    EXECUTE 'delete from console_log_table';
    commit;
  end;
$body$
LANGUAGE PLPGSQL
;
-- REVOKE ALL ON PROCEDURE console.clear () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.groupend () AS $body$
#package
BEGIN
    gGroupIndentIndex := gGroupIndentIndex - 1;

    if gGroupIndentIndex < 0 then
       gGroupIndentIndex := 0;
    end if;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.groupend () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.groupbegin () AS $body$
#package
BEGIN
    gGroupIndentIndex := gGroupIndentIndex + 1;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.groupbegin () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.error (v_pMessage text) AS $body$
#package
BEGIN
    CALL console.print(gLogLevelError, v_pMessage);
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.error (v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.warn (v_pMessage text) AS $body$
#package
BEGIN
    CALL console.print(gLogLevelWarn, v_pMessage);
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.warn (v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.debug (v_pMessage text) AS $body$
#package
BEGIN
    CALL console.print(gLogLevelDebug,v_pMessage);
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.debug (v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.info (v_pMessage text) AS $body$
#package
BEGIN
    CALL console.print(gLogLevelInfo, v_pMessage);
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.info (v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.log (v_pMessage text) AS $body$
#package
BEGIN
    CALL console.print(gLogLevelAll, v_pMessage);
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.log (v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.print (v_pLevel integer, v_pMessage text) AS $body$
#package
DECLARE

    v_xPrefix text := lpad(' ',gGroupIndentIndex,' ');

BEGIN
    

    if gDirection = gDirectionScreen then
      if v_pLevel != gLogLevelAll then
        v_xPrefix := console.getlevelstr(v_pLevel) || ': ';
      end if;

      RAISE NOTICE '% %', v_xPrefix, v_pMessage;
    else
      CALL console.printtotable(v_pLevel,v_pMessage);
    end if;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE console.print (v_pLevel integer, v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE console.printtotable (v_pLevel integer, v_pMessage text) AS $body$
#package
DECLARE
v_xMessage CONSOLE.TLogMessage;

BEGIN AUTONOMOUS

    v_xMessage.LogLevel := v_pLevel;
    v_xMessage.Message  := v_pMessage;

    insert into console_log_table(Level_,Message)
    values (v_xMessage.LogLevel,v_xMessage.Message);

    /*commit;*/

  exception
    when others then
      raise;
  end;
$body$
LANGUAGE PLPGSQL
;
-- REVOKE ALL ON PROCEDURE console.printtotable (v_pLevel integer, v_pMessage text) FROM PUBLIC;



CREATE OR REPLACE FUNCTION console.getlevelstr (v_pLevel integer) RETURNS varchar AS $body$
#package
BEGIN
    case v_pLevel
      when gLogLevelFatal then return 'FATAL';
      when gLogLevelError then return 'ERROR';
      when gLogLevelWarn  then return 'WARNING';
      when gLogLevelInfo  then return 'INFO';
      when gLogLevelDebug then return 'DEBUG';
      when gLogLevelTrace then return 'TRACE';
      when gLogLevelAll   then return 'ALL';
      else
         return 'UNKNOWN';
    end case;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
 IMMUTABLE;
-- REVOKE ALL ON FUNCTION console.getlevelstr (v_pLevel integer) FROM PUBLIC;
-- End of Oracle package 'CONSOLE' declaration



-- Oracle package 'CUSTOMER_PKG' declaration, please edit to match PostgreSQL syntax.

DROP SCHEMA IF EXISTS customer_pkg CASCADE;
CREATE SCHEMA IF NOT EXISTS customer_pkg;
CREATE TYPE customer_pkg.tcustomerinfo AS (
Id          integer,
      Name        varchar(32),
      Description varchar(512)
    
);

CREATE TYPE customer_pkg.tbuffer AS (tbuffer CUSTOMER_PKG.TCustomerInfo[]);






CREATE OR REPLACE FUNCTION customer_pkg.__init__ () RETURNS VOID AS $body$
#import CONSOLE
DECLARE

	gBatchSize constant integer := 100;
	v_gBuffer CUSTOMER_PKG.TBuffer;
	v_cMainCustomer constant integer := 1;
	v_gCurrentCustomerId integer;

BEGIN
  CALL customer_pkg.init_package();
end;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON FUNCTION customer_pkg.__init__ () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE customer_pkg.init_package () AS $body$
#package
BEGIN
    v_gCurrentCustomerId := 0;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.init_package () FROM PUBLIC;



CREATE OR REPLACE PROCEDURE customer_pkg.dump (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) AS $body$
#package
BEGIN
    CALL console.log('------------------  Dump of customer_pkg.TCustomerInfo  ------------------------');
    CALL console.log('Id          => ' || v_pCustomer.Id);
    CALL console.log('Name        => ' || v_pCustomer.Name);
    CALL console.log('Description => ' || v_pCustomer.Description);
    CALL console.log('--------------------------------------------------------------------------------');
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.dump (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE customer_pkg.upd (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) AS $body$
#package
BEGIN
    CALL console.debug('Enter to update customer Id => ' || v_pCustomer.Id || ' ...');

    update customers c
    set
      Name        = v_pCustomer.Name,
      Description = v_pCustomer.Description
    where c.Id =  v_pCustomer.Id;

    v_gCurrentCustomerId := v_pCustomer.Id;

    CALL console.debug('Return from "upd"');
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.upd (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) FROM PUBLIC;



CREATE OR REPLACE PROCEDURE customer_pkg.del (v_pCustomerId bigint) AS $body$
#package
BEGIN
    CALL console.debug('Enter to delete customer Id => ' || v_pCustomerId || ' ...');

    delete from customers c where c.Id =  v_pCustomerId;

    CALL console.debug('Return from "del"');
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.del (v_pCustomerId bigint) FROM PUBLIC;



CREATE OR REPLACE FUNCTION customer_pkg.new (v_pName text, v_pDescription text) RETURNS CUSTOMER_PKG.TCUSTOMERINFO AS $body$
#package
DECLARE

    v_xRes CUSTOMER_PKG.TCustomerInfo;

BEGIN
    insert into customers(Name, Description)
    values (v_pName,v_pDescription) returning id into v_xRes.Id;

    v_xRes.Name          := v_pName;
    v_xRes.Description   := v_pDescription;
    v_gCurrentCustomerId := v_xRes.Id;

    return v_xRes;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON FUNCTION customer_pkg.new (v_pName text, v_pDescription text) FROM PUBLIC;
-- End of Oracle package 'CUSTOMER_PKG' declaration

