sql - Getting this error PLS-00311 the declaration is incomplete or malformed

Question

I'm getting an error while compiling

CREATE OR REPLACE TYPE Test AS OBJECT (
  demo demo_tbl,
  demo2 demo2_tbl   
)

While i created demo2_tbl using

CREATE OR REPLACE TYPE "demo2_tbl"

question from:https://stackoverflow.com/questions/65834618/getting-this-error-pls-00311-the-declaration-is-incomplete-or-malformed

Answer 1

[TL;DR] Don't use quoted identifiers unless you have a very good reason to; and even then, you probably don't want to use quoted identifiers.

---

While i created demo2_tbl using

CREATE OR REPLACE TYPE "demo2_tbl"

I'm assuming that your declaration of that type continued and it was something like:

CREATE OR REPLACE TYPE "demo2_tbl" AS TABLE OF INT;

or

CREATE OR REPLACE TYPE "demo2_tbl" AS OBJECT( ... <rest of declaration here>

If you didn't and that was the complete statement then you have not fully declared the type; you have only done a forward declaration of the type so that it could be used as a REF (like a pointer) target.

If you didn't fully declare the type then that is what your problem is.

If you did fully declare the type then the problem is that you used a quoted identifier for "demo2_tbl" when you created it; so now you need to use a quoted identifier everywhere else when you reference it.

For example:

CREATE TYPE demo_tbl AS TABLE OF INT;
CREATE OR REPLACE TYPE "demo2_tbl" AS TABLE OF INT;

If you do:

CREATE OR REPLACE TYPE Test AS OBJECT (
  demo demo_tbl,
  demo2 demo2_tbl   
)

Then you get the error:

PLS-00201: identifier 'DEMO2_TBL' must be declared

But, if you use quotes:

CREATE OR REPLACE TYPE Test AS OBJECT (
  demo  demo_tbl,
  demo2 "demo2_tbl"
)

It works.

db<>fiddle here

---

Now, the solution could be to use the quoted identifier everywhere... however, a BETTER solution is to fix the source of the problem and drop the badly named type and its dependencies (now, before you start using them) and recreate them with case-insensitively named ones:

DROP TYPE test;
DROP TYPE "demo2_tbl";
CREATE TYPE demo2_tbl AS ... etc. ...
CREATE TYPE Test AS OBJECT ... etc. ...

and then you don't need to remember to always quote it.