OracleBlog

Here is a quick one that I touched on briefly in one of my first blogs:

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html

It can be important to specify which columns you plan on providing values for in an INSERT statement. Repeating Dan Morgan's quote:

"By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."

Here is an example. I created a table, and then wrote a procedure that will insert a row WITHOUT specifying the values. Works fine if the table doesn't change:

SET SERVEROUTPUT ON;

CREATE TABLE MyTable (MyInt NUMBER);

CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);

END InsertIntoMyTable;
/

EXEC InsertIntoMyTable(1);
Inserting 1 into MyTable

PL/SQL procedure successfully completed.

So let's try modifying the table, and try again.

ALTER TABLE MyTable ADD (MyString VARCHAR2(32));

EXEC InsertIntoMyTable(2);
BEGIN InsertIntoMyTable(2); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.INSERTINTOMYTABLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The procedure fails, and can't recompile. Perhaps you want that behaviour, because you want to require all your procedures to be checked every time a related table has changed. But, then again, maybe you don't.

In that case, modify the stored procedure to specify which value you are inserting, and then you're good to go. Let's repeat this test that way:

CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable (MyInt) VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);

END InsertIntoMyTable;
/

SQL> EXEC InsertIntoMyTable(2);
Inserting 2 into MyTable

PL/SQL procedure successfully completed.

SQL> ALTER TABLE MyTable ADD (MyOtherInt NUMBER);

Table altered.

SQL> EXEC InsertIntoMyTable(3);
Inserting 3 into MyTable

PL/SQL procedure successfully completed.