DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Ora-00942

  1. #1
    Join Date
    Jul 2001
    Posts
    108

    Ora-00942

    Hello,

    The system is responding with the ORA-00942 error message when I ran the following code. Please help me..

    ---------------------------------------------------

    DECLARE
    Tname varchar2(30);
    CURSOR C1 IS SELECT table_name FROM dba_tables where chain_cnt > 0;

    BEGIN
    Open C1;
    Loop
    Fetch C1 into Tname;
    dbms_output.put_line( 'Analying the table: ' || Tname );
    execute immediate 'analyze table Tname list chained rows';
    EXIT WHEN C1%NOTFOUND;
    End Loop;
    CLOSE C1;
    End;
    /

    ---------------------------------------------------
    Here is the error message:

    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at line 10

    ---------------------------------------------------

    Thanks,
    Seenu

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You are missing the table owner out, and ...

    execute immediate 'analyze table Tname list chained rows';

    ... should be ...

    execute immediate 'analyze table '||Towner||'.'||Tname||' list chained rows';
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2001
    Posts
    108
    Thank you.

    -Seenu

  4. #4
    Join Date
    Jul 2001
    Posts
    108

    ORA-00904

    Hello,

    I have one last quick question.
    The system is responding with the ORA-00904 error message when I ran the following code:
    --------------------------------------------------------
    DECLARE
    Tname varchar2(30);
    CURSOR C1 IS SELECT table_name FROM dba_tables where chain_cnt > 0;

    BEGIN
    Open C1;
    Loop
    Fetch C1 into Tname;

    Execute Immediate 'CREATE table migrated_rows as SELECT orig.* FROM ' || Tname || ' orig, chained_rows cr WHERE orig.rowid = cr.head_rowid AND cr.table_name = upper('||Tname||')';

    EXIT WHEN C1%NOTFOUND;
    End Loop;
    CLOSE C1;
    End;
    /

    --------------------------------------------------------
    Error Message:

    ERROR at line 1:
    ORA-00904: invalid column name
    --------------------------------------------------------

    Kind regards,
    Seenu

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    select orig.*? Interesting technique; however, this is not in Oracle's functional skill set.

  6. #6
    Join Date
    Jul 2001
    Posts
    108
    Hello steven,

    The CREATE table migrated_rows as SELECT orig.* FROM command works when I execute it individually at he SQL prompt.

    Thanks,
    Seenu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width