Discussion :

[chiheb]

Salut tt le monde
j'ai à ecrire une procedure dans une forme Forms 6i, dans laquelle je fais appel à une colonne de type BLOB pour en extraire le contenu .
j'ai déclaré une variable de type BLOB , comme suit :
declare
v_blob BLOB;
begin
SELECT colonne INTO v_blob FROM table WHERE id = 1;
dbms_lob.OPEN(v_blob, dbms_lob.lob_readonly);

alors j'ai cette erreur , implementation restriction : DBMS_LOB.LOB_READONLY : cannot directly access remote package variable or cursor

[plabrevo]

Sans doute une limitation du moteur PL de forms6.

Je pense qu'un contournement possible est d'utiliser une variable intermediaire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
x := dbms_lob.lob_readonly; 
dbms_lob.open(?,x);

[chiheb]

salut
en fait , lob_readonly est une constante qu'on peut pas appeler au niveau forms , mais cela dois se faire au niveau de la base dans un package ,je l'ai essayé et ça marche
mais le probleme persiste toujours , je trouve toujours pas le moyen de lire mon document word à partir de oracle forms !!!

[SheikYerbouti]

Passez à Forms 9i ou mieux encore 10g.
Vous pourrez utiliser la librairie Webutil pour extraire et afficher le contenu de vos colonnes BLOB.

[chiheb]

trop tard , le client pour lequel je travaille a oracle 8i et forms 6i pour son application , j'y peux rien

[SheikYerbouti]

Dans ce cas, il faut utiliser les container OLE de Forms6i.

d'ailleurs, comment les données binaires ont-elles été insérées dans le colonnes BLOB ?

[chiheb]

avec le container OLE de forms , l'etape de la sauvegarde du doc dans la colonne Blob est faite , mais le sens contarire , la consultation à partir de la base vers forms m'est devenu difficile à faire

[SheikYerbouti]

Il existe une API pour "jouer" les fichiers stockés dans les container OLE.
Cela doit être quelque part dans la doc.

[chiheb]

quel doc ? oracle !

[SheikYerbouti]

La doc Forms.

[SheikYerbouti]

Je me demande s'il ne suffit pas de faire un double-clic sur le container pour en ouvrir le contenu...

[plaineR]

Je me demande s'il ne suffit pas de faire un double-clic sur le container pour en ouvrir le contenu...

Cela dépend de ce que tu as mis dans la propriéte "OLE Activation Style"

[chiheb]

ya un truc dans forms qui s'appele Forms_OLE , vous pensez que çafera l'affaire !!

[plaineR]

Cela me semble plus simple de gérer cela avec un container OLE... Tu n'as rien à faire cela fonctionne tout seul.

Peux-tu nous dire ce qui te bloque ?

[chiheb]

est ce que je peux avoir une forme forms dans laquelle il y a un champ basée de type BLOB qui ramene le contenu d'une colonne de type blob

[chiheb]

supposons que j'ai pu avoir le contenu de la colonne BLOB , et le texte du document est affiché en entier sur le champ BLOB , Comment je peux faire pour affecter le contenu du BLOB , le texte entier dans un autre champ , comment en extraire le contenu ?

[chiheb]

le probléme du BLOB coté oracle forms est heureusement resolu grace à notre ami PlaineR , me reste maintenant l'imression avec oracle reports 6i , c'est faisable déja ou pas ?

[plaineR]

Oui c'est faisable, mais il y a de nombreuses restrictions. Et là je n'ai pas de démo, parce que j'ai abandonné

[plaineR]

Voici une liste des limitations que j'avais trouvé dans une note du metalink :

1. Problem:
-----------------
Client/Server mode using OLE2 to read in a MS Word document will work as long
as the output is sent to the previewer but will not work if DESTYPE is set to
file. That is, an OLE object embedded in the report will display as an empty
box or nothing at all when the report is generated to a PDF, HTML, or RTF file.

Solution:
-------------
This is because RTF, HTML and PDF formats do not support OLE.
References:
Bug:993753 is a documentation bug filed on this issue
Bug:674287 WORD-OLE OBJECTS ARE LOST IN RTF, HTML AND PDF FILES.

On-Line Docs for HTML/PDF restrictions:
OLE objects are ignored when generating PDF or HTML, but space is
reserved for these objects in the output.

On-Line Docs for Web links and Web reports restrictions:
OLE objects are not supported in reports output to HTML or PDF files.

2. Problem:
-----------------
You are unable to read a BLOB that is a binary MS Word document from the
database and have it display in a report in Client/Server mode. This works
if you use a link file to display the document; however, if you try to query
BLOB and change field to OLE2, then it does not work. The documentation states
that this should work. Is this a bug?

Solution:
-------------
This is not a bug. The only way you will be able to select a BLOB column is
if the MS Word (OLE2)object was saved to database using Oracle Forms. Loading
document using a procedure or SQL*Loader will not work. Essentially when Forms
stores an OLE object in the database, it 'wraps' it with some further information
which Reports uses when it reads the image back. If this wrapper is not there,
then Reports does not think that it is really an OLE object.

References:
Bug:1852910 Selecting BLOB column with MS Word document returns blank in previewer

3. Problem:
-----------------
You have created a report with an embedded OLE object. Steps used to embed
object are as follows:
- open the Layout Model.
- choose the button OLE2 Object.
- select the radio button "Create from File"
and select a Word document with some Text.

