Home » SQL & PL/SQL » SQL & PL/SQL » XMLTYPE Column
XMLTYPE Column [message #250175] Mon, 09 July 2007 00:45 Go to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
I have a column which has datatype as “xmltype “. Through sqlplus I could able to insert value which are less than or equal to 4000 characters.

But through java it's feasible.

Suggest me how to populate those xmltype columns using SQL or PLSQL,wherein I can pass values whose length may go beyond 4000.

NOTE: In plsql a variable or string can hold only 4000 characters.

I have a column which has datatype as “xmltype “. Through sqlplus I could able to insert value which are less than or equal to 4000 characters.

But through java it's feasible.

Suggest me how to populate those xmltype columns using SQL or PLSQL,wherein I can pass values whose length may go beyond 4000.

NOTE: In plsql a variable or string can hold only 4000 characters.

Re: XMLTYPE Column [message #250178 is a reply to message #250175] Mon, 09 July 2007 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>NOTE: In plsql a variable or string can hold only 4000 characters.
WRONG! plsql variables can be 32000+ in length
Re: XMLTYPE Column [message #250207 is a reply to message #250178] Mon, 09 July 2007 02:34 Go to previous messageGo to next message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
If you have even larger datatype use oracle text or clob as per your requirement.
icon4.gif  Re: XMLTYPE Column [message #250405 is a reply to message #250207] Mon, 09 July 2007 23:27 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
XMLTYPE can hold 4gb of data. How to load a 4gb of data through plsql.

declare
v_xml_val xmltype;
begin
v_xml_val := 'enter values greater than 4000 in length'
insert into xml_table (xml_col) values (v_xml_var);
end



Error while assigning value to v_xml_val!
Re: XMLTYPE Column [message #250420 is a reply to message #250405] Tue, 10 July 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mssing ";" at the end of the line?

Regards
Michel
Re: XMLTYPE Column [message #250422 is a reply to message #250420] Tue, 10 July 2007 00:44 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
its just for an example.dont consider the syntax.
though u add semi colon it gives err
Re: XMLTYPE Column [message #250425 is a reply to message #250422] Tue, 10 July 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which error?
Copy and paste what you have (removing the data).

Regards
Michel
Re: XMLTYPE Column [message #250429 is a reply to message #250175] Tue, 10 July 2007 00:53 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member


declare
v_xml_val xmltype;
begin

v_xml_val := 'enter values greater than 4000 in length';

--above line enter ur text and its length must be greater than 4k

insert into xml_table (xml_col) values (v_xml_var);


end

Re: XMLTYPE Column [message #250430 is a reply to message #250429] Tue, 10 July 2007 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Doesn't look like a valid xml to me...
Re: XMLTYPE Column [message #250431 is a reply to message #250430] Tue, 10 July 2007 00:56 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
okay try with a valid xml.
Re: XMLTYPE Column [message #250433 is a reply to message #250431] Tue, 10 July 2007 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
no, you try it and copy-paste your outcome.
Re: XMLTYPE Column [message #250435 is a reply to message #250433] Tue, 10 July 2007 01:03 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
ORA-01704:string literal too long
Re: XMLTYPE Column [message #250436 is a reply to message #250175] Tue, 10 July 2007 01:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01704:string literal too long
& your point is WHAT?
Re: XMLTYPE Column [message #250439 is a reply to message #250436] Tue, 10 July 2007 01:16 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
step1: create table xml_table(xml_col xmltype);

no err


step2: insert into xml_table (xml_col) values ('<city> chennai </city>');

1 row inserted

step3: HERE INSTEAD OF TEXT ENTER ATLEAST 400 OR 500 LINES .ITS LENGTH MUST ME GREATER THAN 4000.


insert into xml_table (xml_col) values ('<city> TEXT </city>');



ORA-01704:string literal too long



Re: XMLTYPE Column [message #250440 is a reply to message #250175] Tue, 10 July 2007 01:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
& your point is WHAT?
Re: XMLTYPE Column [message #250442 is a reply to message #250440] Tue, 10 July 2007 01:21 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
step3 is my problem. how to resolve it.
Re: XMLTYPE Column [message #250446 is a reply to message #250175] Tue, 10 July 2007 01:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>step3 is my problem. how to resolve it.
I am dense. What is STEP3? & what were steps #1 & #2?
Re: XMLTYPE Column [message #250447 is a reply to message #250436] Tue, 10 July 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-01704: string literal too long
 *Cause:  The string literal is longer than 4000 characters.
 *Action:  Use a string literal of at most 4000 characters.
            Longer values may only be entered using bind variables.

Regards
Michel
Re: XMLTYPE Column [message #250448 is a reply to message #250447] Tue, 10 July 2007 01:30 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
how to bind it. can you give me an example.
Re: XMLTYPE Column [message #250452 is a reply to message #250448] Tue, 10 July 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> var my_bind_variable varchar2(100)
SQL> begin :my_bind_variable := 'my value'; end;
  2  /

PL/SQL procedure successfully completed.

SQL> declare
  2    my_bind_variable varchar2(100);
  3  begin
  4    my_bind_variable := 'my value';
  5  end;
  6  /

PL/SQL procedure successfully completed.

...

Regards
Michel
Re: XMLTYPE Column [message #250458 is a reply to message #250452] Tue, 10 July 2007 02:01 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
great it works ...but i suppose it gives error once the variable length crosses 32750(approximately)
Re: XMLTYPE Column [message #250465 is a reply to message #250458] Tue, 10 July 2007 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare foo varchar2(32767); begin null; end;
  2  /

PL/SQL procedure successfully completed.

SQL> declare foo varchar2(32768); begin null; end;
  2  /
declare foo varchar2(32768); begin null; end;
                     *
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00215: String length constraints must be in range (1 .. 32767)

Regards
Michel
Re: XMLTYPE Column [message #250478 is a reply to message #250465] Tue, 10 July 2007 03:53 Go to previous messageGo to next message
ramanathan manickam
Messages: 27
Registered: July 2007
Junior Member
YES you are right, but i declared the variable as CLOB.
Re: XMLTYPE Column [message #250495 is a reply to message #250478] Tue, 10 July 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use it with dbms_lob.CREATETEMPORARY to be able to handle more than 32K.

Regards
Michel
Re: XMLTYPE Column [message #250503 is a reply to message #250495] Tue, 10 July 2007 05:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The whole idea of using sqlplus to insert data > 32k seems a bit weird to me.
Previous Topic: User created datatype as OUT param in procedure - how do we read it?
Next Topic: UPDATE Query Help
Goto Forum:
  


Current Time: Sun Dec 11 02:42:49 CST 2016

Total time taken to generate the page: 0.11269 seconds