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 1gKPTY-0006o5-0p for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 15:10:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKPTW-0000gm-IR for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 15:10:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKPTV-0000fm-Tt for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 15:10:34 +0000 Received: from sonic310-14.consmr.mail.bf2.yahoo.com ([74.6.135.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKPTS-0003SE-NQ for pgsql-admin@postgresql.org; Wed, 07 Nov 2018 15:10:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541603429; bh=ajAAMDUFjyrYQbSPoF5Eohzx61im0BMiVY9VMy0ch1I=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=VCdUqpOhlL/waoLem6g74JOQZJ4O2wvA/tIpPSBVITbsv2HrYbpas7HmfS4XvR55DavzCTstMQfKrmmiLSjkkNT/6kcHhHj+MuLNqW0LOaNPeR08sQx5Bh69Xo7AzonmIM7m4vUrA4dGX4iK4008YJ8KT7ZdG5ci4zL3GCx52sD3KpViKAjYrPN/YYvQ8H2IbbauWrUPADYwXfw/WmMiePpNMCtUaC6a8OY3YqyS6AYf1LtIMI2ER2D63RgmZnstCK/glr3hJu7z79KJZfgUrvadgRGeipYo57lBc9Skj7kL8G+yKlYDUbBduGAkEKyzVyNYVnuycv5hH64WUVnMsw== X-YMail-OSG: MOPI678VM1l6IKZEL3cwgRMuvol3chJ0HsZw.TZK2ydXdiQcsyv0N.jjobbOMWf X2rlrLhjmciywI4YQItQ66lbc_BBFjFijkHNa2nL81ZJ8hi9NamstXyTgRSzgqe.t22PZxtEnPSC 8Ih9McT5QOb9pfSDF8M1taFN3d58aDb3ZqMcK2mreK3NBqjfTWOthZFBHcRUupDXcR0U_L94a0z1 bnxNTMWcs3DNjaunEqQ0CnkC9.rM05B6I7tfPyAmuvPYF5H4Q2ZbwVMdApjxW_sRuCRp1f7KF4q5 Ndb9UDYqRqdwWen9KgYxoJLk3EOCaiGFvndv1gTDWzBys6sFLjw6DJCpKDfAXx.PQfJmaSfX_tpx N3WUnwqxVytCDrzsfZU5GNWoi8ON0ur6esrWKPJ75Azp0.TAphg4vzraEf55lITFBs1ScDUxna1h Te5FvZwLy2tv5uSxefo4GZfrswmXqLv5olo0suWLL2GOc2n_zc0LS29KIpaVFn4inSghRxJGyviK H_qw7hsOwALBdkF2pg6cA58jomuDdCvs8SKqhku8fE3WU6yhAMDnLrCRiuaIinKJuuPePnYdWohS 2d94bDjowPs.3Vr6BOVQVsURglRzILDCCPWyA06FSP7txffWwMsqUojI22lnGEzKfEmliJzAmCWm NyYIEgRwzep_caL0ZdXlGkscdh98Y9vpDtCjJIA52rDpN.CX3jHpTPiO17hr_wSZDUlAge3qX9f1 HWWPRb9kqfskrVW7G5YBL3qTOsPqSlxSNpxBxsYoq2a4_fW8ct_m3cc6Ub4b6c6yac4IA6sdOHP1 MFAa1F5NfweKJSFTFnaMzTCTRJpl0Q81LDxFVp3KZHg6C5LTJ97wvuOQpbrbqXFchm98u7PEChCq naHcwQI7Z9SUk2ognelfLRnEj8wwwDPfHGm8hq9LllrP3hS0obBi2LJF5_jSJUI5K6Qlgjk68Uex LOzjaFtlmV9UHGzF3ZV7ejXcoKkAqB8_PGdb4LdJu2oAaggqz8hgf_NDDW_Q- Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.bf2.yahoo.com with HTTP; Wed, 7 Nov 2018 15:10:29 +0000 Date: Wed, 7 Nov 2018 15:10:25 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <1425722754.312953.1541603425493@mail.yahoo.com> Subject: query can't merge into table of the other schema MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_312952_2138311454.1541603425483" References: <1425722754.312953.1541603425493.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12710 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: 49672 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_312952_2138311454.1541603425483 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, Would you please tell me why I can't merge table from another schema? I hav= e 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 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0v_ErrorCode=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 number; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0v_ErrorMsg=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0varchar2(512); =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0v_Module=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0var= char2(32) :=3D 'PR_MIG_STG_DATE_IN'; begin =C2=A0 =C2=A0=C2=A0 ---- =C2=A0=C2=A0 --MERGING: STG_DATE_IN into SC_DATE_IN =C2=A0=C2=A0 ---- =C2=A0=C2=A0MERGE INTO cidrdba.sc_date_in prod =C2=A0=C2=A0USING ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SELECT Receipt_= Number,date_in,mig_filename,mig_insert_dt,mig_modified_dt =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0FROM cidr_stagi= ng.STG_Date_In =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ORDER by mig_se= q =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0) stg =C2=A0=C2=A0 ON (prod.receipt_number =3D stg.receipt_number ) =C2=A0=C2=A0 WHENMATCHED THEN UPDATE SET =C2=A0=C2=A0--=C2=A0=C2=A0 prod.Receipt_Number=C2=A0=C2=A0=C2=A0 =C2=A0=3Ds= tg.Receipt_Number, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0prod.Date_In=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=3D stg.Date_In, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0prod.mig_filename=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=3D st= g.mig_filename, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0--prod.mig_insert_dt=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=3D stg.m= ig_insert_dt, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0--prod.mig_modified_dt=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=3D stg.mig_modifi= ed_dt =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0prod.mig_modified_dt=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=3D sysdate =C2=A0=C2=A0 WHENNOT MATCHED THEN INSERT =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 prod.Receipt_Number, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 prod.Date_In, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 prod.mig_filename, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 prod.mig_insert_dt, =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0prod.mig_modified_dt =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0) VALUES ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 stg.Receipt_Number, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 stg.Date_In, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 stg.mig_filename, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 sysdate, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 --stg.mig_insert_dt, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 null =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 --stg.mig_modified_dt =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0) =C2=A0=C2=A0 ; =C2=A0 =C2=A0 =C2=A0----=C2=A0 =C2=A0-- Set the return code to 0 =C2=A0 =C2=A0---- =C2=A0 =C2=A0v_Ret :=3D SQLCODE; =C2=A0 ---- -- Exception error handler ---- exception =C2=A0=C2=A0 whenothers then =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0v_ErrorCode := =3D SQLCODE; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0v_ErrorMsg=C2=A0 :=3D SQLERRM; =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 v_Ret=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 :=3D v_ErrorCode; =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ---- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 -- Commit the record into theErrorLog =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ---- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 pr_write_error_log(sys_context('userenv','session_user'), =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0sys_context('userenv','host'),v_Modu= le, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 v_ErrorCode,v_ErrorMsg ); =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ---- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 -- Intentionally leaving the"commit" to application =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 ---- end; / I converted to Postgres: CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (v_Ret OUT i= nteger ) RETURNS integeras $$ declare=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0integer;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorMsg=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(512);=C2=A0 =C2=A0 =C2=A0= =C2=A0 v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 var= char(32) :=3D 'PR_MIG_STG_DATE_IN';begin =C2=A0 =C2=A0----=C2=A0 =C2=A0-- MERGING: STG_DATE_IN into SC_DATE_IN=C2=A0= =C2=A0----=C2=A0 =C2=A0MERGE INTO cidrdba.sc_date_in prod=C2=A0 =C2=A0USIN= G (=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT Receipt_Number,date_in, = mig_filename,mig_insert_dt,mig_modified_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0FROM cidr_staging.STG_Date_In=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0ORDER by mig_seq=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0) stg=C2=A0 = =C2=A0ON ( prod.receipt_number =3D stg.receipt_number )=C2=A0 =C2=A0WHEN MA= TCHED THEN UPDATE SET=C2=A0 =C2=A0--=C2=A0 =C2=A0prod.Receipt_Number=C2=A0 = =C2=A0 =C2=A0=3D stg.Receipt_Number,=C2=A0 =C2=A0 =C2=A0 prod.Date_In=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D = stg.Date_In,=C2=A0 =C2=A0 =C2=A0 prod.mig_filename=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D stg.mig_filename,=C2=A0 =C2=A0 = =C2=A0 --prod.mig_insert_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =3D stg.mig_insert_dt,=C2=A0 =C2=A0 =C2=A0 --prod.mig_modified_dt= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D stg.mig_modified_dt=C2= =A0 =C2=A0 =C2=A0 prod.mig_modified_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =3D sysdate=C2=A0 =C2=A0WHEN NOT MATCHED THEN INSERT=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0prod.Receipt_Number,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0prod.Date_In,=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0prod.mig_filename,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0prod.mig_= insert_dt,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0prod.mig_modified_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0) VALUES= (=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.= Receipt_Number,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0stg.Date_In,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0stg.mig_filename,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0sysdate,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--stg.mig_insert_dt,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0null=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--stg.mig_modified_dt=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0)=C2=A0 =C2=A0; =C2=A0----=C2=A0-- Set the return code to 0=C2=A0----=C2=A0 =C2=A0v_Ret := =3D SQLCODE; ------ Exception error handler----=C2=A0=C2=A0 =C2=A0exception when others = then=C2=A0 =C2=A0 =C2=A0 v_ErrorCode :=3D SQLCODE;=C2=A0 =C2=A0 =C2=A0 v_Er= rorMsg=C2=A0 :=3D SQLERRM;=C2=A0 =C2=A0 =C2=A0 v_Ret :=3D v_ErrorCode; =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 -- Commit the record into the= ErrorLog=C2=A0 =C2=A0 =C2=A0 ---- --=C2=A0 =C2=A0 =C2=A0 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,=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode, v_ErrorMsg );end;$$ LANGUAGE p= lpgsql; ERROR:=C2=A0 "cidrdba.sc_date_in" is not a known variableLINE 13:=C2=A0 =C2= =A0 MERGE INTO cidrdba.sc_date_in prod=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^SQL state: 42601Character:= 352=C2=A0thank you.Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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 ------=_Part_312952_2138311454.1541603425483 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,

