Home » SQL & PL/SQL » SQL & PL/SQL » strange ora-00942 in a procedure. (10g Enterprise Edition Release 10.2.0.4.0 - 64bi, on aix)
strange ora-00942 in a procedure. [message #441999] Thu, 04 February 2010 05:42 Go to next message
kang
Messages: 89
Registered: November 2007
Member
I can run the select below(table_columns cursor) on my client.

but strangely enough,
I got error(ora-00942) when I produce a procedure.

Error: PL/SQL: ORA-00942: table or view does not exist
Line: 19
Text: FROM dba_tab_columns col, dba_col_comments com

how could this happen?

CREATE OR REPLACE PROCEDURE an_tab(p_schema VARCHAR2, p_table VARCHAR2) IS

  CURSOR table_columns(v_userid VARCHAR2, v_tabname VARCHAR2) IS
    SELECT col.column_name,
           SUBSTR(col.data_type, 1, 10) || '(' || case
             when col.data_type in
                  ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') then
              col.char_length
             else
              col.data_length
           end || case
             when col.CHAR_USED = 'B' then
              ' Bytes'
             when col.CHAR_USED = 'C' then
              ' Characters'
             else
              ''
           end || ')' || ' ' || com.comments "TYPE"
      FROM dba_tab_columns col, dba_col_comments com
     WHERE col.owner = UPPER(p_schema)
       AND col.table_name = UPPER(p_table)
       AND col.owner = com.owner(+)
       AND col.table_name = com.table_name(+)
       AND col.column_name = com.column_name(+);
       
  
  col_str          VARCHAR2(200);
  v_user           VARCHAR2(30) := UPPER(p_schema);
  v_tabname        VARCHAR2(30) := UPPER(p_table);
  v_colname        VARCHAR2(130);
  v_output         VARCHAR2(480); -- max of 16 cols at 30 chars each
  v_dummy          NUMBER := 0;
  v_delrule        VARCHAR2(4);
  v_status         VARCHAR2(4);
  v_excp           NUMBER(1) := 0;
  v_excptab        VARCHAR2(60) := NULL;
  srch_cond        VARCHAR2(1000);
  v_table_type     VARCHAR2(11);
  v_table_comments VARCHAR2(4000);
  buf              VARCHAR2(250);
  cnt              NUMBER;
  v_big            varchar2(32000);
BEGIN
  DBMS_OUTPUT.ENABLE(1000000); -- Prevents buffer exceeded error
  DBMS_OUTPUT.PUT_LINE(CHR(10));
  DBMS_OUTPUT.PUT_LINE('ANALYZING THE TABLE  ' || p_schema || '.' ||
                       p_table);
  DBMS_OUTPUT.PUT_LINE(CHR(10));

  ------ TABLE COLUMNS ----

  DBMS_OUTPUT.PUT_LINE('------TABLE COLUMNS:------');
  DBMS_OUTPUT.PUT_LINE(CHR(10));
  FOR c1 IN table_columns(v_user, v_tabname) LOOP
    begin
      v_colname := RPAD(c1.column_name, 30, ' ');
      v_big     := v_colname || c1.TYPE;
      put_buffer(v_big);
    
    end;
  END LOOP;

  ------ END TABLE COLUMNS ----
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('This table: ' || v_tabname ||
                         ', Does not exist or has no constraints!');
END an_tab;

Re: strange ora-00942 in a procedure. [message #442001 is a reply to message #441999] Thu, 04 February 2010 05:51 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You have been granted accesses to these objects through a role.

That will work in SQL, in PL/SQL you must have a direct grant on that object.

This topic has been discussed several times, search the forum board next time.
Re: strange ora-00942 in a procedure. [message #442005 is a reply to message #442001] Thu, 04 February 2010 06:02 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thanks.
i 'm not clear.
tell me more keywords.
hard to find that topic.
Re: strange ora-00942 in a procedure. [message #442006 is a reply to message #441999] Thu, 04 February 2010 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
asktom

[Updated on: Thu, 04 February 2010 06:04]

Report message to a moderator

Re: strange ora-00942 in a procedure. [message #442007 is a reply to message #441999] Thu, 04 February 2010 06:09 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

You are trying to view a table which are only accessible by a DBA but not a normal User who wont be having rights to view "dba_col_comments" table. To execute your procedure you should have DBA permissions who may be a Administrator of that Database.
Re: strange ora-00942 in a procedure. [message #442009 is a reply to message #442007] Thu, 04 February 2010 06:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To execute your procedure you should have DBA permissions

It is not the main problem, you must directly have the SELECT privilege, it will also not work with DBA role.

Regards
Michel
Previous Topic: SQL Query and OR Operation
Next Topic: can we use MERGE statement in this scenario
Goto Forum:
  


Current Time: Sat Dec 03 18:04:58 CST 2016

Total time taken to generate the page: 0.05872 seconds