Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Reports as PL/SQL Stored Procedures

unknown-762773Mar 10 2015 — edited Jan 11 2016

Would anyone be interested in a solution & framework that can create Reports generated as Text or HTML output using PL/SQL as the only programming language?

This would be useful for those developers that have skills in PL/SQL and would like to leverage the use of basic & advanced SQL & PL/SQL to create Database Stored Reports and call them within any preferred front-end or back-end technology.

In case those who are familiar with RPT/RPF (Oracle's first reporting language, prior to Oracle Reports), my company had in the past created a PL/SQL Parser that converted RPT/RPF programs into PL/SQL Stored Procedures for a number of global customers to modernize their applications and convert such legacy applications into 10gR2/11gR2 stored procedures and deploy these reports within the Oracle database itself.

The solution consists of the same framework that we had used for the conversion of RPT/RPF into PL/SQL Stored Procedures, and requires one to follow a systematic structure of your Stored Procedure (Report), which would include definition of parameters, variables, cursors, functions, procedures, output layout arrays, and the main report processing logic.

A Package that does the necessary formatting & printing, called from each PL/SQL Stored Report, will also be provided as part of the solution, that is called from various points within your own report to handle the report generation process.

The benefits that this solution offers include:

  • Leverage existing expertise within your current environment for those resources having skills in SQL & PL/SQL, instead of having to learn new reporting languages, syntax, tools/IDE operation, etc.
  • Reports in HTML output allows further enhancement that can be made in the future to use HTML/HTML5 Tags, CSS/CSS3, JavaScript, jQuery, etc,and create a rich interactive report, if required.
  • Easy integration with server-side packages that can email and transfer the output files to the relevant users or folder locations on specific servers for further action.
  • Easy integration with front-end technologies such as Oracle Forms, Apex, ADF, .Net, JSP, PHP, Formspider, etc, that can display the generated output within a new browser window upon completion of the report generation task.
  • Easy integration with server-side Java stored procedures, if required, to perform complex processing within your report.
  • Easy integration with Open Source or 3rd party Print Routing technologies that can send reports output directly to designated printers.
  • Easy integration with PDF tools/utilities to produce a PDF report from the original report output.

Kindly provide your comment or directly email to bob.gill@longbridge.biz to register your interest in such a solution.

Comments

Karthick2003
945400 wrote:
create index ts_portfolio_dm.instrument_ref_dim_indx1 on ts_portfolio_dm.instrument_reference_dim(instrument_type_dvd, revenue_source_dvd, pricing_dt, instrument_dim_key);

when I execute this command the process is taking too long to create an index .is there any additional coding that can help create index fast.
The time taken to create an index depends largely on the the volume of data in the table and number of columns being indexed.
fjfranken
Hi

You could add the "NOLOGGING" option.
This might speed it up a bit, because no redo logging is written for the index-creation. But it is only a small improvement

Your statement would then look like: 
CREATE INDEX ts_portfolio_dm.instrument_ref_dim_indx1
   ON ts_portfolio_dm.instrument_reference_dim (instrument_type_dvd
,                                               revenue_source_dvd
,                                               pricing_dt
,                                               instrument_dim_key) nologging ;
Maybe also add the PARALLEL clause to force parallel execution. But for the remainder I agree with the other reply. It is simply the amount of data that makes the time here
948403
Hi ,

thanks for the reply .

could you please give me the statement that contains both

1.parllel creation 2.no logging.

Is there any further improvements that can be done on this .
The table is very huge and lot of procedures use this .Hence the reson we are not able to allow the statement to execute for more time .
cheers,
P
1 - 3

Post Details

Added on Mar 10 2015
#active, #database-ideas, #database-ideas-ideas
2 comments
917 views