Oracle Report in ms word output

>>is p_file_name the report name?

p_file_name is the value to be inserted into the table my_docs in the name column.  That procedure takes an existing document and loads it from the file system into the table.

I don't see where it actually creates the Word doc.

I would also mention that I beleive following the COM Automation method mentioned in that link, you would need an Microsoft Office license for every potential user of the system.

only one user will be using this report. Any details for com automation?

I tried executing the above procedure

Begin

load_file_to_my_docs ('r_centers_work_schedule');

End;

ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "HBC_DATA.LOAD_FILE_TO_MY_DOCS", line 10
ORA-06512: at line 3

I tried the com automation

ERROR at line 1:
ORA-06550: line 8, column 9:
PLS-00201: identifier 'ORDCOM.CREATEOBJECT' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 28, column 9:
PLS-00201: identifier 'ORDCOM.GETPROPERTY' must be declared
ORA-06550: line 28, column 3:
PL/SQL: Statement ignored
ORA-06550: line 37, column 8:
PLS-00201: identifier 'ORDCOM.INVOKE' must be declared
ORA-06550: line 37, column 3:
PL/SQL: Statement ignored
ORA-06550: line 46, column 3:
PLS-00201: identifier 'ORDCOM.SETARG' must be declared
ORA-06550: line 46, column 3:
PL/SQL: Statement ignored
ORA-06550: line 53, column 8:
PLS-00201: identifier 'ORDCOM.INVOKE' must be declared
ORA-06550: line 53, column 3:
PL/SQL: Statement ignored
ORA-06550: line 66, column 8:
PLS-00201: identifier 'ORDCOM.INVOKE' must be declared
ORA-06550: line 66, column 3:
PL/SQL: Statement ignored
ORA-06550: line 75, column 8:
PLS-00201: identifier 'ORDCOM.DESTROYOBJECT' must be declared
ORA-06550: line 75, column 3:
PL/SQL: Statement ignored

>>I tried executing the above procedure

Did you create the 'documents' directory that is used by BFILENAME?
Do you have a file called 'r_centers_work_schedule' in that folder?

Check the docs for setting up directories:
BFILENAME:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions019.htm#SQLRF00610

Directories:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5007.htm#i2061958

>>I tried the com automation

From what code?
Did you set everything up properly?
Do you have Office installed on the database server?

CREATE TABLE MY_DOCS
(
  NAME  VARCHAR2(200 BYTE)                      NOT NULL,
  DOC   BLOB                                    NOT NULL
)
TABLESPACE PHBC_DAT1
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
LOB (DOC) STORE AS
      ( TABLESPACE  PHBC_DAT1
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
NOMONITORING;


create directory WORD_DIR
  as '/home/hbc_data/juggler'
   


GRANT READ ON DIRECTORY WORD_DIR TO public
GRANT WRITE ON DIRECTORY WORD_DIR TO public
------------------------------
CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE)
AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO my_docs (name, doc)
VALUES ( p_file_name, empty_blob())
RETURN doc INTO v_blob;
v_bfile := BFILENAME('WORD_DIR', p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);
COMMIT;
END;

This is what I did.

>>create directory WORD_DIR   as '/home/hbc_data/juggler'

I forgot you were on Unix.  You will not be able to use Word since you cannot install Office on Unix.

You can try OpenOffice.  I've never tried COM Automation with OpenOffice.

>>This is what I did.

Post the results of: ls -al /home/hbc_data/juggler

