can anyone tell me how to get a login id as part of a table reference

[brian_matthewsbrian matthews]

many thanks Peter,

I find that everything I see on this list is helpful - sooner or later :)

regards Bri,


>From: "Peter Bateman" <pfbcs@hotmail.com>
>To: powerh-l@lists.sowder.com
>Subject: Re: can anyone tell me how to get a login id as part of a table 
>reference
>Date: Fri, 04 Mar 2005 19:41:04 -0400
>
>I think this is where the OWNER  / NOOWNER stuff comes to play.
>Brian:
>
>In older versions I use to run QDESIGN with owner=
>and QDESIGN would compile with the table definitions I had access to.
>
>Then when my user ran it it would execute with the ones that he had access 
>to.
>
>Now we have NOOWNER which prevents the owner name from being attached to 
>the table
>name in generated code so that different users can use the
>same compiled screen to access their own tables (having the
>same name).
>
>I hope this is helpful.
>
>Regards,
>Peter Bateman
>
>
>
>>Brian,
>>
>>I wasn't sure what you were really asking. So I have two answers depending 
>>on what I think you wanted, static or dynamic usage.
>>
>>Where I work every Powerhouse user has an OPS$ name created in our Oracle 
>>database. If we were to create a particular table for each user, the table 
>>would be owned by each OPS$User not User, and the syntax for the cursor or 
>>the file statements is the same; OPS$User.TableName, not User.TableName.
>>
>>Next...
>>
>>If your trying to perform this at run time, you have serious problems.
>>
>>I don't think that it is possible in Powerhouse because it parses the 
>>cursor during compile time and substitution variables don't seem to work 
>>with table names. You'd have to alter and compile the source just before 
>>execution.
>>
>>If there is not a version of the table shared via a public synonym of the 
>>same name, then perhaps you can just refer to the users table without the 
>>user prefixed to the table name. This may depend on if Powerhouse prepends 
>>that information in the background during compile time. Oracle presents 
>>the users table first/over the schemas table if not prefixed with the 
>>schema name. But that might depend on the public synonym.
>>
>>Your sample suggests the table will exist for both the user and the schema 
>>owner, so given the above paragraph, you could try the following;
>>
>>	declare mdat cursor for select count(*) as mycount &
>>	from schemaowner.cust_orders a, cust_orders b ...
>>
>>
>>Curiousity begs the question be asked. Why do you want each user to have 
>>his own copy of the table?
>>
>>
>>Darren
>>
>>
>>brian_matthewsbrian matthews wrote:
>>>Hi all,
>>>
>>>can anyone tell me how to get a loginid as part of a table reference,
>>>
>>>If the database ref in the pdl is scott/tiger owner scott, how do I get a 
>>>table owned by e.g. userid bmw into a cursor ref like that below,
>>>
>>>declare mdat cursor for select count(*) as mycount from cust_orders a, 
>>>?????.cust_orders b ...
>>>
>>>where in this case ?????.cust_orders is bmw.cust_orders , and would vary 
>>>to each users local tablespace where the owner is the loginid.
>>>
>>>many thanks in advance, regards Bri,
>>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>>Mailing list: powerh-l@lists.sowder.com
>>Subscribe: "subscribe" in message body to 
>>powerh-l-request@lists.sowder.com
>>Unsubscribe: "unsubscribe <password>" in message body to 
>>powerh-l-request@lists.sowder.com
>>http://lists.sowder.com/mailman/listinfo/powerh-l
>>This list is closed, thus to post to the list you must be a subscriber.
>
>_________________________________________________________________
>Scan and help eliminate destructive viruses from your inbound and outbound 
>e-mail and attachments. 
>http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines 
>  Start enjoying all the benefits of MSNŽ Premium right now and get the 
>first two months FREE*.
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.sowder.com
>Subscribe: "subscribe" in message body to powerh-l-request@lists.sowder.com
>Unsubscribe: "unsubscribe <password>" in message body to 
>powerh-l-request@lists.sowder.com
>http://lists.sowder.com/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.

_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger 
http://www.msn.co.uk/messenger