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  |
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 #446148 is a reply to message #446145] |
Fri, 05 March 2010 13:52  |
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 #446172 is a reply to message #446160] |
Fri, 05 March 2010 15:52 |
 |
Barbara Boehmer
Messages: 9088
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 #446305 is a reply to message #446178] |
Sun, 07 March 2010 14:52 |
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)
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 #446309 is a reply to message #446308] |
Sun, 07 March 2010 16:12 |
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( "ddmmyyyy" NULLIF INV_DT = '00000000',
INV_NUM_ID position(99:118),
INV_RECV_DT position(119:126) DATE( "ddmmyyyy" NULLIF INV_RECV_DT = '00000000',
PMT_AMT position(127:139),
SRVC_END_DT position(140:147) DATE( "ddmmyyyy" NULLIF SRVC_END_DT = '00000000',
SRVC_START_DT position(148:155) DATE( "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 |
|
Barbara Boehmer
Messages: 9088
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 |
|
Barbara Boehmer
Messages: 9088
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 |
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
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:22:13 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
