Storing Large Volume File In CLOB Column As A String?

Oct 6, 2009

I am having problem in inserting a long file (80000 B) into CLOB cloumn in oracle database 10g.

ERROR at line 23:
ORA-06550: line 23, column 1:
PLS-00172: string literal too long

I am calling the oracle stored procedure through unix shell script as fallows.

#!/bin/ksh
cd /usr/home/dfusr/backup
i=`cat pe_proxy_master_2160.Thu.log | sed "s/'/''/g"`
sqlplus username/password@db 1> temp.log <<!
DECLARE
BEGIN
Write_Text_To_CLOB(1,'$i');
COMMIT;
END;
/

The file pe_proxy_master_2160.Thu.log is about 50000 B.

My Stored Procedure is fallows

CREATE OR REPLACE PROCEDURE Write_Text_To_CLOB (
p_id IN NUMBER
, p_clob IN VARCHAR2
)
IS

[code]....

I also tried with ojdbc5.jar and ojdbc14.jar files in class path.

View 1 Replies


ADVERTISEMENT

Need Clob Variable To Write Large Volume Of Data?

Oct 6, 2010

I am facing a problem with utl_http.write_text in my pl/sql application. My requirement is to write data of size>32k. So I used a clob variable in write_text. But still it is showing numeric or value error when the data size is above 8k.

I have read that chunked transfer encoding will work. But I couldn't find out how this is done.

View 5 Replies View Related

SQL & PL/SQL :: Insert From Large String Into CLOB

Jun 7, 2012

I'm looking for a way to insert strings larger than 40.000 characters in a CLOB-field without geting the "ORA-01461: can bind a LONG value only for insert into a LONG column".

Something like this:

insert into MyClobTable(ID,Data) values ('101','A string containing more than 40000 characters...')

The problem is that a Java-application concatinates the string from a MSSQL-DB so I don't store the string in my oracle-DB. As far as I'm aware this means I can't chop my string in pieces and use declare to put the pieces in variables, right?

Below is an example I found but I don't think I can apply it on my case, correct?

SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);

Table created.

SQL>
SQL> INSERT INTO myClob VALUES (101,null);

1 row created.

SQL>
SQL> declare
2 clob_pointer CLOB;
3 v_buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;

[Code]...

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myClob;

Table dropped.

SQL>

View 3 Replies View Related

Possible To Avoid Encoding When Storing CLOB To File System

Oct 21, 2010

I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.

I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also). I cannot use dblinks and other techniques like this. The point is - how to avoid dealing with encoding?

I mean, that in the best scenario, I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. How this can be accomplished? If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.

If this is not possible, what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?

View 5 Replies View Related

SQL & PL/SQL :: Avoid Encoding Dealing When Storing CLOB To File System?

Oct 21, 2010

I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also).I cannot use dblinks and other techniques like this.

The point is - how to avoid dealing with encoding?

I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.

what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?

View 6 Replies View Related

Client Tools :: Spool Clob Column To A Flag File

Jul 15, 2013

I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?

set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit

View 1 Replies View Related

Client Tools :: Display Clob Column In Text File In Sqlplus

Mar 30, 2012

I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file

1) Outfile file size is huge high compare then table segment data.

2) last column having extra space.

The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.

#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Execute Large (clob) Dynamic Sql By DBMS_SQL?

Aug 2, 2011

is it possible to execute large(clob) dynamic sql by DBMS_SQL .Is there any restriction like length ...

View 4 Replies View Related

Export/Import/SQL Loader :: Field In Data File Exceeds Maximum Length For CLOB Column

Jun 18, 2012

I'm loading data from text file separated by TAB and i got the error below for some lines. Event the column is CLOB data type is there a limitation of the size of a CLOB data type. The error is:

Record 74: Rejected - Error on table _TEMP, column DEST.
Field in data file exceeds maximum length

I'm using SQL Loader and the database is oracle 11g r2 on linux Red hat 5. Here are the line causing the error from my data file and my table description for test:

create table TEMP
(
CODE VARCHAR2(100),
DESC VARCHAR2(500),
RATE     FLOAT,
INCREASE VARCHAR2(20),
COUNTRY VARCHAR2(500),
DEST     CLOB,
[code]........

View 3 Replies View Related

SQL & PL/SQL :: How To Store A Large String In Oracle

Nov 20, 2009

how do i store large string in oracle.

View 19 Replies View Related

SQL & PL/SQL :: Retrieving Column Names And Storing Them

Apr 2, 2010

I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.

View 3 Replies View Related

JDeveloper, Java & XML :: Storing XML File In Server

Dec 20, 2010

My current task is to store XML file from local system ( OS :XP)into server (OS : Linux) specified location with PL/SQL procedure.

Below is the XML file,

<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
[code]......

How to complete this task.

View 6 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

Server Administration :: Size Storing JPG File In Database

Feb 13, 2012

I am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.

I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.

i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.

View 8 Replies View Related

SQL & PL/SQL :: Column Validation For Types And Storing Data For Subtypes?

Apr 3, 2010

I have following structures:

SQL> CREATE TYPE address_typ AS OBJECT (line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));
2 /

