Home » SQL & PL/SQL » SQL & PL/SQL » tables
tables [message #196426] Thu, 05 October 2006 05:50 Go to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi guys...

i am sudying for OCP 9i......i have a question which is kindda noob.....

I am using the book which is provided by oracle itself....
"Introduction to Oracle 9i: SQL"

the table used in this book are diff from the ones which are by default in the oracle 9i package....

is there any way i get the coding of all the table which the book is using.....

i know how to code a create table thing...but it would save a lot of my time if i could get it here.....


thanks
Re: tables [message #196436 is a reply to message #196426] Thu, 05 October 2006 06:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess there IS a way, but - unless you specify which tables exactly you are talking about (is it Scott's EMP schema, HR schema, ...), it is kind of hard to suggest the answer (I, for example, don't have this book to check it myself).
Re: tables [message #196468 is a reply to message #196426] Thu, 05 October 2006 08:17 Go to previous messageGo to next message
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
Member

Most of examples are based on samples schemas you can create when you install an Oracle db server.
The tables mentioned are spread between more than one schema. This manual also provides an ERD showing the relationships between the schemas.

Regards,
Gerardo.
Re: tables [message #196507 is a reply to message #196426] Thu, 05 October 2006 12:24 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

it's a simple table but the problem is that it has many fields...which would takema lot of time if i code them...

maybe a person who has the book can help me with that....
Re: tables [message #196569 is a reply to message #196507] Fri, 06 October 2006 00:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regarding the fact that your initial post was at 12:50 and the last one at 19:24 but you still don't have that simple table (as you've put it), I bet you would have it by now only if you started writing the CREATE TABLE statement yesterday at 14:00.
Re: tables [message #196572 is a reply to message #196569] Fri, 06 October 2006 00:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, no, Litlefoot!
Can't you see the OP is stuck? He cannot continue his book without this table.
Quote:

it's a simple table but the problem is that it has many fields...which would takema lot of time if i code them

You don't want the OP to put too much effort in learning Oracle, do you?
Re: tables [message #196805 is a reply to message #196426] Sat, 07 October 2006 11:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Just search sql script in Oracle-home/ora90/demo here sql scripts for tables creations.
thanx
Re: tables [message #196825 is a reply to message #196426] Sun, 08 October 2006 01:11 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi guys....

i have created the table now....the only thing i am confused about is....

this is table description...

SQL> desc employees;
Name Null? Type
------------------- -------- ----------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


this is the query i am doing...

insert into employees values(100,'Steven','King','SKING','515.123.4567','17-JUN-87','AD_PRES',24000 ,' ',' ',90);

which is giving me this->

ERROR at line 1:
ORA-01722: invalid number

can u tell me at what point i am wrong?? i can't seems to figure this out....

thanks
Re: tables [message #196827 is a reply to message #196825] Sun, 08 October 2006 02:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I see a couple of things:
  • you have to convert the Date literal to a true date via the TO_DATE built-in. Your string literal might work but it can cause problems. Oracle will convert the literal to a date using your nls_date_format. Also, 2 positions for a year could cause oracle to take 87 as 0087. So, I'd change '17-JUN-87' to to_date('17-JUN-1987','DD-MON-YYYY').
  • the empty string you are trying to put in the manager_id is interpreted as VARCHAR2. Put NULL instead. The same goes for commission_pct.
MHE
Re: tables [message #196831 is a reply to message #196426] Sun, 08 October 2006 02:21 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

but i've given the data type for 'hire_date' as date....so should'nt oracle automatically convert it into the date format?
Re: tables [message #196834 is a reply to message #196825] Sun, 08 October 2006 03:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1 insert into test values
2* ( 100,'steven','king','sking','515.12.54','17-jun-87','ad_pres',24000,'',''
,90)
SQL> /

1 row created.

SQL> select * from test;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 steven king
sking 515.12.54 17-JUN-87 ad_pres 24000
90


it works for me.
no problem
Re: tables [message #196835 is a reply to message #196426] Sun, 08 October 2006 03:34 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi..

for a null value should there be a gap in the '' ??

[Updated on: Sun, 08 October 2006 03:35]

Report message to a moderator

Re: tables [message #196837 is a reply to message #196834] Sun, 08 October 2006 03:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Short answer: Yes, Oracle converts it as a date, ...
Long answer: ... but the minute you change the default date format you're in trouble. And I don't know by heart (and I'm too lazy to look it up), what Oracle does with a 2 digit year. Will it become 0087, 1987 or 2087? My guess is 2087 but I might be wrong. The proper thing to do is to take control in your own hands and make it a DATE through to_date.

MHE
Re: tables [message #196838 is a reply to message #196835] Sun, 08 October 2006 03:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
cooler2005 wrote on Sun, 08 October 2006 10:34

hi..

for a null value should there be a gap in the '' ??

No, there should be the string NULL (no quotes).


MHE
Re: tables [message #196841 is a reply to message #196838] Sun, 08 October 2006 03:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> create table test ( id number, no number);

Table created.

SQL> insert into test values (1);
insert into test values (1)
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into test values ( 1,);
insert into test values ( 1,)
*
ERROR at line 1:
ORA-00936: missing expression


SQL> insert into test values ( 1,'');

1 row created.

SQL> insert into test values ( 1,' ');
insert into test values ( 1,' ')
*
ERROR at line 1:
ORA-01722: invalid number

[Updated on: Sun, 08 October 2006 03:51]

Report message to a moderator

Re: tables [message #196842 is a reply to message #196426] Sun, 08 October 2006 03:58 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hmmm....got the point..thanks user52..
Re: tables [message #196843 is a reply to message #196842] Sun, 08 October 2006 04:00 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You're welcome
Re: tables [message #196844 is a reply to message #196841] Sun, 08 October 2006 04:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
user52 wrote on Sun, 08 October 2006 10:50

SQL> create table test ( id number, no number);

Table created.

SQL> insert into test values ( 1,'');

1 row created.




Even though Oracle accepts it, your syntax is WRONG.
The datatype of "NO" is number, so you should NOT insert '' into it. As Maaher indicated, use NULL:
insert into test
( id -- best practice is to explicitly name your columns
, no 
) values
( 1
, null
);
Re: tables [message #196853 is a reply to message #196844] Sun, 08 October 2006 11:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I don't think so.
bcoz value in "no" column accepts null.
even "''" we used "null" it will right.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NUMBER
NO NUMBER

SQL> insert into test values (1,'');

1 row created.

SQL> insert into test values (1,null);

1 row created.

SQL> select * from test;

ID NO
---------- ----------
1
1
Re: tables [message #196878 is a reply to message #196426] Mon, 09 October 2006 00:14 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

it works both the ways actually....
Re: tables [message #196880 is a reply to message #196878] Mon, 09 October 2006 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, it works. Not everything that 'works' is right though.
You insert a string into a numberfield. That is wrong. Just as wrong as thinking that '01-JAN-06' is a date. It is not, it is a string.
Maybe Oracle can convert a string to a date or a number, but that does not make it a number!

The reason I mentioned this is that this is the forum where a lot of new or less experienced programmers come.
Help them to get used to using the right datatype and not relying on some silent conversion.

[Updated on: Mon, 09 October 2006 00:28]

Report message to a moderator

Re: tables [message #196895 is a reply to message #196880] Mon, 09 October 2006 01:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Agree with you.
Re: tables [message #196905 is a reply to message #196426] Mon, 09 October 2006 02:01 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

agreed and understood....thanks guys for clearing my doubts.....
Re: tables [message #196908 is a reply to message #196426] Mon, 09 October 2006 02:07 Go to previous messageGo to next message
Kiran Kammili
Messages: 13
Registered: October 2006
Junior Member
Hi,
Please check the attached zip file for the tables which you are looking for. first run the hr_cre.sql file for creating tables and then run the hr_popul.sql file.

Thanks,
Kiran
  • Attachment: tables.zip
    (Size: 9.24KB, Downloaded 122 times)
Re: tables [message #196970 is a reply to message #196426] Mon, 09 October 2006 06:56 Go to previous messageGo to next message
cooler2005
Messages: 50
Registered: October 2006
Location: India
Member

hi dude...

thanks a lot for the file...but these are not the table i was looking for....but now i have created all of them so i would'nt be needing it.....

thanks anywayzz

bye
Re: tables [message #196999 is a reply to message #196970] Mon, 09 October 2006 08:40 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

I fully agree with the view of Frank ...

bye
ashu
Previous Topic: Trigger mutation error
Next Topic: Create output that represents ranges based on broken consecutive list
Goto Forum:
  


Current Time: Sat Dec 10 22:14:36 CST 2016

Total time taken to generate the page: 0.15765 seconds