Hello,
Would you please tell me why I can't merge table from another schema? I have granted all the privilege from one to another.
this is procedure from Oracle:\
CREATE OR REPLACE EDITIONABLE PROCEDURE
"CIDR_STAGING"."PR_MIG_STG_DATE_IN" (
v_Ret OUT number )
as
v_ErrorCode number;
v_ErrorMsg
varchar2(512);
v_Module varchar2(32) := 'PR_MIG_STG_DATE_IN';
begin
----
--
MERGING: STG_DATE_IN into SC_DATE_IN
----
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in,
mig_filename,mig_insert_dt,mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON (
prod.receipt_number = stg.receipt_number )
WHEN
MATCHED THEN UPDATE SET
-- prod.Receipt_Number =
stg.Receipt_Number,
prod.Date_In
= stg.Date_In,
prod.mig_filename
= stg.mig_filename,
--prod.mig_insert_dt
= stg.mig_insert_dt,
--prod.mig_modified_dt
= stg.mig_modified_dt
prod.mig_modified_dt
= sysdate
WHEN
NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- 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;
/
I converted to Postgres:
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (v_Ret OUT integer ) RETURNS integer
as $$
declare
v_ErrorCode integer;
v_ErrorMsg varchar(512);
v_Module varchar(32) := 'PR_MIG_STG_DATE_IN';
begin
----
-- MERGING: STG_DATE_IN into SC_DATE_IN
----
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in, mig_filename,mig_insert_dt,mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Date_In = stg.Date_In,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = sysdate
WHEN NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- 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 "pr_write_error_log"(%)',( sys_context('userenv','session_user'),
PERFORM pr_write_error_log ( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
end;
$$ LANGUAGE plpgsql;
ERROR: "cidrdba.sc_date_in" is not a known variable
LINE 13: MERGE INTO cidrdba.sc_date_in prod
^
SQL state: 42601
Character: 352
thank you.
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
