2 Replies Latest reply: Jan 3, 2013 10:41 AM by rp0428 RSS

    Comments in Oracle object definitions

    982735
      Looking for some solution to implement all comments in my objects definitions.
      For example I have following code to create view:

      create or replace view vw_act_accounts as
      /*
      AW 29/11/2012 - PBI1234 - list all active accounts today
      */
      -- select valid contracts
      select account_number,name
      from transactions td
      join accounts aa
      on td.REF_NO = aa.account_number and aa.RECTYPE = 'C'
      UNION
      -- select valid accounts
      select account_number,name
      from transactions td
      join accounts aa
      on td.AC_NO = aa.ACCOUNT_NUMBER and aa.RECTYPE = 'A'
      UNION
      -- select valid active accounts
      select select account_number,name
      from accounts aa
      where aa.RECTYPE != 'G';

      Problem is that lines 2 to 5 are not included in view definition when I query definition text.
      All I got is:

      create or replace view vw_act_accounts as
      select account_number,name
      from transactions td
      join accounts aa
      on td.REF_NO = aa.account_number and aa.RECTYPE = 'C'
      UNION
      -- select valid accounts
      select account_number,name
      from transactions td
      join accounts aa
      on td.AC_NO = aa.ACCOUNT_NUMBER and aa.RECTYPE = 'A'
      UNION
      -- select valid active accounts
      select select account_number,name
      from accounts aa
      where aa.RECTYPE != 'G';

      Looks like that any comments from first lines (before Select statement) are truncated. Any further comments are included in definition.

      Any ideas how to fix that.

      11g version database.

      Thanks
        • 1. Re: Comments in Oracle object definitions
          EdStevens
          979732 wrote:
          Looking for some solution to implement all comments in my objects definitions.
          For example I have following code to create view:

          create or replace view vw_act_accounts as
          /*
          AW 29/11/2012 - PBI1234 - list all active accounts today
          */
          -- select valid contracts
          select account_number,name
          from transactions td
          join accounts aa
          on td.REF_NO = aa.account_number and aa.RECTYPE = 'C'
          UNION
          -- select valid accounts
          select account_number,name
          from transactions td
          join accounts aa
          on td.AC_NO = aa.ACCOUNT_NUMBER and aa.RECTYPE = 'A'
          UNION
          -- select valid active accounts
          select select account_number,name
          from accounts aa
          where aa.RECTYPE != 'G';

          Problem is that lines 2 to 5 are not included in view definition when I query definition text.
          All I got is:

          create or replace view vw_act_accounts as
          select account_number,name
          from transactions td
          join accounts aa
          on td.REF_NO = aa.account_number and aa.RECTYPE = 'C'
          UNION
          -- select valid accounts
          select account_number,name
          from transactions td
          join accounts aa
          on td.AC_NO = aa.ACCOUNT_NUMBER and aa.RECTYPE = 'A'
          UNION
          -- select valid active accounts
          select select account_number,name
          from accounts aa
          where aa.RECTYPE != 'G';

          Looks like that any comments from first lines (before Select statement) are truncated. Any further comments are included in definition.

          Any ideas how to fix that.

          11g version database.

          Thanks
          Those are comments to sqlplus. They telling sqlplus "these lines are comments; please ignore them; do not process them". So that's exactly what sqlplus did.
          • 2. Re: Comments in Oracle object definitions
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION, 11g is not a version).
            >
            Looks like that any comments from first lines (before Select statement) are truncated. Any further comments are included in definition.

            Any ideas how to fix that.
            >
            You already know how to fix that. Put the comments AFTER the word SELECT.

            View definitions are stored in the TEXT column (a LONG datatype) in the SYS.VIEW$ table. Only the definition of the view from the word SELECT on is stored.