Home » RDBMS Server » Server Utilities » SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) (Oracle 10G RAC v10.2.0.1 Linux)
SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446143] Fri, 05 March 2010 12:52 Go to next message
adminme
Messages: 31
Registered: May 2006
Member
I successfully loaded a record into table with BFILE data type. However I don't understand how to verify if I did this correctly. I am also suspicious of the value FSSLPB//I. FSSLPB is the directory object name. When I delete the record from the table the BFILE column value defaults to (null) so it seems the table is initialized for accepting bfiles.

I am trying to:

1. Load an external LOB into a staging table using SQLLDR, then
2. Using PL/SQL insert the ext LOB into a BLOB column from the staging table to an end user table (or application table).


I am overwhelmed with reading docs and not getting the complete picture for different approaches to loading into Oracle. Your timely guidance is appreciated.

thanks
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446145 is a reply to message #446143] Fri, 05 March 2010 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Your timely guidance is appreciated.
Guidance with what exactly.

Please realize that we don't have your tables.
We don't have your data.
We don't have your requirements.

I'm not sure the following will get you any closer to a solution,
but sqlldr could be replaced using EXTERNAL TABLE.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446148 is a reply to message #446145] Fri, 05 March 2010 13:52 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
thanks- currently I want to verify the ext LOB has loaded properly into the staging table and that i can "legally" select the BFILE column and insert it into the BLOB column.

Hope I did this properly for you.

Scenario: PDF files will be stored in the directory object's file path. SQLLoader ksh script runs from /ap01/fss/loader/util_inv/ to insert into them and each related record of data into a database row in fx_lpb_inv_stg_2.
Two subsequent procedures will be used one of which will insert the PDF image into the data table fx_spt_doc. A ColdFusion application will display the PDF file.

Directory Object:  FSSLPB ==> /ap01/fss/loader/util_inv/image

Create staging table...

CREATE TABLE FX_LPB_INV_STG_2
(
  IMPORT_SEQ_NUM  NUMBER                        NOT NULL,
  IMPORT_DT       DATE                          NOT NULL,
  ACCT_NUM        VARCHAR2(20 CHAR),
  EF_NUM_ID       VARCHAR2(22 CHAR),
  IMG_NAME        VARCHAR2(50 CHAR),
  INV_DT          DATE,
  INV_NUM_ID      VARCHAR2(20 CHAR),
  INV_RECV_DT     DATE,
  PMT_AMT         NUMBER(13,2),
  SRVC_END_DT     DATE,
  SRVC_START_DT   DATE,
  INV_IMG         BFILE,
  MIME_TYP_TXT    VARCHAR2(20 BYTE),
  USER_ID         CHAR(8 BYTE)
)
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


Prompt Privs on TABLE FX_LPB_INV_STG_2 TO FSS_APPS;
GRANT DELETE, INSERT, SELECT, UPDATE ON  FX_LPB_INV_STG_2 TO FSS_APPS;


Create data table with BLOB...

CREATE TABLE FX_SPT_DOC
(
  SPT_DOC_SEQ_NUM  NUMBER,
  TYP_CD           VARCHAR2(40 CHAR),
  FILE_NAME        VARCHAR2(150 CHAR),
  FILE_DATA        BLOB,
  FILE_TMSTMP      DATE,
  MIME_TYP_TXT     VARCHAR2(30 CHAR),
  CHG_USER_ID      VARCHAR2(8 CHAR),
  CHG_TMSTMP       DATE,
  MASTER_SEQ_NUM   NUMBER,
  XACTION_TYP_CD   VARCHAR2(10 CHAR)
)
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE FX_SPT_DOC ADD (
  CONSTRAINT FXXSPDPK
 PRIMARY KEY
 (SPT_DOC_SEQ_NUM));


SQL Loader Control File...

Load infile '/ap01/fss/loader/util_inv/pgm/LPB_TST_EXPORT.dat' insert into table fx_plsql_adm.fx_lpb_inv_stg_2
  (
   IMPORT_SEQ_NUM             SEQUENCE(MAX,1),
   IMPORT_DT                  SYSDATE,
   ACCT_NUM                   position(01:20),
   EF_NUM_ID                  position(21:40),
   IMG_NAME                   FILLER,
   INV_DT                     position(91:98) DATE(8) "ddmmyyyy" NULLIF INV_DT = '00000000',
   INV_NUM_ID                 position(99:118),
   INV_RECV_DT                position(119:126) DATE(8) "ddmmyyyy" NULLIF INV_RECV_DT = '00000000',
   PMT_AMT                    position(127:139),
   SRVC_END_DT                position(140:147) DATE(8) "ddmmyyyy" NULLIF SRVC_END_DT = '00000000',
   SRVC_START_DT              position(148:155) DATE(8) "ddmmyyyy" NULLIF SRVC_START_DT = '00000000',
   INV_IMG                    BFILE (CONSTANT "FSSLPB", IMG_NAME),
   MIME_TYP_TXT               position(163:177)
   )