Would you please tell me why I can't merge table from another sc= hema? I have granted all the privilege from one to another.

this is procedure from= Oracle:\
CREAT= E OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (
v_Ret OUT number )
a= s
=
    &nb= sp;   v_ErrorCode          &nbs= p;  number;
  =       v_ErrorMsg           = ;   varchar2(512);
  &n= bsp;     v_Module           &= nbsp;    varchar2(32) :=3D 'PR_MIG_STG_DATE_IN';
begin
 =
<= b id=3D"yui_3_16_0_ym19_1_1541536740079_221997">&nbs= p;  ----
   -- MERGING: STG_DATE_IN into SC_DATE_IN
   ----
= &nb= sp;  MERGE INTO cidrdba.sc_date_in prod
   USING (=
   &nb= sp;       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 =3D stg.receipt_number )
   WHEN MATCHED THEN UPDATE SET<= /o:p>
 = ;  --   prod.Receipt_Number     =3D stg.Receipt_Number,
 &n= bsp;    prod.Date_In          &nb= sp;           =3D stg.Date_In,
  =     prod.mig_filename         &nbs= p;       =3D stg.mig_filename,
 &= nbsp;    --prod.mig_insert_dt         &= nbsp;      =3D stg.mig_insert_dt,
 =      --prod.mig_modified_dt         = ;     =3D stg.mig_modified_dt<= /o:p>
 = ;     prod.mig_modified_dt         &= nbsp;    =3D sysdate=
   WHEN NOT MATCHED THEN INSERT<= /o:p>
 = ;          (
=
    &nb= sp;            =   prod.Receipt_Number,
&= nbsp;           &nbs= p;      prod.Date_In,
         &nb= sp;         prod.mig_filename,
     &= nbsp;           &nbs= p; prod.mig_insert_dt,
 =             &n= bsp;    prod.mig_modified_dt
         &= nbsp; ) VALUES (<= /b>
   =             &nb= sp;   stg.Receipt_Number,
           &n= bsp;       stg.Date_In,
        &n= bsp;          stg.mig_filename= ,
=
    &nb= sp;            =   sysdate,
  &= nbsp;           &nbs= p;    --stg.mig_insert_dt,
          &n= bsp;        null
=         &nbs= p;          --stg.mig_modified= _dt
    &= nbsp;      )
