Re: Advanced distributed insert help needed!

Still weird.. It seems I have rights and a synonym.. what am I missing here?

PROD has a synonym called PROJECT_GET_LABEL pointing to SOURCE using a WBM user that has DBA access.

SOURCE has a WBM user that is a DBA and owns PROJECT_GET_LABEL.

SOR also has the WBM user with DBA access.. ALL database links use the WBM users.

All rights are granted via ROLES!
Do I need to grant execute explicitly.

I am logged onto PROD with WBM running the insert statement..

Thanks for your help.. I am still in the dark here...

-Ken Leach
 Shoreline Software

Sybrand Bakker wrote:

> 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 - 17:08:47 CST