Running Reports with Oracle Report Server 10G using a web browser

Of course, it's possible to run Oracle reports with a simple http request.
This approach only requires Oracle Report Server installed on the machine/server and responding on its port 8889.

• Verify that you have all license rights to install Oracle Report Server


• Install a PDF reader (personally I love foxit reader)


• Verify that these environment variables are set:
  REPORTS_PATH = C:\your_reports_folder;
  PATH=C:/DevSuite10_home;C:/OLD_DevSuite9_home;etc...
  Important: Make sure your latest Dev
  Suite is on your PATH environment variable first, before any older versions!


• Start OC4J


• Start your web browser and run your report using this sintax:
  http://<reports server name>/reports/rwservlet?report=<report file name>&<other report parameters>

Example:
http://localhost:8889/reports/rwservlet?report=rep_007.rep&userid=username/password@192.168.19.58:1
521/ORCL&desformat=pdf&destype=cache&YOUR_REPORT_PARAMETER1=image.bmp&OTHER_REPORT_PARAMETERS=...

Common problems:
If your report calls other libraries, these must be in the same directory of the report, and must be compiled/recompiled with Oracle reports builder 10g.

To avoid errors like the following:

Not able to load oracle.reports.util.EnvironmentGlobal class
Using oracle.reports.util.Environment class

you must also generate/regenerate REP file using 10g Report Builder AND check whether the file/folder has enough privilege to the logged in user.
If you still have problems try to copy it to another folder: there might be problem with the acess right in your folder.

For reference:
Useful guide to Oracle Reports http://www.rittmanmead.com/files/quick_start_guide_oracle_reports.html
Using the Oracle Reports Web Service http://download.oracle.com/docs/cd/B10464_05/bi.904/b13673/pbr_webservice.htm
In the case that you can't use report server and you're forced to use something on the clients, thake a look at
http://www.orafaq.com/forum/t/37321/2/

Form Grep & Form Diff

did you ever want to find code fragments into a set of Oracle Forms?
Form Grep is the solution

I also found very useful Form Diff, a software which allows you to locate differences between 2 Forms.

EDIT
A cheaper solution to find some text into a form (*.fmb *.fmx), library (*.pll *.plx) or report (*.rdf *.rep).
Under Linux simply type:

find . | xargs grep 'string' -sl

The -s is for summary and won't display warning messages such as grep: ./directory-name: Is a directory

The -l is for list, so we get just the filename and not all instances of the match displayed in the results.

Table Partitioning, step by spep

1. log in as system

2. create tablespaces for partitioned tables:

-- data tablespace
create tablespace data_2020 datafile 'Z:\ORACLE\ORADATA\CDEP\DATA_2020.DBF'
size 25M reuse default storage (next 160k pctincrease 0);

-- indexes tablespace
create tablespace index_2020 datafile 'Z:\ORACLE\ORADATA\CDEP\INDEX_2020.DBF'
size 25M reuse default storage (next 160k pctincrease 0);

3. submit the alter tabe command for each table to be partitioned:

ALTER TABLE TABLE1 ADD PARTITION DEP_2020
VALUES LESS THAN (2021)
storage (initial 480k) TABLESPACE data_2020;

ALTER INDEX TABLE1_PK REBUILD PARTITION DEP_2020
storage (initial 400k) TABLESPACE index_2020;

Format masks 4 Oracle Reports

NGNN0 ----> 1.234 (thousands separator)

Find locks with Oracle

Most Oracle professionals use of the v$locked_object view in order to gather information about objects that are locked within the Oracle database.

Full article

How to retrieve the start day of the week

trunc(to_date('22-AUG-03'), 'DAY') would return '17-AUG-03'

How to retrieve the first day of the year

trunc(to_date('22-AUG-10'), 'YEAR') would return '01-JAN-10'

How to retrieve the first day of the month

Since there isn't a "first_day" function, we can use the trunc function:

trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'

DUP_VAL_ON_INDEX

The DUP_VAL_ON_INDEX is raised when a SQL statement attempts to create a duplicate value in a column on which a unique index exists.


begin

insert into $table_name[...]

exception

when DUP_VAL_ON_INDEX then

update $table_name[...]

or...

dbms_output.put_line('Error');

end;

Display a calendar using a select statement

Try to execute this select statement... :P

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

How to find the last day of the PREVIOUS month

--


select last_day(add_months(sysdate,-1))
from dual



--

How to determine if a column has non-numeric values

Imagine you have a table with many records.

How to determine if a column of this table has non-numeric values?

This is a quick method:

select *
from MY_TABLE
where (LENGTH (TRIM (TRANSLATE (MY_FIELD, '0123456789', ' ') ) ) is not null)