=
   ;
 
   ----
   -- Set the return code to 0
   ----
   v_Ret :=3D SQLCODE;
=  
----<= /div>
-- Exception error han= dler
----
exception
   when others then=
   = ;        v_ErrorCode :=3D SQLCODE;
= &nb= sp;             = ;  v_ErrorMsg  :=3D SQLERRM;
 
 &= nbsp;           &nbs= p;  v_Ret       :=3D v_ErrorCode;
 
         &n= bsp;      ----
          &nb= sp;     -- Commit the record into the ErrorLog
   &n= bsp;            ----=
<= div id=3D"yui_3_16_0_ym19_1_1541536740079_222208">    &nbs= p;           pr_write_err= or_log( sys_context('userenv','session_user'),
           &n= bsp;            = ;        sys_context('userenv','hos= t'), v_Module,
   &= nbsp;           &nbs= p;            &= nbsp;   v_ErrorCode, v_ErrorMsg );
 
      &= nbsp;         ----
       &nb= sp;        -- Intentionally leaving the "commit" to application<= /o:p>
 = ;            &n= bsp;  ----
end;
/

I conver= ted to Postgres:

CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (v_R= et OUT integer ) RETURNS integer
as $$

=
declar= e
 = ;       v_ErrorCode           =  integer;
        v_ErrorMsg        =       varchar(512);
        v_Module  &nbs= p;             varchar(32) :=3D 'PR_MIG_STG_D= ATE_IN';
