Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Unanswered: Procedure error driving me crazy :(

    Hi all. Heres my procedure:

    create or replace procedure bin_count (gbinname char)
    DECLARE
    Total_Number number;
    begin
    select count(i.name) into Total_Number from item i join warehouse w on i.warehouse_ref=w.warehouse_ref where w.binname = gbinname;
    end;

    but it keeps throwing the following compilation errors:

    SQL> show err;
    Errors for PROCEDURE BIN_COUNT:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
    the following:
    ; is with authid as cluster order using external
    deterministic parallel_enable pipelined

    5/52 PLS-00103: Encountered the symbol "JOIN" when expecting one of
    the following:
    , ; for group having intersect minus order start union where
    connect

    Thanks in advance for any help you may be able to give

    Greg

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Change DECLARE to IS or AS:
    Code:
    create or replace procedure bin_count (gbinname char)
    is
    Total_Number number;
    begin
    select count(i.name) into Total_Number from item i join warehouse w on i.warehouse_ref=w.warehouse_ref where w.binname = gbinname;
    end;
    If JOIN syntax is not understood then I presume you are on Oracle 8i? You need to either upgrade to something less ancient, or change the SQL to avoid using ANSI JOIN.

  3. #3
    Join Date
    Oct 2005
    Posts
    18
    Tony you are a legend thanks ! Could you please tell me how I can display the Total_Number when calling the procedure as when I run the sql:

    CALL bin_count('LIGHTING');

    I just get a reurn of 'call completed'

    Thanks !

    Greg

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could turn it into a function returning the number and then call it like this:

    SQL> select bin_count('LIGHTING') from dual;

    Or, you could use DBMS_OUTPUT.PUT_LINE and SET SERVEROUT ON - but that is only reallyof use if you run from inside SQL Plus. An application program calling the procedure would not see this output.

Posting Permissions

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