Sql Loader - oracle - sql

My table column type is clob. Field value contain double quotes
id,text_clob,date
1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"
can you kindly help loader query. i cant use replace in clob field . Its a 20GB data so i cant do any manual replace

Related

How to use like operator with CLOB data in Oracle sql?

In 1 table, i have data stored in CLOB format. In sql i want to write a query to check whether that CLOB column contains any strings of data from Employee ID, which is coming from another table.

How to store Japanese character in oracle DB?

I want to store Japanese(or any language) character in one column of my oracle database table.
I have used varchar2 as data type. when I am trying to insert this character(ꏕ) to this column its stored as '[]'.
Don't know what to do. Need help.
Note:
I tried to change the data type to nvarchar2 still didn't work.
https://translate.google.co.in/?hl=en&tab=wT#auto/en/Kinj%20A%20%CC%88%EA%8F%95%20%C9%82%20%C8%82%20%C4%82%20%E6%82%A9%0A%ED%82%A9%20%C3%84%20%EA%8F%95%20%C9%82%C8%82%C4%82%20%E6%82%A9

SQL Error: ORA-02017: integer value required

I am trying to create a table in Oracle 11g. This is a backup table of already existing table which has NVARCHAR2(382.5) column in that.
But, when I am trying to create another backup table using create command, I am getting this error -
SQL Error: ORA-02017: integer value required
02017. 00000 - "integer value required"
*Cause:
*Action:
This is my create statement,
CREATE TABLE "MYSCHEMA"."BACKUPTABLE"
(
INPUT_FILE_NAME NVARCHAR2(382.5)
);
Why is that table was already created with that datatype and now its not allowing?
There is something else at play here. An NVARCHAR column requires an integer parameter. You cannot have a fraction of a character.
The common sense is how the alphabetical figure can be in fraction. Is there any example?
Your already created table should have example data in that field.
If you want to create backup table then you can use follows:
create table <name_for_backup_table> as select * from <raw_table>
So that all the columns of the table to get the correct types. And not have to write another query to copy raw data.

Converting LONG to CLOB in INFORMATICA

I am getting below error when i run the informatica mapping, which has a LONG column in source and target column has data type of CLOB. I am trying to use TO_LOB and TO_CLOB, but not helping. In source qualifier in infa mapping there is no LONG or CLOB, so I have used data type in Source qualifier as string, text, nsting and ntext for this column, but still throwing the same error.
ORA-00932: inconsistent datatypes: expected CLOB got LONG
Let me know if you have come across this kind of error, converting from LONG to CLOB.
Thanks
Kriti
If you read reference for TO_LOB function http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions205.htm#SQLRF06134
then you see it works only in INSERT ... SELECT ... statements. You can't convert LONG into CLOB on fly. The only way is to replace the table with new one with CLOB used instead of LONG.
However there is workaround from Tom Kyte using PL/SQL function reading by rowid LONG into VARCHAR2(32767). See here https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1037232794454
But it does not work for views where you can't read by rowid. You need to write another more complex function to accept PK columns to read row inside function.

Cannot export table from PL/SQL developer having a column datatype as CLOB

I need to export table having a column datatype as clob.
I need to have inserts scripts for that table. But I get following error when I try to export the table from PL/SQL developer
Table XYZ contains one or more CLOB columns. Cannot export in SQL format, use PL/SQL Developer format instead
Is there a way I can export tables with CLOB column datatype using plain SQL scripts?
As of the latestest version of SQLDeveloper you can try out xml export, this at least worked for me for CLOBs.
I know that topic is old but maybe someone in future need this:
When You export a blob field use this
dbms_lob.substr( blob_field , 3999, 1) blob_field
where blob_field is your cblob

Resources