Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIcEu-0003WO-CE for pgsql-admin@arkaria.postgresql.org; Fri, 02 Nov 2018 16:24:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gIcEs-00065v-P9 for pgsql-admin@arkaria.postgresql.org; Fri, 02 Nov 2018 16:24:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIcEs-00060y-7S for pgsql-admin@lists.postgresql.org; Fri, 02 Nov 2018 16:24:02 +0000 Received: from sonic314-13.consmr.mail.bf2.yahoo.com ([74.6.132.123]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIcEn-0004aw-9J for pgsql-admin@lists.postgresql.org; Fri, 02 Nov 2018 16:24:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541175835; bh=a2l5CAKVMHnbtusLrowmLAZ1U7W1aAbUagrYTH/Kph0=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=EgCOeM4/Qi3fyUFD1pAZWrR20dAQluDBUMTHeltbBKEGKepgP+PAfphA/utL618gvaa5ip1rRLHkR/1jmvmtVA8ctJGFrtGC/7PSBMyA+u1fRISkZNKJngpbt0kAI3i4hv2VvGiq5rqDohxPGB4sa3E4Y0WxNjJb5iU0UvaSccg6E7z1hSmgNyZ7FwPFamH35t6q5Uh9d8zwyO633NNZCIlZ8LrmpuOuSbUbRsLmHyM47J3Kih3p+ciCOyT03Eyk5bywEAPtEklkvDs1ibaMAEFFSPnAFoy0L4enaWgosr2c6wKd4lw5DGoB/y9a2IOYyIGlGttA9Ww13sJg4NsfRw== X-YMail-OSG: OmEhpmIVM1mWVR1PO7xZLXyVPdINysd95U5JKame5G3pieqNEpuu9HHid6zTwsN mc9ptxKVkebq1GosM1.Rc69jMOaPZ7owuj0QRPlMY18CeDkqeVW_prFJF99zxH2kdGur1vV4QJge 22pS3bDFfstFQRG3eQncsTChUgEKdWfCCS.K6rjfUoIBRnO0YoIDtKWu9H2wxNzzwfuoQoyZcaZO uxHIIb0K_JasfRBUJn4dmmNpVFTlYnmyWLJeiv55lWpJN3IdH01o8TVUrgCgFvfh9yzImSM0iB9K QmtgfztpOlttW_am7TBFc7ATJfl8k0.Q8dL.EkaxIH19OQgWjQ5TWwJcurzZ2QSsoTJPpOUly5O6 SiouLkx1GD8110W3_yfmGeOOf8mEaR39iiidOjW1sd82wuWSF6t1pqOEVY90JBB6UwrweP5Tu9mY .J91Z_RNRIqCSzPg_zXZwA9e.bpX.dfkikVFMAtvZg4odzKhW8wTz63cKDOp9pEf6erZRI1i70HZ VAC..VsJO_VxGNUKpw5hlJSDNpJky3L9G2ekR2VzDxp8Jow2rIZh10EtXXzEIVGgXIxxPhHZQb6W i4EGQTAkdAv6LJRwgNSQZk7AehM.Z2Kfanx9kWSdeajyh83mDtYo3y47KC.Urb.vetmyYip8yXEd BXB1QmnwJ0sANk4bedHA2zD0F2zGs.mvkU7EXZbBVI2JZAZKUo3ArNQCT7oavmvWqBRcxmSvNoLy NAHOgCH.LYZ3aWs0gOZrstaAObuo7AJ9aMGhpYqbB0H8HxkG2dfWDZi1g2HTX2shDtYkn094BHA4 VEEhLAkNXdjvXIudXAkeYgiSIVzciv5JoowJpt7i3OK3q3PH27Ky3XRqqTd957Z_E5vkIjZgGEpC ERpsKnC7SbsDb46GODIC95FPeYIm4gtyfHv2auITkFkyXyOC3QHmhEWFa_nJ2.kgZK_W0YauRcuU VRXA.m2uXKLychBmY6_vI781ICR5BGdIlpt9MFyz2awZ6VMneI6H2fqk1ewuq7u2r6ZLH00ikf1q I.L.dI1JAvw-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.bf2.yahoo.com with HTTP; Fri, 2 Nov 2018 16:23:55 +0000 Date: Fri, 2 Nov 2018 16:23:40 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "pgsql-admin@lists.postgresql.org" Message-ID: <2069914204.21291758.1541175820863@mail.yahoo.com> Subject: new postgresql.org community account MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_21291757_784955352.1541175820811" References: <2069914204.21291758.1541175820863.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12512 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36 Content-Length: 529051 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_21291757_784955352.1541175820811 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =C2=A0 I am new to this community and couldn't find how to post my question.=C2=A0= When I created a new article, it asks for organisations and nothing under = the drop down list.=C2=A0 Click on the organisation list and what do I need= to choose?=C2=A0 Nothing link for me to select.For the general Postgres Su= pport - all archives information and there's no where for me to create a ne= w question.=C2=A0 The link is not very friendly and/or I don't know how.=C2= =A0 Anyway, I'm new to Postgres and need to covert oracle procedure to Postgres= with transaction write into the error. Took me two days to find out how to= create how to create prama autonomous transaction and i'm sure it's correc= ted and my next step of insert information to each table on the procedure.= =C2=A0 It throws me a bunch of error and I couldn't find how to check the p= ostgres error.=C2=A0 I am evaluation this to convert oracle to postgres bef= ore permanent using it and get rid of Oracle.=C2=A0 Can someone please help= me revise this query off Oracle to Postgres? from Oracle: CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_WRITE_ERROR_LOG"= is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into cidrmgmt.errorlog(tstamp, os_user, host, module, errorcode, e= rrormsg) values (sysdate, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg ); commit; end; / ------------------ CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MERGE_STAGING_TA= BLES" (debug IN varchar2,v_Ret OUT number ) as v_ErrorCode number; v_ErrorMsg varchar2(512); v_Module varchar2(32) :=3D'PR_MERGE_STAGING_TABLES'; begin pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Starting MERGE Process' ); v_Ret :=3D 0; ---- ---- -- STG_REF_ACTION_CODES ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_ACTION_CODES ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context= ('userenv','host'), v_Module, 0, 'Started merging STG_REF_ACTION_CODES' ); cidr_staging.pr_mig_stg_action_codes( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('u= serenv','host'), v_Module, 0, 'Finished merging STG_REF_ACTION_CODES' ); end if; ---- -- STG_REF_COUNTRY_CODES ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_COUNTRY_CODES ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context= ('userenv','host'), v_Module, 0, 'Started merging STG_REF_COUNTRY_CODES' ); cidr_staging.pr_mig_stg_country_codes( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_COUNT= RY_CODES' ); end if; ---- -- STG_REF_EAD_ELIGIBILITY_CODES ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_EAD_ELIGIBILITY_CODES ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_EAD_ELIGIBILITY_CODES' ); cidr_staging.pr_mig_stg_ead_elig_codes( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_EAD_ELIGIBILITY_CODES'); end if; ---- -- STG_REF_FORMS ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_FORMS ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_FORMS' ); cidr_staging.pr_mig_stg_ref_forms( v_Ret ); v_Ret :=3D SQLCODE; pr_write_erro_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_FORMS' ); end if; ---- -- STG_REF_IMMIGRANTCLASS ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_IMMIGRANTCLASS ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_IMMIGRANTCLASS' ); cidr_staging.pr_mig_stg_immigrantclass( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_IMMIGRANTCLASS' ); end if; ---- -- STG_REF_JOB_CODES ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_JOB_CODES ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_JOB_CODES' ); cidr_staging.pr_mig_stg_job_codes( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_JOB_CODES' ); end if; ---- -- STG_REF_NONIMMCLASS ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_NONIMMCLASS ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_NONIMMCLASS' ); cidr_staging.pr_mig_stg_nonimmclass( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_NONIMMCLASS' ); end if; ---- -- STG_REF_PART_2_1 ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_PART_2_1 ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_PART_2_1' ); cidr_staging.pr_mig_stg_part_2_1( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_PART_2_1' ); end if; ---- -- STG_REF_PART_2_2 ---- if v_Ret =3D 0 then ---- -- Perform the merge on REF_PART_2_2_CODES ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_PART_2_2' ); cidr_staging.pr_mig_stg_part_2_2( v_Ret ); v_Ret :=3D SQLCODE; pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_PART_2_2' ); end if; ---- -- ##### Starting BASE Tables ##### ---- ---- -- STG_ADJUDICATIVE_STATUS ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_ADJUDICATIVE_STATUS' ); cidr_staging.pr_mig_stg_adj_status( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_ADJUDICATIVE_STATUS' ); end if; ---- -- STG_DATE_IN ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_DATE_IN' ); cidr_staging.pr_mig_stg_date_in( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_DATE_IN' ); end if; ---- -- STG_G28 ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_G28' ); cidr_staging.pr_mig_stg_g28( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_G28' ); end if; ---- -- STG_HISTORY ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_HISTORY' ); cidr_staging.pr_mig_stg_history( v_Ret ); pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_HISTORY' ); end if; ---- -- STG_I864 ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_I864' ); cidr_staging.pr_mig_stg_i864( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_I864' ); end if; ---- -- STG_MBN ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_MBN' ); cidr_staging.pr_mig_stg_mbn( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_MBN' ); end if; ---- -- STG_PETAPP ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_PETAPP' ); cidr_staging.pr_mig_stg_petapp( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_PETAPP' ); end if; ---- -- STG_REMITTANCE ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REMITTANCE' ); cidr_staging.pr_mig_stg_remittance( v_Ret ); pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REMITTANCE' ); end if; ---- -- This does the final commit or rollback for the entire merge -- process. ---- if v_Ret =3D 0 then pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished MERGE Process - COMMIT WORK' ); commit work; else pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished MERGE Process - ROLLBACK WORK' ); rollback work; end if; ---- -- Exception error handler ---- exception when others then v_ErrorCode :=3D SQLCODE; v_ErrorMsg :=3D SQLERRM; v_Ret :=3D v_ErrorCode; ---- -- Rollback any uncommitted changes ---- rollback work; ---- -- Insert record into ErrorLog ---- pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg ); end; / -----I did the 1st procedure oracle converted to Postgres successful w/= o error but not sure it's right:in Postgres:create or replace FUNCTION "PR_= WRITE_ERROR_LOG" ( v_os_user IN varchar(4000), v_host IN varchar(4000), v_m= odule IN varchar(4000), v_errorcode IN int, v_errormsg IN varchar(4000) )= =20 RETURNS VOID=20 as $$ BEGIN START TRANSACTION; insert into cidrmgmt.errorlog(tstamp, os_user, host, module, errorcode,er= rormsg) values (current_timestamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMs= g ); /* commit; */ end; $$ LANGUAGE plpgsql;as I mentioned earlier, the second procedure is still w= ith many errors and then the data type issue even I laready change varchar2= to varchar, number to integer and/or int.=C2=A0 Any help to revise the script and explain how to convert would be appreciat= e thank you. Bach Nga =20 ------=_Part_21291757_784955352.1541175820811 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


 

I am new to this community and could= n't find how to post my question.  When I created a new article, it as= ks for organisations and nothing under the drop down list.  Click on t= he organisation list and what do I need to choose?  Nothing link for m= e to select.
For the gene= ral Postgres Support - all archives information and there's no where for me= to create a new question.  The link is not very friendly and/or I don= 't know how. 

= Anyway, I'm new to Postg= res and need to covert oracle procedure to Postgres with transaction write = into the error. Took me two days to find out how to create how to create pr= ama autonomous transaction and i'm sure it's corrected and my next step of = insert information to each table on the procedure.  It throws me a bun= ch of error and I couldn't find how to check the postgres error.  I am= evaluation this to convert oracle to postgres before permanent using it an= d get rid of Oracle.  Can someone please help me revise this query off= Oracle to Postgres?

from Oracle:
=
CREA=
TE OR REPLACE EDITIONABLE =
PROCEDUR=
E =
"CIDR_STAGING"<=
/span>."PR_WRITE_ERROR_L=
OG" is PRAGMA AUTONOMOUS_T=
RANSACTION;
begin
  insert=
 <=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(1=
6, 16, 148);" id=3D"yui_3_16_0_ym19_1_1541173846885_46168">into cidrmgmt.errorlog(tstamp, os_user, host, module, e=
rrorcode,=
 errormsg=
)<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46185">
  values=

  (sysdate<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46190">, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
  commit=
;<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46204">
end;
/<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46209">
------=
------------

CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MERGE_STAGING_TAB=
LES" (debug IN varchar2,v_Ret OUT number )
as
  v_ErrorCode      number;
  v_ErrorMsg       varchar2(512);
  v_Module         varchar2(32) :=3D<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(1=
25, 39, 39);" id=3D"yui_3_16_0_ym19_1_1541173846885_46243">'PR_MERGE_STAGIN=
G_TABLES'=
;
begin
   pr_write_error_log(sys_context('=
userenv',=
'session=
_user'),<=
/span>
   sys_context('u=
serenv',<=
/span>'host'), v_Module, 0, 'Starting MERGE Process' );

   v_Ret =
:=3D 0;
   ---=
-
   ---=
-
   -- =
STG_REF_ACTION_CODES
   ---=
-
   if v_Ret =3D 0 then
   ---=
-
   -- =
Perform the merge on REF_ACTION_CODES
   ---=
-
      pr_write_error_log(sys_context('userenv','sess=
ion_user'=
), sys_co=
ntext('userenv','host'), v_Module, 0, 'Started merging STG_REF_ACTION_CODES' );

   cidr_staging.pr=
_mig_stg_action_codes( v_Ret );
   v_Ret =
:=3D SQLC=
ODE;

   pr_write_error_log(sys_context('=
userenv',=
'session=
_user'),<=
/span> sys_conte=
xt('userenv','host'), v_Module, 0, 'Finished merging STG_REF_ACTION_CODES' );
   end if;

   ---=
-
   -- =
STG_REF_COUNTRY_CODES
   ---=
-
   if v_Ret =3D 0 then
   ---=
-
   -- =
Perform the merge on REF_COUNTRY_CODES
   ---=
-
      pr_write_error_log(sys_context('userenv','sess=
ion_user'=
), sys_co=
ntext('userenv','host'), v_Module, 0, 'Started merging STG_REF_COUNTRY_CODES' );

cidr_staging.pr_mi=
g_stg_country_codes( v_Ret =
);
   v_Ret =
:=3D SQLC=
ODE;
      pr_write_error_log(sys_context('userenv','sess=
ion_user'=
),
sys_context('user=
env','host'), v_Module, 0, 'Finished merging STG_REF_COUNTRY_CODES' );

       e=
nd if;

       ----
       -- STG_REF_EAD_ELIGIBILITY_CODES
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_EAD_ELIGIBILITY_CODES
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46475">
             0, 'Started merging S=
TG_REF_EAD_ELIGIBILITY_CODES' );

          cidr_staging.pr_mig_stg_ead_elig_codes( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log( sys_context('userenv',=
'session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46508">
             0, 'Finished merging =
STG_REF_EAD_ELIGIBILITY_CODES');
       e=
nd if;

       ----
       -- STG_REF_FORMS
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_FORMS
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46555">
             0, 'Started merging S=
TG_REF_FORMS' );

          cidr_staging.pr_mig_stg_ref_forms( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_erro_log(sys_context('userenv','s=
ession_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46588">
             0, 'Finished merging =
STG_REF_FORMS' );<=
/span>
       e=
nd if;

       ----
       -- STG_REF_IMMIGRANTCLASS
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_IMMIGRANTCLASS
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46636">
             0, 'Started merging S=
TG_REF_IMMIGRANTCLASS' );

          cidr_staging.pr_mig_stg_immigrantclass( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46669">
             0, 'Finished merging =
STG_REF_IMMIGRANTCLASS' );
       e=
nd if;

       ----
       -- STG_REF_JOB_CODES
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_JOB_CODES
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46717">
             0, 'Started merging S=
TG_REF_JOB_CODES' =
);

          cidr_staging.pr_mig_stg_job_codes( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46750">
             0, 'Finished merging =
STG_REF_JOB_CODES' );
       e=
nd if;

       ----
       -- STG_REF_NONIMMCLASS
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_NONIMMCLASS
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46798">
             0, 'Started merging S=
TG_REF_NONIMMCLASS' );

          cidr_staging.pr_mig_stg_nonimmclass( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46831">
             0, 'Finished merging =
STG_REF_NONIMMCLASS' );
       e=
nd if;

       ----
       -- STG_REF_PART_2_1
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_PART_2_1
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46879">
             0, 'Started merging S=
TG_REF_PART_2_1' )=
;

          cidr_staging.pr_mig_stg_part_2_1( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46912">
             0, 'Finished merging =
STG_REF_PART_2_1' =
);
       e=
nd if;

       ----
       -- STG_REF_PART_2_2
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          ----
          -- Perform the merge on REF_PART_2_2_CODES
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46960">
             0, 'Started merging S=
TG_REF_PART_2_2' )=
;

          cidr_staging.pr_mig_stg_part_2_2( v_Ret );
          v_Ret :=3D SQLCODE;

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_46993">
             0, 'Finished merging =
STG_REF_PART_2_2' =
);
       e=
nd if;

       ----
       -- ##### Starting BASE Tables #####
       ----
       ----
       -- STG_ADJUDICATIVE_STATUS
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47041">
             0, 'Started merging S=
TG_ADJUDICATIVE_STATUS' );

          cidr_staging.pr_mig_stg_adj_status( v_Ret );

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47070">
             0, 'Finished merging =
STG_ADJUDICATIVE_STATUS' );
       e=
nd if;

     -=
---
       -- STG_DATE_IN
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47112">
             0, 'Started merging S=
TG_DATE_IN' );

          cidr_staging.pr_mig_stg_date_in( v_Ret );

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47141">
             0, 'Finished merging =
STG_DATE_IN' );
       e=
nd if;

       ----
       -- STG_G28
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47183">
             0, 'Started merging S=
TG_G28' <=
/span>);<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47190">

          cidr_staging.pr_mig_stg_g28( v_Ret =
);

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47212">
             0, 'Finished merging =
STG_G28' =
);=

       e=
nd if;

       ----
       -- STG_HISTORY
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47254">
             0, 'Started merging S=
TG_HISTORY' );

             cidr_staging.pr_mig_stg_history( v_Ret );

          pr_write_error_log( sys_context('userenv',=
'session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47283">

             0, 'Finished merging =
STG_HISTORY' );
       e=
nd if;

       ----
       -- STG_I864
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47325">
             0, 'Started merging S=
TG_I864' =
);=


          cidr_staging.pr_mig_stg_i864( v_Ret );

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47354">
             0, 'Finished merging =
STG_I864'=
 );
       e=
nd if;

       ----
       -- STG_MBN
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47396">
             0, 'Started merging S=
TG_MBN' <=
/span>);<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47403">

          cidr_staging.pr_mig_stg_mbn( v_Ret =
);

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47425">
             0, 'Finished merging =
STG_MBN' =
);=

       e=
nd if;
       ----
       -- STG_PETAPP
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47467">
             0, 'Started merging S=
TG_PETAPP' );
          cidr_staging.pr_mig_stg_petapp( v_Ret );

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47496">
             0, 'Finished merging =
STG_PETAPP' );
       e=
nd if;

       ----
       -- STG_REMITTANCE
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47538">
             0, 'Started merging S=
TG_REMITTANCE' );<=
/span>

          cidr_staging.pr_mig_stg_remittance( v_Ret );

          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47567">
             0, 'Finished merging =
STG_REMITTANCE' );=

       e=
nd if;

       ----
       -- This does the final commit or rollback for the entire merge
       -- process.
       ----
       i=
f v_Ret <=
/span>=3D=
 0 then
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47611">
                0=
, =
'Finished MERGE=
 Process - COMMIT WORK' );
          commit =
work;
       e=
lse
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47640">
                0=
, =
'Finished MERGE=
 Process - ROLLBACK WORK' );
          rollback work;
       e=
nd if;

    --=
--
    --=
 Exception error handler
    --=
--
    exception
       w=
hen other=
s then
          v_ErrorCode :=3D SQLCODE;
          v_ErrorMsg  :=3D SQLERRM;
          v_Ret       :=3D v_ErrorCode;
          ----
          -- Rollback any uncommitted changes
          ----
          rollback work;
          ----
          -- Insert record into ErrorLog
          ----
          pr_write_error_log(sys_context('userenv','=
session_user'),
             sys_context('userenv','host'),=
 v_Module=
,<=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(4=
8, 51, 54);" id=3D"yui_3_16_0_ym19_1_1541173846885_47710">
             v_ErrorCode, v_ErrorMsg );

    end<=
/span>;
    /
    --=
---
I did the 1st procedure oracle converted to Pos= tgres successful w/o error but not sure it's right:
in Postgres:<= /div>
create or repla=
ce FUNCT=
ION "PR_WRITE_ERR=
OR_LOG" <=
/span>( v_os_user =
IN varchar(4000), v_host IN varchar(4000), v_module IN varchar=
(4000), v_errorcode IN int, v_errormsg IN varchar(4000) )=20
RETURNS VOID=20
as $$

BEGIN
  START<=
/span> TRANSACTION;
  insert=
 <=
span style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; =
font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-he=
ight: inherit; font-family: inherit; vertical-align: baseline; color: rgb(1=
6, 16, 148);" id=3D"yui_3_16_0_ym19_1_1541173846885_47778">into cidrmgmt.errorlog(tstamp, os_user, host, module, e=
rrorcode,=
errormsg<=
/span>)
  values=

    (current_time=
stamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );

/* com=
mit; */

end;
$$=
 LANGUAGE plpgsq=
l;=
= as I mentioned earlier, the second procedure is still with many errors and = then the data type issue even I laready change varchar2 to varchar, number = to integer and/or int. 

Any help to revise the script and explain how to convert would be apprec= iate
thank you.

Bach Nga
<= /div>

<= /div>
------=_Part_21291757_784955352.1541175820811--