Creating procedures in PL/SQL

I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/Hello,

which errors do you get ?

Here are some suggestion ...

1) define your in or out vars for better reading
2) define a field name as a placeholder for the result in your select
3) What is print_line ? Do you mean put_line ?

PROCEDURE displayProd(prono IN PRODUCTS.PNO%TYPE, name OUT PRODUCTS.PNAME%TYPE)

IS
BEGIN
SELECT PRODUCTS.PNAME
INTO name
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.put_line('The name of the product is ' || name);

END displayProd;

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.|||Originally posted by izy
I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.

Does it have to be a procedure? I think a function would be more appropriate. You would pass in one argument (the product number) and return one argument (the associated product name).

However, if you need to write a procedure (i.e., a homework assignment) then the recommendations from the previous reply should work (i.e., including the INTO clause into your SQL statement).|||Hello,

of course the beginning is
CREATE OR REPLACE PROCEDURE ...

If you would like to see the errors use f.e. the AlligatorSQL at
http://www.alligatorsql.com/download/alligator.zip

or use the statement

SELECT e.*
FROM sys.error$ e,
sys.all_objects a
WHERE a.object_id = e.obj#
AND a.owner = 'SCOTT'
AND a.object_name = 'MYPROC'
ORDER BY e.sequence#

(Please change the owner SCOTT and the object_name MYPROC to your schema and procedure name)

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/

Originally posted by izy
I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/|||Hello,

the only thing I can see is, that you have declared the myname parameter as an IN and you shift the result into this var.

Please declare it as IN OUT ...

Again ...

If you have error use a GUI that displays you the errors or select the error table like I mentioned before.

Otherwise you are developing in the dark

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Originally posted by izy
Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/
In SQL Plus, just type SHOW ERROR after receiving the warning. In this case, you should see this:

SQL> show error
Errors for PROCEDURE ITEMSSHIPPED:

LINE/COL ERROR
--- --------------------
4/1 PL/SQL: SQL Statement ignored
5/6 PLS-00403: expression 'MYNAME' cannot be used as an INTO-target
of a SELECT/FETCH statement

This is bacause IN parameters are read-only. Change IN to OUT or IN OUT.