Skip to Main Content

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.

Inserting to a clob field using cx_Oracle via a stored procedure

615703Jan 2 2008 — edited Jun 5 2009
Hello,

Does anyone have experience using cx_Oracle to call a stored procedure which inserts to a clob field? I have found examples of doing this via straight SQL but I was hoping it was possible to do this via a stored procedure call. Thanks for any help you can provide.

Jason

Comments

EdStevens

In that undelimited string of input data, what is the 'id', what is the 'sid', and what is the 'date'?

Frank Kulash

Hi, @user-iap38
Whenever you have a problem, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include a CREATE TABLE statement and a complete data file, including any special cases you may need to handle, and as much of the control file as you can write. Always give your full Oracle version (e.g. 18.4.0.0.0).
in SQL loaded that csv file contains the data as below format
1111320220124
1121420220124
Is this really a csv file? There aren't any commas. Are you saying the csv file looks like this
A,1111320220124,1.6
B,1121420220124,.9
and the second column (always exactly 13 digits) needs to be broken into three columns (a three-digit NUMBER, a 2-digit NUMBER and a DATE)?

BluShadow

CSV = Character Separated Values
Your data = No characters separating anything.
Regardless, you can use position format in your SQL*Loader control file to specify the exact positions and sizes of each "field" in the data.
SQL*Loader Field List Reference (0 Bytes)

selvi m

i have updated with actual code and details
Thanks

BluShadow

So, now you've updated it (which makes our answers look out of place, so don't do that, just add comments with further information if you must)... what's not working with the control file you've created?

Frank Kulash
Answer

Hi, User_IAP38
You can use this control file:

-- SQLLOADER CTL FILE
LOAD DATA
REPLACE
INTO TABLE tmp_order
DATE FORMAT "YYYYMMDD"
TRAILING NULLCOLS
(
ID       POSITION(1:3)  INTEGER EXTERNAL(3),
SID      POSITION(4:5)  INTEGER EXTERNAL(2),
EXP_DATE POSITION(6:13) DATE    EXTERNAL(8)
)

Make sure POSITION is spelled correctly

Marked as Answer by selvi m · Jan 25 2022
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 3 2009
Added on Jan 2 2008
6 comments
19,116 views