Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

What is default size of XMLType column?

765321Apr 14 2010 — edited Apr 14 2010
I have a table with 1 raw type and 1 XMLType column. In the XMLType column I am inserting following XML,

<ocaStatus xmlns="http://xmlbeans.apache.org/ocastatus">
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-19</statusDate>
<userId>OcaNimsAcf</userId>
<comment>DocumentnotinNIMS</comment>
</status>
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-19</statusDate>
<userId>OcaNimsAcf</userId>
<comment>DocumentnotinNIMS</comment>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-27</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-26</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-25</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>306</statusCode>
<statusDateclass="sql-date">2008-04-21</statusDate>
<userId>u0075970</userId>
</status>
<status>
<statusCode>301</statusCode>
<statusDateclass="sql-date">2008-02-05</statusDate>
<userId>DMS_WORKFLOW</userId>
<comment></comment>
</status>
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-17</statusDate>
<userId>OcaNimsAcf</userId>
<comment>Comparedfornimsacf</comment>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-27</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-26</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-25</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>306</statusCode>
<statusDateclass="sql-date">2008-04-21</statusDate>
<userId>u0075970</userId>
</status>
<status>
<statusCode>301</statusCode>
<statusDateclass="sql-date">2008-02-05</statusDate>
<userId>DMS_WORKFLOW</userId>
<comment></comment>
</status>
</ocaStatus>

The xml is very long. For inserting into the table I am using xmlparse() function to get XMLType.
While doing this I am getting ORA-01704: string literal too long error. It says can accommodate at the most 4000 characters.
I guess XMLType is stored as a CLOB by default.So it should allow more than 4000 characters.
How can I store xml with more than 4000 characters.

Thanks.
This post has been answered by odie_63 on Apr 14 2010
Jump to Answer

Comments

Gbenga Ajakaye

What bit is your sql developer (32 or 64bit)? This error happens when you have a Driver that is 32 bit, but your SQL Developer install has a 64 Bit JDK.

If that's the case, then you'll need to install a 64 bit Client Driver to match your sql developer 64 bit JDK .

thatJeffSmith-Oracle

that's usually a different error, and it's kinda hard to get a 32 bit java going on a mac, but always good to check to make sure

cj.travis

What is the most efficient way to verify the architecture (32v64) of SQL Developer on OS X?  The driver client libraries are 64bit.

Gary Graham-Oracle

From what you posted, it seems that java.library.path does not begin with contain the instant client location as required by the message...

Check it to verify that  the expected native library directory /Applications/instantclient_12_1 is present and precedes any other client installations.

I don't have a Mac, but under Windows we solve this by prepending the PATH environment variable with the instant client, but which you show as being the third directory...

# echo $PATH 
/opt/local/bin:/opt/local/sbin:/Applications/instantclient_12_1:..
1 person found this helpful
cj.travis

Hi Gary.  Thanks for the response.  I updated the $PATH again to ensure the instant client path was first and tried again, producing the same results.  It's as if SQL Developer isn't picking up the path properly.

Path:

[11:08 AM]    cjtravis@MBP01   ~

# echo $PATH

/Applications/instantclient_12_1:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin

Results:

Testing the Instant Client located at /Applications/instantclient_12_1

Testing client directory ... OK

Testing loading Oracle JDBC driver ... OK

Testing checking Oracle JDBC driver version ... OK

  Driver version: 12.1.0.2.0

Testing testing native OCI library load ... Failed:

  Error loading the native OCI library

  The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable DYLD_LIBRARY_PATH. Check it to verify that

  the expected native library directory /Applications/instantclient_12_1 is present and precedes any other client installations.

  java.library.path = /Users/cjtravis/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.

Gary Graham-Oracle 
It's as if SQL Developer isn't picking up the path properly.

So it would seem -- very strange.  If you check the jdbc.library property in Help > About > Properties does it point to an ojdbc jar file in your SQL Developer installation or to somewhere else? 

