8i bug(s)?

From: Will <sdbillsfan_at_gmail.com>
Date: 26 Oct 2004 11:15:06 -0700
Message-ID: <e881d789.0410261015.22e167a9@posting.google.com>

I apologize in advance if either one of these are known bugs/issues, I tried searching through the newsgroups prior to posting but couldn't find anything.

PROBLEM #1
Below is a simple example to reproduce a problem I'm experiencing. It works fine on 9i but the pl/sql block at the end produces the following output on an 8i box (even though the statement just previous to the block works fine when referencing that column).

ERROR at line 4:

ORA-06550: line 4, column 9:
PLS-00201: identifier 'COLUMN_2' must be declared
ORA-06550: line 4, column 2:

PL/SQL: SQL Statement ignored

I am adding the columns after the table because in my real scenario the additions are in an update script that needs to run on an existing schema without affecting the existing data. The work around I finally came up with was to drop the trigger, add the columns, and then recreate the trigger.

• begin script

ALTER TABLE TEST DROP PRIMARY KEY CASCADE
/

DROP TABLE TEST CASCADE CONSTRAINTS
/

CREATE TABLE TEST
( TEST_ID NUMBER(4)
);

• Create trigger for updating LAST_MODIFIED_DATE create or replace trigger trg_TEST_bu before update on TEST for each row BEGIN NULL; END;
  /
  show errors;

INSERT INTO TEST(TEST_ID) VALUES (1);
COMMIT; ALTER TABLE TEST ADD(COLUMN_1 CHAR DEFAULT 'N' NOT NULL); ALTER TABLE TEST ADD(COLUMN_2 CHAR DEFAULT 'N' NOT NULL ); select COLUMN_2 from TEST;

declare
v_count CHAR;
BEGIN
        SELECT COLUMN_2 into v_count from TEST; END;
/

********************* end script

PROBLEM #2
The script below also works fine on 9i but the pl/sql block produces the following output on an 8i box(Once again, the statement outside the block still works as expected):

ERROR at line 5:

ORA-06550: line 5, column 11:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the
following:

( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>

ORA-06550: line 5, column 31:
PLS-00103: Encountered the symbol "+" when expecting one of the
following:

; return returning and or

• begin script select (select 1 from dual) + (select 2 from dual) as total from dual;

declare
v_count number;
begin

        select ((select 1 from dual) + (select 2 from dual)) into v_count from dual;

end;
/

************* end script

Thanks,
Will Received on Tue Oct 26 2004 - 13:15:06 CDT