Let's say we have the following data,
<rowset>
<row>
<data>A</data>
</row>
<row>
<data>B</data>
</row>
...
...
<row>
<data>E</data>
</row>
</rowset>
And we want to print them in the following way:
A B
C D
E
- With using of MS Word basic method (Format->columns), it will print the data first down and then across. Like,
A D
B E
C
- I found something workaround to resolve this issue.
1. Create one table with two columns.
2. Create another table with one row and column in column1 and second table in column2
See below:
Output:
Friday, October 26, 2007
Thursday, August 16, 2007
Friday, August 10, 2007
Easiest way to insert image into oracle database
I have had requirement to store some signature files of USERs in oracle database. So we can display that signature on Letter when we generate reports from BI Publisher. I tried two methods and both worked fine as expected.
Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
Once file is loaded you can see data length is displayed as 4664
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).
Method 1 (Standard way):
First we need to create directory on the database server to store files on it.
command:
Create or replace directory as TEMP_DIR '<database server path>/temp'
Now copy all the files to that directory.
Sql>create table reader(reader_id varchar2(10),signature BLOB);
Sql>Insert into reader (reader_id) values ('01');
Create Procedure to update table with signature file.
CREATE OR REPLACE procedure user_signature(v_id IN number)
IS
BEGIN
DECLARE
v_bfile bfile := BFILENAME('TEMP_DIR', 'sign.JPG'); --first parameter in all caps, second is case sensitive
v_lob blob;
BEGIN
select signature into v_lob from reader where reader_id = v_id for update;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.CLOSE(v_bfile);
commit;
END;
END;
Now run it with sql plus
SQL> exec user_signature('01')
or in TOAD (sql editor)
begin
user_signature('01');
end;
Method 2 (Easiest way): Need TOAD !!!
Select table from schema browser and click on data tab.
Double click on signature data (HugeBlob) (Hint: Not all cap in BLOB data)
Now you'll see following window. See data length is displayed as zero.
At the top-left corner of window you can see "Load a File" icon. Just click on it and it will ask you for file location.
Once file is loaded you can see data length is displayed as 4664
(Hint: now BLOB data dispalys as in all caps letters i.e HUGEBLOB)
Ohhh, It's done !!! now what ?
If you want to copy file on your local hard disk which is stored in oracle database then just do double-click on BLOB data and click on "Save to File" icon at top-left side of window (second icon).
Friday, August 3, 2007
Passing Report name as Report Title
Found something interesting question here posted by Haresh.
Question :Using Desktop Publisher for Word and a standalone BIP Enterprise version, is it possible to pass the report name to the rtf template so that it could be used as the report title?
This feature would be useful when there are multiple reports (different names and slightly different queries/criteria) that use the same templates. We could then dynamically change the report title based on the report name.
Solution
Do not sure if there is any simple way to print report name in rtf template.
There is a work around. Follow below steps...
1>create parameter called Report_Name in every report
2>put default value for that parameter as report name (Title)
3>select parameter type as Hidden
Now in rtf template.
<?param@begin:Report_Name?>
<?$Report_Name?>
Done......
Question :Using Desktop Publisher for Word and a standalone BIP Enterprise version, is it possible to pass the report name to the rtf template so that it could be used as the report title?
This feature would be useful when there are multiple reports (different names and slightly different queries/criteria) that use the same templates. We could then dynamically change the report title based on the report name.
Solution
Do not sure if there is any simple way to print report name in rtf template.
There is a work around. Follow below steps...
1>create parameter called Report_Name in every report
2>put default value for that parameter as report name (Title)
3>select parameter type as Hidden
Now in rtf template.
<?param@begin:Report_Name?>
<?$Report_Name?>
Done......
Tuesday, July 24, 2007
Monday, July 16, 2007
Design reports to print on pre-printed stationary
Guys,
Here you can find good article about how to design report which will print on pre-printed stationary in EBS.
http://oracle.anilpassi.com/xml-publisher-developing-reports-printed-on-pre-printed-stationary-2.html
Here you can find good article about how to design report which will print on pre-printed stationary in EBS.
http://oracle.anilpassi.com/xml-publisher-developing-reports-printed-on-pre-printed-stationary-2.html
Friday, June 29, 2007
Formatting char/number to US style phone number
Someone asked this question on Oracle BI publisher forum and i just wondered whether it is possible with any formatting XSL function or not. I tried with format-number function but finally i got with extended XSL functions for use in RTF template
Case:
Let's say we have xml data in below format
1234567890
Now we want to display it as (123)456-7890 (US style Ph #)
Solution:
<?xdofx:’(‘||substr(Phone_No,1,3)||’) ’||substr(Phone_No,4,3)||’-‘||substr(Phone_No,7,4)?>
Case:
Let's say we have xml data in below format
Now we want to display it as (123)456-7890 (US style Ph #)
Solution:
<?xdofx:’(‘||substr(Phone_No,1,3)||’) ’||substr(Phone_No,4,3)||’-‘||substr(Phone_No,7,4)?>
Subscribe to:
Posts (Atom)