PL/SQL: Creating objects

Question

I have to create a PL/SQL package and have been delivered a sample package but trying to follow it I couldn't create an object

CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT (
    db_id                         NUMBER(6),
    usuaricreacio                 VARCHAR2(20),
    datacreacio                   VARCHAR2(25),
    nom                           VARCHAR2(25),
    signatura                     VARCHAR2(55),
    disponible                    BOOLEAN,);

CREATE OR REPLACE TBL_PERSONA_SIGNANT IS TABLE OF OBJ_PERSONA_SIGNANT;

But I'm still getting:

Error: PLS-00201: identifier 'OBJ_PERSONA_SIGNANT' must be declared

Shouldn't this be enough? I honestly don't get why would I use an object while all this info is already in a table but new job, new rules.

I'm guessing I should initialize it or something but have spent the last hour trying to find out what/how should I do and my google-fu is not as it was :/

Any idea will be greatly appreciated.

I doubt you could compile the first statement CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT successfully. Firstly, there is an extra comma, secondly the datatype BOOLEAN in PLSQL — Hawk
– Hawk, Commented Dec 7, 2015 at 8:40

vishnu sable · Accepted Answer · 2015-12-07 09:00:36Z

1

--first you need to check in user object of your type is VALID
select * from user_objects where object_type = 'TYPE' and object_name ='YOUR_TYPE_NAME'
--you can check moere details in user_types 
select * from user_types where type_name ='YOUR_TYPE_NAME'

--you will get type defination in follwing way
select text from user_source where name = 'V_TEMP' order by line
SELECT dbms_metadata.get_ddl('TYPE', 'V_TEMP') FROM DUAL;

finaly if you are using developer tool like toad/ plsql developer then open that type in edit mode and try to compile it from tool window. here you will get exact error and you can correct it.

answered Dec 7, 2015 at 9:00

vishnu sable

3581 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lalit Kumar B · Accepted Answer · 2015-12-07 09:15:06Z

1

There are two issues in your TYPE creation:

A typo error in the end due an extra comma.
TYPE keyword is missing in second statement.

Resolving the above two issues would let you create the objects:

SQL> CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT (
  2      db_id                         NUMBER(6),
  3      usuaricreacio                 VARCHAR2(20),
  4      datacreacio                   VARCHAR2(25),
  5      nom                           VARCHAR2(25),
  6      signatura                     VARCHAR2(55),
  7      disponible                    VARCHAR2(1))
  8  /

Type created.

SQL> CREATE OR REPLACE TYPE TBL_PERSONA_SIGNANT IS TABLE OF OBJ_PERSONA_SIGNANT
  2  /

Type created.

answered Dec 7, 2015 at 9:15

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Comments

Avrajit Roy · Accepted Answer · 2015-12-07 09:29:31Z

1

Hey just made some minor modications  in your code and it works for me. Let me know if this helps.
DROP TYPE OBJ_PERSONA_SIGNANT;

CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT (
    db_id                         NUMBER(6),
    usuaricreacio                 VARCHAR2(20),
    datacreacio                   VARCHAR2(25),
    nom                           VARCHAR2(25),
    signatura                     VARCHAR2(55),
    disponible                    VARCHAR2(10) -- ut may contain 'TRUE' or 'FALSE' wich can be manipulated accrordingly
    );

DROP TYPE TBL_PERSONA_SIGNANT;

CREATE OR REPLACE TYPE TBL_PERSONA_SIGNANT IS TABLE OF OBJ_PERSONA_SIGNANT;

answered Dec 7, 2015 at 9:29

Avrajit Roy

3,3131 gold badge15 silver badges25 bronze badges

Comments

Johns · Accepted Answer · 2015-12-07 11:09:01Z

1

First, let me thank everyone for your input. You gave me enough information to find the issue on my own.

Since the declaration was properly written (checked by you guys) I realized I wasn't putting this where it should be.

To any pl/sql developer that might find this in the future: objects are not defined inside the package, you have to go to types on the Object Browser and do a "new" from there!

Again, thanks everyone!

answered Dec 7, 2015 at 11:09

Johns

816 bronze badges

1 Comment

Lalit Kumar B Over a year ago

In PL/SQL you need to create RECORD. OBJECT type is created and stored in the database.

Praveen · Accepted Answer · 2015-12-07 16:52:43Z

1

disponible                    BOOLEAN,);

Here you have an extra , at the end
Again Oracle don't have a boolean datatype link

Change it to

disponible                   VARCHAR2(1));

Try;

CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT (
    db_id                         NUMBER(6),
    usuaricreacio                 VARCHAR2(20),
    datacreacio                   VARCHAR2(25),
    nom                           VARCHAR2(25),
    signatura                     VARCHAR2(55),
    disponible                  VARCHAR2(1)
)
/ 

CREATE OR REPLACE
TYPE TBL_PERSONA_SIGNANT AS TABLE OF OBJ_PERSONA_SIGNANT
/

EDIT
How would I know if is declared on the schema?
Use ALL_TYPES view

select * from ALL_TYPES
where owner = 'your_schema'

edited Dec 7, 2015 at 16:52

answered Dec 7, 2015 at 8:46

Praveen

9,4634 gold badges38 silver badges51 bronze badges

3 Comments

Johns Over a year ago

Thanks for that. The comma came because I deleted some fields but I already changed the boolean declaration. How would I know if is declared on the schema? Sorry but it's been like 15 years since I last touched pl/sql :S

Lalit Kumar B Over a year ago

There is typo as you have used sql terminator ; and multi-line terminator / together.

William Robertson Over a year ago

@Praveen - surely VARCHAR2 for strings. CHAR is just for ANSI compatibility checklists, not for actual use. asktom.oracle.com/pls/asktom/…

Collectives™ on Stack Overflow

PL/SQL: Creating objects

5 Answers 5

Comments

Comments

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related