Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOB.SUBSTR (11g)
DBMS_LOB.SUBSTR [message #672995] Thu, 01 November 2018 12:20 Go to next message
EDT
Messages: 3
Registered: November 2018
Junior Member
Hi All,

Can someone please help me how to get string value from BLOB column.
I will get below string in my data.

1 PIN_FLD_ERROR_CODE STR [0] "20321"

And I want to get error code .. that is 20321 from that string.

Please help me.

ED
Re: DBMS_LOB.SUBSTR [message #672997 is a reply to message #672995] Thu, 01 November 2018 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
EDT wrote on Thu, 01 November 2018 10:20
Hi All,

Can someone please help me how to get string value from BLOB column.
I will get below string in my data.

1 PIN_FLD_ERROR_CODE STR [0] "20321"

And I want to get error code .. that is 20321 from that string.

Please help me.

ED

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read



How to determine or know how or where in BLOB to locate the error code?
Re: DBMS_LOB.SUBSTR [message #673001 is a reply to message #672995] Thu, 01 November 2018 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I will get below string in my data.
String is not BLOB it is CLOB.
Or is this a BLOB that contains strings? In this case why not using CLOB instead?

Post something we can work with (in addition to answer to BlackSwan's question).

[Updated on: Thu, 01 November 2018 13:07]

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #673003 is a reply to message #673001] Thu, 01 November 2018 13:51 Go to previous messageGo to next message
EDT
Messages: 3
Registered: November 2018
Junior Member
Michael,

In blob datatype I am getting this below value

1 PIN_FLD_ERROR_CODE STR [0] "20321"

Out of above value I want to get 20321 only when I run select command.

all the time error code will be in double quotes ""

ED
Re: DBMS_LOB.SUBSTR [message #673004 is a reply to message #673003] Thu, 01 November 2018 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
EDT wrote on Thu, 01 November 2018 11:51
Michael,

In blob datatype I am getting this below value

1 PIN_FLD_ERROR_CODE STR [0] "20321"

Out of above value I want to get 20321 only when I run select command.

all the time error code will be in double quotes ""

ED
SELECT command?
I don't see any SELECT statement from you.
We don't know the table name or any column names.

What should be result when BLOB contain below?

1 "ID10T" PIN_FLD_ERROR_CODE STR [0] "20321"

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: DBMS_LOB.SUBSTR [message #673005 is a reply to message #673003] Thu, 01 November 2018 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How can you get this from a BLOB as a BLOB is BINARY data and so NOT characters?
You MUST post something we can reproduce.
Copy and paste what you did (Please use [code] tags).

[Updated on: Thu, 01 November 2018 14:02]

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #673007 is a reply to message #673005] Thu, 01 November 2018 14:07 Go to previous messageGo to next message
EDT
Messages: 3
Registered: November 2018
Junior Member
Michael,

I am new to oracle database. Sorry if I am not clear on my question.

Below data I am getting in of the table and they storing below data in one of the BLOB column for one record.

0 PIN_FLD_POID POID [0] 0.0.0.1 /account 1 0
0 PIN_FLD_STATUS ENUM [0] 0
0 PIN_FLD_PRODUCT_NAME STR [0] "PNRS-10-DHCP-10K"
0 WBX_FLD_ITEM_ID STR [0] "348011798"
0 WBX_FLD_ITEM_DESC STR [0] "PNR R10 DHCP Upgrades 3yr - 10,000 IP Leases"
0 PIN_FLD_ERROR_INFO ARRAY [0] allocated 20, used 3
1 PIN_FLD_NAME STR [0] ""
1 PIN_FLD_DESCR STR [0] "Product: PNRS-10-DHCP-10K\nPermitted: Subscription"
1 PIN_FLD_ERROR_CODE STR [0] "20321"

And my work to get the error code (i.e. 20321) out of this data..

Hope fully you understood.

ED
Re: DBMS_LOB.SUBSTR [message #673009 is a reply to message #673007] Thu, 01 November 2018 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Below data I am getting in of the table
Once again, how do you get it?
New or not to Oracle you should know how you get this.
Just copy and paste what you do (before read How to use [code] tags and make your code easier to read).
Without this, I can't help you.

[Updated on: Thu, 01 November 2018 14:16]

Report message to a moderator

Re: DBMS_LOB.SUBSTR [message #673011 is a reply to message #673007] Thu, 01 November 2018 15:31 Go to previous message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So forgetting about the BLOB stuff and assuming these are just string data and you want what's between " in the row that contains 'PIN_FLD_ERROR_CODE' then it is something like:
select regexp_substr(string,'"([^"]*)',1,1,'',1) 
from t
where string like '%PIN_FLD_ERROR_CODE%'
/
Previous Topic: How to print Boolean or True/False value in Sql
Next Topic: SQL query
Goto Forum:
  


Current Time: Sun Apr 21 15:07:06 CDT 2019