[cx-oracle-users] How to insert a Blob or Clob using cx_oracle?

[Frank v. L. <fvl...@ca...>]

I am trying to insert a file into oracle. I first do a 
base64.encodestring and then insert it. The sql string for it looks like 
the one below. If I copy this string to a sqlplus command line it is 
being inserted. However if I do it within Python using cx_oracle it 
gives me an error:
cx_Oracle.DatabaseError: ORA-00911: invalid character

The python code can be found below and below that the table definition. 
  I tried both blob and clob but neither works. What is the proper way 
to insert (text) files and pickled objects in Oracle using cx_oracle? In 
MySQL I use the mediumtext object, and the approach below does not give 
any problems.


INSERT INTO 
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type) 
VALUES ('myOwnRequestID0','fvlingen','c21hbGwgc3BlYw==
','KGRwMQpTJ0ZhaWx1cmUnCnAyClMnUmF0aW9CYXNlZCcKcDMKc1MnSm9icy5Bc3NpZ25tZW50Jwpw
NApTJ0V2dFNpbmdsZUNvbnQnCnA1CnNTJ1JlcXVlc3QuQ29tcGxldGVuZXNzJwpwNgpTJ0V2dFBl
cmNlbnRhZ2UnCnA3CnNTJ0FsbG9jYXRpb24uQXNzaWdubWVudCcKcDgKUydFdnRCYXNlZEFsbG9j
YXRpb24nCnA5CnNTJ0FsbG9jYXRpb24uQ29tcGxldGVuZXNzJwpwMTAKZzcKcy4=
','KGRwMQpTJ0ZhaWx1cmUnCnAyCihkcDMKUydyZXF1ZXN0UmF0aW8nCnA0CkYwLjE0OTk5OTk5OTk5
OTk5OTk5CnNTJ21pbkpvYnMnCnA1Ckk0CnNTJ293bmVyUmF0aW8nCnA2CkYwLjE0OTk5OTk5OTk5
OTk5OTk5CnNzUydSZXF1ZXN0LkNvbXBsZXRlbmVzcycKcDcKKGRwOApTJ3RyZXNob2xkJwpwOQpG
MC41OTk5OTk5OTk5OTk5OTk5OApzc1MnQWxsb2NhdGlvbi5Db21wbGV0ZW5lc3MnCnAxMAooZHAx
MQpnOQpGMC42OTk5OTk5OTk5OTk5OTk5Ngpzcy4=
','event');


import cx_Oracle
orcl = cx_Oracle.connect('user/passwd')
curs = orcl.cursor()
sqlStr='INSERT INTO ws_request(string_id,owner,request_type) VALUES 
("myOwnRequestID0","fvlingen","event");'
sqlStr=" INSERT INTO 
ws_request(string_id,owner,workflow_spec,policies,parameters,request_type) 
VALUES 
('myOwnRequestID0','fvlingen','c21hbGwgc3BlYw==','KGRwMQpTJ0ZhaWx1cmUnCnAyClMnUmF0aW9CYXNlZCcKcDMKc1MnSm9icy5Bc3NpZ25tZW50JwpwNApTJ0V2dFNpbmdsZUNvbnQnCnA1CnNTJ1JlcXVlc3QuQ29tcGxldGVuZXNzJwpwNgpTJ0V2dFBlcmNlbnRhZ2UnCnA3CnNTJ0FsbG9jYXRpb24uQXNzaWdubWVudCcKcDgKUydFdnRCYXNlZEFsbG9jYXRpb24nCnA5CnNTJ0FsbG9jYXRpb24uQ29tcGxldGVuZXNzJwpwMTAKZzcKcy4=','KGRwMQpTJ0ZhaWx1cmUnCnAyCihkcDMKUydyZXF1ZXN0UmF0aW8nCnA0CkYwLjE0OTk5OTk5OTk5OTk5OTk5CnNTJ21pbkpvYnMnCnA1Ckk0CnNTJ293bmVyUmF0aW8nCnA2CkYwLjE0OTk5OTk5OTk5OTk5OTk5CnNzUydSZXF1ZXN0LkNvbXBsZXRlbmVzcycKcDcKKGRwOApTJ3RyZXNob2xkJwpwOQpGMC41OTk5OTk5OTk5OTk5OTk5OApzc1MnQWxsb2NhdGlvbi5Db21wbGV0ZW5lc3MnCnAxMAooZHAxMQpnOQpGMC42OTk5OTk5OTk5OTk5OTk5Ngpzcy4=','event');"


curs.execute(sqlStr)


CREATE TABLE ws_request
      (
      allocations            integer default 0,
      completed_events       integer default 0,
      end_event              integer default -1,
      id                     integer,
      CONSTRAINT cons_ws_request_id primary key(id),
      failed_jobs            integer default 0,
      owner                  varchar2(255),
      parameters             clob,
      policies               clob,
      priority               integer default 0,
      processed_jobs         integer default 0,
      request_type           varchar2(100) not null,
      CONSTRAINT cons_ws_request_request_type CHECK (request_type IN 
('event', 'file')),
      run_number             integer default 0,
      start_event            integer default 0,
      status                 varchar2(100) default 'new',
      CONSTRAINT cons_ws_request_status CHECK(status IN 
('new','active','finished','failed')),
      string_id              varchar2(100) not null,
      workflow_spec          clob,
      CONSTRAINT cons_ws_request_string_id unique(string_id)
      );

CREATE SEQUENCE ws_request_seq
start with 1
increment by 1
nomaxvalue;

CREATE TRIGGER ws_request_autoincrement
BEFORE INSERT ON ws_request
FOR EACH ROW
     DECLARE m_no INTEGER;
BEGIN
     SELECT ws_request_seq.nextval INTO :new.id FROM dual;
END;
.
run;

Any help would be appreciated.

Frank


-- 
--------------------------------------
Frank van Lingen
California Institute of Technology
CA 91125 Pasadena
United States
--------------------------------------
Mail Code:356-48
bld      :340 Lauritsen
email    :fvl...@ca...
im(aim)  :marcellus0872
tel      :(+1) 626 395 3862
mobile   :(+1) 310 968 5584
url      :http://www.van-lingen.name/
--------------------------------------