Q
Problem solve Get help with specific problems with your technologies, process and projects.

Ways to handle an error on an INSERT statement

I am receiving an error on my INSERT statement in a PL/SQL procedure. I had a table in that name during compilation of the procedure and I purposefully renamed it. Due to the table being missing this error is coming. I want to drop or handle this error. If the table is missing I want to skip the insertion operation and so as not to generate any error. What are all the possible ways to do this?

I am receiving the following error on my INSERT statement in a PL/SQL procedure. I had a table in that name during compilation of the procedure and I purposefully renamed it. Due to the table being missing this error is coming. I want to drop or handle this error. If the table is missing I want to skip the insertion operation and so as not to generate any error. What are all the possible ways to do this?
ORA-06550: line 1, column 7:
PLS-00905: object MCP.MYPROC3 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
You can handle this in one of two ways. One way is to code an exception. If an ORA error is raised on INSERT, then write code to handle the exception. The problem with this approach might be that when the exception handling routine is executed, the stored procedure execution ends.

The other approach is to verify that the table exists before executing this portion of the code in your PL/SQL block. Such code might look like the following:

SELECT COUNT(*) INTO num_tables
FROM dba_tables WHERE table_name='MY_TABLE';
IF num_tables > 0 THEN
   INSERT INTO my_table VALUES (.....);
END IF;
In the above code sample, the INSERT statment only occurs if the table exists. If the table does not exist, the INSERT statement is not executed.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close