?
Solved

Passing Parameters from forms to generate report,Urgent Please

Posted on 2003-03-18
11
Medium Priority
?
4,652 Views
Last Modified: 2009-12-16
Hi...

I would like some help in a project am doing.. The project is an oracle statistics form that gives the user the ability to select first the table name they want to create the statistic from then the fields that would be included in the statistic,which will be presented in oracle reports to the user upon request.

The problem is that when we pass the table and field names to reports we want the values to be used in the reports SQL in the select and from phrase through as we think lexical refrences..to generate the statistic report However, this method is not working

let say  table name : emp
         field name : emp_id, emp_name, emp_salary

these values are to be pass from the form level to report builder and used
in the select statement that generate the report:

eg : select emp_id, emp_name, emp_salary from emp ;


as u can see the only way to do that is to pass the values in temp varibles
and use these varibles in the select statement

eg      v_1 = emp;
        v_2 = emp_id;
           
        select v_2 from v_1;

but we can't find the right code or method to perform this procedure so if you can help us or give us some tips on how to slove this problem we will be very greafull for you

please note that we need this argent coz we running on a late deadline ^_^
0
Comment
Question by:smsm63
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 

Expert Comment

by:Avotar
ID: 8164975
why not pass a string to reports..

just before calling the report you composed your string and then send it to the report.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8164978
This is from http://www.dulcian.com - Conferences,Papers&Presentations - Oracle Reports - article: Don't Build 200 Reports...Build Just One!

Lexical parameters can replace all parts of the query on which the report is based

SELECT empno,ename
FROM emp
&p_where

In the example above, the lexical parameter  p_where can replace the WHERE or ORDER BY clauses or a combination of them. The lexical parameter can be built either by the application that calls the report, or by the report itself in a program unit like an AFTER-PARAMETER-FORM trigger or a formula column.

Henka
0
 

Author Comment

by:smsm63
ID: 8165021
how can i do this  pass a string to reports..

sorry to bother u but i'm kind new to this so if u can give me some example i will be greatfull to u
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:smsm63
ID: 8165086
how can i do this  pass a string to reports..

sorry to bother u but i'm kind new to this so if u can give me some example i will be greatfull to u
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 672 total points
ID: 8165253
You can read this article from Metalink :

Invoking Oracle Reports from Oracle Forms using RUN_PRODUCT

The RUN_PRODUCT builtin is used by Oracle Forms to achieve integration with the other Developer 2000 products. This article looks at the calling of Oracle Reports from Oracle Forms. There are two methods by which an Oracle Reports  module may be accessed from an Oracle Forms form. These are:
1)Passing parameters from the calling application (Forms) to the called application (Reports).

2)Passing data records from the calling application (Forms) to the called application (Reports). This is more efficient since it reduces the need for the called product to execute a query against the database. The Shared Services Layer (SSL) will detect that data is being passed and replace the query in Oracle Reports with data from the Oracle Forms record cache (record group). The names and datatypes of the elements of the select list in the reports query must match with the query sourcing the     record group.You cannot pass a record group to a child query in Reports. Data passing only works for master queries. Child queries in Reports have a line appended to the 'where' clause containing a reference to the bind from the master query. Reports would have no way of qualifying (applying the bind) to the data coming from the record group from Forms. The objective of this article will be to create a standard Dept/Emp Form from which a user may launch an Oracle Reports module via a  button. The report will be based on the 'current department' displayed in the form. Creation of Oracle Forms objects, such as default blocks, relations and buttons is not discussed. The article concentrates on the Forms procedures required in the two scenarios described above. The first Oracle Forms procedure documents simply passing a  bind parameter to the report, in this case the current department  number from the dept block. The Reports query then binds this value in and produces the desired report.The second procedure looks at the passing of a lexical parameter, a string containing quotes. The third passes the 'where' clause of the last executed Forms query and finally, the last, looks at the method by which a record group is passed to Oracle Reports and actually replaces the Reports query. The RUN_PRODUCT builtin is adequately documented in the Oracle Forms reference manual and developers should refer to this for further information. However, for reference there are two basic types of parameters for RUN_PRODUCT:
TEXT_PARAMETERs for passing bind and lexical parameters
DATA_PARAMETERs for associations between Oracle Forms record groups and Oracle Reports queries.

1. Passing Bind Parameters
Oracle Reports Component
Create a report based on a single query.
Q_emp:
SELECT empno, sal FROM emp WHERE deptno = :departmentno ORDER BY sal

