Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: when is a variable not a variable

Re: when is a variable not a variable

From: <Solomon.Yakobson_at_entex.com>
Date: 1997/01/29
Message-ID: <854559775.24968@dejanews.com>#1/1

Yes, your stored procedure is not working the way you planned. The reason is a misunderstanding of USER_ views. When you execute a stored/packaged procedure/function ORACLE switches to procedure/function owner's schema. In order to implement that, ORACLE changed USER_ views. ORACLE V6 USER_ views were referencing ORACLE function UID to select user objects. In V7 USER_ views are referencing ORACLE function USERENV('SCHEMAID'). Now by simply changing schema id to procedure/function owner's ORACLE USER_ views will point to procedure/function owner's objects.
Moral: Never use USER_ views in procedure/function to get info on USER (who will run it) objects. Instead query ALL_ or DBA_ views WHERE OWNER=USER. ORACLE function USER always returns login user name even within procedure/function.

Solomon.Yakobson_at_entex.com.

In article <5cma2o$874_at_panix.com>,
  bach_at_panix.com (John Steinbach) wrote:
>
> PROCEDURE count_tables IS
> v_name CHAR(20);
> v_count INTEGER;
> CURSOR c_tables IS
> SELECT table_name
> FROM user_tables
> WHERE tabelspace_name = 'tablespacename';
> BEGIN
>
> FOR x IN c_tables LOOP
> v_name := x.table_name;
> SELECT COUNT(*) INTO v_count
> FROM v_name;
>
> INSERT INTO table_count(table_name,count)
> VALUES(v_name,v_count);
> ENND LOOP;
> END;
>
> The following code does not work. I was tring to get a row count of all my
> table spaces without hardcoding the table names.
>
> Any Thoughts?
>
> John STeinbach
> Bartlett Tree Experts

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Wed Jan 29 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US