When i invoke below procedure i get 'string literal required' exception, can any one help please, i'm sure that i'm writing (Execute Immediate) in wrong form :
PROCEDURE ADD_TAB_COL_COMMENT (
P_TAB_NAME IN VARCHAR,
P_COL_NAME IN VARCHAR,
P_COMMENT IN VARCHAR,
P_LEVEL IN NUMBER
)
IS
BEGIN
IF(P_LEVEL = 1)THEN
EXECUTE IMMEDIATE 'COMMENT ON TABLE ' || P_TAB_NAME || ' IS ' || P_COMMENT;
ELSIF(P_LEVEL = 2)THEN
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || P_TAB_NAME ||'.'|| P_COL_NAME || ' IS ' || P_COMMENT;
END IF;
END ADD_TAB_COL_COMMENT ;
You are embedding the unquoted value from P_COMMENT
into your command. If you used dbms_output
to display the command being executed, for a call like
exec add_tab_col_comment ('T42', 'ID', 'Test', 2);
you would see it trying to do:
COMMENT ON COLUMN T42.ID IS Test
The actual comment Test
has to be enclosed in single quotes for that command. In your dynamic SQL you need to add those quotes, and also escape them:
PROCEDURE ADD_TAB_COL_COMMENT (
P_TAB_NAME IN VARCHAR,
P_COL_NAME IN VARCHAR,
P_COMMENT IN VARCHAR,
P_LEVEL IN NUMBER
)
IS
BEGIN
IF(P_LEVEL = 1)THEN
EXECUTE IMMEDIATE 'COMMENT ON TABLE ' || P_TAB_NAME
|| ' IS ''' || P_COMMENT || '''';
ELSIF(P_LEVEL = 2)THEN
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || P_TAB_NAME ||'.'|| P_COL_NAME
|| ' IS ''' || P_COMMENT|| '''';
END IF;
END ADD_TAB_COL_COMMENT ;
The same call would then generate the statement:
COMMENT ON COLUMN T42.ID IS 'Test'
which is now valid.
Whenever you get an error with dynamic SQL, the simplest way to tell what's going on is to use dbms_output
to display the actual command being executed, and if it isn't then obvious what the problem is, try running that displayed command manually. This approach will usually quickly reveal missing or mismatched quotes, missing whitespace, etc.