Oracle SQL: Select specific string from CLOB field

Question

I have a table where one of the fields is a CLOB, it stores a error message information.

The field CLOB as the following content:

oracle.retail.sim.common.core.SimServerException: Error processing message! [Inbound: true, MessageType: ItemLocCre, BusinessId: 1101505002]
    at oracle.retail.sim.service.mps.SimMessageCommand.buildException(Unknown Source)
    at oracle.retail.sim.service.mps.SimMessageProcessCommand.doExecute(Unknown Source)
    at oracle.retail.sim.common.core.Command.execute(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor273.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
Caused by: oracle.retail.sim.common.core.SimServerException: Item not found for Id: 1101505002
    at oracle.retail.sim.server.integration.consumer.itemloc.ItemLocConsumer.buildItemNotFoundException(Unknown Source)
    at oracle.retail.sim.server.integration.consumer.itemloc.ItemLocCreateConsumer.handleMessage(Unknown Source)
    at oracle.retail.sim.server.integration.consumer.itemloc.ItemLocCreateConsumer.handleMessage(Unknown Source)
    at oracle.retail.sim.server.integration.consumer.SimMessageConsumerFactory.consume(Unknown Source)
    ... 56 more

Im trying to display the result of the clob directly in the PL/SQL output, so im using the following query:

select id, dbms_lob.substr(message_error, 4000, 1) AS ERROR_MESSAGE
  from THE_TABLE;

What i pretend is to select only the line that contains the 'Caused by..' string. What i need is to extract only the following error message:

Item not found for Id: 1101505002

It is possible to do so whith only a select statement?

Thanks in advance, Best regards,

Hello, Is not a procedure, is just a simple query that will be used for reporting proposes. — Carlos Pinho
– Carlos Pinho, Commented Feb 2, 2017 at 22:14
So where does the "PL/SQL output" come from if you don't have a stored procedure? — user330315
– user330315, Commented Feb 3, 2017 at 6:37
Hello, it comes directly when doing the select statement in plsql developer. What i need is The function to retrieve only The sentence containing The string causes By. Mathguy answers works as expected. Thanks — Carlos Pinho
– Carlos Pinho, Commented Feb 4, 2017 at 10:13

user5683823 · Accepted Answer · 2017-02-02 20:53:25Z

1

The following query (substitute your actual table and column names) will extract one line of text, from the words Caused by to the end of that line. It doesn't matter if the line of text begins with Caused by - you will only get everything from those words to the end of the line.

If you need a shorter sub-string from that, you will need to explain in more detail how it can be "recognized" - how do you decide what can be left out and what must be returned. How is that delimited?

select regexp_substr(message, 'Caused by:.*) as caused_line
from   test_data;

(Note that by default the wildcard character . does not match the end-of-line in regular expressions in Oracle.)

answered Feb 2, 2017 at 20:53

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Pinho Over a year ago

Hello, This did work i used the following in order to make the clob visible directly in the output without the need to open all the clob file: dbms_lob.substr(regexp_substr(message_error, 'Caused by:.*'), 4000, 1) AS error_message Thanks, i will not try to find a way to remove the Caused by: from the output.

Collectives™ on Stack Overflow

Oracle SQL: Select specific string from CLOB field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related