YES! YES! YES! ;-)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

YES! YES! YES! ;-)

Norman Dunbar
I've got Toad back again, I have found a way around our work firewalls
that allows me to ssh tunnel from my laptop to the databases. I've been
without Toad abilities for a year now, and I've hated having to use
SQL*Plus for all that time. No more!

And now a question.

I am looking at a CLOB in an 11203 database with Toad 11.0.0.116 (I'll
upgrade soon, I promise!) and I see in the hex tab of the pop-up editor,
cr/lf end of lines. However, when I select a dump of a substring of the
same CLOB actually on the database, in <spit> SQL*Plus, I only see the
lf characters.

In my options I have the following set:

Oracle/General - Unix Style checked for "Newline format for character data"

Files/General - Save files in Unix format.


In Toad, when I dump the CLOB in MOE using a DBMS_LOB.SUBSTR() call, I
see the last half dozen characters as:

61,74,61,3e,0a,00 Which = 'ata>' lf ? (where ? = a binary zero that
shouldn't be there but is!)

On the server, I get exactly the same. Note Unix style line ends.

In the popup editor, hex tab, I get the following at the end:

61 74 61 3E OD OA OO Which = 'ata>' cr lf ?


Hmm, just found something. I edited the text to remove the unwanted and
illegal (XML) binary zero at the end. When I saved it back to the table,
it shows me "Unix style newlines (LF)" on the pop-up editor status line
and on the hex tab, all the line ends are now still in Microsoft style,
except the final one, which is in Unix style. Ulp!

Confused? Me? You bet!



--
Cheers,
Norm. [TeamT]
Reply | Threaded
Open this post in threaded view
|

RE: YES! YES! YES! ;-)

mgainty

Norm

apparently direct writes to any LOB are verboten.. one should always invoke DBMS_LOB.WRITE(clob,length(varchar_input),1,varchar_input)

http://geekswithblogs.net/robertphyatt/archive/2010/03/24/write-read-and-update-oracle-clobs-with-plsql.aspx

since youve mastered SSH i take it you are now a Eunuchs guy?

Have Fun,
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.


To: [hidden email]
From: [hidden email]
Date: Wed, 26 Sep 2012 15:59:58 +0100
Subject: [toad] YES! YES! YES! ;-)
















 



 


   
     
     
      I've got Toad back again, I have found a way around our work firewalls

that allows me to ssh tunnel from my laptop to the databases. I've been

without Toad abilities for a year now, and I've hated having to use

SQL*Plus for all that time. No more!



And now a question.



I am looking at a CLOB in an 11203 database with Toad 11.0.0.116 (I'll

upgrade soon, I promise!) and I see in the hex tab of the pop-up editor,

cr/lf end of lines. However, when I select a dump of a substring of the

same CLOB actually on the database, in <spit> SQL*Plus, I only see the

lf characters.



In my options I have the following set:



Oracle/General - Unix Style checked for "Newline format for character data"



Files/General - Save files in Unix format.



In Toad, when I dump the CLOB in MOE using a DBMS_LOB.SUBSTR() call, I

see the last half dozen characters as:



61,74,61,3e,0a,00 Which = 'ata>' lf ? (where ? = a binary zero that

shouldn't be there but is!)



On the server, I get exactly the same. Note Unix style line ends.



In the popup editor, hex tab, I get the following at the end:



61 74 61 3E OD OA OO Which = 'ata>' cr lf ?



Hmm, just found something. I edited the text to remove the unwanted and

illegal (XML) binary zero at the end. When I saved it back to the table,

it shows me "Unix style newlines (LF)" on the pop-up editor status line

and on the hex tab, all the line ends are now still in Microsoft style,

except the final one, which is in Unix style. Ulp!



Confused? Me? You bet!



--

Cheers,

Norm. [TeamT]



   
     

   
   






       
Reply | Threaded
Open this post in threaded view
|

Re: YES! YES! YES! ;-)

Norman Dunbar
Afternoon Martin,

On 26/09/12 17:55, Martin Gainty wrote:
> Norm
>
> apparently direct writes to any LOB are verboten.. one should always
> invoke DBMS_LOB.WRITE(clob,length(varchar_input),1,varchar_input)
That's news to me, but I don't work with them as much as I should. This
one is XML stored in a CLOB and every single row in the table has a
binary zero character at the end of the XML data.

Problem is, only 25% of the data are being flagged up as invalid XML by
the application, so I suspect that the application may be written in C
or C++ and the binary zero is an "end of string" byte. I don't think it
should be in the data.

Saving to a file saves the binary zero and stops the data loading in a
browser. Firefox helpfully says "invalid character at line x column n,
IE says "the file is invalid" - not helpful!


> http://geekswithblogs.net/robertphyatt/archive/2010/03/24/write-read-and-update-oracle-clobs-with-plsql.aspx
If you read down the comments, the second one updates the original code
for "later versions of Oracle" and does this as an update:

UPDATE TBL_CLOBHOLDERDDOC
SET CLOBHOLDERDDOC = p_document
WHERE CLOBHOLDERDDOCID = p_id;

So, Toad is fine - I'm doing all this on 11203.


> since youve mastered SSH i take it you are now a Eunuchs guy?
I'm not sure anyone has mastered SSH, but I did write an Ask Toad
article, years ago, about using ssh tunnelling to get to a database from
a problem location. I've been foxed by a firewall between me and the
database, so I couldn't even use ssh tunnelling.

However, after a few eRants about this, I've been given the ability to
ssh tunnel onto my databases. Or, at least, some of them.

Toad 11.5 upgrade installed this Am and working perfectly. I am a happy
Norm again.


--
Cheers,
Norm. [TeamT]


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [hidden email]
    [hidden email]

<*> To unsubscribe from this group, send an email to:
    [hidden email]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/