SELECT CLOB

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Mon, 28 May 2012 1:44 pm

GOOD AFTERNOON, I am beginner with PL/SQL and got the following question: do I need a manipulation on a table that has a clob field, within that field contains an xml code in which I have to extract part of the code (a block to be more specific) and play this block in the html code.
The question is: How do I not know clob handling muito 's, how would I do this extraction?!
Since already thank you!
amarorafael

Poston Mon, 28 May 2012 3:57 pm

Hi Amarorafael, I'm no expert in XML and CLOB, but found a great example on ASKTOM website (http://asktom.oracle.com), specifically this LINK: http://asktom.oracle.com/pls/apex/f?p=1 ... 5019878716 in the opinion of Tom Kyte (the creator of ASKTOM), CLOB fields are not very recommended to store XML code. In the absence of a better option, you can rely on the example that he put in the LINK above. The method used to extract XML information from the CLOB field is XMLTYPE (..).EXTRACT :
Code: Select all
Here is a temporary solution -- long term, use the right type for the data:

ops$tkyte@ORA920LAP> create table t ( x clob );
Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t values (
  2  '<REQUEST_DETAIL>
  3    <GROUP_TYPE>PR</GROUP_TYPE>
  4    <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
  5    <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
  6    <PROJ_MGR_ID></PROJ_MGR_ID>
  7    <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
  8   </REQUEST_DETAIL>' );
1 row created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select
e.x.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
  2    from t e
  3  /
select e.x.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
           *
ERROR at line 1: ORA-22806: not an object or REF

ops$tkyte@ORA920LAP> select
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
  2    from t e
  3  /

XMLTYPE(E.X).EXTRACT('/REQUEST_DETAIL/GROUP_TYPE/TEXT()').GETSTRINGVAL()
------------------------------------------------------------------------
PR
the ASKTOM site is excellent to learn PL/SQL in detail, because the Tom Kyte always bases its explanations in examples that you can reproduce later in your environment.

I hope that the explanation of ASKTOM is sufficient to clarify your doubts. It may be that the foristas the ADDS can introduce other suggestions.

Good luck, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Tue, 29 May 2012 3:48 pm

perfect! I managed to remove a block of xml that is in a clob using basically this feature, so I put together the following select:
Code: Select all
SELECT
       extract(XMLType(coluna_da_tabela), 'bloco_xml', 'xmlns="endereco"')
  FROM tabela
WHERE condicao
and it worked!
only now I'm in another dilemma, I have to add this snippet of xml that I pulled out and stores it in a html cod (within a text area) and insert this cod htm in a clob type column of the table. the html code is this:
Code: Select all
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<form  method="post">
<input type="submit" value="enviar">
        <textarea name="arquivoXml" cols="150" rows="50" style="visibility:hidden" >
TRECHO XML DEVE VIR AQUI!!!!!!
           </textarea>
   </form>
<script>

document.one.submit();
      </script>
   </body>
</html>

amarorafael

Poston Tue, 29 May 2012 4:03 pm

Ola amaro, wouldn't know exactly how to suggest a definitive solution, but you can try this store HTML code in a field VARCHAR2 (4000) and try to use the search functions and replacing in strings such as INSTR and REPLACE.

Maybe the staff can give any other tips on this topic.

Hugs, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Tue, 29 May 2012 4:33 pm

Have a little problem ... HTML is not the same as XML, and is much more complex to do the parse, so you don't have to use the Oracle XML functions to help you there.

You will probably have to do as STCOUTINHO said, create a VARCHAR2 and treat with string manipulation function, INSTR, SUBSTR etc, locate the start of your tag:
Code: Select all
<textarea name="arquivoXml" cols="150" rows="50" style="visibility:hidden" >
make sure you don't have other textarea tags nested inside, check the consistency and look for the end of the tag and insert into the XML.

Can be up to 32767 long in PL/SQL. In SQL is 4000 maximum VARCHAR2.

Detail, the Extract function is a function deprecated and Oracle recommends using the function XMLQUERY: http://docs.oracle.com/cd/E11882_01/ser ... ons060.htm http://docs.oracle.com/cd/E11882_01/app ... b13gen.htm
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Wed, 30 May 2012 9:39 am

I wonder if you could give me an example of how I'm going to do these manipulations?!
I'm new to the area and I'm not getting to accomplish here!
If you can I will be grateful!
amarorafael


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: Bing [Bot] and 8 guests

cron