Same mistake, three different errors…

Funny thing I had today. Well funny, not when you want to make progress in your work, but still interesting what I discovered when I tried to isolate the problem…I found a way to make the same mistake and have Oracle return three different errors, dependent on the order of columns in an Insert statement. I cannot explain why, so any feedback I am very willing to receive.

At first I encountered an ORA-600 when I tried to compile some changes I did to a package body:

ORA-06550: line 5, column 19:
PL/SQL: ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
ORA-06550: line 5, column 7:
PL/SQL: SQL Statement ignored

With some progressive commenting of the last changed code I found the reason: I did an insert statement inside a PL/SQL procedure block, I explicitly named the columns in the INSERT INTO table (col1, col2) part and accidentally named one of these columns after the input parameter of the procedure, so something like INSERT INTO table (col1, col2_in). OK problem solved, but I was curious whether this only happens only in my version (11.1.0.7) or others as well. So I wrote a small test script and could reproduce the ORA-600, but to me surprisingly enough I first received error:

  • ORA-01732: data manipulation operation not legal on this view

And that made me see that both the number of columns in the insert statement as well as the (in case there is more than 1 column in it)  order of it determines my error. See for yourself in the code below:

  1. When only 1 column is mentioned and I make my mistake, I get ORA-01732: data manipulation operation not legal on this view
  2. When two or more columns are mentioned and the mistake is made in any BUT the first one, I receive the ORA-600 nasty internal error
  3. When two or more columns are mentioned and the mistake is made in the FIRST column, I receive a third error version: ORA-01733: virtual column not allowed here

Bottom line is to prevent from making this mistake off course (that is also where generated code comes in…a generated insert procedure will not make this error, but then again…why these three versions of the error? I received it on both 11.1.0.7 and 10.2.0.4, my two versions of the database I hade around here.

DROP TABLE tst;

CREATE TABLE tst (a VARCHAR2 (10));

DECLARE
PROCEDURE ins (a_in tst.a%TYPE)
IS
BEGIN
INSERT INTO tst (a_in)
VALUES (a_in);
END;
BEGIN
ins (‘test’);
END;
/

–ORA-06550: line 5, column 3:
–PL/SQL: ORA-01732: data manipulation operation not legal on this view
–ORA-06550: line 5, column 3:
–PL/SQL: SQL Statement ignored

DROP TABLE tst;

CREATE TABLE tst
(
a VARCHAR2 (10),
b VARCHAR2 (10)
);

DECLARE
PROCEDURE ins (b_in IN tst.b%TYPE, a_in tst.a%TYPE)
IS
BEGIN
INSERT INTO tst (b, a_in)
VALUES (b_in, a_in);
END;
BEGIN
ins (‘testb’, ‘testa’);
END;
/

–ORA-06550: line 5, column 19:
–PL/SQL: ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
–ORA-06550: line 5, column 7:
–PL/SQL: SQL Statement ignored

DROP TABLE tst;

CREATE TABLE tst
(
a VARCHAR2 (10),
b VARCHAR2 (10)
);

DECLARE
PROCEDURE ins (a_in tst.a%TYPE, b_in IN tst.b%TYPE)
IS
BEGIN
INSERT INTO tst (b_in, a)
VALUES (b_in, a_in);
END;
BEGIN
ins (‘testa’, ‘testb’);
END;
/

–ORA-06550: line 5, column 24:
–PL/SQL: ORA-01733: virtual column not allowed here
–ORA-06550: line 5, column 7:
–PL/SQL: SQL Statement ignored

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s