Below statement use a mixture of bind variable, constant and Oracle keyword for insertion. ABC REVIEW and N are actual values to be inserted.
l_sql :='INSERT INTO EMP
(BUSINESS_DATE,GROUP_NAME, DELETED,UPDATE_DATE) VALUES
(:BIZ_DATE,ABC REVIEW,N,SYSDATE)';
Is this the correct way for parameter reference using dynamic SQL? Doesn't it require additional parenthesis?
Just esacpe the quote sign around actual string values:
l_sql :='INSERT INTO EMP
(BUSINESS_DATE,GROUP_NAME, DELETED,UPDATE_DATE) VALUES
(:BIZ_DATE,''ABC REVIEW'',''N'',SYSDATE)';
ABC REVIEW and N is the actual value to be inserted.
If you don't include the literals in ' '(quotes) then Oracle will treat them as variable and expect values. In your case this is causing the error. What @user7294900 suggest is definitely the correct solution however it would need a high accuracy while putting the quotes which sometime lead to issues. Oracle had introduced q'[ to handle such situation where you just have to put your statement inside a q'[ and oracle will handle the quotes. See below:
l_sql :=q'[INSERT INTO EMP
(BUSINESS_DATE,GROUP_NAME, DELETED,UPDATE_DATE) VALUES
(:BIZ_DATE,'ABC REVIEW','N',SYSDATE)]';
read more about q' at http://www.oracle-developer.net/display.php?id=311
I realized it can use pipe to concatenate.
l_sql='INSERT INTO EMP
(BUSINESS_DATE,GROUP_NAME,DELETED,UPDATE_DATE) VALUES
(:BIZ_DATE,'|| 'ABC REVIEW'|| ','|| 'N'|| ',SYSDATE);