Sasa Sasa - 2 months ago 21
SQL Question

Dynamic add comment on table or view

I want to add comment on table or view with procedure using dynamic SQL.

I have tried this but unsuccessfully:

create or replace procedure add_comment(
p_table in varchar2,
p_comment in varchar2
)
as

BEGIN
EXECUTE IMMEDIATE 'comment on table "' || p_table || '" is

' || p_comment || ' end;'

;
END;

Answer

Your dynamic SQL should be edited to avoid the end and handle the quotes:

CREATE OR REPLACE PROCEDURE add_comment(p_table IN VARCHAR2, p_comment IN VARCHAR2) AS
BEGIN
    EXECUTE IMMEDIATE 'comment on table "' || p_table || '" is     
    ''' || p_comment || '''';
END;
Comments