[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle Report in ms word output

Posted on 2012-08-23
34
Medium Priority
?
866 Views
Last Modified: 2012-08-27
Experts has anyone worked on a report to get the output in word?

http://www.dba-oracle.com/t_create_word_doc_oracle_data.htm

INSERT INTO my_docs (name, doc)
VALUES ( p_file_name, empty_blob())
RETURN doc INTO v_blob;

Have a confusion. is p_file_name the report name? ANy help appreciated. If any other solutions are available. Thanks in aadvance
0
Comment
Question by:anumoses
  • 18
  • 16
34 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38324989
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325008
only one user will be using this report. Any details for com automation?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325012
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Author Comment

by:anumoses
ID: 38325036
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325041
>>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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325048
>>I tried the com automation

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

Author Comment

by:anumoses
ID: 38325069
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325097
>>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
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325118
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325129
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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325138
For BFILENAME

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

what is value? here in this context?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325148
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325163
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325200
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325221
Blob is only for images. I want that to be a doc right? If so I need to change table definations.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325250
>>Blob is only for images

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

Author Comment

by:anumoses
ID: 38325294
OK
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325306
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325331
>>ORA-00932: inconsistent datatypes: expected BLOB got FILE

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

Author Comment

by:anumoses
ID: 38325340
CREATE TABLE MY_DOCS
(
  NAME  VARCHAR2(200 BYTE)                      NOT NULL,
  DOC   BLOB                                    NOT NULL
)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325355
You aren't answering my questions:  What does BFILENAME return?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38325363
I know the answers to these questions.  I'm trying to get you to figure this out for yourself.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325942
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?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38325978
What does BFILENAME return?
What is the data type of doc in my_docs?

doc
datatype of doc in my_docs is  BLOB
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38326187
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38326245
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38326315
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38326333
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38326378
>>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
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38326461
Based on your previous questions I have a feeling that creating an actual Word doc using COM automation is probably above your skill set and if this is required, I would strongly suggest you hire a consultant to write this for you.

Depending on the exact requirement:  You might be able to create an HTML file and open that in Word.

Copy the following text and save it as a file like: q.htm

The open Word, then open q.htm.

<html>

	<p>
		<b>This is an html file.</b>
	</p>
	<p>
		It was opened in word.
	</p>
</html>

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 38326498
This is an html file.
It was opened in word.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38326508
coming back to my report query, will I have to create a hidden parameter to generate this htm
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38326542
>>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?
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38337569
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question