Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    27

    Unanswered: Creating a view of a long string

    Hi,

    I have an an audit field VARCHAR2(2000). It is held in the format:
    'OLD=
    NAME=TIM
    AGE=12
    NEW=
    NAME=TIM
    AGE=13'

    I.E. values separated by return carriages.

    I do not know how many values are in each audit string or what the values are called. Only OLD and NEW will remain constant throughout.

    Is it possible to write a view to return these values in the form:

    Select * from audit_vw where.....

    ATTRIBUTE OLD NEW
    ------------ ---------- ---------------
    NAME TIM TIM
    AGE 12 13

    Any suggestions appreciated.

    Thanks,
    Tim

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    You cannot create a VIEW for this....
    what i suggest is that you write a PL/SQL block that plays with the string and insert the values into a new table.
    It is simple and easy.
    Use INSTR(<Source string>, CHR(13) ) function to locate the enter keys in the field. CHR(13) is the enter key!!!
    Regards
    Last edited by shelva; 11-20-03 at 07:32.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Your apporach may not be proper

    any audit table design usually is
    (field_name,old_value,new_value)
    to cleanup the mud write a pl/sql and do it once for all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •