Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: oracle member methods isn't inserting records in a table

    I have been trying to figure out since last evening what might be wrong with my code.

    I have created a heirarchy of objects (attractions being parent),and coaster its child.

    In the coaster object I have a member procedure to to insert a coaster object into an attractions object table. Then I wrote a PL/SQL block and have tried to use that method to insert a record. However, no matter what I do it triggers my exception under other and prints that message.

    Cannot for the life of me figure out what could be wrong. Is someone could look and advise I would be eternally grateful.

    My code for attractions and all its related objects is :

    --jodee aspin assignment2

    --part b).


    --begin drops
    --drop table park_objtab;

    --drop attraction table
    drop table attraction_objtab;

    --drop coaster_objtyp if it exists
    --drop table coaster_objtab;
    drop type coaster_objtyp;



    --drop attraction object and table if exists
    --drop table attraction_objtab;
    drop type attraction_objtyp;

    --drop budget table and object if exists
    drop table budget_objtab;
    drop type budget_objtyp;

    --drop park table and object
    drop table park_objtab;
    drop type park_objtyp;

    --drop phone lines if exists
    drop type phone_lines;
    --end object and table drops


    --begin coding objects and tables


    --create phone object array

    create type phone_lines is varray(4) of varchar2(30);
    /

    --end phone object code



    --create park object and table

    create type park_objtyp as object(
    parkname varchar2(100),
    manager varchar2(100),
    opentime varchar2(10),
    closetime varchar2(10),
    contact phone_lines)not final;
    /

    create table park_objtab of park_objtyp(constraint pk_pkname primary key(parkname))
    object identifier is primary key;

    --end park code



    --create budget object, methods and table

    create type budget_objtyp as object(
    year varchar2(4),
    budgetvalue number(20),
    totalspent number(20),
    parkname varchar2(100),
    member procedure addattractioncost(yr varchar2, amt number),
    member function lefttospend(yr date) return number);
    /


    create table budget_objtab of budget_objtyp(constraint pk_year primary key(year,parkname),
    constraint fk_park foreign key(parkname) references park_objtab)
    object identifier is primary key;


    --create budget object body

    create or replace type body budget_objtyp as
    member procedure addattractioncost(yr varchar2, amt number) is

    e_invalparam exception;
    e_over_budget exception;
    begin

    if (yr!=null) and (amt !=null) then

    if amt > 100000 then

    raise e_over_budget;

    else
    update budget_objtab
    set totalspent= totalspent + amt
    where year = yr;
    end if;
    else
    raise e_invalparam;

    end if;

    exception
    when e_invalparam then
    dbms_output.put_line('warning: attempting to insert null values for parameters.');
    when e_over_budget then
    dbms_output.put_line('warning: amount exceeds 100,000 allowed per attraction.');
    when others then
    dbms_output.put_line('warning: an error occured when inserting data in table.');

    end addattractioncost;


    member function lefttospend(yr date) return number is

    e_invalparam exception;
    begin

    if (yr!=null) then

    return budgetvalue - totalspent;

    else
    raise e_invalparam;
    end if;

    exception
    when e_invalparam then
    dbms_output.put_line('warning: attempting to insert null values for parameters.');
    when others then
    dbms_output.put_line('warning: an error occured when inserting data in table.');

    end lefttospend;
    end;
    /

    --end budget code




    ----changes here

    --create attraction parent object

    create type attraction_objtyp as object(
    attractionid number(6),
    attractionname varchar2(100),
    cost number(20),
    yearopened date,
    heightrestriction varchar2(10),
    ridecapacity number(3),
    category varchar2(25),
    parkname varchar2(100)) not instantiable not final;
    /


    create table attraction_objtab of attraction_objtyp(constraint pk_attractionid primary key(attractionid))
    object identifier is primary key;


    --create coaster object as subtype of attraction

    create type coaster_objtyp under attraction_objtyp(
    loopcount number(2),
    maxspeed_mph number(3),
    duration_min number(3),
    thrillfactor number(20),
    member procedure add_coaster(pattractid number,pattrname varchar2,pcost number,pyear date,pheightlim varchar2,pridecap number,pcat varchar2,pname varchar2,ploopnum number,pmaxspd number,pduration number,pthrillf number));
    /

    --create body for coaster object
    create or replace type body coaster_objtyp as
    member procedure add_coaster(pattractid number,pattrname varchar2,pcost number,pyear date,pheightlim varchar2,pridecap number,pcat varchar2,pname varchar2,ploopnum number,pmaxspd number,pduration number,pthrillf number) is

    v_attridstore number(6);

    v_budget budget_objtyp;
    v_left_spend number;

    e_invalidparameter exception;
    e_value_too_large exception;
    e_budget_overspill exception;


    begin

    select attractionid into v_attridstore
    from attraction_objtab
    where attractionid = pattractid;

    select value(p) into v_budget
    from budget_objtab p
    where year = pyear;

    v_left_spend := v_budget.lefttospend(pyear);


    if (v_left_spend < 0) then
    dbms_output.put_line(v_left_spend);
    raise e_budget_overspill;
    end if;

    if (pattractid = null) then
    dbms_output.put_line(pattractid);
    raise e_invalidparameter;

    end if;

    if (pattrname = null) then
    dbms_output.put_line(pattrname);
    raise e_invalidparameter;
    end if;

    if (pcost = null) then
    dbms_output.put_line(pcost);
    raise e_invalidparameter;

    end if;

    if (pyear = null) then
    dbms_output.put_line(pyear);
    raise e_invalidparameter;
    end if;

    if (pheightlim = null) then
    dbms_output.put_line(pheightlim);
    raise e_invalidparameter;

    end if;

    if (pridecap = null) then
    dbms_output.put_line(pridecap);
    raise e_invalidparameter;

    end if;

    if (pcat = null) then
    dbms_output.put_line(pcat);
    raise e_invalidparameter;
    end if;

    if (pname = null) then
    dbms_output.put_line(pname);
    raise e_invalidparameter;
    end if;


    if (ploopnum = null) then
    dbms_output.put_line(ploopnum);
    raise e_invalidparameter;

    if (ploopnum > 10) then
    raise e_value_too_large;
    end if;
    end if;

    if (pmaxspd = null) then
    dbms_output.put_line(pmaxspd);
    raise e_invalidparameter;

    if (pmaxspd > 120) then
    raise e_value_too_large;
    end if;
    end if;

    if (pduration = null) then
    dbms_output.put_line(pduration);
    raise e_invalidparameter;

    if (pduration > 5) then
    raise e_value_too_large;
    end if;
    end if;

    if (pthrillf < 1) or (pthrillf > 10) then
    dbms_output.put_line(pthrillf);
    raise e_value_too_large;

    end if;
    insert into attraction_objtab values(coaster_objtyp(pattractid,pattrname,pcost,p year,pheightlim,pridecap,pcat,pname,ploopnum,pmaxs pd,pduration,pthrillf));
    --v_budget.addattractioncost(pyear,pcost);


    exception
    when dup_val_on_index then
    dbms_output.put_line('input violates foreign key constraint.');
    when e_invalidparameter then
    dbms_output.put_line('warning: attempting to insert null values for parameters.');
    when e_value_too_large then
    dbms_output.put_line('warning: outside allowable values.');
    when e_budget_overspill then
    dbms_output.put_line('warning: budget amount over.');
    when others then
    dbms_output.put_line('warning: an error occured when inserting data in table.');


    end add_coaster;
    end;
    /
    show errors


    --end attraction types hierarchy

    --part c

    --i).


    --populate park table

    insert into park_objtab values('amazon jungle adventure','havi abdul','09:00','08:00',(phone_lines('02100 123 456','02100 123 457',
    '02100 123 458','02100 123 459')));

    --end populate park table



    --begin populate table budget

    insert into budget_objtab values(budget_objtyp(2005,300000,0,'amazon jungle adventure'));

    --end populate budget



    MY PL/SQL BLOCK is:



    set serveroutput on
    set echo off;
    set verify on;

    declare
    --block to enter details in attraction table object
    --prompt user for data
    v_attraction_id number(6) := &p_attract_id;
    v_name varchar2(100):= '&p_attraction_name';
    v_cost number(20):= &p_cost;
    v_yearopened date:= to_date('&p_date','dd-mm-yyyy');
    v_hr varchar2(10):= '&p_hr';
    v_ridecapacity number(3):=&p_ridecap;
    v_category varchar2(25):='&p_ridecat';
    v_parkname varchar2(100):='&p_parkname';
    v_loopcount number(2):= &p_loops;
    v_maxspd number(3):=&p_max;
    v_duration number(3):=&p_dur;
    v_thrillfactor number(20):=&p_thrillfactor;

    --declare coaster object
    v_coaster coaster_objtyp;

    begin
    dbms_output.put_line(v_attraction_id);
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_cost);
    dbms_output.put_line(v_yearopened);
    dbms_output.put_line(v_hr);
    dbms_output.put_line(v_ridecapacity);
    dbms_output.put_line(v_category);
    dbms_output.put_line(v_parkname);
    dbms_output.put_line(v_loopcount);
    dbms_output.put_line(v_maxspd);
    dbms_output.put_line(v_duration);
    dbms_output.put_line(v_thrillfactor);
    --initialise coaster object

    v_coaster:=coaster_objtyp(null,null,null,null,null ,null,null,null,null,null,null,null);

    --insert coaster objects
    --insert into attraction_objtab values(coaster_objtyp(v_attraction_id,v_name,v_cos t,v_yearopened,v_hr,v_ridecapacity,v_category,v_pa rkname,v_loopcount,v_maxspd,v_duration,v_thrillfac tor));
    v_coaster.add_coaster(v_attraction_id,v_name,v_cos t,v_yearopened,v_hr,v_ridecapacity,v_category,v_pa rkname,v_loopcount,v_maxspd,v_duration,v_thrillfac tor);
    end;
    /


    --pl/sql block to add wetride to attraction table


    Thanks guys its really driving me mad.

    Jeng

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Hey -- you have just showed one of the worst use of a when others.. and that is 'hide the error for me, please'. How about if you change the code so you can actually see the error ?

    I mean, how about if you change this..
    Code:
    when others then
    dbms_output.put_line('warning: an error occured when inserting data in table.');
    for
    Code:
    when others then
    dbms_output.put_line('warning: an error occured when inserting data in table.');
    raise;

  3. #3
    Join Date
    Jan 2005
    Posts
    3

    to j martinez

    HI Jmartinez,

    First, forgive me because I am just learning how to use exceptions and pl/sql.

    I took your advice and it returned the following reply:

    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "ASPINJ.COASTER_OBJTYP", line 124
    ORA-06512: at line 39

    all of my variables are getting the values but for some reason it isn't making it into the coaster_objtyp. Not sure why? Anybody have an idea?

    thanks,

    jaspin

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Remove the WHEN OTHERS clause entirely: then Oracle will tell you which line really had the error (rather than just pointing you to the RAISE statement). Presumably it is one of these two SELECT INTOs:
    select attractionid into v_attridstore
    from attraction_objtab
    where attractionid = pattractid;

    select value(p) into v_budget
    from budget_objtab p
    where year = pyear;
    You could put some debug messages in to see what the values of pattractid and pyear are before the SELECT.

Posting Permissions

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