External data file record for test...


 ACCT1234567890123456EFNUM123456789012345IMGNAME123456789012345678901234567890123456789012312022010INVNUM12345678901234210220100000000 0003251802201016022010wxy.pdfapplication/pdf


SQL Loader log file output...

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 5 12:39:28 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   /ap01/fss/loader/util_inv/pgm/sqlload_util_inv.ctl
Data File:      /ap01/fss/loader/util_inv/pgm/LPB_TST_EXPORT.dat
  Bad File:     /ap01/fss/loader/util_inv/pgm/LPB_TST_EXPORT.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table FX_PLSQL_ADM.FX_LPB_INV_STG_2, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IMPORT_SEQ_NUM                                            SEQUENCE (MAX, 1)
IMPORT_DT                                                 SYSDATE
ACCT_NUM                             1:20    20           CHARACTER
EF_NUM_ID                           21:40    20           CHARACTER
IMG_NAME                             NEXT     1           CHARACTER
  (FILLER FIELD)
INV_DT                              91:98     8           DATE ddmmyyyy
    NULL if INV_DT = 0X3030303030303030(character '00000000')
INV_NUM_ID                         99:118    20           CHARACTER
INV_RECV_DT                       119:126     8           DATE ddmmyyyy
    NULL if INV_RECV_DT = 0X3030303030303030(character '00000000')
PMT_AMT                           127:139    13           CHARACTER
SRVC_END_DT                       140:147     8           DATE ddmmyyyy
    NULL if SRVC_END_DT = 0X3030303030303030(character '00000000')
SRVC_START_DT                     148:155     8           DATE ddmmyyyy
    NULL if SRVC_START_DT = 0X3030303030303030(character '00000000')
INV_IMG                           DERIVED                 BFILE
    Arguments are:
        CONSTANT 'FSSLPB'
        IMG_NAME
MIME_TYP_TXT                      163:177    15           CHARACTER


Table FX_PLSQL_ADM.FX_LPB_INV_STG_2:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  10688 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Mar 05 12:39:28 2010
Run ended on Fri Mar 05 12:39:28 2010

Elapsed time was:     00:00:00.39
CPU time was:         00:00:00.01



SQL> describe fx_lpb_inv_stg_2;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 IMPORT_SEQ_NUM                            NOT NULL NUMBER
 IMPORT_DT                                 NOT NULL DATE
 ACCT_NUM                                           VARCHAR2(20 CHAR)
 EF_NUM_ID                                          VARCHAR2(22 CHAR)
 IMG_NAME                                           VARCHAR2(50 CHAR)
 INV_DT                                             DATE
 INV_NUM_ID                                         VARCHAR2(20 CHAR)
 INV_RECV_DT                                        DATE
 PMT_AMT                                            NUMBER(13,2)
 SRVC_END_DT                                        DATE
 SRVC_START_DT                                      DATE
 INV_IMG                                            BINARY FILE LOB
 MIME_TYP_TXT                                       VARCHAR2(20)
 USER_ID                                            CHAR(8)

SQL> select * from fx_lpb_inv_stg_2;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus


Attachment: dat file with test record data


mod-edit: code tags added; next time please add them yourself



[Updated on: Fri, 05 March 2010 14:17] by Moderator

Report message to a moderator

Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446150 is a reply to message #446143] Fri, 05 March 2010 13:55 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Additionally, Oracle is in the OS group which has read privileges to the images and data file.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446152 is a reply to message #446150] Fri, 05 March 2010 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
00678, 0, "Column or attribute type can not be displayed by SQL*Plus\n"
// *Cause:  The type specified is not supported.
// *Action: Rewrite the query to select the data with types that
//          SQL*Plus supports.


Simply put, SQ*plus can not display LOB data; so do "Action" above.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446156 is a reply to message #446152] Fri, 05 March 2010 14:25 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
I suspected that SQL PLus would say that if it was an img or graphic which leads me to believe the load worked correctly.

Is it even possible to confirm this with SQL Plus? If so- how?