Forms Component
Create the Oracle Forms procedure pass_parameter, outlined below as a PL/SQL program unit and reference it from a when-button-pressed trigger. By default, the reports runtime parameter form will appear everytime  the report is run. Pass_parameter will pass two parameters, one being the department number, the other, a parameter to suppress  the display of the parameter form. The parameters are passed via a parameter list. Please refer to the Oracle Forms reference manual  for further information about parameter lists.
PROCEDURE pass_parameter IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
BEGIN
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if it does destroy it */
plid := create_parameter_list(the_param);
/* create it afresh */
add_parameter(plid, 'departmentno', TEXT_PARAMETER,to_char(:dept.deptno));
/* associate the param in the form with the param in the report */
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */   run_product(REPORTS,'formrep.rdf',SYNCHRONOUS,RUNTIME,FILESYSTEM,plid,null);
END;

2. Passing Lexical Parameters
Oracle Reports Component
Create the lexical parameter MYWHERE and give it an initial value of:  where 1=1
The aim of this example will be to pass from the Form a string: where loc like 'NEW YORK'  Create a report based on a single query:
Q_emp:  SELECT empno, sal FROM emp &MYWHERE

Forms Component
Create the Oracle Forms procedure pass_parameter, outlined below as  a PL/SQL program unit and reference it from a when-button-pressed trigger. By default, the reports runtime parameter form will appear everytime the report is run.Pass_parameter will pass two parameters, one being the string for the where clause, the other, a parameter to suppress the display of the parameter form. The parameters are passed via a parameter list.

PROCEDURE pass_parameter IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
clause varchar2(60);
BEGIN plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if it does destroy it */
plid := create_parameter_list(the_param);
/* create it afresh */
clause := '"'||'where loc like '||''''||'NEW YORK'||''''||'"';
add_parameter(plid, 'MYWHERE', TEXT_PARAMETER, clause);
/* associate the param in the form with the param in the report */
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */   run_product(REPORTS,'formrep.rdf',SYNCHRONOUS,RUNTIME,             FILESYSTEM,plid,null);
END;

3. Passing the WHERE clause from the last query executed in Oracle Forms
Oracle Report component
Create the lexical parameter and query outlined below.   Parameter:
MYWHERE (initial value is: where 1=2)
Q_emp: SELECT * FROM emp &MY_WHERE

Oracle Forms component
Create the Oracle Forms procedure pass_where, outlined below as a PL/SQL program unit and reference it from a when-button-pressed trigger. Pass_parameter will pass two parameters, one being the where clause obtained from system.last_query, the other, a parameter to suppress the display of the parameter form.The parameters are passed via a parameter list as in the example above.

PROCEDURE pass_where IS
plid  paramlist;
the_param varchar2(15) := 'tmpdata';
the_query varchar2(1000);   /* store the query */
i   number(4);              /* will hold the position of WHERE */
BEGIN
plid := get_parameter_list (the_param);
if not id_null(plid)  then
destroy_parameter_list(the_param);
end if;
plid := create_parameter_list(the_param);      
the_query := :system.last_query ;
/* find the position of the first instance of the word where */
i := instr(the_query,'WHERE');
/* check, if the query contains a WHERE clause, i=0 if not
update the_query with just the contents of the where clause */
IF i > 0  THEN
the_query := substr(the_query, i, length(the_query) - i + 1) ;
ELSE
the_query := ' ';
END IF;
add_parameter(plid, 'MYWHERE', TEXT_PARAMETER, the_query);
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');  
run_product(REPORTS,'formrep.rdf',SYNCHRONOUS,RUNTIME,FILESYSTEM,plid,null);
end;

4. Passing a record group to replace a query defined in Reports
Oracle Reports Component
Create a report based on a single query. No WHERE clause is included in order to show the true effect of passing the record group. The record group will contain a restricted list of employees.
Q_emp:  SELECT empno, sal FROM emp

Oracle Forms Component
Create the Oracle Forms procedure outlined below as a PL/SQL program units and reference it from a when-button-pressed trigger.

PROCEDURE pass_record IS
repquery varchar2(15) := 'q_emp'; /* name of the reports query */
queryrec varchar2(15) := 'recgroup'; /* name of the record group */
the_param varchar2(15) := 'tmpdata';
plid paramlist;
rgid recordgroup;
the_query varchar2(1000);  /* store query in this */  return_cd number;
BEGIN
the_query := 'SELECT empno, sal FROM emp WHERE deptno='||to_char(:dept.deptno)||' ORDER BY sal';
/* create the query string */
rgid := find_group(queryrec);
/* get handle to record group */
IF id_null(rgid) THEN
rgid := create_group_from_query(queryrec, the_query);
END IF;
/* creating group from query, if it wasnt found */
delete_group_row(rgid,all_rows);
/* empty the record group */
return_cd := populate_group_with_query(rgid,the_query);
/* populate record group using the defined query */
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if yes - destroy it */
plid := create_parameter_list(the_param);
/* create it and associate the record group with the query in Reports */
add_parameter(plid, repquery, DATA_PARAMETER, queryrec);
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS,'formrep.rdf',SYNCHRONOUS,RUNTIME,FILESYSTEM,plid,null);
END;

