Solved

insert text into a blob field

Posted on 2009-05-18
1
550 Views
Last Modified: 2012-05-07
Good morning, I am trying to replicate the northwind sample database in oracle 11.1.0.7.
The employees table is set up with 2 blob fields, one of which (NOTES)should contain a text string.
The inserts fail on this column with the error: " ORA-01465 Invalid hex number"

This is one of the insert statements:
INSERT INTO Employees(EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath) VALUES (2, 'Fuller', 'Andrew', 'Vice President, Sales', 'Dr.', TO_DATE('1952-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss'), TO_DATE('1992-08-14 00:00:00','yyyy-mm-dd hh24:mi:ss'), '908 W. Capital Way', 'Tacoma', 'WA', '98401', 'USA', '(206) 555-9482', '3457', null, 'Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.', null, 'http://accweb/emmployees/fuller.bmp');
Here is the DDL of the table:
CREATE TABLE "NORTHWIND"."EMPLOYEES" 
   (	"EMPLOYEEID" NUMBER(10,1) NOT NULL ENABLE, 
	"LASTNAME" VARCHAR2(20) NOT NULL ENABLE, 
	"FIRSTNAME" VARCHAR2(10) NOT NULL ENABLE, 
	"TITLE" VARCHAR2(30), 
	"TITLEOFCOURTESY" VARCHAR2(25), 
	"BIRTHDATE" DATE, 
	"HIREDATE" DATE, 
	"ADDRESS" VARCHAR2(60), 
	"CITY" VARCHAR2(15), 
	"REGION" VARCHAR2(15), 
	"POSTALCODE" VARCHAR2(10), 
	"COUNTRY" VARCHAR2(15), 
	"HOMEPHONE" VARCHAR2(24), 
	"EXTENSION" VARCHAR2(4), 
	"PHOTO" BLOB, 
	"NOTES" BLOB, 
	"REPORTSTO" NUMBER, 
	"PHOTOPATH" VARCHAR2(255)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "NORTWIND" 
 LOB ("PHOTO") STORE AS BASICFILE (
  TABLESPACE "NORTWIND" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) 
 LOB ("NOTES") STORE AS BASICFILE (
  TABLESPACE "NORTWIND" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
 
  CREATE INDEX "NORTHWIND"."LASTNAME" ON "NORTHWIND"."EMPLOYEES" ("LASTNAME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "NORTWIND" ;
 
  CREATE INDEX "NORTHWIND"."POSTALCODE" ON "NORTHWIND"."EMPLOYEES" ("POSTALCODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "NORTWIND" ;
 
  CREATE UNIQUE INDEX "NORTHWIND"."SYS_IL0000072054C00015$$" ON "NORTHWIND"."EMPLOYEES" (
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "NORTWIND" 
  PARALLEL (DEGREE 0 INSTANCES 0) ;
 
  CREATE UNIQUE INDEX "NORTHWIND"."SYS_IL0000072054C00016$$" ON "NORTHWIND"."EMPLOYEES" (
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "NORTWIND" 
  PARALLEL (DEGREE 0 INSTANCES 0) ;
 
  ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("EMPLOYEEID" NOT NULL ENABLE);
 
  ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("LASTNAME" NOT NULL ENABLE);
 
  ALTER TABLE "NORTHWIND"."EMPLOYEES" MODIFY ("FIRSTNAME" NOT NULL ENABLE);

Open in new window

0
Comment
Question by:ODOTServer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
ODOTServer earned 0 total points
ID: 24412920
Changed Blob to Clob.

You can close this question.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question