Can you describe the values/ meaning of the BFILE column value FSSLPB//I (NoExist) please.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446160 is a reply to message #446143] Fri, 05 March 2010 14:37 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member

Could you comment on loading external LOB directly into a BLOB column?

If not, thanks anyway.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446163 is a reply to message #446160] Fri, 05 March 2010 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
http://asktom.oracle.com has many fine coding examples.

You are NOT the 1st person to do this.

GOOGLE is your friend, but only when you use it.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446165 is a reply to message #446163] Fri, 05 March 2010 14:56 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
my life - 2 hours
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446172 is a reply to message #446160] Fri, 05 March 2010 15:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
adminme wrote on Fri, 05 March 2010 12:37

Could you comment on loading external LOB directly into a BLOB column?


Yes. If you ultimately want it to end up in a blob column, then you might as well load it there directly and skip the bfile column. Here is a simplified example that you should be able to adapt. In this example, I have two files (banana.pdf and cranberry.pdf) in the c:\oracle11g directory. I also have a sample.dat file that lists those file names. I have created a table and loaded those files into a blob column in the table. I have then demonstrated how to check the length of the blob and how to use Oralce Text to search and display some of the data within the blobs, so that you can see that it is loaded and accessible.

-- sample.dat:
banana,banana.pdf,
cranberry,cranberry.pdf,


-- create table:
SCOTT@orcl_11g> CREATE TABLE recipe_tab
  2    (name	  VARCHAR2 (11),
  3  	ext_fname VARCHAR2 (40),
  4  	recipe	  BLOB)
  5  /

Table created.


-- test.ctl:
LOAD DATA
INFILE sample.dat
INTO TABLE recipe_tab
FIELDS TERMINATED BY ','
(name           CHAR (10),
ext_fname      "'c:\oracle11g\' || :ext_fname",
"RECIPE"       LOBFILE (ext_fname) TERMINATED BY EOF)


-- load data:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log


-- check length of blob column:
SCOTT@orcl_11g> SELECT name, DBMS_LOB.GETLENGTH (recipe)
  2  FROM   recipe_tab
  3  /

NAME        DBMS_LOB.GETLENGTH(RECIPE)
----------- --------------------------
banana                          222824
cranberry                       202959


-- demonstrate display of excerpts from blob column
-- using Oracle Text to show snippets of document
-- surrounding the searched phrase "fruit of the month":
SCOTT@orcl_11g> CREATE INDEX test_idx
  2  ON recipe_tab (recipe)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN key_words_in_context FORMAT A45 WORD_WRAPPED
SCOTT@orcl_11g> SELECT name,
  2  	    CTX_DOC.SNIPPET
  3  	      ('test_idx',
  4  	       ROWID,
  5  	       'fruit of the month')
  6  	      AS key_words_in_context
  7  FROM   recipe_tab
  8  WHERE  CONTAINS (recipe, 'fruit of the month') > 0
  9  /

NAME        KEY_WORDS_IN_CONTEXT
----------- ---------------------------------------------
banana      <b>Fruit of the Month</b>
            Banana
            Bananas are the most popular

cranberry   <b>Fruit of the Month</b>
            Cranberries
            Cranberries grow on vines


SCOTT@orcl_11g>

[Updated on: Fri, 05 March 2010 15:54]

Report message to a moderator

Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446177 is a reply to message #446143] Fri, 05 March 2010 17:15 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Barbara:

Thank you for your time. I will try this straight away.

May I ask if the ext lob must be stored in an oracle directory. I am using an application server where my files are in a separate file system than Oracle.

Thank you- I will follow up with you.
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446178 is a reply to message #446177] Fri, 05 March 2010 17:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
One of the advantages to using SQL*Loader is that the data to be loaded does not have to be on the server; it can be on the client. SQLLDR can be executed from the operating system, so anything that can be accessed from there can be loaded, such as the data in your .dat file and your .pdf files. Note that I concatenated an operating system directory path with the file name and did not use an Oracle directory object.




Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446305 is a reply to message #446178] Sun, 07 March 2010 14:52 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Barbara:

Thank you for the direction. My oracle tools are accessed through a network NAL and I do not see the oracle directories as I used to. We use sqlldr on our linux application servers that is where the client programs are installed.

I did attempt your scenario from that server (not my local machine)and used the directory object name (in UCASE) in lieu of 'c:\oracle11g\' and received the following error.

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Mar 7 14:26:55 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting ), found "fname".
"RECIPE" LOBFILE(ext fname) TERMINATED BY EOF)


