Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: Commit Invoice So It Cannot Be Deleted

    hi guys , this is my first post hope someone helps, i am writing a databse for a vehicle servicing company and it has a sales order invvoicing system. basically i need to write a function to commit an invoice so it cannot be deleted. i wrote it this way but it does not really work, any help will be really appreciated, its a practice exercise .

    i wrote a function like this

    CREATE OR REPLACE FUNCTION COMMITINVOICE(INVOICENUMBER CHAR)

    RETURN CHAR AS

    INNUMBER CHAR(5);

    CURSOR INV1 IS
    SELECT INVOICE_ID
    FROM INVOICE
    WHERE INVOICE_ID = INVOICENUMBER;


    BEGIN
    OPEN INV1;
    FETCH INV1 INTO INNUMBER;
    CLOSE INV1;
    DBMS_OUTPUT.PUT_LINE('INVOICE NUMBER '||INNUMBER || 'HAS BEEN SAVED NO MORE CHANGES CAN BE MADE');


    RETURN INNUMBER;

    COMMIT;

    END;
    /

    i cant seem to run it , lets say if i have a invoice number 20 what do i do?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Nice homework assignment.
    You should be using VARCHAR2; not CHAR.
    Please research & explain why this is the case.

    If INVOICENUMBER really is a NUMBER, then CHAR and/or VARCHAR2 is wrong data type.

    COMMIT is only meaningful after DML (INSERT, UPDATE, or DELETE)
    & is useless & nonsensical after a SELECT.

    The (ab)use of explicit cursor is overkill when the following is sufficient.
    SELECT INVOICE_ID INTO INNUMBER FROM INVOICE WHERE INVOICE_ID = INVOICENUMBER;

    >i cant seem to run it
    In the future please be more vague & nondescript, to make answering even more of a challenge.

    While using sqlplus, use CUT & PASTE to post whole session back here
    so we can see what you are actually doing & exactly how Oracle responds.

    Even after COMMIT, the record can still be deleted.

    If/when this application gets deployed into Production,
    no end user will ever see anything from DBMS_OUTPUT.

    How will you (or anyone) know when this problem has been successfully resolved?
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Moreover, no statement you have placed behind the RETURN statement will ever be executed:
    Code:
    SQL> create or replace function fun_test
      2  return number
      3  is
      4  begin
      5    dbms_output.put_line('Step 1');
      6    return (1000);
      7    dbms_output.put_line('Step 2');
      8  end;
      9  /
    
    Function created.
    
    SQL> select fun_test from dual;
    
      FUN_TEST
    ----------
          1000
    
    Step 1
    SQL>
    See? No "Step 2" output message.

    If you want to disable removing records from a table if certain conditions are met, perhaps you should use a database TRIGGER instead (not a function) along with the RAISE_APPLICATION_ERROR (instead of the DBMS_OUTPUT package), such as this example:
    Code:
    SQL> create table test (id number, car varchar2(20));
    
    Table created.
    
    SQL> insert into test (id, car) values (1, 'Mercedes');
    
    1 row created.
    
    SQL> create or replace trigger trg_bd_test
      2    before delete on test
      3    for each row
      4  begin
      5    raise_application_error(-20001, 'You can not delete records!');
      6  end;
      7  /
    
    Trigger created.
    
    SQL> delete from test where id = 1;
    delete from test where id = 1
                *
    ERROR at line 1:
    ORA-20001: You can not delete records!
    ORA-06512: at "SCOTT.TRG_BD_TEST", line 2
    ORA-04088: error during execution of trigger 'SCOTT.TRG_BD_TEST'
    
    
    SQL>
    If anything of the above (both Anacedent's and my post) makes sense to your problem, try to write a new piece of code. Show us what you did, and - if necessary - ask for additional help.

  4. #4
    Join Date
    Feb 2008
    Posts
    4
    thanks a lot for your reply, i'll write a code with a trigger and get back to you in a few minutes

Posting Permissions

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