Hi,
I have a requirement wherein I need to get BLOB ID or the BLOB handle in
select statement that create an insert script. This is part of the data
migration activity that I am testing.
For example: If the table structure is as follows:
PFD_REF_NO VARCHAR(15) Not Null
DOC_NAME VARCHAR(80) Not Null
FILE_NAME VARCHAR(255) Not Null
DOC_DESC BLOB segment 80, subtype TEXT Nullable
FILE_SIZE VARCHAR(10) Nullable
UPLOADED_BY INTEGER Nullable
DATE_UPLOADED TIMESTAMP Nullable
LOCKED CHAR(1) Nullable
CONSTRAINT INTEG_30:
SELECT 'insert into PROJECT_DOCS (PFD_REF_NO, DOC_NAME, FILE_NAME, DOC_DESC,
FILE_SIZE, UPLOADED_BY, DATE_UPLOADED, LOCKED) values ('||''''||
TRIM(PFD_REF_NO) ||''''||','||''''|| REPLACE(TRIM(DOC_NAME), '&','N')
||''''||','||''''|| TRIM(FILE_NAME) ||''''||','||''''|| TRIM(CAST(DOC_DESC AS
VARCHAR(655))) ||''''||','||''''|| REPLACE(TRIM(COALESCE(FILE_SIZE,'')), ',',
'.') ||''''||','|| COALESCE(UPLOADED_BY,'''''') ||',
TO_TIMESTAMP('||''''||TRIM(COALESCE(DATE_UPLOADED,''))||''''||','||'''DD-MON-YYYY
HH24:MI:SS:FF'||''')'||','||''''|| TRIM(COALESCE(LOCKED,'')) ||''''||');' FROM
PROJECT_DOCS;
when i run the above query from ISQL, it works. However, in the statement:
TRIM(CAST(DOC_DESC AS VARCHAR(655))) will return value only if the
CHARACTER_LENTTH(DOC_DESC) is less than 32000. For text beyond this it would
truncate and does not work.
But when I run a simple query after executing SET BLOBDISPLAY OFF; at ISQL ,
the select statement returns the BLOB ID or the handle like 8d:106
Where is this ID / handle stored? How to read it in a query from ISQL.
Any help is much appreciated.
Thanks & Regards
Ramana.
