Search code examples
oracle-databasestored-proceduresplsqlpls-00103

SQLPLUS How can I fix this procedure to make it work correctly?


Alright. I copied this code from class, but I must have been half asleep at the time because it doesn't actually work.

Create or replace procedure display_row is
    (p_itemid IN item.itemid%TYPE) is
    v_itemid Number;
    v_itemdesc varchar2(30);
    v_category varchar2(30);
Begin
    Select *
    Into v_itemid, v_itemdesc, v_category
    From item
    Where itemid = p_itemid;
    Dbms_output.put_line(v_itemid || ' ' || v_itemdesc || ' ' || v_category);
End;

How can I fix this procedure so that it actually works?

Here's the user errors:

LINE   POSITION TEXT

     2          6 PLS-00103: Encountered the symbol "(" when expecting one of
                  the following:

                     begin function package pragma procedure subtype type use
                     <an identifier> <a double-quoted delimited-identifier> fo
                  rm
                     current cursor external language

     2         37 PLS-00103: Encountered the symbol "IS" when expecting one of
                   the following:

                     return

Solution

  • Remove the is operator from first sentence

    Create or replace procedure display_row is 
                                            <--Here    
    

    Your procedure should look like

    Create or replace procedure display_row
        (p_itemid IN item.itemid%TYPE) is
        v_itemid Number;
        v_itemdesc varchar2(30);
        v_idemdesc varchar2(30);
    Begin
        Select *
        Into v_itemid, v_itemdesc, v_category
        From item
        Where itemid = p_itemid;
        Dbms_output.put_line(v_itemid || ' ' || v_itemdesc || ' ' || v_category);
    End;