Re: altering table and column comments from PL/SQL

Good point! Sorry:

It's a trigger on a table I'm using to store comments for reporting. When this table is updated, i want the comments on the table/view/column to be updated...

TRIGGER DOCS_COMMENTS
 AFTER INSERT OR UPDATE ON ELLIPSIS_DOCS FOR EACH ROW
DECLARE
szSQL VARCHAR2(1000);
BEGIN    IF :new.COMMENTS IS NOT NULL THEN

          IF :new.ENTITY_TYPE IN ('TABLE', 'VIEW') THEN

	  	 szSQL := 'COMMENT ON TABLE '
		 	   ||:new.OWNER||'.'||:new.ENTITY_NAME||' IS '
			   ||''''||:new.COMMENTS||''';';

	  ELSIF :new.ENTITY_TYPE IN ('COLUMN') THEN

	  	 szSQL := 'COMMENT ON COLUMN '||
                  :new.OWNER||
                  '.'||
                  :new.TABLE_NAME||
                  '.'||:new.ENTITY_NAME||' IS '
			   ||''''||:new.COMMENTS||''';';

	  END IF;

	  IF szSQL IS NOT NULL THEN

	  	 EXECUTE IMMEDIATE szSQL;

	  END IF;

   END IF; END DOCS_COMMENTS; Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CACEB9D.1B0B349F_at_exesolutions.com>...
> I would be extremely helpful if you posted your code ... because this is
> undoubtedly an issue with what you wrote rather than with what you were
> trying to write.
>
> Daniel Morgan
>
>
>
> Adam Rothberg wrote:
>
> > Does anyone know how to set the COMMENT on tables and columns from a
> > PL/SQL procedure? I tried contructing the necessary command string
> > and using EXECUTE IMMEDIATE but it did not work.
> >
> > I'm using Oracle 8.1.6
> >
> > Thanks.
Received on Fri Apr 05 2002 - 07:29:08 CST