Re: Stored Procedure/Schema Question

How about using dynamic sql, and using the logged in id, build the sql 'delete from '||user||'.'||'PROJECT'

read up on dbmssql.sql in the ORACLE_HOME/rdbms/admin(unix) or ORACLE_HOME/rdbms73/admin change 73 to 80 if using oracle8.

i assume master schema has delete on all of the particular schema's tables.

if you'd like a full blown example, email me at mailto:dba_at_oracle-dba.com

and I'll code one up for you.

joe
http://www.oracle-dba.com

In article <6q7gcm$2mu$1_at_nnrp1.dejanews.com>,   mflach_at_my-dejanews.com wrote:
> I hope one of you can help me with the following problem.
>
> I have an Oracle instance with 200 different schemas. Each schema except one
> has tables which hold temporary data. The tables in each schema are
> identical in structure (User1.Table, User2.Table, etc.). There is one Master
> schema (MASTER) which stores permanant data tranferred from the temporary
> tables in the other schemas (It also has the same structure as the temporary
> tables).
>
> I have a package which was created and stored in the MASTER scema. Inside
> this package I want to have procedures that will execute on the tables
> ssociated with the schema that called the procedure, NOT the MASTER tables.
> A simple example would be a procedure within the package stating stating:
>
> CREATE or REPLACE Procedure Test
> AS
> BEGIN
> DELETE FROM PROJECT;
> END
>
> Each user has a Project table (USER1.Project, USER2.Project, MASTER.Project).
> I would like to have this procedure, when called under any scema to delete
> the data in the calling scema's project table.
>
> for example -- If User3 is logged on and executes the statement:
>
> execute MASTER.Package.TEST;
>
> I would like the data in User3's Project table to be deleted.
> As it is now, the procedure will delete from the MASTER schemas project table.
>
> I would appretiate any help anyone has to offer. Thanks.
>
> -Michael Flach-
> MFlach_at_crmcom.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Aug 04 1998 - 14:35:40 CDT