Type created.
SQL> CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));
2 /
Type created.
SQL> CREATE TYPE phone_list_typ AS TABLE OF phone_typ;

[code]...

My questions are:

1. I have created an object table person_obj of parent type. I have not created object tables of salesperson_typ and customer_typ and want to store data related to both in person_obj. What is the difference between storing data in an object table of parent type and object table of subtypes? In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?

2. Second question is regarding validating the column values before the creation of object instance. Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0. I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose? Is there any other alternate way?

View 4 Replies View Related

PL/SQL :: Storing File In Blob Object And Publishing Content As Email?

Jun 14, 2013

Oracle Database Version: 11.2.0.2.0

We have a requirement below:

The basic requirement is to send an email to the user about order information, with specific subject and the corresponding e-mail body.

Here the E-mail body is very exhaustive, so we are asked to store the content of the e-mail in a word document and store this word document in a tables column of type BLOB.

Once we store this file in the BLOB column, when we send the e-mail to the customer we need to publish the content of the file in the e-mail body and send the e-mail to the user (This E-mail body has some dynamic content as well, that needs to be generated on the fly). This e-mail sending part is done by the shell script.

How can i publish the content of the BLOB object in the e-mail body.

View 9 Replies View Related

SQL & PL/SQL :: Remove Last Comma End Of String And Load Clob Data Into Table

Aug 29, 2012

To remove the last comma end of string and load the Clob data into table. create table test(name clob)

View 2 Replies View Related

Value Too Large For Column MSG

Mar 9, 2011

1. When querying the "alert_log" table I created from the alert log using the script below, 2 new files were created ALERT_LOG_30499.bad and ALERT_LOG_30499.log.

The ALERT_LOG_30499.log. contains this error message:

error processing column MSG in row 2910 for datafile /u02/damistst/admin/bdump/alert_damistst.log
ORA-12899: value too large for column MSG (actual: 82, maximum: 80)

the ALERT_LOG_30499.bad , so far, only contains datafile resize information. The datafiles have plenty of space and there is plenty of space on the San slice the datafiles reside.

2. then each time I recreate the table and increased the increased the varchar2 size, the "actual" size will also increase in the log file.

error processing column MSG in row 2910 for datafile /u02/damistst/admin/bdump/alert_damistst.log ORA-12899: value too large for column MSG (actual: 92, maximum: 90)

3. When I increased the varchar2 size to 120+ it gave me this error message:

[oracle@tds_dw bdump]$ cat ALERT_LOG_30715.log

LOG file opened at 03/09/11 14:46:20

Field Definitions for table ALERT_LOG
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

MSG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader

TABLE DDL:

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_damistst.log')
)
reject limit 1000;

**** QUESTION
I can still query the alert_log table in sqlplus, but those log and bad files are generated, is this an issue?

example of a piece of the results from " select * from alert_log; "

MSG
--------------------------------------------------------------------------------
Thread 1 advanced to log sequence 5254 (LGWR switch)
Current log# 1 seq# 5254 mem# 0: /tds_oradata/redo01a.log
Current log# 1 seq# 5254 mem# 1: /u02/damistst/REDO_LOGS/redo01b.log
Thread 1 cannot allocate new log
Checkpoint not complete
Current log# 1 seq# 5254 mem# 0: /tds_oradata/redo01a.log
Current log# 1 seq# 5254 mem# 1: /u02/damistst/REDO_LOGS/redo01b.log
Wed Mar 9 14:33:09 2011
Thread 1 advanced to log sequence 5255 (LGWR switch)
Current log# 2 seq# 5255 mem# 0: /tds_oradata/redo02a.log
Current log# 2 seq# 5255 mem# 1: /u02/damistst/REDO_LOGS/redo02b.log

13076 rows selected.

View 7 Replies View Related

SQL & PL/SQL :: Getting Error / Value Too Large For Column

Jul 29, 2011

I am using trim function in my select query. But still I am getting white space in my output. because of this, I am getting the error "value too large for column... " when I load the data into a table through sqlloader.

define APPName="&1"
set heading off;
set verify off;
set newpage 0
set feedback off;
set rtrimspool on;
set termout off;
set pagesize 40000;

[code].....

View 3 Replies View Related

Adding Column To Large Table

Aug 12, 2013

I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?

View 2 Replies View Related

SQL & PL/SQL :: Data Of Column Is Large - How To Insert

Oct 9, 2013

I have encountered some problems in SQL I want to create a table with a bunch of prepared data. For ease of use, I choose to generate a SQL file which contains all the sql clauses used to create the table and insert the data. So all the data can only be inserted to a table using sql clause.

My questions:
1) If data of a column is large (for example, 1 M text), how to insert it using SQL, is there a piecewise method.
2) And how can I insert BLOB data using SQL clause.

What I what is to enclose all the operations in a single SQL file, and when the table is needed, just execute this SQL file.

View 2 Replies View Related