begin

   ----
   -- MER= GING: STG_DATE_IN into SC_DATE_IN
   ----
   MERGE INTO cidrdba.sc_da= te_in prod
   USING (
           SELECT Receipt_N= umber,date_in, mig_filename,mig_insert_dt,mig_modified_dt
      &nb= sp;    FROM cidr_staging.STG_Date_In
          =  ORDER by mig_seq
           ) stg
   ON ( p= rod.receipt_number =3D stg.receipt_number )
   WHEN MATCHED THEN UPDATE = SET
&nb= sp;  --   prod.Receipt_Number     =3D stg.Rec= eipt_Number,
      prod.Date_In          &= nbsp;           =3D stg.Date_In,
      pro= d.mig_filename                 = ;=3D stg.mig_filename,
      --prod.mig_insert_dt    &nbs= p;           =3D stg.mig_insert_dt,
     = --prod.mig_modified_dt              =3D= stg.mig_modified_dt
      prod.mig_modified_dt     =         =3D sysdate
   WHEN NOT MATCHED THEN INSERT=
 =          (
            &nb= sp;      prod.Receipt_Number,
          &n= bsp;        prod.Date_In,
          &= nbsp;        prod.mig_filename,
        &n= bsp;          prod.mig_insert_dt,
      &n= bsp;            prod.mig_modified_dt
    &= nbsp;      ) VALUES (
            &nb= sp;      stg.Receipt_Number,
          &nb= sp;        stg.Date_In,
          &nb= sp;        stg.mig_filename,
        &nb= sp;          sysdate,
          =          --stg.mig_insert_dt,
       =            null
          =          --stg.mig_modified_dt
       = ;    )
   ;

<= div dir=3D"ltr" id=3D"yui_3_16_0_ym19_1_1541536740079_222579">
 ----
 -- Set the return code to 0
<= div dir=3D"ltr" id=3D"yui_3_16_0_ym19_1_1541536740079_222583"> ----
  &n= bsp;v_Ret :=3D SQLCODE;

----
-- Exception err= or handler
---- 
   exception when others then
      v_ErrorCode := =3D SQLCODE;
      v_ErrorMsg  :=3D SQLERRM;
      v_R= et :=3D v_ErrorCode;

    &nb= sp; ----
      -- Commit the record into the ErrorLog
     = ----
<= br id=3D"yui_3_16_0_ym19_1_1541536740079_222600">
--      RAISE NO= TICE 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_use= r'),
PE= RFORM pr_write_error_log ( sys_context('userenv','session_user'), sys_conte= xt('userenv','host'), v_Module,
              &= nbsp;                 v_ErrorCode, = v_ErrorMsg );
end;
$$ LANGUAGE plpgsql;

=
ERROR:  "cidrdba.sc_date_in" is n= ot a known variable
LINE= 13:    MERGE INTO cidrdba.sc_date_in prod
            &nbs= p;          ^
SQL state: 42601
Character: 352
 
thank you.
Bach-N= ga

No one in this world is pure an= d perfect.  If you avoid people for their mistakes you will be alone. = So judge less, love and forgive more.
To call h= im a dog hardly seems to do him justice though in as much as he had four le= gs, 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 gene= rously **Care deeply **Speak kindly.
*** Genuinely rich *** = Faithful talent *** Sharing success
<= /html> ------=_Part_312952_2138311454.1541603425483--