This is the control file:
LOAD DATA
INFILE sample.dat
INTO TABLE recipe_tab
FIELDS TERMINATED BY ','
(name           CHAR(10),
ext_fname       "'FSSLPB' || :ext_fname",
"RECIPE"        LOBFILE(ext fname) TERMINATED BY EOF)
Originally I did not receive an error inserting into a BFILE column using SQLLDR i just couldn't verify the data. I am currently using DBMS_LOB procedures in an attempt to read/convert/insert the BFILE into a BLOB column. I am also reviewing permissions.

Would you work with me on running sqlldr from the application server?

thank you

[EDITED by LF: applied [code] tags]

[Updated on: Sun, 07 March 2010 14:56] by Moderator

Report message to a moderator

Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446307 is a reply to message #446305] Sun, 07 March 2010 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you are missing an underscore between "ext" and "fname".
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446308 is a reply to message #446305] Sun, 07 March 2010 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ext_fname "'FSSLPB' || :ext_fname",

"RECIPE" LOBFILE(ext fname) TERMINATED BY EOF)

Regards
Michel


Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446309 is a reply to message #446308] Sun, 07 March 2010 16:12 Go to previous messageGo to next message
adminme
Messages: 31
Registered: May 2006
Member
Michel;

Well that certainly fixed it. It ran successfully but output isn't happening.

SQL> SELECT name, DBMS_LOB.GETLENGTH(recipe) FROM recipe_tab;

NAME DBMS_LOB.GETLENGTH(RECIPE)
----------- --------------------------
banana 7370
cranberry 7390

SQL>
SQL> CREATE INDEX test_idx ON recipe_tab (recipe) INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL>
SQL> EXEC CTX_DOC.SET_KEY_TYPE('ROWID');

PL/SQL procedure successfully completed.

SQL>
SQL> COLUMN key_words_in_context FORMAT A45 WORD_WRAPPED
SQL>
SQL> SELECT name,
2 CTX_DOC.SNIPPET
3 ('test_idx', ROWID, 'fruit of the month')
4 AS key_words_in_context
5 FROM recipe_tab
6 WHERE CONTAINS (recipe,'fruit of the month') > 0;

no rows selected



When I apply to my application's control file I get the following error:


SQL*Loader: Release 10.2.0.1.0 - Production on Sun Mar 7 15:55:40 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 14.
Expecting ), found ",".
INV_IMG LOBFILE(CONSTANT "FSSLPB", IMG_NAME),
^



Here is the control file;

