Re: LOBS
Date: Thu, 13 Mar 2008 12:06:02 -0700 (PDT)
Message-ID: <19f80503-97c4-4fce-b7f6-0ba263fe82e7@n75g2000hsh.googlegroups.com>
On Mar 13, 12:42 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi,
>
> I've been reading some examples on DBMS_LOB.
>
> We have a table with a CLOB column which contains a lot of text, with
> new lines and all.
>
> When I run a command like: Select dbms_lob.substr(email_text,1,10)
> from email_test;
>
> It gives me like, only 1 character, which is not even the first
> character of the string??
>
> I'm still looking for examples, maybe someone can help?
>
> Thanks!
Are you planning on again deleting your original post after you get your answer??
The manual has wonderful information on the dbms_lob.substr() function, including, wonder of wonders, how to use it:
dbms_lob.substr(
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
It appears you have your parameters reversed, as it doesn't function like the plain-vanilla SUBSTR() function. An example follows:
SQL> -- SQL> -- Let's create a table with a LONG SQL> -- column to start with SQL> -- SQL> create table longex( 2 shouldabeenalob long
3 );
Table created.
SQL> SQL> -- SQL> -- Let's now create our LOB table SQL> -- SQL> create table lobex( 2 isalob clob
3 );
Table created.
SQL> SQL> -- SQL> -- We'll insert data into the LONG SQL> -- SQL> insert into longex
2 values ('NAME:BRENT LUWIS COUNTRY: NIGERIA ATTN: BELOVED, GREETINGS IN THE NAME OF OUR LORD JESUS CHRIST. I AM MR BRENT LUWIS A CITIZEN OF THE REPUBLIC OF NETHE
RLAND BUT PRESENTLY IN NIGERIA WORKING FOR THE FEDERAL GOVERNMENT,I AM 50 YEARS OLD, I AM NOW A NEW CHRISTAINALL INDICATION MY CONDITIONS IS REALLY DETERIORATING AND
IT IS QUITE CONVERT, SUFFERING FROM LONG TIME CANCER OF THE HEART, FROM OBVIOUS THAT I WON"T LIVE MORE THAN 1 YEAR,ACCORDING TO MY DOCTORS,THIS IS BECAUSE THE CANCE
R STAGE HAS GOTTEN TO A VERY BAD STAGE. MY LATE WIFE DIED LAST FIVE YEARS,AND DURING THE PERIOD OF OUR MARRIAGE WE COULDN"T PRODUCE ANY CHILD. MY LATE WIFE WAS VERY
WEALTHY AND AFTER HER DEATH, I INHERITED ALL HER BUSINESS AND WEALTH. THE DOCTORS HAS ADVISED ME THAT I MAY NOT LIVE FOR MORE THAN 1 YEAR,SO I NOW DECIDED TO DIVIDE
THE PART OF THIS WEALTH, TO CONTRIBUTE TO THE DEVELOPMENT OF THE CHURCH IN AFRICA, AMERICA ASIA,AND EUROPE. I SELECTED YOU AFTER VISITING THE WEBSITE AND I PRAYED OV
ER IT. I AM WILLING TO DONATE THE SUM OF $25,000.000.00U.USD( TWENTY FIVE MILLION UNITED STATES DOLLARS) TO THE LESS PRIVILEGED. PLEASE I WANT YOU TO NOTE THAT FUND
IS LYING IN A SECURITY COMPANY IN NIGERIA. ONCE I HEAR FROM YOU, I WILL FORWARD TO YOU ALL THE INFORMATIONS YOU WILL USE TO GET HER FUND RELEASED FROM THE SECURITY C
OMPANY AND TO BE TRANSFERRED TO YOUR ACCOUNT. I HONESTLY PRAY THAT THIS MONEY WHEN TRANSFERRED TO YOUR ACCOUNT WILL BE SURE FOR THE SAID PURPOSE, BECAUSE I HAVE COME
TO FIND OUT THA WEALTH ACQUISITION WITHOUT CHRIST IS VANITY. MAY THE GRACE OF OUR LORD JESUS THE LOVE OF GOD AND THE FELLOWSHIP OF GOD BE WITH YOU AND YOUR FAMILY.
PLEASE PROVIDE ME WITH THE FOLLOWING INFORMATION SO I CAN FORWARD IT TO MY LATES WIFE LAWYER WHO IS GOING TO HANDLE THE TRANSACTION WITH YOU AND LEAD, ADVICE YOU ON
HOW TO BE SUCCESSFUL AND GET THIS FUND INTO YOUR ACCOUNT WITHOUT THE
BRIDGE OF LAW. INFORMATION NEEDED FULL NAME :...................................... FULL CONTACT ADDRESS............................. NAME OF COMPANY :................................ COMPANY ADDRESS:................................. POSITION:.................. ...................... MARITAL STATUS :................................. RELIGION:........................................ SEX:...................................... ....... PRIVATE FAX NO................................... PRIVATE PHONE NO:................................ I AWAIT YOUR URGENT REPLY.YOUR BROTHER IN CHRIST. MR BRE
NT LUWIS. '); 1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> -- SQL> -- Now let's populate that LOB SQL> -- SQL> insert into lobex
2 select to_lob(shouldabeenalob) from longex;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> -- SQL> -- Call the DBMS_LOB.SUBSTR() function SQL> -- with our parameters backwards SQL> -- SQL> -- We don't get what we'd expect SQL> -- SQL> select dbms_lob.substr(isalob, 1, 10)2 from lobex;
DBMS_LOB.SUBSTR(ISALOB,1,10)
T
SQL> SQL> -- SQL> -- Let's call this correctly SQL> -- SQL> -- SQL> -- Amazing how it works when we follow SQL> -- the directions SQL> -- SQL> select dbms_lob.substr(isalob, 10, 1)2 from lobex;
DBMS_LOB.SUBSTR(ISALOB,10,1)
NAME:BRENT SQL> So I guess now you'll remove your original post...
David Fitzjarrell Received on Thu Mar 13 2008 - 14:06:02 CDT