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

[brian_matthewsbrian matthews]

Hi all (many thanks Darren),

to answer your last question first - the reason for this requirement is to 
provide an on-screen clustered list of rows in a table, where each row 
containing a column substring that matches a user entered search string is 
highlited in reverse, surrounded by other rows.

I think that a primary requirement for this screen is to be able to create 
an extract table of row_key_id's from the given table, where each row is 
identified by a number indicating its position in the file.

The numbering will enable the screen to page up or down or to the 
next/previous string match by simply adding/subtracting the cluster/occurs 
value e.g. 20 to a temp counter with syntax like (select * from 
extract_table where line_no >= :temp_counter).  If the value of temp_counter 
needs to be changed the screen can either run out to a hub screen - or 
presumably a 'push find' will restart the retieval at the new temp value.

If using sqlserver I will have to create a user owned identity column which 
always starts with 1, if oracle, a user owned sequence which always starts 
with 1; but each user must not overwrite the other users' objects. Once the 
extract table is created for each userid, the contents can be written into a 
generically owned table e.g. scott.table and the screens can process against 
this where userid is part of the where clause match.

Back to Darren's suggestion, can I assume that when logged in by command 
sqlplus as a OS session logged in as userid with the required ops$userid 
addition, that all created tables/objects will default to 
userid.tables/objects  ?

If the latter/former is the case I can simply reference all other 
tables/objects as scott.tables/objects e.g. referencing the userid 
tables/objects as e.g. 'select * from tablea' ( meaning userid.tablea ).

In other words I can say as below,

   create table my_tablea ( my_ord_no number(10) , line_no number(10));
   insert into my_tablea  ( my_ord_no )  select orig_ord_no ,
                                                      myseqnce.nextval from 
scott.some_other_table;

and this will create table userid.my_tablea for any userid that has a OS 
session ( and ops$ setup) is this a reasonable assumption ?


regards Bri,




>From: Darren Reely <darren.reely@latticesemi.com>
>To: brian_matthewsbrian matthews <brian_matthews_bmw@hotmail.com>
>CC: 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 11:01:26 -0800
>
>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.

_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger