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.