Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: when is a variable not a variable
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 UsenetReceived on Wed Jan 29 1997 - 00:00:00 CST