I am new to this community and couldn't find how to post my question. When I created a new article, it asks for organisations and nothing under the drop down list. Click on the organisation list and what do I need to choose? Nothing link for me to select.
For the general 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 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 corrected and my next step of insert information to each table on the procedure. It throws me a bunch 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 and get rid of Oracle. 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, errormsg)
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_TABLES" (debug IN varchar2,v_Ret OUT number )
as
v_ErrorCode number;
v_ErrorMsg varchar2(512);
v_Module varchar2(32) :='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 := 0;
----
----
-- STG_REF_ACTION_CODES
----
if v_Ret = 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 := SQLCODE;
pr_write_error_log(sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_ACTION_CODES' );
end if;
----
-- STG_REF_COUNTRY_CODES
----
if v_Ret = 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 := SQLCODE;
pr_write_error_log(sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_COUNTRY_CODES' );
end if;
----
-- STG_REF_EAD_ELIGIBILITY_CODES
----
if v_Ret = 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 := 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 = 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 := 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 = 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 := 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 = 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 := 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 = 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 := 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 = 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 := 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 = 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 := 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := 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_module IN varchar(4000), v_errorcode IN int, v_errormsg IN varchar(4000) )
RETURNS VOID
as $$
BEGIN
START TRANSACTION;
insert into cidrmgmt.errorlog(tstamp, os_user, host, module, errorcode,errormsg)
values
(current_timestamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
/* commit; */
end;
$$ LANGUAGE plpgsql;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 appreciate
thank you.
Bach Nga
