Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: 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;

    /

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Hello

    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

  3. #3
    Join Date
    Jul 2003
    Posts
    10
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    Germantown, MD
    Posts
    5
    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).

  5. #5
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Hello

    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

  6. #6
    Join Date
    Jul 2003
    Posts
    10

    Re: Creating procedures in PL/SQL

    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;

    /

  7. #7
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    name declared as IN

    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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Creating procedures in PL/SQL

    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.

  9. #9
    Join Date
    Oct 2010
    Posts
    5

    Smile Pls-00403


    Thank you very much for your help.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •