Re: DBMS_SQL and variables

From: Rona Crystal <r#as#crystal_at_d#as#ri.mc#as#graw-hill.com>
Date: 1997/06/17
Message-ID: <5o6j2r$r1s_at_mgh_cs1.mgh.com>#1/1

In article <33A55FC3.6EC5_at_mb.sympatico.ca>, sysdev_at_mb.sympatico.ca says...
>
>DBMS_SQL
>
>I am using DBMS_SQL to create tables from within procedures.
>I have CREATE ANY TABLE rights.
>If I don't specify a username, the table is created as my own
>(myself.tablename), in my own schema.
>
>I want more than one person to use this procedure, and
>I want to ensure that the table is always created in the
>user's own schema.
>
>How can I get the username into a variable,
>and prefix the tablename with the username:
>
>ie.
>...
>Create table username.tablename as
>(select ...)
>...
>
>???
>
>
 

> select user into user_name from dual;

In the sample you showed, when you select user as above, I believe that you may be picking up the name of the user that owns the stored procedure. I also have my users running stored procedures belonging to another user and have to pick up the running user not the owner user. The package below works for me.

The first stored procedure that gets run in my application calls the procedure 'initialize_user' in the package 'user_info'. In this case it really does nothing but when the package is loaded the variable 'pv_user_name' is filled in with the name of the user who ran the package (not the owner of the package). Because the variable is global it can be used for the duration of the session.

CREATE OR REPLACE PACKAGE user_info AS

   PROCEDURE initialize_user;
   pv_user_name VARCHAR2(30) := USER;
END user_info;
/

CREATE OR REPLACE PACKAGE BODY user_info AS

   PROCEDURE initialize_user IS

         x    NUMBER;
      BEGIN
         x:=1;
      END initialize_user;

END user_info;
/

I use this package to store other pieces of global information that I need available for different stored procedures. It also contains other stored procedures that perform important functions.

Hope this helps.

Rona Crystal

---

Note: My email address has been modified to discourage unsolicited commercial and offensive email that is sent by bulk mailers. To respond personally to this post, remove all strings beginning and ending with #. i.e. j#xx#doe_at_my#yy#comp.com becomes jdoe_at_mycomp.com

---

All opinions expressed here are mine alone and do not reflect the opinion of any other individual or organization.

---

Received on Tue Jun 17 1997 - 00:00:00 CEST