When running the report using Reports Builder (rwbld60)or Runtime (rwrun60)
to printer or printing from previewer/screen, the OLE object is printed.

However, trying to run the report over a Reports server to printer, the selected
MS-Word document text is missing in the report. Only an empty square appears
on your printed paper.

It does not matter how the OLE object is referenced (ie., using formula column
with pl/sql or just using OLE icon).

Solution:
-------------
You are running into Bug:1268176 which is fixed in 6.0.8.14.0. This is Oracle
Reports 6i with Patch 5. This is available for download off Metalink.

4. Problem:
-----------------
Can you access a MS Word document stored in BLOB column in database from
Oracle Reports on the web?

Solution:
-------------
No. Storing MS Word document in database as BLOB and embedding this into
reports will *not* work.

5. Problem:
-----------------
What alternatives are there to be able to display a MS Word document when
run reports on web?

Solution:
-------------
The alternatives are as follows:

1) The best approach is to store the documents on a file system (or Internet
File Server) and use hyperlinks to pull the appropriate MS Word documents back
to the browser. This will display the Word document in MS Word itself.

Example of hyperlink method
--------------------------------------------
Create a simple report mstest_hyper.rdf based on:
select 'click_here' from dual

In the field's property palette, add the following in the hyperlink section:
file:///D|/temp/mstest.doc
(This assumes that your Word document resides in d:\temp directory)

Run report using syntax:
http://<server>:<port>/cgi-bin/rwcgi60?server=<your_repserver>+report=d:\temp\mstest_hyper.rdf+userid=scott/tiger@db+destype=cache+desformat=html

2)
Use some kind of script that pulls the document out of the database and
returns it to the browser (you may write a servlet to do this).

Note that this is before we get into the formatting issues with OLE
(most notably the page boundary problem that Microsoft has not addressed).

3) Use mimetype to specify Word as the application to bring up. See
Note:120435.1 for detailed information.

4) There is an approach that one of our customers has tried. This is not
something that we support, but want to give you all the information we have
on the topic. The method is to store MS Word documents in the rdbms as CLOBs.
Use an ISO context filter to convert the documents from Word to HTML/text and
store that in the database. The problem with this approach is that they
get created okay in Oracle Reports with fields being created based on the
background CLOB objects, but they lose pagination and formatting information
that was previously stored in the MS Word documents.

[plaineR]

Et voici une note qui explique comment utiliser les container OLE dans report :

SCOPE & APPLICATION
===================
This document is intended for new and experienced users of Oracle Reports.

STEPS TO FOLLOW
===============
1. In order to insert a company logo on a report, do the following:
a. Create the report.
b. Expand the layout model and go to View-->Layout Section-->Margin View.
c. Then click on the OLE button on the tool palette and draw a box of the
desired size in the margin area of the report.
d. A dialog box will appear stating "Insert Object." You are given two
choices to either Create New or Create from File. Assuming the company
logo is stored in an existing .bmp file on the filesystem, choose
Create From File, and then browse to locate the applicable .bmp file.
e. The image can be embedded in the report or linked. If linking is
desired, ensure that the Link Box is checked.
f. Click OK and run the report. The company logo is now displayed in the
margin area on each page of the report.

2. Another use of OLE2 objects, is to embed a Microsoft Word or Excel file in
an Oracle Report. This can be done as described in step 1 above, or by
using a formula column. The steps to using a formula column are as follows:

a. Create the SQL statement and groups in the data model.
b. Create a formula column, CF_1. Set the type=Character, set Read from
File=Yes, set File Format as OLE2, and create a PL/SQL formula as
follows:

function CF_1Formula return Char is
begin
return ('c:\orawin95\report30\tmp\mywordfile.doc');
/ * specify path and file name of the Word document */
end;

c. Expand the Layout Model, and use the ABC/text tool to add a field to
the layout. This field will contain the contents of the Word file, so
ensure that it is positioned where desired. Set the source of the ABC
field to CF_1. Set the field Print Condition to Vertical Elasticity
Variable.
d. Set the Print Condition on any enclosing Repeating or Master Frame to
Vertical Elasticity Variable as well.
e. When you run the report, the Word document will be displayed in the
report.

KNOWN PROBLEMS OR LIMITATIONS
=============================
1. The reports OLE2 object can display only a one page Word or Excel document.
If the document exceeds one page, Oracle Reports will still display only one
page. This limitation is described in Bug 658560.

A workaround to this problem is to use a text file instead of an OLE2 file.
The .txt file can be attached to the report in the same way as described
in step 2 above. However, the File Format of the Formula Column must be
set to Text File instead of OLE2. When the text file is used, the entire
contents of the file will display, even if it exceeds one page. Keep in
mind, however, that text files are limited in graphical display and any
special fonts and bolding will be lost.

2. Known bugs with OLE Objects:

Bug 807914, OLE: Cannot Open RDF File With OLE Object - problem originally
occurred in Reports Version 3.0.5.8.0 and subsequently fixed in version
3.0.5.12.0.

Causes following errors:
PDE-PER001 Internal error
REP-3335: Unhandled Internal CA Error aaaaz36

Bug 644912 OLE Object Disappears After Error Message REP-4004 AND REP-4036
in a loop.

Problem reported in Reports version 3.0.5.7 and fixed in 6.0.5.

3. OLE objects are ignored when generating PDF or HTML, but space is reserved
for these objects in the output. This information is found in the Online
Help with the key word HTML Restrictions.