1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with compilation of oracle Function

Discussion in 'SQL PL/SQL' started by sb22, Aug 25, 2009.

  1. sb22

    sb22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you have a semicolon missing. Try this

    Code (SQL):
    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;
     
  3. sb22

    sb22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks,it worked.....but i am stupid enough not to close statement.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is no stupidity.. It happens sometimes :)