Oracle-Powerhouse: use of links / synonyms

[Johnnie Sheppard]

Thanks for your reply.

We are using Sql*Plus on the same machine that Powerhouse is running on.
The TNSNAMES.ORA file is ok.

We are currently trying to use a synonym in a Quick screen to create new records

in the remote table.  Powerhouse documentation indicates we have to declare a
cursor
in our screen.  This is what we did....

SQL IN RDBMS DECLARE  MS_PO_LOAD CURSOR FOR &
  SELECT * FROM  SIPOINTF_PROD
CURSOR   MS_PO_LOAD  DESIGNER


We created an internal procedure which attempted to add records to the remote
table.
Originally, we also had a Fetch statement after the LOAD line to attempt
retrieval of
remote records (just as a test).  It appeared to attempt the retrieval, & we
could successfully
check for ACCESSOK.  We got the message "There is no more data to fetch.".
Now, in this example, we are simply trying to create the new record without
attempting a retrieval.......

   OPEN  MS_PO_LOAD

    LET TRANSACTION_ID OF  MS_PO_LOAD         = T_PO_TRANSID
    LET PROCESS_CODE     OF MS_PO_LOAD          = T_EXP_CODE
    LET PONO                       OF MS_PO_LOAD           = T_EXP_PONO
   <etc, etc.......>

 PUT MS_PO_LOAD


And we get the messages.....

Data access error. (SIPOINTF_PROD*01)          [where SIPOINTF_PROD is the
synonym used]
DMS-E-GENERAL, A general exception has occurred during operation 'prepare
request'.

ORA-00942: table or view does not exist
Error encountered. Data has been rolled back to a stable state.


Any idea what we're doing wrong?   Is there anything special we need to set up
on the remote
database/dataset being called from the link/synonym?   I'm open to any
suggestions.
Thanks again.




"Robert J.M. Edis" wrote:

> G'day Johnnie
>
> I am using PowerHouse 7.33 (HP-UX 10.20) with an Oracle 7.3.4 RDBMS.  A
> number of the programs refer to tables existing in other databases (Oracle
> 8.0.5) for both read and update wit hno problem at all.
>
> No change was made to the PHD and the tables are referenced as if they are
> local.
>
> The 'foreign' database tables are set up as a dblink in the 'host' database
> using a privileged account.  The specific tables needed are declared with
> synonyms using the appropriate rights.
>
> The ORACLE_SID variable has not been changed.
>
> Are you using SQL*Plus from the same machine as PowerHouse is running on?
> Has the TNSNAMES.ORA file been completed correctly?  (E.g. are you using the
> 'world' option on the instance name?
>
> Regards,
> Blue
>
> -----Original Message-----
> From: Johnnie Sheppard
> To: powerh-l
> Sent: 8/2/00 11:18 AM
> Subject: Oracle-Powerhouse:   use of links / synonyms
>
> Has anyone used Oracle external  links &/or synonyms to access a remote
> database
> via Powerhouse?   Are there any special issues that need to be addressed
> before
> Powerhouse can use the links &/or synonyms.  Our DBA has added an
> external
> database link to a local database which can be used in SqlPlus but not
> in Powerhouse.
> She also added a synonynm, but we apparently still can't access the
> remote table
> in Powerhouse.  Any ideas would be greatly appreciated.
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> = =
> Subscribe: "subscribe powerh-l" in message body to
> majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to
> majordomo@lists.swau.edu
> This list is closed, thus to post to the list, you must be a subscriber.
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> This list is closed, thus to post to the list, you must be a subscriber.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.