Hello,
Most of my posts were deleted and I don't know and actually, I'm not sure where and how to post a new question. Someone told me to join the pssql group and I received so many email post and reply from different posts and/or email pgsql-admin for new question.
Please let me know if this is not a right place to do since the forum is not as same as oracle and/or mysql.
I have script in Oracle procedure as Pragma autonomous_transaction:
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;
/
and another procedure to call transaction procedure:
CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT number )
as
v_ErrorCode number;
v_ErrorMsg varchar2(512);
v_Module varchar2(32) := 'PR_MIG_STG_FORMS';
begin
----
-- Simply delete the data from production table
----
delete from cidrdba.ref_forms where 1=1;
----
-- Simply copy the data from staging into production
----
insert into cidrdba.ref_forms(
form_number, form_title, mig_filename
)
select form_number, form_title, mig_filename
from cidr_staging.stg_ref_forms;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
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;
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
----
-- Intentionally leaving the "commit" to application
----
end;
/
and I converted it to Postgres Pragma autonomous_transaction and I'm not sure its corrected but it didn't give any error.
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;
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT int ) RETURNS integer
as $$
declare
v_ErrorCode int;
v_ErrorMsg varchar(512);
v_Module varchar(32) = 'PR_MIG_STG_FORMS';
begin
----
-- Simply delete the data from production table
----
delete from cidrdba.ref_forms where 1=1;
----
-- Simply copy the data from staging into production
----
insert into cidrdba.ref_forms(
form_number, form_title, mig_filename
)
select form_number, form_title, mig_filename
from cidr_staging.stg_ref_forms;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
RAISE INFO 'calling "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
----
RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
0, 'Started merging STG_REF_ACTION_CODES' );
or?
PERFORM pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
PERFORM cidr_staging.pr_mig_stg_action_codes( v_Ret ); --> should I use RAISE INFO to call this procedure too?
v_Ret := SQLCODE;
RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
0, 'Finished merging STG_REF_ACTION_CODES' );
end if;
or?
PERFORM pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
RAISE NOTICE 'Calling "CIDR_STAGING"."PR_WRITE_ERROR_LOG"(%)', ( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
or
PERFORM pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
--
-- Intentionally leaving the "commit" to application
----
end;
$$ LANGUAGE plpgsql;
and how should I execute another function within the function like Oracle? I have searched and found parameters raise log, raise info, raise before and perform. I want to understand how to call the function w/in a function like Oracle would you please explain it?
thank you for all your help.
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
