Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Question Unanswered: syntax error in function

    Hi Friends,

    I am new to oracle and I was required to convert my T-SQL function which created by me into Oracle function.

    I have tried many samples and still fail to run this function on my TOAD 9.0:

    Please any kind heart there guide me through:

    Following is my Function which I have attempted in oracle:
    CREATE OR REPLACE FUNCTION BASS_FNGETAccessList
    ( var_Id VARCHAR2(15),var_uType CHAR(1))

    RETURN VARCHAR2(1000)

    IS

    var_ItemList VARCHAR2(1000);
    var_acsFlag INT;

    BEGIN




    IF var_uType = 'R' THEN
    SELECT ACCESSFLAG INTO var_acsFlag from BASSCOMMON.BASS_SEC_ROLE_GRANT where ROLEID = var_Id and ACCESSRIGHTID = 'VESSEL' aND ELEMENTID = 'MY_VESSELS';
    ELSIF var_uType = 'U' THEN
    SELECT ACCESSFLAG INTO var_acsFlag from BASSCOMMON.BASS_SEC_USER_GRANT where USERID = var_Id and ACCESSRIGHTID = 'VESSEL' aND ELEMENTID = 'MY_VESSELS';
    END IF;


    IF var_acsFlag = 2 THEN
    var_ItemList := 'ALL';

    ELSIF var_uType = 'R' THEN

    SELECT RTRIM(XMLAGG(XMLELEMENT(e,ITEM || ',')).EXTRACT('//text()'),',') INTO var_ItemList from BASSCOMMON.BASS_SEC_ROLE_GRANT_ITEM
    WHERE ROLEID = var_Id and REC_DELETED = 0;

    ELSIF var_uType = 'U' THEN

    SELECT RTRIM(XMLAGG(XMLELEMENT(e,ITEM || ',')).EXTRACT('//text()'),',') INTO var_ItemList
    FROM(
    SELECT ITEM from BASSCOMMON.BASS_SEC_USER_GRANT_ITEM
    where USERID = var_Id and REC_DELETED = 0
    UNION
    SELECT ITEM FROM BASS_SEC_ROLE_GRANT_ITEM
    where ROLEID = var_Id and REC_DELETED = 0
    )tmp;

    END IF;


    return var_ItemList;

    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What syntax error?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It has already been solved.

  4. #4
    Join Date
    Apr 2011
    Posts
    2
    Quote Originally Posted by andrewst View Post
    What syntax error?

    Hi,

    I got solved this problem. I realize that Oracle does not allow sizes for input parameters or return types, so the function should begin:

    CREATE OR REPLACE FUNCTION BASS_FNGETAccessList
    (var_Id VARCHAR2,
    var_uType CHAR)
    RETURN VARCHAR2
    IS
    ...


    Thanks for your help

Posting Permissions

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