locked
Stored Procedure error RRS feed

  • Question

  • User-1507566129 posted

    Iam trying to create stored procedure using oracle sql developer

    this is my code:

    create or replace PROCEDURE SP_CATEGORY
    ( Cat IN VARCHAR2
    ) AS
    BEGIN
      SELECT Latin_Name, English_Name, Arabic_Name, Category from Flora, Category_List WHERE Flora.Category=Category_List.Category_Name and Category_List.Category_Name in (Select * from SplitList(Cat, ','));
    END SP_CATEGORY;

     

    and this is the error I got:

    An error occured while executing the query:

    ORA-06550: Line 1 column 7

    Pl\SQL: Statement ignored

     

    So what is the problem?

    Wednesday, December 1, 2010 1:52 AM

Answers

  • User1224194097 posted

    Try specifying size for Category Input paramter and remove SP_CATEGORY after END;

    CREATE OR REPLACE PROCEDURE Sp_category 
    ( cat IN VARCHAR2(4000)
    ) AS 
    BEGIN 
      SELECT latin_name, english_name, arabic_name, category 
          FROM flora, category_list 
         WHERE flora.category=category_list.category_name 
                 AND category_list.category_name IN (SELECT * FROM splitlist(cat, ',')); 
    END;  
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 1, 2010 8:21 AM

All replies

  • User1224194097 posted

    Try specifying size for Category Input paramter and remove SP_CATEGORY after END;

    CREATE OR REPLACE PROCEDURE Sp_category 
    ( cat IN VARCHAR2(4000)
    ) AS 
    BEGIN 
      SELECT latin_name, english_name, arabic_name, category 
          FROM flora, category_list 
         WHERE flora.category=category_list.category_name 
                 AND category_list.category_name IN (SELECT * FROM splitlist(cat, ',')); 
    END;  
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 1, 2010 8:21 AM
  • User364663285 posted

    you need to use ref_cursor for returning the records from the SP. Here are some examples

    http://psoug.org/reference/ref_cursors.html 

    Thursday, December 2, 2010 12:22 AM