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

Home -> Community -> Usenet -> c.d.o.server -> Re: all_tab_columns in a stored procedure?

Re: all_tab_columns in a stored procedure?

From: RK <rajXesh_at_hotmail.com>
Date: 29 Jul 2002 05:47:36 -0700
Message-ID: <548b9514.0207290447.aa13e91@posting.google.com>


Yes, I know that roles are disabled in Stored Procedures, BUT what I wanted to know is why a query on ALL_TAB_COLUMNS in a SP works fine when I specify the tables that I own, not for any other tables even though I can see the results in SQL*Plus.

I expect a query on ALL_TAB_COLUMNS, either to behave like it does in SQL*PLus or Not to give any results.

Why is ALL_TAB_COLUMNS behaving like USER_TAB_COLUMNS

Oracle 9.0.1.0.1 on Solaris

PS: Just in case anyone was wondering, I was trying to cobble up an automatic SP generator for query/insert/update/delete sp's on tables which will be called by java programs. Didnt want to write those dumb procs by hand.

jusungyang_at_yahoo.com (Jusung Yang) wrote in message news:<42ffa8fa.0207262310.3a1e6ffd_at_posting.google.com>...
> This is one of those FAQs. Roles are disabled within stored
> procedures. In SPs, you may not have access to some objects granted to
> you thru roles - which makes querying ALL* views within SPs somewhat
> dangerous. Do a direct grant if you have to.
>
>
> rajxesh_at_hotmail.com (RK) wrote in message news:<86c750f4.0207261105.7e24b86a_at_posting.google.com>...
> > I have created a procedure
> >
> > PROCEDURE ColCount ( psTableName IN VARCHAR2) IS
> > nCount NUMBER;
> > sSQL VARCHAR2(100);
> > BEGIN
> > sSQL := ' select count(*) ' ||
> > ' from all_tab_columns ' ||
> > ' where table_name = ''' || upper(psTableName) || '''' ;
> > dbms_output.put_line(sSQL);
> > EXECUTE IMMEDIATE sSQL INTO nCount;
> > dbms_output.put_line(psTableName || ' has ' || to_char(nCount) || '
> > columns');
> > END;
> >
> > When I run it with a table_name in my schema (ME.MY_TABLE), it works
> > fine (The parameter passed is MY_TABLE)
> >
> > But, when I run it with a table_name in another schema
> > (THEY.THEIR_TABLE, with THEIR_TABLE as the parameter), Count(*)
> > returns 0
> >
> > Note: My UserId has not been granted direct access to
> > THEY.THEIR_TABLE, but only thru a role.
> >
> > If run just the select count(*)from all_tab_columns where table_name =
> > &#8216;THEIR_TABLE&#8217; from SQL*Plus, I get the correct column
> > count
> >
> > So Why does the SELECT on ALL_TAB_COLUMNS in a stored procedure work
> > only for my tables, not for any other tables even though I can see the
> > result in SQL*Plus
> >
> > -- rajXesh
Received on Mon Jul 29 2002 - 07:47:36 CDT

Original text of this message

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