Advanced distributed insert help needed!

From: <l8tr0n_at_my-dejanews.com>
Date: Thu, 25 Feb 1999 15:05:28 GMT
Message-ID: <7b3orf$tv5$1@nnrp1.dejanews.com>

Needed... one Oracle God!....

Here is a weird one...

I have four instances, EN,SOURCE,PROD and SOR all running on Oracle 7.3.4 on SP2 boxes.

SOURCE has a table called PROJECT_SOURCE which is a snapshot of a messy local view.

EN has a view called EN_PROJECT pointing to table/snap PROJECT_SOURCE in SOURCE. EN also has a function accessing EN_PROJECT view.

CREATE OR REPLACE
Function PROJECT_GET_LABEL (pvs_project IN VARCHAR2) RETURN VARCHAR2 IS

   lvs_return VARCHAR2(7);
BEGIN
  SELECT label
  INTO lvs_return
  FROM en_project
  WHERE project_id = TO_NUMBER(pvs_project);   RETURN lvs_return;
EXCEPTION
  WHEN others THEN
  BEGIN
    SELECT MAX(label)
    INTO lvs_return
    FROM en_project
    WHERE label = pvs_project
    OR label = SUBSTR (pvs_project, 1, 2) || '-' || SUBSTR (pvs_project,
-4);

    IF lvs_return IS NULL THEN

      SELECT label
      INTO   lvs_return
      FROM   en_project
      WHERE  project_code  = pvs_project
      OR     project_ha    = pvs_project;

    END IF;
    RETURN lvs_return;
  EXCEPTION
    WHEN others THEN
      RETURN NULL;
  END;
END; PROD has a SNAPSHOT of the EN_PROJECT view in EN (also called EN_PROJECT in the PROD instance) PROD also has a synonym for the function in the EN instance, however since PROD has a SNAPSHOT of EN_PROJECT local the mapped function SHOULD use the local EN_PROJECT table in PROD, not the EN_PROJECT (view) in EN. (arrghh how did I dig such a deep hole!?) PROD also has a synonym called INTERFACE pointing to the INTERFACE table in SOR.

SOR has a table called INTERFACE that I am trying to populate from PROD.

If you understand that then maybe you can help us, and I will certify you as a DBA.. heh

While on the PROD instance, I issue a insert statement populating the INTERFACE table in the SOR instance.

INSERT INTO INTERFACE_at_DBL_SOR (project,title,date) SELECT PROJECT_GET_LABEL(wo.project),

       wo.title,
       SYSDATE

FROM work_order wo;

The PROJECT_GET_LABEL function causes the error: ORA-00904: invalid column name ORA-02063:preceding line from DBL_SOR

(DBL_SOR is the name if the database link in PROD pointing to SOR.)

While on PROD I can issue a single INSERT STATEMENT: INSERT INTO INTERFACE_at_DBL_SOR (project,title,date) VALUES ('HA1234','Sample Project',SYSDATE);
That works fine..
I can also run the select part of the insert. SELECT PROJECT_GET_LABEL(wo.project),

       wo.title,
       SYSDATE

FROM work_order wo;
This returns the proper result set.

I can NOT get the insert statement to run with the select. If I remove the PROJECT_GET_LABEL function the insert will work!??

I hope this is clear enough for you to understand.

HELP?
-Ken Leach

 Shoreline Software

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Feb 25 1999 - 09:05:28 CST