Gemmer Word dokumenter i DB

Et script som kan kaldes direkte fra SQLPlus?
- Eller fra f.eks. Access eller C++ ?

SQLPlus

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 :-)

pnielsen >> Jeg vil gerne se opskriften, selvom det bare er med en almindelig ASCII tekstfil.

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.

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.

pnielsen >> Tak for info!

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