Create table and insert

I have encountered a very strange situation. I have a procedure that checks if a table name exists, if it does, it drops and re-create the table, else just create it. Following is the code : SELECT COUNT(*) INTO V_TABLE_EXIST FROM USER_TABLES WHERE TABLE_NAME = 'X'; IF (V_TABLE_EXIST = 1) THEN EXECUTE IMMEDIATE 'DROP TABLE X'; GOTO create_table; ELSIF (V_TABLE_EXIST = 0) THEN GOTO create_table; END IF; <<create_table>> EXECUTE IMMEDIATE 'CREATE TABLE X (A VARCHAR2(15), B VARCHAR2(500), C VARCHAR2(20), D VARCHAR2(50), E VARCHAR2(200), F VARCHAR2(200), G VARCHAR2(20)) TABLESPACE USERS'; Strange thing is that this all works fine, untill I try and insert data into this new table, then I get a message that the table does not exists, and only when the table gets created and it did not exist before, else the insert works fine.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Why are you dropping the table? If you are using oracle (i recognize the pl/sql statements) you can use the statement truncate table x. That would be a smarter solution

You say that there is only a problem with insert when the table didn’t exist before your run of this procedure. That suggests that you are inserting in the old tabel. How many records are in the table ?

Furthermore you don’t give a complete source of your procedure so we don’t know what exactly is happening.

And as a last note don’t use goto.
Goto leads to ugly code and here it isn’t even necessary.
And in your sample the create will always be executed.

if (v_table_exists=1)
then drop end if;
create x

is the same

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Welcome
    Hi, If you perform the insert in the same procedure that created the table, then the INSERT itself should be a dynamic statement, otherwise, if the INSERT is static and the procedure is compiled when the table DOES NOT EXIST, then you will get a compilation error. On the other side, if you compile while the table still exists and then perform a drop, then your procedure is invalidated (if it contains a static reference to the table), so it needs to be recompiled on further execution, which may not succeed if the table is still not existing. As the previous reader answered, using TRUNCATE is a much better solution. Rgds, Iudith
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: