Home » SQL & PL/SQL » SQL & PL/SQL » CLOB access ORA-06550 (Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit)
CLOB access ORA-06550 [message #387685] Fri, 20 February 2009 05:18 Go to next message
bzoe
Messages: 2
Registered: February 2009
Junior Member
Hi,
in our database we have two tables containing CLOBs. However, using the same syntax I am not able to access the CLOB in one of them through PL/SQL.

The essential part of table creation scripts is:
CREATE TABLE BEDAPMELDUNG
(
  ...
  MELDUNGSINHALT          CLOB,
  ...
)
LOB (MELDUNGSINHALT) STORE AS 
      ( TABLESPACE  ZPVDATA 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
...


and
CREATE TABLE AENDERUNGSPROTOKOLL
(
  ...
  ZUSTANDVORHERFUERDB             CLOB,
  ...
)
  LOB (ZUSTANDVORHERFUERDB) STORE AS 
      ( TABLESPACE  ZPVDATA 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
...


Accessing the CLOB through PL/SQL results in this:
ZPV @ zpvttm23 #> declare
  2  mylob CLOB;
  3  begin
  4    select meldungsinhalt into mylob from bedapmeldung where dapmeldungid = 1;
  5  end;
  6   /

PL/SQL procedure successfully completed.

ZPV @ zpvttm23 #> declare
  2   mylob CLOB;
  3  begin
  4    select ZUSTANDVORHERFUERDB into mylob from AENDERUNGSPROTOKOLL where AENDERUNGSPROTOKOLLID = 8500;
  5  end;
  6   /
  select ZUSTANDVORHERFUERDB into mylob from AENDERUNGSPROTOKOLL where AENDERUNGSPROTOKOLLID = 8500;
                                             *
ERROR at line 4:
ORA-06550: line 4, column 46:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored


select ZUSTANDVORHERFUERDB from AENDERUNGSPROTOKOLL where AENDERUNGSPROTOKOLLID = 8500;
delivers the correct result when executed in sqlplus.

Any idea what is the problem accessing the CLOB in AENDERUNGSPROTOKOLL

Thanks for your hints
Re: CLOB access ORA-06550 [message #387926 is a reply to message #387685] Sat, 21 February 2009 17:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Although I appreciate that you have attempted to simplify the problem and provide only the relevant details, you have probably left out the part that is causing the problem. It might help to see the complete structure of the AENDERUNGSPROTOKOLL table and the rest of the code that the pl/sql block is used in. I suspect that you have hit a bug such that when there is a column in a table that has the same name as its datatype, it causes code that tries to run on that table to fail. Please see the example below, where the first code executes without error, but after adding a column with the name timestamp and also the datatype timestamp, the code then fails, even though that column is not specifically mentioned in the code, although the same select executes succesfully in sql.

SCOTT@orcl_11g> CREATE TABLE AENDERUNGSPROTOKOLL
  2  (
  3    AENDERUNGSPROTOKOLLID	       NUMBER,
  4    ZUSTANDVORHERFUERDB	       CLOB
  5  )
  6  /

Table created.

SCOTT@orcl_11g> INSERT INTO AENDERUNGSPROTOKOLL VALUES (8500, 'testing')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> create or replace trigger test_trig
  2    before insert on AENDERUNGSPROTOKOLL
  3  declare
  4    mylob CLOB;
  5  begin
  6    select ZUSTANDVORHERFUERDB
  7    into   mylob
  8    from   AENDERUNGSPROTOKOLL
  9    where  AENDERUNGSPROTOKOLLID = 8500;
 10  end;
 11  /

Trigger created.

SCOTT@orcl_11g> ALTER TABLE AENDERUNGSPROTOKOLL ADD (TIMESTAMP TIMESTAMP)
  2  /

Table altered.

SCOTT@orcl_11g> create or replace trigger test_trig
  2    before insert on AENDERUNGSPROTOKOLL
  3  declare
  4    mylob CLOB;
  5  begin
  6    select ZUSTANDVORHERFUERDB
  7    into   mylob
  8    from   AENDERUNGSPROTOKOLL
  9    where  AENDERUNGSPROTOKOLLID = 8500;
 10  end;
 11  /
  before insert on AENDERUNGSPROTOKOLL
                   *
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed


SCOTT@orcl_11g> select ZUSTANDVORHERFUERDB
  2  from   AENDERUNGSPROTOKOLL
  3  where  AENDERUNGSPROTOKOLLID = 8500;

ZUSTANDVORHERFUERDB
--------------------------------------------------------------------------------
testing

SCOTT@orcl_11g>

[Updated on: Sat, 21 February 2009 17:16]

Report message to a moderator

Re: CLOB access ORA-06550 [message #388014 is a reply to message #387926] Mon, 23 February 2009 00:37 Go to previous messageGo to next message
bzoe
Messages: 2
Registered: February 2009
Junior Member
Thanks for your hint. Your guess was very good, the table indeed contains a column named TIMESTAMP which is of datatype timestamp.
The full table creation statement reads:
CREATE TABLE AENDERUNGSPROTOKOLL
(
  AENDERUNGSPROTOKOLLID           NUMBER(19),
  VERSION                         NUMBER(10),
  TIMESTAMP                       TIMESTAMP(6),
  WURZELELEMENTVORHER             VARCHAR2(255 CHAR),
  WURZELELEMENTDISKRIMINATORV     VARCHAR2(255 CHAR),
  WURZELELEMENTNACHHER            VARCHAR2(255 CHAR),
  WURZELELEMENTDISKRIMINATORN     VARCHAR2(255 CHAR),
  IDENTIFIKATION                  VARCHAR2(255 CHAR),
  VERAENDERTERPARTNER             NUMBER(19),
  VERAENDERTEPARTNERROLLE         NUMBER(19),
  PARTNERROLLENARTKURZ            VARCHAR2(5 CHAR),
  WEITERERVERAENDERTERPARTNER     NUMBER(19),
  WEITEREVERAENDERTEPARTNERROLLE  NUMBER(19),
  WEITEREPARTNERROLLENARTKURZ     VARCHAR2(5 CHAR),
  SPEZIFISCHERMELDENDESTELLECODE  VARCHAR2(2 CHAR),
  ZUSTANDVORHERFUERDB             CLOB,
  ZUSTANDNACHHERFUERDB            CLOB,
  ERSTELLERMSTCODE                VARCHAR2(2 CHAR),
  ERSTELLER                       VARCHAR2(8 CHAR),
  ERSTELLTS                       TIMESTAMP(6),
  LETZTAENDMSTCODE                VARCHAR2(2 CHAR),
  LETZTAEND                       VARCHAR2(8 CHAR),
  LETZTAENDTS                     TIMESTAMP(6),
  BEARBGRUND                      VARCHAR2(25 CHAR),
  TRANSACTIONUUID                 VARCHAR2(32 CHAR),
  FUNKTIONKURZ                    VARCHAR2(8 CHAR),
  AKTION                          VARCHAR2(1 CHAR),
  ZUSAETZLICHEPARTNER             VARCHAR2(255 CHAR),
  ZUSAETZLICHEPARTNERROLLEN       VARCHAR2(255 CHAR),
  SSVERSION                       VARCHAR2(8 CHAR),
  SEQUENZNUMMER                   NUMBER(19)
)
TABLESPACE ZPVDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
LOB (ZUSTANDNACHHERFUERDB) STORE AS 
      ( TABLESPACE  ZPVDATA 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
  LOB (ZUSTANDVORHERFUERDB) STORE AS 
      ( TABLESPACE  ZPVDATA 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
ROWDEPENDENCIES
MONITORING;

Re: CLOB access ORA-06550 [message #388208 is a reply to message #388014] Mon, 23 February 2009 17:46 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
That is why it is strongly recommended that you not use Oracle reserved keywords, like timestamp, for column names. In order to fix the problem, you will need to rename your timestamp column. Of course that means you will have to change any code that references that column name as well. Hopefully, you haven't used that column name in a lot of code. You can find a list of word that should not be used as names for columns, tables, or other objects by using:

select keyword from v$reserved_words order by keyword;
Previous Topic: Need suggestions on Debugging a huge Union query with inline views (merged)
Next Topic: ORA-01422 / ORA-0651 but why? Please help! (merged 4)
Goto Forum:
  


Current Time: Mon Dec 05 10:48:00 CST 2016

Total time taken to generate the page: 0.10437 seconds