Home » SQL & PL/SQL » SQL & PL/SQL » Reporting
Reporting [message #206796] Fri, 01 December 2006 09:09 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have a single CLOB column in a table having values in the following format.

Event type: Security database administration
Command type: Modify user
Status: Successful
Administrator: TECHSRV
Class: USER
Object: edmzpar
Terminal: DCGSUSSDAKZZ13
Date: 02 Oct 2006
Time: 14:56
Details: Command allowed for admin user.
Command: chusr ("edmzpar") password("********")

I want this to be split up into multiple columns in a report such that
Status, Object, Date appears as separate columns for readability.

Please help,.
Re: Reporting [message #206808 is a reply to message #206796] Fri, 01 December 2006 10:57 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I was able to proceed one step ahead and split the column. But getting stuck at a point I used the following query.
select
trim(ASCII(substr(msgtext,instr(msgtext,':',1,2)+1, instr(msgtext,':',1,3)-instr(msgtext,':',1,2)-1))) "Command Type",
ltrim(rtrim(substr(msgtext,instr(msgtext,':',1,3)+1, instr(msgtext,':',1,4)-instr(msgtext,':',1,3)-1))) "Status",
ltrim(rtrim(substr(msgtext,instr(msgtext,':',1,6)+1, instr(msgtext,':',1,6)-instr(msgtext,':',1,5)-1))) "Modified User",
ltrim(rtrim(substr(msgtext,instr(msgtext,':',1,7)+1, instr(msgtext,':',1,7)-instr(msgtext,':',1,6)-1))) "Terminal From"
from TEXTDATA;

However The problem I am facing is between Command Type and Status there is a newline. So the value of the 1st field and parameter before : in the 2nd field are shown. For eg.instead of displaying 'Security database Administration' it displays 'Security Database Administration <newline> Status' and it repeats like this for the rest of the fields.

How to state in Oracle that ignore anything after a newline or a carriage return?

Regards
Re: Reporting [message #206817 is a reply to message #206808] Fri, 01 December 2006 11:56 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
chr(10) is a line feed and chr(13) is a carriage return

Re: Reporting [message #206824 is a reply to message #206817] Fri, 01 December 2006 12:12 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
replace(replace (my_data, chr(10), ' '), chr(13), null) or similar
Re: Reporting [message #206848 is a reply to message #206796] Fri, 01 December 2006 14:13 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thank you. It worked as expected.

Regards,
Previous Topic: Loading XML into a table
Next Topic: Making logs - CREATE MATERIALIZED VIEW
Goto Forum:
  


Current Time: Thu Dec 08 00:16:59 CST 2016

Total time taken to generate the page: 0.05283 seconds