Jeg tror at det er lidt svært med sql*plus alene ;-)
Skurk:
Det du spørger til, er ikke helt ligetil. Først skal vi kigge på din version af oracle ?
I 8.0.x og derunder hedder det Context Kører du f.eks. 8.1.x skal du kigge under Intermedia i din manual. Er det en 9.x version, hedder det Oracle Text eller Ultra search.
Det er ret komplekst - og kræver en del for at det kører ordenligt. Oracle kan ikke læse en word fil uden et filter der kan oversætte dokumentet for dig. Det er f.eks. en af de ting intermedia gør. Udover det sørger optionen også for at du kan lave MEGET hurtige fritekstsøgninger.
Det tog mig en hel dag at få sat det ordenligt op - ved at følge manualen, så det er ikke noget man "bare" lige gør.
Ønsker du derimod at indsætte en ganske almindelig notepad txt fil - er det en helt anden snak. Det er ret nemt - og kan faktisk klares fra sqlplus :-)
Alt efter hvordan din tabel ser ud, kan du bruge dbms_lob pakken, eller læse filen ved at bruge utl_file.
Her er et doc der fortæller om utl_file : The following is a simple example demonstrating how to set up your environment, and includes a small PL/SQL procedure that writes out to an operating system flat file. 1. Edit the initSID.ora file. The UTL_FILE_DIR parameter must be added to the init<SID>.ora file. This parameter should only be listed once. If there are numerous directory paths, then all paths should be listed with commas or spaces separating them. Note that the wildcard (*) can be used. However, setting 'UTL_FILE_DIR = *' makes any directory accessible to the UTL_FILE functions and should only be used with great caution. Oracle does not recommend using the '*' on a production system. Examples: UTL_FILE_DIR = /u05/home/output/mydir, /output or UTL_FILE_DIR = * ** VERY IMPORTANT ** the Oracle instance must be brought down and back up for the changes in the init.ora file to be effective. 2. Write a PL/SQL procedure. The following is a test table named testtab: Name Null? Type ------------------------------- -------- ---- C1 NUMBER C2 NUMBER The following is the data in the testtab table: C1 C2 ---------- ---------- 10 25 20 50 Example: CREATE OR REPLACE PROCEDURE test1 IS file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file col1 NUMBER; -- C1 retrieved from testtab table retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file BEGIN -- Open file to write into and obtain its file_handle. file_handle := UTL_FILE.FOPEN('/u05/home/output/mydir','myfile.txt','W'); -- Write a line of text out to the file. UTL_FILE.PUT_LINE(file_handle, 'this is line 1 as a test'); -- Select the c1 from the testtab table where empno = 7900. SELECT c1 INTO col1 FROM testtab WHERE c2 = 25; -- Using PUTF write text with the col1 argument out to the file. UTL_FILE.PUTF (file_handle, 'This is the c1 %s when the c2 is %s.\n', col1,'25'); -- Close the file. UTL_FILE.FCLOSE(file_handle); -- Open the same file to read from. file_handle := UTL_FILE.FOPEN('/u05/home/output/mydir','myfile.txt','R'); -- Read a line from the file. UTL_FILE.GET_LINE (file_handle, retrieved_buffer); -- Print fetched line out to the SQL*Plus prompt. DBMS_OUTPUT.PUT_LINE(retrieved_buffer); -- CLose the file. UTL_FILE.FCLOSE(file_handle); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('no_data_found'); UTL_FILE.FCLOSE(file_handle); WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH'); UTL_FILE.FCLOSE(file_handle); WHEN UTL_FILE.READ_ERROR THEN DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR'); UTL_FILE.FCLOSE(file_handle); WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR'); UTL_FILE.FCLOSE(file_handle); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('other stuff'); UTL_FILE.FCLOSE(file_handle); END; / 3. Compile your PL/SQL procedure and run it. 4. The following is a listing of the /u05/home/output/mydir directory before running the PL/SQL program: total 6 -rw-r--r-- 1 usupport dba 2279 Dec 27 16:52 test.sql The following is a listing of the /u05/home/output/mydir directory after running the PL/SQL program: total 8 -rw-r--r-- 1 osupport dba 62 Dec 27 16:53 myfile.txt -rw-r--r-- 1 usupport dba 2279 Dec 27 16:53 test.sql The following are the contents of the myfile.txt file: this is line 1 as a test This is the c1 10 when the c2 is 25. The following is output to the SQL*Plus screen: SQL> execute test1 this is line 1 as a test PL/SQL procedure successfully completed.
Tak for dit svar pnielsen, jeg har endnu ikke helt fået det til at virke, men du har ledt mig i den rigtige retning
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.