Home » SQL & PL/SQL » SQL & PL/SQL » Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book (WinXP)
Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296461] Sun, 27 January 2008 12:46 Go to next message
tims1chap
Messages: 3
Registered: January 2008
Junior Member
I am getting this error when running the code listed below it from the book. I am driving myself crazy trying to figure out what is missing. I have tried running it in sqlplus and sql developer same error. I first run "set serveroutput on" then I run the code below. It is straight from the book Oracle 10g PL/SQL 101. I am very new to this.

Thanks

Here is the ERROR:
sql failed, ORA-06550: line 46, column 7:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 44, column 7:
PL/SQL: SQL Statement ignored

Here is the code:

declare
type perfomance_type is record
(person_code plsql_person.person_code%type,
person_name plsql_person.last_name%type,
current_sales number (8, 2),
perform_percent number (8, 1),
status varchar2 (30)
);

one_perform performance_type;

cursor person_cur is
select *
from plsql_person;

/* This procedure computes the perfomance and current total sales
by one salesperson. The information for the salesperson is
passed in as a record named a_person. If there are no sales for
the day by the person then current_sales is set to zero. If the
person has no history, for example, the person just joined today,
then the perform_percent is set to zero.
*/
procedure current_performance
(a_person plsql_person%rowtype,
a_perform out performance_type)
is
cursor history_cur (person varchar2) is
select avg (tab2.product_price * tab1.quantity) avg_order
from plsql_purchase_archive tab1,
plsql_product tab2
where tab1.product_name = tab2.product_name
group by tab1.SALESPERSON
having tab1.SALESPERSON = person;

hist_rec history_cur%rowtype;
current_avg_sales number (8, 2) := 0;

begin
a_perform.person_code := a_person.person_code;
a_perform.person_name := a_person.last_name;
a_perform.status := null;

begin
select sum(tbl2.product_price * tbl1.quantity),
avg(tbl2.product_price * tbl1.quantity),
into a_perform.current_sales,
current_avg_sales
from plsql_purchase tbl1,
plsql_product tbl2
where tbl1.product_name = tbl2.product_name
group by tbl1.salesperson
having tbl1.salesperson = a_person.person_code;
exception
when no_data_found
then
a_perform.status := 'Current purchases exception';
a_perform.current_sales := 0;
end;

open history_cur (a_person.person_code);
fetch history_cur into hist_rec;
if (history_cur%notfound)
then
a_perform.perform_percent := 0;
if (a_perform.status is null)
then
a_perform.status := 'Erroneous or no history';
end if;

else
a_perform.perform_percent :=
100 * (current_avg_slaes - hist_rec.avg_order)/
hist_rec.avg_order;
a_perform.status := 'All Fine';
end if;
close history_cur;
exception
when no_data_found
then
a_perform.status := 'Exceptions found';
end current_performance;
Begin
for person_rec in person_cur
loop
current_performance (person_rec, one_perform);

dbms_output.put_line (one_perform.person_code ||
' ' ||
one_perform.person_name ||
' ' ||
one_perform.current_sales ||
' ' ||
one_perform.perform_percent ||
' ' ||
one_status);
end loop;
end;
/
Re: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296463 is a reply to message #296461] Sun, 27 January 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ We don't have your tables
2/ We don't have the line numbers
3/ Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use SQL*Plus and post the execution with line numbers.
Use the "Preview Message" button to verify.
4/ Always post your Oracle version (4 decimals).

Regards
Michel
Re: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296465 is a reply to message #296461] Sun, 27 January 2008 14:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
begin
  select sum(tbl2.product_price * tbl1.quantity),
         avg(tbl2.product_price * tbl1.quantity),       --> what is this comma doing here?
  into a_perform.current_sales,
       current_avg_sales
  from plsql_purchase tbl1,
       plsql_product tbl2
  where ... 
Re: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296472 is a reply to message #296465] Sun, 27 January 2008 18:06 Go to previous messageGo to next message
tims1chap
Messages: 3
Registered: January 2008
Junior Member
thank you do you see any other items that may not work, I found the one you mentioned but then it gave me the following error:
sql failed, ORA-06550: line 10, column 13:
PLS-00201: identifier 'PERFORMANCE_TYPE' must be declared
ORA-06550: line 10, column 13:
PL/SQL: Item ignored
ORA-06550: line 25, column 17:
PLS-00201: identifier 'PERFORMANCE_TYPE' must be declared
ORA-06550: line 23, column 1:
PL/SQL: Item ignored
ORA-06550: line 85, column 30:
PLS-00201: identifier 'PERSON_REC' must be declared
ORA-06550: line 85, column 9:
PL/SQL: Statement ignored
ORA-06550: line 87, column 32:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 87, column 9:
PL/SQL: Statement ignored

Like I said I am very new to this and this is code straight from the book and I may have missed something, now I know why code review is a good thing.
Re: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296473 is a reply to message #296465] Sun, 27 January 2008 18:14 Go to previous messageGo to next message
tims1chap
Messages: 3
Registered: January 2008
Junior Member
You put me on the right path, all is good now.
Re: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book [message #296503 is a reply to message #296461] Mon, 28 January 2008 00:32 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


type perfomance_type is record
(person_code plsql_person.person_code%type,
person_name plsql_person.last_name%type,
current_sales number (8, 2),
perform_percent number (8, 1),
status varchar2 (30)
);

one_perform performance_type;


Thumbs Up
Rajuvan.
Previous Topic: ORA-01427 single row subquery returns more than one row
Next Topic: Partitioning on table
Goto Forum:
  


Current Time: Fri Dec 09 07:40:20 CST 2016

Total time taken to generate the page: 0.13092 seconds