How to create Oracle Type Object

Filed Under: Database

Today we will learn how to create Oracle Type Object. If you are working on a large project with lots of tables and data, you might have seen the usage of Packages and DB Objects to perform database specific operations.

Oracle Type Object

Here we will see how to create a database object in Oracle DB.


CREATE OR REPLACE TYPE "MY_DBA"."EMP_OBJ" AS OBJECT
(empFirstName			VARCHAR2(80),
empLastName			VARCHAR2(80),
empId				NUMBER,
);

GRANT EXECUTE on EMP_OBJ to MY_USER;

Notice that you need to be logged in with MY_DBA user to create the object and MY_USER is the application user that I am providing privilege to execute this package.

From Security point of view, it’s always better to have a DBA user for DDL changes and an application specific user that will have only the required privileges to perform the database operations.

If I don’t separate these accesses, then the application user will have full access to the DB Object/Packages and they can drop it also, mistakenly or may be by a hacker.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages