Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: using PL/SQL script to update order-lines

    I have the following requirement:

    Withing an ordertable I want to renumber the orderlines according to the alphabetical order of the productcodes in the order-lines.
    The problem is that the orderline itself is part of the primary key itself, so changing it will automatically cause a constraint conflict.

    Is this possible using SQL or PL/SQL

    I suspect that I need to create a temporary set for a given order , do the renumbering and then update the original table with the temporary table?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    beware of any foreign keys pointing to the primary key values you want to change. It is not common to change the values of the primary key, since you're playing with your data integrity !
    If you really have to, check out any foreign keys pointing to the primary key.
    Changing the values is best to be done using a temp table, disabling the primary key constraint and changing the values and enabling the primary key.

    Better is to create a different primary key, so u can update the columns needed without riscing your data integrity
    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    There is no candidate for another primary key and we can't change it anyway. There is no foreign key issue and we are not going to create new primary keys through new linenumber, just renumbering them in another order like:

    order F 1
    order Y 2
    order H 3
    order R 4
    order A 5


    will become:

    order A 1
    order F 2
    order H 3
    order R 4
    order Y 5

    I cannot disable the primary key constraint for the original table of course, but how to disable it for the temp table and then renumbering the linenumbers? Can this be done in a PL/SQL - SQL script?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    When using a relational database, the order in which the rows are inserted in the table should not be an issue (the is one of the specs of a relational database), so i don't see any need to change the order.If u want the correct aplhabetic order shown in a report or a query, just use the ORDER BY clause in your queries.
    Selecting without a order by and with the rows in the correct alphabethic order in the database wll never guarentee the rows are shown in correct alphabethic order using a query, although it may appear so in most cases.

    i think the best way would be to copy the rows to a temp table, resort them there or resort them before inserting and then delete the source rows from the source table and copy the sorted rows back to the source table and commit after the transaction completed.
    But be sure to read the first paragraph carefully before doing so.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by blom0344
    There is no candidate for another primary key and we can't change it anyway. There is no foreign key issue and we are not going to create new primary keys through new linenumber, just renumbering them in another order like:

    order F 1
    order Y 2
    order H 3
    order R 4
    order A 5


    will become:

    order A 1
    order F 2
    order H 3
    order R 4
    order Y 5

    I cannot disable the primary key constraint for the original table of course, but how to disable it for the temp table and then renumbering the linenumbers? Can this be done in a PL/SQL - SQL script?
    You should rebuild your primary key as a DEFERRABLE
    constraint, ie a constraint that is checked at commit
    time and not at statement execution time.
    The you set the constraints as deferred at session time...

    I've created a table 'o' similar to yours, populated it,
    and then created the pk as

    alter table o
    add constraint o_pk
    primary key (orderid, linen)
    DEFERRABLE INITIALLY IMMEDIATE;

    SQL> desc o
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ORDERID NUMBER(38)
    CODE VARCHAR2(1)
    LINEN NUMBER(38)

    SQL> select * from o order by code;

    ORDERID COD LINEN
    ---------- --- ----------
    1000 A 5
    1000 F 1
    1000 H 3
    1000 R 4
    1000 Y 2

    SQL> alter session set constraints = deferred;

    Session altered.

    SQL> declare
    2 type refcursor is ref cursor;
    3 c refcursor;
    4 row_id rowid;
    5 new_linen o.linen%type;
    6 begin
    7 open c for 'select rowid row_id,
    8 row_number() over (partition by orderid order by code) new_linen
    9 from o';
    10 loop
    11 fetch c into row_id, new_linen;
    12 exit when c%notfound;
    13 update o set linen = new_linen
    14 where rowid = row_id;
    15 end loop;
    16 close c;
    17 end;
    18 /

    PL/SQL procedure successfully completed.

    SQL> commit;

    Commit complete.

    SQL> select * from o order by code;

    ORDERID COD LINEN
    ---------- --- ----------
    1000 A 1
    1000 F 2
    1000 H 3
    1000 R 4
    1000 Y 5

    Anyway I would say that altering the pk values is a bad idea in general, better left alone.

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Edwin,

    I am quite aware about the things you mention in your first paragraph, but this issue has nothing to do with queries. I want the orderlines changed according to the products, because the picking process starts with orderline 1 and then proceeds with 2,3,4 etc.

    By changing the orderlinenumber in accordnance with the products we can experiment with picking products based on alfabetical order. The reason why would take too long to explain.

    The second paragraph sounds okay to me, the catch is how to do this in one run , either using PL/SQL / SQL or any other method. Manually I can do all this stuff, but I need a procedure to be run by administrative personel, not by myself.

    Alberto,

    Changing the way the constraint is activated is no option, cause we are not at liberty due to support-issue with the vendor of the application.

    It is however an interesting idea. I thought of a script with:

    1. Creating a temp table (no prim key)
    2. Inserting the prim key fields for a certain order
    3. Resorting and changing the orderline accordingly
    4. Adding 1000 to the order lines
    5. Updating the data-table with the temp table data
    6. Subtracting 1000 from the orderlines

    Adding the dummy 1000 to the orderline should avoid triggering the constraint.

    Can this be done within one PL/SQL script?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  7. #7
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by blom0344

    Alberto,

    Changing the way the constraint is activated is no option, cause we are not at liberty due to support-issue with the vendor of the application.

    It is however an interesting idea. I thought of a script with:

    1. Creating a temp table (no prim key)
    2. Inserting the prim key fields for a certain order
    3. Resorting and changing the orderline accordingly
    4. Adding 1000 to the order lines
    5. Updating the data-table with the temp table data
    6. Subtracting 1000 from the orderlines

    Adding the dummy 1000 to the orderline should avoid triggering the constraint.

    Can this be done within one PL/SQL script?
    What about issuing

    update o set linen = linen + 1000;

    and then running the pl/sql block I provided above, without changing the constraint type of course ?

    That should do the trick, without using a temp table that are almost always unnecessary in Oracle, and are of course "slow".

    Be careful that there must be no activity on the table while you make the reorganization, otherwise you may observe funny results. You might lock the table if appropriate.

    Side note: the approach I proposed put the pk in DEFERRABLE (note that -ABLE) and INITIALLY IMMEDIATE, meaning that if you don't issue the alter session above, the constraint behaves as a regular one - the only other visibile difference being that the index supporting the pk is not-unique, which is always a good idea IMHO. That should cause no support issues - but i know the paranoia of third-party application providers ;-)

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    your idea is quite suitable, but in that case u don't have to use a temp table. Just write a PL/SQL block with a cursor selecting the rows in the aphabethic order and use that cursor in a cursor ..for loop.
    Because of the consistent view on the database you will have no problem with users adding lines.
    But u have to make sure that u only change the lines for orders that are completed i think. Otherwise u have to change the orders multiple times. Another thing is the orderline number. If an order consists of more then 1000 lines, u have a problem again. You can avoid this by selecting the max(linen) for that order an start changing the linen at tthatnumber+1.
    The code could be like this :

    create or replace procedure change_line_number is
    cursor c_orders is
    select order_id
    , max(linen) start_num
    from order_table
    group by order_id;

    cursor c_order_lines(bn_order_id order_table.order_id%type) is
    select linen, code
    from order_table
    where order_id = bn_order_id
    ordr by code asc;

    ln_conter number := 0;

    begin
    for r_orders in c_orders
    loop
    ln_counter := r_order.start_num+1;
    for r_order_lines in c_order_lines
    loop
    update order_table
    set linen = ln_counter
    where order_id = r_order.order_id
    and code = r_order_lines.code
    and linen= r_order_lines.linen;
    ln_counter := ln_+counter+1;
    end loop;
    ln_counter := 0;
    end loop;
    exception
    ...
    end;

    Again, be sure u have a way to only change the orders that are complete.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  9. #9
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    another thought :
    Make sure u can identify the orders that are not yet re-ordered. Otherwise everytime time u run the procedure, all orders are re-ordered.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  10. #10
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Thanks Edwin,

    For supplying the code for the PL/SQL block. Actually I am looking for a solution for a specific order. Most orders with small sets of lines can be handled with the current way the process is running.
    It is for specific orders from 1 customer that involve huge amounts of lines that we want to renumber the lines.
    So , if we can deal with this in a procedure it should have a parameter that restricts the actions to one ordernumber at a time.

    Also, after re-ordering orderlines should be 'reset' to a set that starts with 1 instead of start_num to keep things tidy I guess, though having an order that has otherwise may not be a problem
    (no ideaif that would give problems within the application)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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