Oracle-Powerhouse: use of links / synonyms

[Whittall, Conrad]

Just wondering whether you have the synonym declared as a public synonym or
not? If it is a public one then it should be visible to all database
schemas, otherwise it will only be visible within the schema where it was
created.

By default, PowerHouse will prefix each database object (table, view,
synonym, etc.) that it tries to access with the current OWNER name, as
specified on the DATABASE statement in the data dictionary, or through the
owner= command line parameter. If the synonym isn't public and is in a
different schema to the one owned by OWNER then you will need to explicitly
name the schema where it exists (public synonyms appear as virtual synonyms
in all schemas within a database).

You could also try using SET LIST SQL when you compile the QDESIGN
statements to see what the generated SQL statements are, and how they are
qualified.

I would also refer you to page 223 of the current PowerHouse Language Rules
manual on the PowerHouse Books CD-ROM, a topic entitled "ORACLE Synonyms in
PowerHouse".

Best regards,
Conrad

Conrad Whittall
Marketing Manager, Application Development Tools, Cognos Incorporated

SeeBusiness. ShareBusiness. UniteBusiness.

Developer productivity never goes out of style!

Save up to 90% of your developers' time on your Web, Windows
and terminal-based business applications. See how Cognos PowerHouse can
help you do this at http://www.cognos.com/powerhouse.


-----Original Message-----
From: Johnnie Sheppard [mailto:codeboy@ix.netcom.com]
Sent: Thursday 3 August 2000 15:15
To: Robert J.M. Edis
Cc: 'powerh-l@list.swau.edu'
Subject: Re: Oracle-Powerhouse: use of links / synonyms


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.