Since I only have access to Windows and Oracle Linux I may not be of much more help. I did notice, however, that the instant client install example used the home directory rather than /Applications. You might review your install steps.

cj.travis

Looks like it points to a valid jar to me. I've always kept it in /Applications to prevent myself from accidentally dropping the folder in the future. I don't *think* being in the /Applications directory would impact this sort of thing, much like on Linux.

Screenshot 2016-11-30 12.41.54.png

cj.travis

On my prior post I began to ponder how it seemed like SQL Developer wasn't "picking up" the DYLD_LIBRARY_PATH although I had it set as an environment variable. I decided to take a look at the shell script responsible for launching the application to see if it offered any hints.

On my local device this is located at: /Applications/SQLDeveloper 4.1.1.app/Contents/MacOS

In this directory is the script sqldeveloper.sh with the following contents:

#!/bin/bash

export JAVA_HOME=`/usr/libexec/java_home -v 1.8`

here="${0%/*}"

cd "${here}"

cd ../Resources/sqldeveloper/sqldeveloper/bin

bash ./sqldeveloper >>/dev/null

I added the variable for DYLD_LIBRARY_PATH (line 04) on a whim and gave it another shot:

#!/bin/bash

export JAVA_HOME=`/usr/libexec/java_home -v 1.8`

export DYLD_LIBRARY_PATH=/Applications/instantclient_12_1

here="${0%/*}"

cd "${here}"

cd ../Resources/sqldeveloper/sqldeveloper/bin

bash ./sqldeveloper >>/dev/null

After launching SQL Developer things appear to be working!

Testing Results:

Testing the Instant Client located at /Applications/instantclient_12_1

Testing client directory ... OK

Testing loading Oracle JDBC driver ... OK

Testing checking Oracle JDBC driver version ... OK

Driver version: 12.1.0.2.0

Testing testing native OCI library load ... OK

Success!

Property validation:

Screenshot 2016-11-30 13.42.04.png

And hey, the password reset prompt works, too!

Screenshot 2016-11-30 13.50.42.png

Thanks for the help and feedback today -- much appreciated!

1 person found this helpful
Gary Graham-Oracle

Thanks for the feedback -- it helps us learn, too.  It looks like this may be the explanation:

https://github.com/oracle/node-oracledb/issues/231

Which may be why I also ran across some blogs describing how to get Oracle Instant Client SQL*Plus and other features to work without setting DYLD_LIBRARY_PATH at all.

John McGinnis-Oracle

There is an easier way to get InstantClient 12.1 to work on the Mac - one that does not involve editing any files inside the SQLDeveloper.app file.

At startup, SQL Developer will read an additional configuration file from the user's home directory. We look for the file ~/.sqldeveloper/<version>/sqldeveloper.conf. By default this file doesn't exist, but it can be created to specify additional user-specific configuration options. You can find the exact path that SQL Developer is expecting by looking at the tool.user.conf property value in the Properties tab of the About Box.

As I mentioned, this file probably doesn't exist. So create the file and add the following line to it:

AddVMOption -Djava.library.path=<instant client directory>

This will explicitly set the java.library.path to the instant client directory. SQL Developer will now be able to load the OCI libraries.

A couple of caveats:

  1. The Test button in the Oracle Home configuration dialog won't work correctly. It's trying to use the default java.library.path, and not the path that SQL Developer is actually using. That's something we'll need to fix in a future release.
  2. The default entries in the java.library.path are not going to be present. In the majority of cases, that won't be an issue, as those directories are likely empty or don't even exist. The libraries Java needs are on a different path, so only third-party libraries should be using the default directories.

I tested this with 4.1.5 on El Capitan (10.11.6), but it should work on Yosemite or Sierra and with 4.1.3 or the Early Access release of 4.2

- John

SQL Developer Team

3 people found this helpful
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 12 2010
Added on Apr 14 2010
#of, #pl-sql-xml-programming, #xml
1 comment
2,526 views