Skip to Main Content
  • Questions
  • Unable to find EMP & Dept in Object Browser (APEX oracle)

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Junaid.

Asked: March 17, 2018 - 8:21 pm UTC

Answered by: Connor McDonald - Last updated: December 13, 2019 - 3:15 am UTC

Category: PL/SQL - Version: APEX

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: "Call me!" Many many times!

You Asked

i have recently made an account on APEX oracle, my trainer illustrated how to make the account on Apex and use it for training purpose PL/SQL, therefore i tried by myself when i make the account and go to object browser there i am unable to find any table, views or any sort of objects, i have to work on EMP & DEPT tables to become an expert in SQL but the problem is i am unable to find any thing in the object browser,

i have also googled and go through with the oracle's documentation but i don't even find anything how to activate these accounts or i think these are called schema tables, can anybody help me how can i activate these tables to work on.


thanks

and we said...

You can load the data into your own schema using the below script

drop table emp;
drop table dept;
drop table salgrade;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_fk foreign key (deptno) references dept (deptno);




and you rated our response

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Appreciated, Timely and Useful Reply

March 18, 2018 - 10:50 am UTC

Reviewer: Junaid Ahmed from Karachi, Pakistan

i am really thankful to you, i just run the script that you provided to me and it created all the required tables.



thank you so much.
Connor McDonald

Followup  

March 19, 2018 - 4:47 am UTC

glad we could help

Not working

October 18, 2018 - 9:58 am UTC

Reviewer: Angelina from San Jose, CA USA

ORA-00922: missing or invalid option

When running the above code due to not seeing the default tables, I received this error message.
Connor McDonald

Followup  

October 20, 2018 - 4:16 pm UTC

Save the script I provided as a file, go into Apex, and upload it as a SQL Script and run it.

If you have problems, post the output here after OpenWorld when Chris and I are back on board.

Thank you so much, it helped!

April 23, 2019 - 5:13 pm UTC

Reviewer: A reader


May 19, 2019 - 8:01 am UTC

Reviewer: Nikhil from India

Thank you for posting this:)
Connor McDonald

Followup  

May 21, 2019 - 4:05 am UTC

Glad we could help

Query

June 05, 2019 - 6:51 am UTC

Reviewer: Arun from India

I am really sorry to ask you this but can you please tell me the steps to load schema using the below script?
Connor McDonald

Followup  

June 11, 2019 - 5:47 am UTC

Literally just fire up SQL Developer, cut-paste the code section and hit F5.

That's all there is to it.


Very Helpful

June 21, 2019 - 1:58 am UTC

Reviewer: Sudha


Very Useful

June 21, 2019 - 8:59 pm UTC

Reviewer: Hemal from Mississauga Ontario Canada

This is very useful to get into the groove of practicing different queries on the go.


Connor McDonald

Followup  

June 23, 2019 - 6:32 pm UTC

glad we could help

How can I load this Script

December 07, 2019 - 6:11 am UTC

Reviewer: Takunda from Zimbabwe

I'm just a few hours old in the Oracle Apex, how can I load this script.
Connor McDonald

Followup  

December 13, 2019 - 3:15 am UTC

Paste into the SQL Workshop, or just save it as as file and use upload in SQL Scripts.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.