4 Replies Latest reply on Jul 10, 2020 1:31 PM by Frank Kulash

    execute immediate throwing an ORA-00942: table or view does not exist

    mist123

      Hi All,

       

      I hope all of you doing well.

       

      I have the below code in the procedure and giving an error.

       

      BEGIN

      EXECUTE IMMEDIATE 'drop table XXC_TEST';

      EXECUTE IMMEDIATE '

      CREATE TABLE XXC_TEST

      AS

        SELECT columns

        FROM   tables

        WHERE  joins=joins';

      EXCEPTION

        WHEN OTHERS THEN

          dbms_output.put_line('Error...'||SQLERRM);

      END;

       

      Please help me.

        • 1. Re: execute immediate throwing an ORA-00942: table or view does not exist
          Frank Kulash

          Hi,

          mist123 wrote:

           

          Hi All,

           

          I hope all of you doing well.

           

          I have the below code in the procedure and giving an error.

           

          BEGIN

          EXECUTE IMMEDIATE 'drop table XXC_TEST';

          EXECUTE IMMEDIATE '

          CREATE TABLE XXC_TEST

          AS

          SELECT columns

          FROM tables

          WHERE joins=joins';

          EXCEPTION

          WHEN OTHERS THEN

          dbms_output.put_line('Error...'||SQLERRM);

          END;

           

          Please help me.

          Whenever you have a problem, please post a compete test script that the people who want to help you can run to re-create the problem and test their ideas.  In this case, include CREATE TABLE and INSERT statements for all the tables that need to exist before running the PL/SQL block.  .Also post the exact results you want (e.g, what XXC_TEST should contain when the PL/SQL code finishes).

          Always say which version of Oracle you're using.

          See the Forum FAQ: Re: 2. How do I ask a question on the forums?

           

          Get rid of the EXCEPTION section; it's only hiding information about the error.   (As written, you can't even tell which which statement causes the error.  It could be that SSC_TEST doesn't already exist.)  If you ever do use EXCEPTION WHEN OTHERS ... , it always needs to be followed by RAISE.

           

          Avoid dynamic SQL, especially creating tables.  In this case, what is the business problem you need to solve?  Whatever it is, I'll bet there's a simpler, more reliable way to do it without dynamic SQL.

           

          If you absolutely must use dynamic SQL, put the entire SQL statement into a single variable, and (during testing and debugging) display that variable before executing it.  For example, instead of

          EXECUTE IMMEDIATE 'drop table XXC_TEST';

          you should use:

          sql_stmt := 'drop table XXC_TEST';

          dbms_output.put_line (sql_stmt || ' = DROP TABLE statement');

          EXECUTE IMMEDIATE sql_stmt;

          • 2. Re: execute immediate throwing an ORA-00942: table or view does not exist
            mist123

            Thank you, Frank.

            what is the data type size for sql_stmt?

            • 3. Re: execute immediate throwing an ORA-00942: table or view does not exist
              BluShadow

              mist123 wrote:

               

              Hi All,

               

              I hope all of you doing well.

               

              I have the below code in the procedure and giving an error.

               

              BEGIN

              EXECUTE IMMEDIATE 'drop table XXC_TEST';

              EXECUTE IMMEDIATE '

              CREATE TABLE XXC_TEST

              AS

              SELECT columns

              FROM tables

              WHERE joins=joins';

              EXCEPTION

              WHEN OTHERS THEN

              dbms_output.put_line('Error...'||SQLERRM);

              END;

               

              Please help me.

               

               

               

              Well it's likely you're getting the error because you're trying to drop a table that doesn't yet exist when the code runs.  If you're dropping the table on the basis that it "might exist" then you would need to put exception handling around that drop statement to ignore the error of it not existing if it doesn't yet.

               

              However

               

              a) It's bad practice to drop and recreate objects as part of your production code - why do you need to do that?

              b) If you want some sort of temporary table, then you are better to create a Global Temporary Table once, up front, that will work for the session and ensure data isn't visible to other sessions.  It doesn't need to be dropped and created all the time, but the data won't remain in it after the session has ended (or even after a commit if you set it up that way)

              • 4. Re: execute immediate throwing an ORA-00942: table or view does not exist
                Frank Kulash

                Hi,

                mist123 wrote:

                 

                Thank you, Frank.

                what is the data type size for sql_stmt?

                The data type should be VARCHAR2.

                The size can be 32767.