PL|SQL problem: PLS-00302:

660136 · Oct 6, 2008 5:01AM

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;
/

cdkumar · Oct 6, 2008 5:24AM

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

Karthick2003 · Oct 6, 2008 5:03AM

can you past the error message with line number

BluShadow · Oct 6, 2008 5:03AM

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 · Oct 6, 2008 5:11AM

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 · Oct 6, 2008 5:15AM

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 · Oct 6, 2008 5:17AM

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 · Oct 6, 2008 5:24AM

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 · Oct 6, 2008 5:27AM

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;

663551 · Oct 6, 2008 5:28AM

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;

Forum Stats

Quick Links

Categories

PL|SQL problem: PLS-00302:

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

PL|SQL problem: PLS-00302:

Best Answer

Answers