Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2010
    Posts
    13

    Unanswered: Oracle 10g Express help..Student..simple problem...URGENT!!!!

    Hi,

    I'm a student and very new to Oracle 10g Express. I havea an assignment due in a few weeks.

    In order to do the assignment I obviulsy need to understand this program properly so I've been going through all the tutorials given to us in class.

    Pasted beow here is a problem and the solution given to us for what is called "UDT2"

    Oracle Object-Relational Modelling Exercise 2


    Model the ER diagram shown below making sure you instantiate both sides of the relationship. I leave it you to decide which cars are associated with each salesman. Write some retrieval queries to explore the possibilities of this database, particularly through making use both sides of the relationship.





    Reg# Make Model
    CN02VBY Ford Mondeo
    Y246ALU VW Beetle
    OV55RTA Nissan Micra
    R189LKN Vauxhall Astra
    BR03KPU Toyota Yaris
    G982BCD Renault Megane





    SalesMan# SalesName
    s123 Smith
    s234 Jones
    s345 Evans
    s456 Rees
    s567 Morris
    s678 Farthing









    drop table car_tab;

    drop table salesman_tab;

    drop type car_list_t force;

    drop type car_t force;

    drop type salesman_t force;


    create type salesman_t;
    create type car_t;


    create type car_t as object (
    reg# char(7),
    make varchar2(10),
    model varchar2(10),
    is_sold_by ref salesman_t
    );

    create type car_list_t as table of ref car_t;

    create type salesman_t as object (
    salesman# char(4), salesname varchar2(10), sells car_list_t);


    create table salesman_tab of salesman_t (
    primary key (salesman#))
    nested table sells store as sells_car_table;


    create table car_tab of car_t (
    primary key (reg#),
    scope for (is_sold_by) is salesman_tab) ;


    insert into salesman_tab
    values ('s123', 'Smith', car_list_t());

    insert into salesman_tab
    values ('s234', 'Jones', car_list_t());

    insert into salesman_tab
    values ('s345', 'Evans', car_list_t());

    insert into salesman_tab
    values ('s456', 'Rees', car_list_t());

    insert into salesman_tab
    values ('s567', 'Morris', car_list_t());

    insert into salesman_tab
    values ('s678', 'Farthing', car_list_t());


    insert into car_tab
    select 'CN02VBY', 'Ford', 'Mondeo', REF(s)
    from salesman_tab s
    where s.salesman#='s123';

    insert into car_tab
    select 'Y246ALU', 'VW', 'Beetle', ref(s)
    from salesman_tab s
    where s.salesman#='s123';

    insert into car_tab
    select 'OV55RTA', 'Nissan', 'Micra', ref(s)
    from salesman_tab s
    where s.salesman#='s234';

    insert into car_tab
    select 'R189LKN', 'Vauxhall', 'Astra', ref(s)
    from salesman_tab s
    where s.salesman#='s234';

    insert into car_tab
    select 'BR03KPU', 'Toyota', 'Yaris', ref(s)
    from salesman_tab s
    where s.salesman#='s234';

    insert into car_tab
    select 'G982BCD', 'Renault', 'Megane', ref(s)
    from salesman_tab s
    where s.salesman#='s678';


    insert into TABLE(select s.sells
    from salesman_tab s
    where s.salesman#='s123')
    select ref(c)
    from car_tab c
    where c.is_sold_by.salesman#='s123';

    insert into TABLE(select s.sells
    from salesman_tab s
    where s.salesman#='s234')
    select ref(c)
    from car_tab c
    where c.is_sold_by.salesman#='s234';

    insert into TABLE(select s.sells
    from salesman_tab s
    where s.salesman#='s678')
    select ref(c)
    from car_tab c
    where c.is_sold_by.salesman#='s678';


    select reg#, make, model from car_tab;

    select salesman#, salesname from salesman_tab;


    select s.salesman#, s.salesname, value(c).reg#
    from salesman_tab s, table (s.sells) c;

    select s.salesman#, s.salesname, value(c).reg#
    from salesman_tab s, table (s.sells) c
    where value(c).make='Ford';

    select s.salesman#, s.salesname, value(c).reg#
    from salesman_tab s, table (s.sells) c
    where s.salesname like 'J%';

    select count(s.salesman#) "number of cars", s.salesname name
    from salesman_tab s, table (s.sells) c
    where s.salesname like 'J%'
    group by s.salesname;


    select c.reg#, c.make, c.model, c.is_sold_by.salesname salesman
    from car_tab c;

    select c.is_sold_by.salesname salesman, count(c.reg#) "cars sold"
    from car_tab c
    group by c.is_sold_by.salesname;



    This is the issue:

    When I run the solution script from UDT2 Solution in Oracle, it compiles properly, but then when I go to the object browser and look at either of the two tables (person_tab or car_tab), the "table" tab indicates that the attributes (datatypes which define, or are "mapped" to the columns in the table) are correct, but when I look in the next tab, "data", I get an ORA error message that says it was expecting a NUMBER datatype, but instead found a CAR_T (or PERSON_T) datatype, and is calling it a datatype mismatch. I was careful to include the "/" operator after each TYPE declaration as required, but still get this error. I need to know if I am doing something wrong or if there is a potential bug in the software. Either way, I can't move on until this is resolved!!!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    without knowing the actual SQL & complete error code & message, it is challenging to speculate on problem or solution.

    Most folks do not have Oracle Express installed which make producing the problem impossible.
    Express has been replaced by XE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mrempes87 View Post
    This is the issue:

    When I run the solution script from UDT2 Solution in Oracle, it compiles properly, but then when I go to the object browser
    You script works fine for me.

    Which "object browser"?
    Which SQL client are you using?
    Did you try it with SQL*Plus or SQL Developer?

    but when I look in the next tab, "data", I get an ORA error message that says it was expecting a NUMBER datatype, but instead found a CAR_T (or PERSON_T) datatype, and is calling it a datatype mismatch.
    Clearly a bug in whatever software you are using to display the data.
    I was careful to include the "/" operator after each TYPE declaration as required,
    No, it's not "required".
    The / is only required when you run e.g. create a stored procedure where the definition itself has a ; in it (but then the / doesn't hurt either in SQL*Plus at least, I don't know about the SQL tool you are using)
    or if there is a potential bug in the software.
    I'd say it's a bug in the software you are using.
    Either way, I can't move on until this is resolved!!!!!
    Well, then use a differenz SQL tool. SQL*Plus should be available in the Express edition as well.
    SQL Developer is free and can be downloaded from the Oracle Website and there are a lot more SQL frontends out there (but most "generic" ones will probably not be able to cope with the TYPE declarations)

    Btw: you should enclose SQL statements with the [code] tag, printing them in bold face will put some people off, as this is considered "screaming"

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Shammat
    The / is only required when you run e.g. create a stored procedure where the definition itself has a ; in it (but then the / doesn't hurt either in SQL*Plus at least, I don't know about the SQL tool you are using)
    I'll complain a little bit here (about the underlined part).

    In SQL*Plus, the / will repeat the last stement (i.e. re-execute it). Here's an example:
    Code:
    SQL> get p
      1  select sal from emp where ename = 'KING';
      2  update emp set sal = sal * 2 where ename = 'KING';
      3  /
      4* select sal from emp where ename = 'KING';
    SQL> @p
    
           SAL
    ----------
          5000
    
    
    1 row updated.
    
    
    1 row updated.
    
    
           SAL
    ----------
         20000
    
    SQL>
    See? Instead of "salary x 2", King got a really nice raise.

    So - carefully with /.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    See? Instead of "salary x 2", King got a really nice raise.

    So - carefully with /.
    That's a well documented behaviour

    The / will run the last buffer and as you used a semicolon to terminate the statement the / will re-run it.

    But I agree that is a bit confusing...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I know that you know that it is a documented behaviour.

    But I want you to tell him all the information you just told me. I want him to know what I know. I want him to know I want him to know. And I want them all to know they'll all soon be as dead as O-Ren.

    Ooops! Wrong lines!

  7. #7
    Join Date
    Mar 2010
    Posts
    13

    Which version of Oracle

    Hi,

    I went in to class today to get help from my lecturer

    She coudln't help me


    Yep. Thats right. Pretty messed up huh!


    We use Oracle Sql Plus 10.2.1 at college

    I can't seem to find it anywhere (torrents)

    But I know that for whatever reason I can't get this code to run properly in Oracle 10g Express

    What version of oracle 10g should i be using?

    Please help, I have like ten days here and this assignment will determine if I pass or fail this module.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    sqlplus typically resides in ORACLE_HOME/bin

    If you really need it, sqlplus is included on Client CD/install media
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Mar 2010
    Posts
    13
    I don't have any disc

    I dont own any oracle software nor is it provded to us

    We can either download a free version

    or my other option is find a torrent for a version

    But whatever option, please let me know what you suggest

    I can definitely confirm Oracle 10g Express WILL NOT work for me.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mrempes87 View Post
    I can't seem to find it anywhere (torrents)
    Downloading from Torrent would be illegal.

    But that is not necessary because if it's indeed not included int XE (which I doubt) then you can download the instantclient with the SQL*Plus package from the Oracle website:

    Instant Client downloads for Windows

    As an alternative you can download (as I have already mentioned) SQL Developer, also from the Oracle website

  11. #11
    Join Date
    Mar 2010
    Posts
    13
    Thanks,


    But

    I cant get the program to open up!!!

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle 10g XE installs successfully and easily on most computers. It also provides SQL*Plus as a command line tool to access the database.

    The fact that you don't know how to install software and use it tells more about you than Oracle.

    I can definitely confirm Oracle 10g Express WILL NOT work for me.
    It would, if you know how. Reading and following steps provided in documentation helps in this case. Did you read it? I bet not! You don't even know where to find Oracle software, for heaven's sake! Torrents, you say ... you must be kidding, I say.

    Did you consider studying something simpler than that?

  13. #13
    Join Date
    Mar 2010
    Posts
    13
    Well Littebrain,

    I can tell you this


    My 7 year old brother could probably install Oracle XE. That is not the issue. If you actually read the thread properly you would see that is not the issue.

    The issue is that I was given code as a solution to a tutorial we did in class.

    When I run it on Oracle XE (the program we ARE TOLD to use) It wont work. Out of frustration and no help from anywhere else as of yet, I bring my laptop in to class to my degree level lecturer. She has a look and cannot explain to me why it dosent work.

    From my point of view. I am by no means in the wrong here. My lecturer certainly is.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mrempes87 View Post
    When I run it on Oracle XE (the program we ARE TOLD to use)
    You don't run a query "on Orcle XE", you run it using some kind of client program.
    That could be SQL*Plus, TOAD, SQL Developer or any other SQL query tool.

    You still haven't told use which tool you use to run the SQL.

    "Oracle XE" is not the answer to that question and you have not told us whether your query works with SQL*Plus which is part of an Oracle installation and is somewhat the reference for running SQL statements against an Oracle database.

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK then, I ran statements you provided in the initial post. Everything was OK. Using SQL Developer, I checked your statement which says:
    but when I look in the next tab, "data", I get an ORA error message that says it was expecting a NUMBER datatype, but instead found a CAR_T (or PERSON_T) datatype, and is calling it a datatype mismatch
    Here is the screenshot - it appears that this is OK as well.

    Could you post your screen so that we could see what's going on?
    Attached Thumbnails Attached Thumbnails dbf_xe.PNG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •