CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Steve
Steve

Reputation: 551

Trying to Insert contents of text files into an Oracle table

I have about 500 Linux scripts. I am trying to insert the source code from each script into an Oracle table:

CREATE TABLE "SCRIPT_CODE" (
    "SCRIPT_NAME" VARCHAR2(200 BYTE), 
    "CODE_LINE" NUMBER(*,0), 
    "CODE_TEXT" VARCHAR2(2000 BYTE)
)

I was using a (painful) manual Excel solution. Opening each script and pasting the code into a column. I ran into difficulties and switched gears.

I decided to change the table and place the entire source code from each script into a CLOB field….

CREATE TABLE "SCRIPT_CODE_CLOB" (
    "SCRIPT_NAME" VARCHAR2(200 BYTE), 
    "CODE_TEXT" CLOB
)

Here is the Insert code that I wrote: 

set define off;
Declare Code Clob;
Script Varchar2(100);
sql_exec varchar2(1000);
Begin
Script := 'Some Script Name'
;
Code := '
[pasted code here]
'
;

sql_exec := 'INSERT INTO SCRIPT_CODE_CLOB VALUES (:1, :2)';
EXECUTE IMMEDIATE(sql_exec) USING Script, Code;
COMMIT;
End;

This was going great until I ran into a script that had 1,700 lines of code. When I pasted all the code in and ran the script, it gave me:

ORA-01704: string literal too long

I am looking for a better way of doing this. Is it possible to Import the files somehow and automate the process?

There are some external tables in the Oracle database, and I can get to the folder location that they point to.

Thanks very much for any assistance. - Steve

Environment: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production Oracle SQL Developer Version 4.0.2.15, Build 15.21

Upvotes: 0

Views: 994

Answers (1)

Chris
Chris

Reputation: 23171

In order to insert into the clob, you need to use the DBMS_LOB functions (specifically DBMS_LOB.WRITE) rather than reading it into a variable and passing that directly into your insert statement. Check out the documentation for the package. You'll need to read the data into a buffer or temporary lob and then use that in the insert.

Upvotes: 1

Related Questions