Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Advanced distributed insert help needed!

Re: Advanced distributed insert help needed!

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 25 Feb 1999 20:12:14 +0100
Message-ID: <36D5A08D.ABD5B5F9@sybrandb.demon.nl>


Not so weird, once you have seen the light... You either don't have privilege to execute the project_get_label function, or you don't have a synonym. Of course in this case this applies to the user you are running with on the remote database. If there is no username/password in the link the same as the local one.
I will not comment on using functions like the project_get_label in other sql statements. In my previous job the developers had turned this in a kind of art. It was a disaster. Basically we needed a mainframe to run a small database. They dropped the thing and bought SAP.

Can I get my certification now?

Hth,

Sybrand Bakker, Oracle DBA

l8tr0n_at_my-dejanews.com wrote:

> 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 - 13:12:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US