This discussion is archived
2 Replies Latest reply: Jan 3, 2013 8:41 AM by rp0428 RSS

Comments in Oracle object definitions

982735 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points