Oracle SQL - Query A Large Library File?

Dec 1, 2008

I'm currently new to Oracle 10g SQL and I need to query a large library file.I need to find the different first keywords associated with references written by authors who have more than 10 references in the readings table.

View 4 Replies View Related

SQL & PL/SQL :: ORA-12899 / Value Too Large For Column (actual / 2 - Maximum / 1)

Feb 14, 2013

While creating the external Table, i am getting error.

ORA-12899: value too large for column PRC_REC_TYPE (actual: 2, maximum: 1)

But while checking the CSV file, i found that prc_rec_type is having one 1 length value. I am uploading the csv file also.

-- Create table

create table ET_PGIT_POL_RISK_COVER
(
prc_pol_no VARCHAR2(60),
prc_end_no_idx VARCHAR2(22),
prc_sec_code VARCHAR2(12),
prc_risk_id VARCHAR2(12),
prc_smi_code VARCHAR2(12),

[code].....

View 23 Replies View Related

SQL & PL/SQL :: Export Large Number Of Records From Select Into Text File

Feb 9, 2011

I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?

DECLARE
fileid UTL_FILE.file_type;
BEGIN
fileid := UTL_FILE.fopen ('VRS_CEM', 'cust.txt', 'W');
UTL_FILE.put ( fileid, 'IMSI|MSISDN|CONTRACT');
UTL_FILE.NEW_LINE(fileid);
[code]....

View 8 Replies View Related

Server Administration :: Large Number Of Trace File Generation In Bdump

May 7, 2010

We are facing one issue on one of the database. The database is generating large trace files(14000) from last two days. That consumes around 15G space on the disk. And the content of the trace files is not having any meaningful message to debug:

cat /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc

*** TRACE DUMP CONTINUES IN FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***
Dump file /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc

*** TRACE DUMP CONTINUED FROM FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***

... (Many lines with above message)

The alert log is having one repeated error yesterday:

Thu May 6 22:00:03 2010
Errors in file /apps/oracle/admin/fs90uat/bdump/fs90uat_j000_11811.trc:
ORA-12012: error on auto execute of job 2647927
ORA-04063: ORA-04063: package body "ORACLE_OCM.MGMT_DB_LL_METRICS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "ORACLE_OCM.MGMT_DB_LL_METRICS"
ORA-06512: at line 1

The corresponding trace file is having error:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/product/10.2.0/db_1
System name: SunOS
Node name: corpqadb30
[Code] .......

View 2 Replies View Related

XML DB :: Clob Column To XML

Apr 26, 2013

I have table like-

CREATE TABLE C2X_TEST(NAME VARCHAR2(100),XML_CLOB CLOB,XML_XML XMLType);

here in XML_CLOB column xml files are stored.

Now i want to update XML_XML column with corresponding XML_CLOB value .

View 8 Replies View Related

Export/Import/SQL Loader :: ORA-12899 / Value Too Large For Column XXXX (actual / 51 Maximum / 50)

Apr 10, 2013

i am exporting / importing from 10.2.0.4.0 to 11.2.0.3.0 but while doing import some of rows are rejected ...

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column XXXX (actual: 51, maximum: 50)
Column 1 264
Column 2 123432
Column 3
Column 4 7

[code]....

having looked at data in source system i cant see see character "Â" in the column 11 i think this is what causing issue !!why is oracle adding this character to this field ? how can i fix this ? without having to modify table in new system to allow more characters?

View 11 Replies View Related

SQL & PL/SQL :: Clob To Flat File?

Nov 20, 2012

Just wanted to export a clob field to .txt file, the maximum length of the clob field exceeds the limit 32767. So only partial data is exported to flat file. is there any way to export the entire data available in clob field irrespective of the size or lenght.

Length of the clob is 301829

l_file := UTL_FILE.fopen('LOCALDIR', '3.txt', 'w', 32767);
LOOP
DBMS_LOB.read (v_text_exp, l_amount, l_pos, l_buffer);
UTL_FILE.put(l_file, l_buffer);
l_pos := l_pos + l_amount;
END LOOP;

View 14 Replies View Related

SQL & PL/SQL :: Pass The File Into CLOB?

Nov 9, 2012

Is it possible to pass the file into CLOB column in real-time without DIRECTORY in oracle sp. I have not come across but would like to know is there anyway to pass the file directly from .net to oracle.

View 2 Replies View Related

PL/SQL :: Write Clob Or Xml To A File?

Jul 9, 2013

I have read the following article:[URL] 11070I want to know wherer if there exists a possibility of write a clob or an xml into a file on disk, if we do not have the CREATE ANY DIRECTORY privilege. Many functions, like UTL_FILE.FOPEN, or dbms_xslprocessor.clob2file, or dbms_xmldom.writetofile, need an Oracle directory to be created (with CREATE OR REPLACE DIRECTORY...). But if we don't have this privilege, is there a possibility to export a clob into a file as xml (the clob contains 100% xml, but this is the column data type, CLOB) if we don't have that privilege?The clob data contains 48200 characters.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved