Forum Stats

  • 3,874,323 Users
  • 2,266,718 Discussions
  • 7,911,812 Comments

Discussions

PL|SQL problem: PLS-00302:

660136
660136 Member Posts: 27
edited Oct 6, 2008 5:28AM in SQL & PL/SQL
Hi,

I have trouble runing a piece of PL/SQL. The problem is that I keep on getting an initialisation error, however the variable does not need to be initialised.
set serveroutput on;
declare
      cursor cust is
                      select order_id, item_id from ph2 
                      order by order_date, customer_id, ship_date, order_id, item_id
                      for update of order_id, item_id;
                      
      neworder_id  ph2_item.order_id%type;
      multicust multi_site_cust.cust%type;
      newitem_id ph2_item.item_id%type;
      
      
begin
        /*Initialising neworder_id */
        select 
              max(order_id)+1
        into
              neworder_id
        from
              sales_order;     
        
        newitem_id := 1;
                  
         for c_rec in cust loop
         
         /*initilising multicust */
            select
                  customer
            into
                  multicust
            from
                  multi_ships_cust
            order by
                  customer;

                update 
                        ph2_item p 
                    set 
                        p.order_id =
                                  (
                                    case
                                          when 
                                                c_rec.customer_id = multicust
                                          then 
                                                neworder_id
                                          else
                                                neworder_id + 1
                                     end
                                  ),
                        p.item_id = 
                                 (
                                  case
                                          when 
                                                c_rec.customer_id = multicust
                                          then
                                                newitem_id + 1 
                                          --else
                                               -- newitem_id
                                    end
                                 )
               where current of cust;
          end loop;
end;
/
Tagged:

Best Answer

  • cdkumar
    cdkumar Member Posts: 131 Blue Ribbon
    edited Oct 6, 2008 5:26AM Answer ✓
    Wrong in the cursor defn. you have to select the customer_id from the cust cursor defn. check the below code

    set serveroutput on;
    declare
    cursor cust is
    select order_id, item_id,customer_id from ph2
    order by order_date, customer_id, ship_date, order_id, item_id
    for update of order_id, item_id;

    neworder_id ph2_item.order_id%type;
    multicust multi_site_cust.cust%type;
    newitem_id ph2_item.item_id%type;


    begin
    /*Initialising neworder_id */
    select
    max(order_id)+1
    into
    neworder_id
    from
    sales_order;

    newitem_id := 1;

    for c_rec in cust loop

    /*initilising multicust */
    select
    customer
    into
    multicust
    from
    multi_ships_cust
    order by
    customer;

    update
    ph2_item p
    set
    p.order_id =
    (
    case
    when
    c_rec.customer_id = multicust
    then
    neworder_id
    else
    neworder_id + 1
    end
    ),
    p.item_id =
    (
    case
    when
    c_rec.customer_id = multicust
    then
    newitem_id + 1
    --else
    -- newitem_id
    end
    )
    where current of cust;
    end loop;
    end;
    /

    Edited by: cdkumar on Oct 6, 2008 2:55 PM

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    can you past the error message with line number
  • BluShadow
    BluShadow Member, Moderator Posts: 42,555 Red Diamond
    Which variable is it referring to? What line is the error on. No good just saying you're getting an error and posting the code; show us the error message and the line it refers to.
  • Gurjas
    Gurjas Member Posts: 1,190
    set serveroutput on;
    declare
          cursor cust is
                          select order_id, item_id from ph2 
                          order by order_date, customer_id, ship_date, order_id, item_id
                          for update of order_id, item_id;
                          
    --      neworder_id  ph2_item.order_id%type; --- double declaration 
          multicust multi_site_cust.cust%type;
          newitem_id ph2_item.item_id%type;
          
          
    begin
            /*Initialising neworder_id */
            select 
                  max(order_id)+1
            into
                  neworder_id
            from
                  sales_order;     
            
            newitem_id := 1;
                      
             for c_rec in cust loop
             
             /*initilising multicust */
                select
                      customer
                into
                      multicust
                from
                      multi_ships_cust
                order by
                      customer;
     
                    update 
                            ph2_item p 
                        set 
                            p.order_id =
                                      (
                                        case
                                              when 
                                                    c_rec.customer_id = multicust
                                              then 
                                                    neworder_id
                                              else
                                                    neworder_id + 1
                                         end
                                      ),
                            p.item_id = 
                                     (
                                      case
                                              when 
                                                    c_rec.customer_id = multicust
                                              then
                                                    newitem_id + 1 
                                              --else
                                                   -- newitem_id
                                        end
                                     )
                   where current of cust;
              end loop;
    end;
    / 
    Good practise is to define all variable with prefix v_.
  • Gurjas
    Gurjas Member Posts: 1,190
    why the multicust in the loop and is there only one row in the multi_ships_cust table otherwise it will give error or you have to use cursor or where clause.
            select
                      customer
                into
                      multicust
                from
                      multi_ships_cust
                order by
                      customer; 
  • 660136
    660136 Member Posts: 27
    Hi,

    Sorry about here is the error message that I am recieving:

    ORA-06550: line 53, column 49:
    PL/SQL: ORA-00904: "C_REC"."CUSTOMER_ID": invalid identifier

    It's occurring on Line 53

    p.item_id =
    (
    case
    when
    c_rec.customer_id = multicust
  • cdkumar
    cdkumar Member Posts: 131 Blue Ribbon
    edited Oct 6, 2008 5:26AM Answer ✓
    Wrong in the cursor defn. you have to select the customer_id from the cust cursor defn. check the below code

    set serveroutput on;
    declare
    cursor cust is
    select order_id, item_id,customer_id from ph2
    order by order_date, customer_id, ship_date, order_id, item_id
    for update of order_id, item_id;

    neworder_id ph2_item.order_id%type;
    multicust multi_site_cust.cust%type;
    newitem_id ph2_item.item_id%type;


    begin
    /*Initialising neworder_id */
    select
    max(order_id)+1
    into
    neworder_id
    from
    sales_order;

    newitem_id := 1;

    for c_rec in cust loop

    /*initilising multicust */
    select
    customer
    into
    multicust
    from
    multi_ships_cust
    order by
    customer;

    update
    ph2_item p
    set
    p.order_id =
    (
    case
    when
    c_rec.customer_id = multicust
    then
    neworder_id
    else
    neworder_id + 1
    end
    ),
    p.item_id =
    (
    case
    when
    c_rec.customer_id = multicust
    then
    newitem_id + 1
    --else
    -- newitem_id
    end
    )
    where current of cust;
    end loop;
    end;
    /

    Edited by: cdkumar on Oct 6, 2008 2:55 PM
  • Gurjas
    Gurjas Member Posts: 1,190
    change the cursor definition
        cursor cust is
                          select order_id, item_id, customer_id from ph2 
                          order by order_date, customer_id, ship_date, order_id, item_id
                          for update of order_id, item_id; 
    Gurjas
  • 663551
    663551 Member Posts: 1
    Your Cursor Declaration does not have customer_id You need to include it in the cursor definition

    cursor cust is
    select order_id, item_id,*customer_id* from ph2
    order by order_date, customer_id, ship_date, order_id, item_id
    for update of order_id, item_id;
    663551
This discussion has been closed.