Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Problem with compiling function

    Hi I have this function to join multiple rows with same id and this must be used in a view....

    code;

    create or replace
    Function fun_comma
    ( project_id IN varchar2 )
    RETURN varchar2
    IS
    total_val varchar2(999);


    cursor c1 is
    SELECT VALUE_1
    FROM PROJECTS P_EXTPROJ3301
    Left Outer Join PROJECT_EXT_ATTR_MULTI PEAM_EXTPROJ3301 ON
    P_EXTPROJ3301.PROJECT_ID = PEAM_EXTPROJ3301.PROJECT_ID
    Left Outer Join ENCODED_LIST_ITEMS ELI3301 ON
    PEAM_EXTPROJ3301.ENCODED_LIST_ITEM_ID = ELI3301.ENCODED_LIST_ITEM_ID
    Left Outer Join ENCODED_TITLES ET3301 ON
    ELI3301.Encoded_Title_ID = ET3301.Encoded_Title_ID
    WHERE (P_EXTPROJ3301.PROJECT_ID = project_id)
    BEGIN

    total_val:= '';



    FOR employee_rec in c1
    LOOP
    total_val := total_val ||', '|| employee_rec.value_1;
    END LOOP;


    RETURN total_val;


    END;


    the following errors are hunting me..


    Error(10,6): PL/SQL: SQL Statement ignored
    Error(18,59): PL/SQL: ORA-00933: SQL command not properly ended
    Error(25,1): PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
    begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form
    current cursor The symbol "begin" was substituted for "FOR" to continue.


    any one can help me in this?

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    All declarations need to be terminated with a ';'. Your cursor is not.

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    I did not get u....can u tell me more ..please

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE FUNCTION Fun_comma 
         (project_id  IN VARCHAR2) 
    RETURN VARCHAR2 
    IS 
      total_val  VARCHAR2(999); 
      CURSOR c1 IS 
        SELECT value_1 
        FROM   projects p_extproj3301 
               LEFT OUTER JOIN project_ext_attr_multi peam_extproj3301 
                 ON p_extproj3301.project_id = peam_extproj3301.project_id 
               LEFT OUTER JOIN encoded_list_items eli3301 
                 ON peam_extproj3301.encoded_list_item_id = eli3301.encoded_list_item_id 
               LEFT OUTER JOIN encoded_titles et3301 
                 ON eli3301.encoded_title_id = et3301.encoded_title_id 
        WHERE  (p_extproj3301.project_id = project_id); 
    BEGIN 
      total_val := ''; 
       
      FOR employee_rec IN c1 LOOP 
        total_val := total_val 
                     ||', ' 
                     ||employee_rec.value_1; 
      END LOOP; 
       
      RETURN total_val; 
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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