REP-1340 -------- The error REP-1340 is raised if the Forms procedure passes a query to Reports containing a mismatch between the columns passed  and those expected by the report. This will happen if:
- the number of columns is different.
- the parameter list assembled in the form describes a column differently to that which is expected by the report i.e the datatype is different.

-------------------------------------------------

However 2)Passing data records from the calling application (Forms) to the called application (Reports)
works only on client/server.
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8166780
I disagree that it is a strait-forward task and can be acomplished with lexical parameters. The problem is that the requirement is to have different list/number of columns and different source table. You just can accomplish that in the data model, not talking about the layout at all!
The fact that the number/datatype/order of columns can vary is the biggest issue.
The only solution I can see is to build the report manually, i.e. have just one field (placeholder) in the layout and fill it in manually using a query/cursor in the before-report trigger. You'd need to take care of ALL the formatting in the report.
In short it should be something like that:
- Loop thru the dynamic cursor.
- For each record concatenate all the fields together and add a carriage return.
If you are expecting just one record from the query it will be even easier...
Hope that helps!
0
 

Author Comment

by:smsm63
ID: 8189270
hi pennnn
i read ur comment and i couldn't understand it ^_^
could u please give me an example on how to do that snice i'm new with lexical parameters soooory to be too much bother to u
0
 
LVL 11

Assisted Solution

by:pennnn
pennnn earned 664 total points
ID: 8190440
OK, let's use your example: two parameters - one containing the list of columns, the other - the list of tables:
v_1 = 'emp';
v_2 = 'emp_id, emp_name, emp_salary';

Those parameters get passed to the report form the form.
You have to forget about the data model and do everything in the pre-report trigger (or any trigger that gets fired before generating the layout).
You have to define a placeholder, let's say p_1 (character datatype, max posible length).
In the trigger you can do something like that:
DECLARE
   TYPE cur_type IS REF CURSOR;
   cur_rec cur_type;
   v_tmp VARCHAR2(2000);
BEGIN
   OPEN cur_rec FOR 'SELECT ' || replace(v_2,',','||') ||
                    ' FROM ' || v_1;
   LOOP
      FETCH cur_rec INTO v_tmp;
      EXIT WHEN cur_rec%NOTFOUND;
      :p_1 := :p_1 || v_tmp || chr(10);
   END LOOP;
END;

That's pretty high-level... What it does is getting all records from the query into a single placeholder. All you need to do is display the placeholder in the layout.
Of course there's more you need to do - you have to take care of the formatting. In my example all I do is just replace the commas with '||', so all columns get concatenated together. You have to decide how to format the output better. E.g. you can replace it with '||<tab>||' - so there is a tab character between the fields.
It would be extremely hard to generate nice formatting for all cases (e.g. many columns) but if you want a report as generic as you've described you'd need to put quite a lot of effort into it.
Hope all that makes sence.


0
 
LVL 1

Assisted Solution

by:retronaut
retronaut earned 664 total points
ID: 8197802
This really depends on what you want the report to do. Here is how lexical references are most likely to be used.

The columns used in the data model must be pre-defined at design time. In the query designer the default value of the parameter is substituted for the lexical reference when parsing the sql statment.

EXAMPLE:

select &p_col1 as col1, &p_col2 as col2 from &p_table1

The default values for p_col1, p_col2 and p_table1 are substituted for the lexical references when you click OK in the query designer. For the columns use something like lpad(2000,'A') to force a varchar(2000) datatype.  Look at the properties of the column in the data model to confirm its datatype.

Design problems include predefining how many columns you want and dealing with datatype inconsistencies. An earlier suggestion dealt with this by concatenating the columns together to effectively return one column from the sql statement.

In the after parameter clause trigger use a function to determine each column's datatype and convert the selected values to a varchar2. You may wish to raise an exection for long, raw etc...

EXAMPLE:

function AfterPForm return boolean is

  function COL_TO_CHAR ( p_col in varchar2, p_tab in   varchar2)
  return varchar2 is

    cursor col_datatype (cp_col in varchar2, cp_tab in varchar2)
    select data_type from all_tab_columns
    where table_name = p_tab
    and column_name = p_col;

    v_datatype = all_tab_columns.data_type%type;
    v_return varchar2(100);
 
  begin

    open col_datatype (p_col, p_tab);
    fetch col_datatype into v_datatype;
    close col_datatype;
 
    if v_datatype in ('CHAR','VARCHAR2') then
      v_return = p_col;
    elsif v_datatype = 'DATE' then
      v_return = 'to_char(' || p_col || ', ''YYY-MM-DD'')';
    else
      v_return = 'to_char(' || p_col || ')';
    end if

    return v_return;
  end;

begin
  :p_col1 := COL_TO_CHAR ( :p_col1, :p_tab1 );
  :p_col2 := COL_TO_CHAR ( :p_col2, :p_tab1 );
end;



Now the SQL statment when running the report should not raise any datatype errors...


0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

752 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