-1
Create or Replace FUNCTION Hello_Fun ( P_SCHEMA IN varchar2, P_OBJECT IN 

varchar2) Return varchar2 IS
V_Hello Varchar2(400);
BEGIN
If User = 'Admin' then
V_Hello := '';
Else
V_Hello := 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT('Hello_Context','COMP_ID'),0)');
END IF;
RETURN  V_Hello;
END;
/

So the V_Hello:='cus_id..... is having compilation errors. Does it need quote delimiters or what is the issue:

LINE/COL  ERROR  
9/102  PLS-00103: Encountered the symbol "Hello_Context" 
when expecting one of the following: 
* & = - + ; < / > at in is mod remainder 
not rem <an exponent (**)> <> or != or ~= 
>= <= <> and or lik e LIKE2_ LIKE4_ LIKEC_ between 
|| multiset member SUBMULTISET_    
4

You have just ended the single quote near

 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT('
so that the compiler is expecting the ; to end the statement but it has found Hello_Context','COMP_ID'),0)');.

If you want to use single quote inside another single quote you can use q operator as demonstrated below. For details:Literals

SQL> Create or Replace FUNCTION Hello_Fun ( P_SCHEMA IN varchar2, P_OBJECT IN 
varchar2) Return varchar2 IS
V_Hello Varchar2(400);
BEGIN
If User = 'Admin' then
V_Hello := '';
Else
V_Hello := 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT(' || q'['Hello_Context']' || ',' || q'['COMP_ID']' || '),0))';
END IF;
RETURN  V_Hello;
END;
/  2    3    4    5    6    7    8    9   10   11   12  

Function created.

SQL> select hello_fun('ADMIN','CUSTOMER') from dual;

HELLO_FUN('ADMIN','CUSTOMER')
--------------------------------------------------------------------------------
cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT('Hello_Con
text','COMP_ID'),0))
1
  • 1
    or just the q'[ ] round the whole expression, i.e. q'[cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT('Hello_Context','COMP_ID'),0))]' - looks even cleaner. – BriteSponge Apr 13 '16 at 13:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.