PL\SQL program blocks

[hal]

I'm working on PL\SQL program block trying to retrieve each customer
from a customer table and create a list that displays each customers
name, address, and phone number.

I'm also having trouble working on a PL\SQL program block that
identifies each customer's name and the total amount owed by each
customer, in which I also am trying to display the total amount owed
by all customers as the final output line.

I'm new this PL\SQL stuff for my class and the professor isn't much
help either. Anyone that can help me out in anyway that would be
greatly appreciated.
Halston W

[Rob Wolfe]

So ... ummm .. if I get you right ... you want us to do your homework
for you?

Please tell me that isnt what you are asking here...

[Michael Moore]

Giving the crappy instructors that I have had, and the upfront honesty that this is a homework problem, I've got no problem with trying to give some help.

So, Hal, I don't know exactly what you mean my "a list". Are you talking about the way you want to format the output?

It would help do do a "DESCRIBE" on the tables you are working with and show us the result.

Mike

[Rob Wolfe]

Oh Mike you are such a softie :)

> > > Halston W- Hide quoted text -
>
> - Show quoted text -

[Michael Moore]

Also, show us what you have tried to do so far. This will help show that you really want help, and not simply somebody to do it for you. :-)

[Michael Moore]

Shhhhhhhhh, I have a reputation to maintain!

[hal]

I have a customer table that I'm dealing with in a sample database and
the problem wants me to build a PL\SQL program block that retrieves
each customer from the CUSTOMER table and creates a list that displays
each customer's name , address, and daytime phone number on a single
line.

This is what I have so far and I ran it, and came up with error, I
don't even know if I coded it right.

SQL> DECLARE
2 CURSOR customer_cursor IS
3 SELECT * FROM customer
4 customer_row customer_cursor%ROWTYPE;
5 BEGIN
6 OPEN customer_cursor;
7 LOOP
8 FETCH customer_cursor INTO customer_row;
9 EXIT WHEN customer_cursor%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE('Clearwater Traders Mailing List');
11 DBMS_OUTPUT.PUT_LINE(customer.c_first || customer.c_last ||
customer.dphone);
12 END LOOP;
13 CLOSE customer_cursor;
14 END;
15 /
customer_row customer_cursor%ROWTYPE;
*
ERROR at line 4:
ORA-06550: line 4, column 14:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored

And the output is suppose to be displayed on a single line
Customer Name 1 Addresss Day Phone
Customer Name 2 Addresss Day Phone
so on through customer name 6

On Jun 30, 1:50 pm, "Michael Moore" <michaeljmo...@gmail.com> wrote:

> Giving the crappy instructors that I have had, and the upfront honesty that
> this is a homework problem, I've got no problem with trying to give some
> help.
>
> So, Hal, I don't know exactly what you mean my "a list". Are you talking
> about the way you want to format the output?
>
> It would help do do a "DESCRIBE" on the tables you are working with and show
> us the result.
>
> Mike
>

[hal]

SQL> DECLARE
2 CURSOR order_cursor IS
3 SELECT o_id, item_desc
4 FROM order_line, inventory, item
5 WHERE order_line.inv_id = inventory.inv_id
6 AND inventory.item_id = item.item_id;
7 order_row order_cursor%ROWTYPE;
8 BEGIN
9 OPEN order_cursor
10 LOOP
11 FETCH order_cursor into order_row

This is the other problem that I've started but am not really sure
where to go from here, not sure if I'm even on the right path but for
this they want me to create a PL\SQL program that displays each order
and the items on that order, however this does involve me using an
orders, order_line, inventory, and item table.

On Jun 30, 1:50 pm, "Michael Moore" <michaeljmo...@gmail.com> wrote:

> Giving the crappy instructors that I have had, and the upfront honesty that
> this is a homework problem, I've got no problem with trying to give some
> help.
>
> So, Hal, I don't know exactly what you mean my "a list". Are you talking
> about the way you want to format the output?
>
> It would help do do a "DESCRIBE" on the tables you are working with and show
> us the result.
>
> Mike
>

[Michael Moore]

not at work so I can't test this, but try putting a semi colon after SELECT * FROM customer

[Michael Moore]

try something like

begin
for cur1 in SELECT o_id, item_desc
  FROM order_line, inventory, item
   WHERE order_line.inv_id = inventory.inv_id
   AND inventory.item_id = item.item_id
loop

dbms_output.put_line(cur1.o_id||' '||cur1.item||' '||cur1.item_desc);
end loop;
end;
/

you might need to tweek this to get what you actually want, but you don't need to do all of that extra stuff.
Mike

[Amol Mitkari]

Hi hal,

 

Please check this modified code of yours it should work..

 

 DECLARE
  CURSOR customer_cursor IS
   SELECT * FROM customer; <----------- you missed this ;

   customer_row customer_cursor%ROWTYPE;
  BEGIN
    OPEN customer_cursor;
    LOOP
    FETCH customer_cursor INTO customer_row;
  EXIT WHEN customer_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Clearwater Traders Mailing List');

   DBMS_OUTPUT.PUT_LINE(customer_row.c_first || customer_row.c_last || ----------------------> use the variable name.column name not the table name customer_row.dphone);
    END LOOP;
   CLOSE customer_cursor;
    END;
 

 

--
Thanks & Regards,
Amol Mitkari