Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
PL|SQL problem: PLS-00302:

660136
Member Posts: 27
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.
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; /
Best 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
-
can you past the error message with line number
-
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.
-
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_. -
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;
-
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 -
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 -
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;
-
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;
This discussion has been closed.