| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problems with Long fields
A copy of this was sent to "Woody" <agodinho_at_zipmail.com.br>
(if that email address didn't require changing)
On 1 Mar 1999 19:00:03 GMT, you wrote:
>
>Recently I'm try to use a long data field to save one great amount of
>details about a propriety of my project, a detailed description, where this
>will be used in a seek process.
>
>Well, my problem is very simple: When I try to insert my details (greater
>than 4K) I receive one error message and nothing happens, the program abort
>and the details are lost ...
>
>I try this with clob type to, but the same problem occurs.
>
>If anyone can help-me ...
>PLEASE
>
you need to use bind variables for
larger strings instead of character string constants.
One method in SQL plus (and probably will work with your environment as well) that works upto 32k is as such:
drop table demo;
create table demo ( x long );
declare
myvar long default '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx(lots of stuff here)......
insert into demo values ( myvar );
dbms_output.put_line( length( myvar ) );
end;
The pl/sql variable can be initialized to upto 32k of static text. It can then be inserted into the table (it is a bind variable, not a constant at that point).
For example, I just ran it and it showed me:
Table dropped.
Table created.
24726
PL/SQL procedure successfully completed.
So, that put a 24,726 character string into the table...
>Below, the source code:
>
>'----- Main declarations
>
>Dim OraSessao As OraSession
>Dim OraBase As OraDatabase
>Dim Consulta As OraDynaset
>
>'----- Open database
>
>Set OraSessao = CreateObject("OracleInProcServer.XOraSession")
>Set OraBase = OraSessao.OpenDatabase("intranet.world",
>"intranet/intraunesa", ORADB_DEFAULT)
>
>'----- BAD Code ?
>
>OraBase.ExecuteSQL _
> "INSERT INTO CdaContents( id,contents ) " & _
> "VALUES('" & idContent & "','" & sContents & "')"
>
>'' where sContens is a string greatar than 4K
>
>'----- Error message
>
>Error in SQL statement, ORA-01704: string literal too long (error #440)
>
>'-----------
>
>VB5 / Windows 95
>Project/References:
>OLE Automation
>Oracle InProc Server 2.3 Type Library
>
>Anaximandro de Godinho
>agodinho_at_estacio.br
>
>Universidade Estácio de Sá
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |