Re: Stored Procedure/Schema Question

On Tue, 04 Aug 1998 17:28:54 GMT, mflach_at_my-dejanews.com wrote:

you will need to use dynamic SQL.

In the master schema create the following procedure...

create or replace procedure test as
  c number;
  s number;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(
    c,
    'delete from ' || user || '.project',     dbms_sql.native );
  s := dbms_sql.execute( c );
  dbms_sql.close_cursor( c );
end test;
/

Preform the following grants...

grant delete any table to master;

grant execute on test to public;

Now any user can execute test and it will delete from there project table.

Hope this helps

chris.

>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
Received on Wed Aug 05 1998 - 07:27:02 CDT