drwxrwxrwx   2 hbc_data   users         8192 Aug 22 13:25 .
drwxr-xr-x   9 hbc_data   labuser       8192 Aug 14 15:15 ..
-rw-------   1 ftpjug     users           18 Aug 20 10:31 .sh_history
-rw-r--r--   1 oracle     dba           6612 Jul 31 08:27 JUGGLER_14083.log
-rw-r--r--   1 oracle     dba           5510 Jul 24 15:24 JUGGLER_14399.log
-rw-r--r--   1 oracle     dba           6612 Jul 17 14:01 JUGGLER_15249.log
-rw-r--r--   1 oracle     dba           1102 Jul 19 08:56 JUGGLER_1805.log
-rw-r--r--   1 oracle     dba           1102 Aug 15 08:40 JUGGLER_18530.log
-rw-r--r--   1 oracle     dba          64622 Jul 25 14:38 JUGGLER_1882.log
-rw-r--r--   1 oracle     dba           1102 Aug 20 09:42 JUGGLER_19734.log
-rw-r--r--   1 oracle     dba           4408 Jul 17 15:23 JUGGLER_21540.log
-rw-r--r--   1 oracle     dba           3306 Aug  1 08:35 JUGGLER_21620.log
-rw-r--r--   1 oracle     dba           2204 Jul 12 10:08 JUGGLER_23270.log
-rw-r--r--   1 oracle     dba           1102 Jul 12 09:46 JUGGLER_23480.log
-rw-r--r--   1 oracle     dba           4408 Aug 15 14:25 JUGGLER_24002.log
-rw-r--r--   1 oracle     dba           1102 Jul 23 09:11 JUGGLER_25562.log
-rw-r--r--   1 oracle     dba           3306 Jul 25 14:20 JUGGLER_28762.log
-rw-r--r--   1 oracle     dba           3306 Aug 15 08:37 JUGGLER_4465.log
-rw-r--r--   1 oracle     dba           4408 Aug  3 11:25 JUGGLER_4624.log
-rw-r--r--   1 oracle     dba           3306 Aug 15 08:42 JUGGLER_5775.log
-rw-r--r--   1 oracle     dba          14388 Aug 15 14:33 JUGGLER_6433.log
-rw-r--r--   1 oracle     dba           1102 Jul 31 16:15 JUGGLER_657.log
-rw-r--r--   1 oracle     dba           7454 Aug 15 08:53 JUGGLER_6783.log
-rw-r--r--   1 oracle     dba          15880 Aug 22 13:31 JUGGLER_8962.log
-rw-r--r--   1 oracle     dba           4785 Aug 20 09:42 adddraws.txt
-rwxrwxrwx   1 hbc_data   users       472212 Aug 23 09:19 adp_dump.ps
-rw-r-----   1 hbc_data   users       495378 Aug  1 13:16 adp_dump.save
-rw-r--r--   1 oracle     dba         501111 Aug  2 13:35 adp_dump.txt
-rwxrwxrwx   1 hbc_data   users       501111 Aug  2 13:07 adp_dump_save1.ps
-rw-rw-rw-   1 hbc_data   users         5076 Aug 22 13:25 juggler.dat
-rw-r-----   1 hbc_data   users        11040 Feb 14  2012 juggler.log
-rw-rw-rw-   1 hbc_data   users         3752 Jul 12 08:37 juggler.sh
-rw-rw-rw-   1 hbc_data   users        45188 Aug 22 13:20 juggler.upl

See the issue?

You called the procedure like this:  load_file_to_my_docs ('r_centers_work_schedule');

The parameter is an actual file name that must exist.  You have no file called r_centers_work_schedule in that folder.

For BFILENAME

INSERT INTO my_docs (name,doc)
  VALUES (BFILENAME('WORD_DIR', 'r_centers_work_schedule.doc'));

what is value? here in this context?

>>what is value? here in this context?

Did you read to doc link for BFILENAME?

Also that insert will fail as posted.  You have two columns but are only providing one value.

For BFILENAME

INSERT INTO my_docs (name,doc)
  VALUES (name,BFILENAME('WORD_DIR', 'r_centers_work_schedule.doc'));

for name- what is the value to be inserted? That was my doubt.

>>for name- what is the value to be inserted? That was my doubt.

That is just a varchar2 in your table.  It is used to store the file name for the BLOB you are inserting.

I suggest you use the file name passed in to the procedure.

Blob is only for images. I want that to be a doc right? If so I need to change table definations.

>>Blob is only for images

Blobs are for anything binary.  Word documents are binary.  This is the correct data type.

OK

INSERT INTO my_docs (name,doc)
  VALUES ('r_centers_work_schedule',BFILENAME('WORD_DIR', 'r_centers_work_schedule.doc'));


ORA-00932: inconsistent datatypes: expected BLOB got FILE

>>ORA-00932: inconsistent datatypes: expected BLOB got FILE

What does BFILENAME return?
What is the data type of doc in my_docs?

CREATE TABLE MY_DOCS
(
  NAME  VARCHAR2(200 BYTE)                      NOT NULL,
  DOC   BLOB                                    NOT NULL
)