Load infile '/ap01/fss/loader/util_inv/pgm/LPB_TST_EXPORT.dat' insert into table fx_plsql_adm.fx_lpb_inv_stg_b
(
IMPORT_SEQ_NUM SEQUENCE(MAX,1),
IMPORT_DT SYSDATE,
ACCT_NUM position(01:20),
EF_NUM_ID position(21:40),
IMG_NAME FILLER,
INV_DT position(91:98) DATE(Cool "ddmmyyyy" NULLIF INV_DT = '00000000',
INV_NUM_ID position(99:118),
INV_RECV_DT position(119:126) DATE(Cool "ddmmyyyy" NULLIF INV_RECV_DT = '00000000',
PMT_AMT position(127:139),
SRVC_END_DT position(140:147) DATE(Cool "ddmmyyyy" NULLIF SRVC_END_DT = '00000000',
SRVC_START_DT position(148:155) DATE(Cool "ddmmyyyy" NULLIF SRVC_START_DT = '00000000',
INV_IMG LOBFILE(CONSTANT "FSSLPB", IMG_NAME),
MIME_TYP_TXT position(163:177)
)
Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446312 is a reply to message #446309] Sun, 07 March 2010 19:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
The fact that you got lengths greater than 0 (7370 and 7390) for your recipe column indicates that it did work. Since I did not supply you with my banana.pdf and cranberry.pdf files, I assume that you substituted something of your own. You did not get any results from your text search because apparently whatever you used did not have the phrase "fruit of the month" in it. Try replacing that phrase with a word or phrase that you would expect in whatever documents you used for your pdf files.

You have then gone back to using the same syntax in your control file that previously did not work for you, instead of trying to apply my example. Try using exactly the syntax that I did, being careful to watch for things like underscores and colons as pointed out by others. Start with what you ran that worked and modify one little thing at a time, retesting after each modification. Just get it to load into three columns like I did, then you can add the other columns using positions. Just substitute your actual directory paths and file names and table name and column names for the ones in my example. Since I used a full directory path, not an oracle directory object, you should do the same. Be careful of things that are case sensitive. When in doubt, use the same case, upper or lower, as I did.

If you like, I can demonstrate how to load your data into a bfile column. However, that is not what you need to be doing. You should be loading it into a blob column, so you should go back to trying to do that. It would be a whole lot more hassle, more code, and much less efficient to load into a bfile column first, then try to get it from there to a blob column.

Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446316 is a reply to message #446309] Sun, 07 March 2010 20:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8586
Registered: November 2002
Location: California, USA
Senior Member
Since you seem to be having trouble adapting the example, I thought I would substitute some of your names for mine to make it clearer. In the process, I discovered that my original example was apparently reading another copy of banana.pdf and cranberry.pdf in Oracle's default directory path instead of the directory path that I concatenated to the file name. Apparently, the directory path must be specified in the data file and cannot be concatenated to it. I hope that is something that you can obtain. In the following example, I have created directories and file names and table name and column names more similar to yours, to try to make it clearer. I copied my banana.pdf to wxy.pdf and that is the file I am loading. In your sample data, I am guessing that was the name of your file and you appeared to be attempting to load the wrong thing, some sort of image name, instead of the file name. I used positions instead of delimiters like you were doing, since you have a fixed format file, not a delimited one.

If obtaining the directory path in the .dat file is a problem, then you can load the .dat file into a table, then spool it back out, concatenating the directory path, then use that as your .dat file.

-- C:\ap01\fss\loader\util_inv\pgm\LPB_TST_EXPORT.dat:
some data C:\ap01\fss\loader\util_inv\pgm\wxy.pdf more data


-- create table:
SCOTT@orcl_11g> CREATE TABLE fx_spt_doc
  2    (file_name    VARCHAR2 (150),
  3  	file_data    BLOB)
  4  /

Table created.


-- test.ctl:
LOAD DATA
INFILE 'C:\ap01\fss\loader\util_inv\pgm\LPB_TST_EXPORT.dat'
INTO TABLE fx_spt_doc
(file_name    POSITION (11:49),
"FILE_DATA"  LOBFILE (file_name) TERMINATED BY EOF)


-- load data:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log


-- check length of blob column:
SCOTT@orcl_11g> COLUMN file_name FORMAT A40
SCOTT@orcl_11g> SELECT file_name, DBMS_LOB.GETLENGTH (file_data)
  2  FROM   fx_spt_doc
  3  /

FILE_NAME                                DBMS_LOB.GETLENGTH(FILE_DATA)
---------------------------------------- -----------------------------
C:\ap01\fss\loader\util_inv\pgm\wxy.pdf                         222824


-- demonstrate display of excerpts from blob column
-- using Oracle Text to show snippets of document
-- surrounding the searched phrase "fruit of the month":
SCOTT@orcl_11g> CREATE INDEX test_idx
  2  ON fx_spt_doc (file_data)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN key_words_in_context FORMAT A35 WORD_WRAPPED
SCOTT@orcl_11g> SELECT file_name,
  2  	    CTX_DOC.SNIPPET
  3  	      ('test_idx',
  4  	       ROWID,
  5  	       'fruit of the month')
  6  	      AS key_words_in_context
  7  FROM   fx_spt_doc
  8  WHERE  CONTAINS (file_data, 'fruit of the month') > 0
  9  /

FILE_NAME                                KEY_WORDS_IN_CONTEXT
---------------------------------------- -----------------------------------
C:\ap01\fss\loader\util_inv\pgm\wxy.pdf  <b>Fruit of the Month</b>
                                         Banana
                                         Bananas are the most popular


SCOTT@orcl_11g>

[Updated on: Sun, 07 March 2010 21:09]

Report message to a moderator

Re: SQL Loader BFILE - Column Pointer Value is: FSSLPB//I (NoExist) [message #446478 is a reply to message #446316] Mon, 08 March 2010 14:29 Go to previous message
adminme
Messages: 31
Registered: May 2006
Member
Barbara:

You were right! I did not have the text 'fruit of the month' in my image file as you suspected was the problem. I did replicate your example yesterday and today w/o using 'c:\...' and it worked fine but I could not read the image data in the recipe column.. Here is the output with the newly inserted text 'fruit of the month'.

Below, I have included my implementation (thanks to everyone's input) from the Linux environment. I did change the BLOB's file name reference position from file_name POSITION (11:49) to file_name position (50:57) as the actual image was in the later of the 2. I have used Barbara's examples to demonstrate.


LOAD DATA
INFILE sample.dat
INTO TABLE recipe_tab
FIELDS TERMINATED BY ','
(name CHAR(10),
ext_fname ":ext_fname",
"RECIPE" LOBFILE(ext_fname) TERMINATED BY EOF)


SQL> SELECT name, DBMS_LOB.GETLENGTH (recipe) FROM recipe_tab;

NAME DBMS_LOB.GETLENGTH(RECIPE)
----------- --------------------------
banana 7417
cranberry 7438

SQL>
SQL> CREATE INDEX test_idx
2 ON recipe_tab (recipe)
3 INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID');

PL/SQL procedure successfully completed.

SQL>
SQL> COLUMN key_words_in_context FORMAT A45 WORD_WRAPPED
SQL> SELECT name,
2 CTX_DOC.SNIPPET
3 ('test_idx',
4 ROWID,
5 'fruit of the month')
6 AS key_words_in_context
7 FROM recipe_tab
8 WHERE CONTAINS (recipe, 'fruit of the month') > 0;

NAME KEY_WORDS_IN_CONTEXT
----------- ---------------------------------------------
banana BANANA.PDF test image file <b>fruit of the
month</b> Banana


cranberry CRANBERRY.PDF test image file <b>fruit of the
month</b> Cranberry



SECOND EXAMPLE:


SQL> CREATE TABLE fx_spt_doc_2
2 (file_name VARCHAR2 (150),
3 file_data BLOB);

Table created.

SQL> commit;

Commit complete.


SQL> describe fx_spt_doc_2;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(150)
FILE_DATA BLOB



SQLLDR CTL FILE #1:

CHMOD 777 *.dat

LOAD DATA
INFILE 'C:\ap01\fss\loader\util_inv\pgm\LPB_TST_EXPORT.dat'
INTO TABLE fx_spt_doc_2
(file_name POSITION (11:49),
"FILE_DATA" LOBFILE (file_name) TERMINATED BY EOF)


HOST BOX: RHORADEV01
INSTANCE/DB: DEVA


RHORADEV01 SQLLDR fx_plsql_adm/***** CONTROL=test_2.ctl LOG=test_2.log
ksh: RHORADEV01: not found

DEVA SQLLDR fx_plsql_adm/***** CONTROL=test_2.ctl LOG=test_2.log
ksh: SQLLDR: not found

SQLLDR fx_plsql_adm/***** CONTROL=test_2.ctl LOG=test_2.log
ksh: SQLLDR: not found


LOAD DATA
INFILE LPB_TST_EXPORT_2.dat
INTO TABLE FX_SPT_DOC_2
(file_name position (50:57),
"FILE_DATA" LOBFILE (file_name) TERMINATED BY EOF)


sqlldr fx_plsql_adm/plsql4dev@deva \
control=/ap01/fss/loader/util_inv/pgm/test_2.ctl, \
data=./LPB_TST_EXPORT_2.dat, \
bad=./test_2.bad, \
log=./test_2.log


SQL*Loader: Release 10.2.0.1.0 - Production on Mon Mar 8 13:56:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1



SQL> SELECT file_name, DBMS_LOB.GETLENGTH (file_data) FROM fx_spt_doc_2;

FILE_NAME
--------------------------------------------------------------------------------
DBMS_LOB.GETLENGTH(FILE_DATA)
-----------------------------
wxy.pdf
7571


SQL>
SQL> CREATE INDEX test_idx
2 ON fx_spt_doc_2 (file_data)
3 INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL> COMMIT;

Commit complete.

SQL> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID');

PL/SQL procedure successfully completed.

SQL> COLUMN key_words_in_context FORMAT A45 WORD_WRAPPED
SQL> SELECT file_name,
2 CTX_DOC.SNIPPET
3 ('test_idx',
4 ROWID,
5 'wxy.pdf')
6 AS key_words_in_context
7 FROM fx_spt_doc_2
8 WHERE CONTAINS (file_data, 'wxy.pdf') > 0;

FILE_NAME
--------------------------------------------------------------------------------
KEY_WORDS_IN_CONTEXT
---------------------------------------------
wxy.pdf
FOR BAMS- EFNUM 0928063
File name: <b>wxy.pdf</b>



Thank you both so much for you guidance. I couldn't have figured all this out without your help.

Much appreciated!

adminme





Previous Topic: Database Installation using ASM in DBCA???
Next Topic: export data from excel to oracle_10g
Goto Forum:
  


Current Time: Wed Sep 28 10:58:01 CDT 2016

Total time taken to generate the page: 0.10043 seconds