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

[Darren Reely]

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,