You aren't answering my questions:  What does BFILENAME return?

I know the answers to these questions.  I'm trying to get you to figure this out for yourself.

INSERT INTO my_docs (name,doc)
  VALUES ('r_centers_work_schedule',BFILENAME('WORD_DIR', 'r_centers_work_schedule.doc'));

.doc is the problem. So what will be my value there?

What does BFILENAME return?
What is the data type of doc in my_docs?

doc
datatype of doc in my_docs is  BLOB

>>What does BFILENAME return?

What is 'doc' in the answer to this question?

Take a look at the docs I provided.  It tells you what BFILENAME returns.

>>.doc is the problem.

No it isn't.

The problem is with trying to insert a binary file directly with an insert this way.  It will never work like this.

If it were that simple, why did Burleson need the rest of the code in the first link you provided?

The other issue is form the ls output above, you don't have a file named r_centers_work_schedule.doc unless you have created it recently.

This is a new one one that the user asked for. This report was in excel. Now the users wants it in word format. So the /home/hbc_data/juggler has no such file.

>>This is a new one one that the user asked for.

So?  The only code you have referneced in this entire question was from Burleson's website and it takes a file on the file system and loads it into a BLOB column in a table.

Nothing you have posted so far even attempts to 'create' a file.

select case when to_number(to_char(ds.schedule_date,'fmD'))= 1 then 'Sun'
            when to_number(to_char(ds.schedule_date,'fmD'))= 2 then 'Mon'
            when to_number(to_char(ds.schedule_date,'fmD'))= 3 then 'Tue'
            when to_number(to_char(ds.schedule_date,'fmD'))= 4 then 'Wed'
            when to_number(to_char(ds.schedule_date,'fmD'))= 5 then 'Thu'
              when to_number(to_char(ds.schedule_date,'fmD'))= 6 then 'Fri'
              when to_number(to_char(ds.schedule_date,'fmD'))= 7 then 'Sat'
       end as day,
         to_char(ds.schedule_date,'MM/DD/RR') schedule_date,
         --ds.start_time,
         SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2)||ds.start_ampm as start_time,
         SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2)||ds.end_ampm as end_time,
         to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
       -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90') as total_hours,
         he.first_name ||' '||he.mail_last_name as employee,
         he.position_cd,
         ds.reason_code,
                   decode(he.bilingual,'Y','B') as bilingual,
                   ds.department_id,
                   d.department_name
  from dept_staff ds,
       department d,
         hbc_employees he,
         not_avail_reason nar  
 where ds.department_id = d.department_id
   and ds.payroll_id = he.payroll_id
   and ds.reason_code = nar.reason_code
   and ds.schedule_date between :p_start_date and :p_end_date
   and d.area_cd = 'IN'
   and d.department_id not in (19,25,91)
   and pay_hours_flag = 'Y'
   and punch_required_flag = 'Y' and schedule_required_flag = 'Y'
   order by d.department_name,
            ds.schedule_date,
            to_date(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2)||ds.start_ampm || 'M','HH:MIAM'),
            start_time,
            he.mail_last_name

-------------------------

This is a query that produced report in excel format
Now the user want if we could do a word format. This I do not know. When I was searcing in google I got Burleson's link.

>>When I was searcing in google I got Burleson's link.

That's fine but the code you are using from that link has the following steps:
1) Create a table to hold the MS-Word document:
2) Create a directory object which stores all your Word docs:
3) Create a procedure to store the Word doc in Oracle:

There is nothing there on actually creating the Word doc itself.  He references links that attempt to show it.  One still works.  The other is dead.


The docs are always your best friend once you know what to look for and that is the COM Automation feature.

Here is the site with Word examples.  There is a problem though:  As I mentioned before, to create a "Word" document, you need Office installed on the database server.  The examples will use this.

Since you cannot install Microsoft Office on Unix, you will need to install OpenOffice and see if you can twaek the doc examples to call OpenOffice not Word.


http://docs.oracle.com/cd/E11882_01/appdev.112/e10591/ch4plsql.htm#sthref213

This is an html file.
It was opened in word.

coming back to my report query, will I have to create a hidden parameter to generate this htm

>>will I have to create a hidden parameter to generate this htm

ummm... OK?  Is this a question for me or a comment for yourself?

thanks