Re: Stored Procedure Execution Problem

From: Philippe <parnaud_at_yahoo.com>
Date: 5 Aug 1998 06:39:44 GMT
Message-ID: <01bdc03b$432566d0$2a822c95@pdcpm>

The only way is to use dynamic sql

CREATE or REPLACE Procedure Test(schema in varchar2) AS

  C_CUR	INTEGER;
  ROWS	INTEGER;

BEGIN
  C_CUR := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE('DELETE FROM ' || schema || '.PROJECT',DBMS_SQL.NATIVE);   ROWS := DBMS_SQL.EXECUTE(C_CUR);
  DBMS_SQL.CLOSE_CURSOR(C_CUR); EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(C_CUR) THEN
      DBMS_SQL.CLOSE_CURSOR(C_CUR);
    END IF;
END; You call the procedure like this : execute MASTER.Package.TEST(USER);

HTH Philippe

mflach_at_my-dejanews.com wrote in article <6q7jsd$a6p$1_at_nnrp1.dejanews.com>...
> I hope one of you can help me with the following problem.
>
> I have an Oracle (Oracle version 7.34) 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 permanent data
transferred
> 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 schema.
Inside
> this package I want to have procedures that will execute on the tables
> associated 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 schema to
delete
> the data in the calling schema'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 schema's project table.
>
> I would appreciate 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 - 01:39:44 CDT