- CALL_FORM
- NEW_FORM
- OPEN_FORM
Parameters
can also be used to pass parameter to other Oracle products such as
Oracle REPORTS
- Run_Report_Object()
HOW
TO PASS PARAMETER WITH SINGLE QUOTE FROM FORMS TO REPORTS
And
from one form to another
form using
a
- menu item
instead
of a regular push button.
To
do this you can use:
1) The Default Parameter List
Each
form includes a built-in parameter List named Default. The Default
parameter List contains all of the form parameters that were defined
in the form at design time. For example, if you define parameters p1,
p2, and p3 in Form A at design time, they are automatically included
in the Default parameter List for Form A.
The
Default parameter List can be passed to a called form by including it
in the argument List of the OPEN_FORM, CALL_FORM, or NEW_FORM
built-in procedures.
DECLARE
the_List PARAMLIST:= Get_Parameter_List('default');
BEGIN
Open_Form('form_B',ACTIVATE, NO_SESSION,'default');
END;
the_List PARAMLIST:= Get_Parameter_List('default');
BEGIN
Open_Form('form_B',ACTIVATE, NO_SESSION,'default');
END;
To
create a parameter follow the step
1)
In the Object Navigator, select the Parameters node and choose
NAVIGATOR->CREATE.
2)
Bring up the properties of the parameter and set the properties as
needed.
For
example, Set the datatype, default value, and name of the parameter.
3)
To access the value of the parameter, add the reserved word PARAMETER
as
a prefix to the parameter name. If you need to assign a value to
a
parameter, use a regular assignment statement such as:
temp_var
:= :parameter.variab;
--
assigns the value of the parameter to test_var
:parameter.Variab
:= 'value';
--
assigns a value 'value' to the parameter
Whenever
you create a regular parameter, it is actually added to a default
parameter
list.
All
parameters that you define in the Object Navigator belong to the
default
parameter
list. You can also pass the default parameter list to another
form
if you need. For example:
WHEN-BUTTON-PRESSED
BEGIN
CALL_FORM('test',
NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, 'default');
END;
When
passing the default parameter list as well as any other parameter
list,
make
sure that every parameter exists with the same name in the called
form.
2)
Own parameter list
You
can also create your own parameter list programmatically and pass it
as an argument in a CALL_FORM or OPEN_FORM or Report
You
can use the following built-in subprograms to create and manipulate a
parameter List:
ADD_PARAMETER
CREATE_PARAMETER_LIST
DELETE_PARAMETER
DESTROY_PARAMETER_LIST
GET_PARAMETER_ATTR
GET_PARAMETER_LIST
SET_PARAMETER_ATTR
Tip:
Keep in mind the following when you create parameter Lists:
- CREATE_PARAMETER_LIST is a function whose return value is the ID of the List being created. You must assign the ID to a variable that you declared as type PARAMLIST (an Oracle Forms data type).
- A call to CREATE_PARAMETER_LIST creates a parameter List that does not yet contain any parameters. To add parameters to the List, execute the ADD_PARAMETER procedure.
- GET_PARAMETER_ATTR and SET_PARAMETER_ATTR can be used to get and set the type and value of a parameter that has been added to a parameter List with the ADD_PARAMETER built-in. Do not use these built-ins to get or set the value of a form parameter that was defined at design time; instead, refer to the parameter using bind variable syntax or indirect reference.
- GET_PARAMETER_LIST is a function that returns the ParamList ID of an indicated parameter List, similar to the FIND-* functions available for other object types.
The
following example creates a parameter List, adds two parameters to
it, and then passes the List to a form by way of the CALL_FORM
procedure:
/*
** Declare a variable of type ParamList to store
** the parameter List ID
*/
DECLARE
List_id ParamList;
BEGIN
/*
** Create a parameter List named "input_params"
*/
List_id := Create_Parameter_List('input_params');
/*
** Add two parameters to the List to pass values for each
** Declare a variable of type ParamList to store
** the parameter List ID
*/
DECLARE
List_id ParamList;
BEGIN
/*
** Create a parameter List named "input_params"
*/
List_id := Create_Parameter_List('input_params');
/*
** Add two parameters to the List to pass values for each
One
way to use a parameter list is to pass the value of a text item
that
is used as a search criterion when calling another form.
Another
example is to pass a value to a report parameter that is used in
the
WHERE clause to retrieve only certain rows.
Suppose
you need to pass the department number from one form (department) to
another form (employee);
you
use the department number to query all employees working in that
department
in the employee form.
You
can execute the following code from
a
When-Button-Pressed trigger or even from a menu item:
DECLARE
List_id
ParamList;
BEGIN
List_id:=
GET_PARAMETER_LIST('input_params');
IF
NOT ID_NULL(param_list_id) THEN
DESTROY_PARAMETER_LIST(list_id);
END
IF;
List_id
:= Create_Parameter_List('input_params');
ADD_PARAMETER(list_id, 'emp_query', TEXT_PARAMETER, :deptno);
CALL_FORM('employee',
NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, list_id);
END;
- You must declare an object of type ParamList
- Use the GET_PARAMETER_LIST built-in to find out if the parameter already exists. If it exists, destroy it and recreate it. You can use the DESTROY_PARAMETER_LIST built-in to delete a parameter list.
3)
You can then add one or more parameters to the parameter list.
The
ADD_PARAMETER requires the following arguments:
--
The parameter list id, or parameter name
--
The name of the parameter being created
--
The parameter type
--
The parameter value
In
the example above, 'emp_query' is the name of the parameter,
TEXT_PARAMETER is
the
parameter type, and :deptno is the value of the parameter. :
emp_query gets
the
value from a text item called deptno. Most of the parameters that you
add
are
of the type TEXT_PARAMETER, unless you try to pass a record group.
Record
group parameters must be defined as DATA_PARAMETER instead of
TEXT_PARAMETER.
To
access the value of a parameter in a called form, you must create the
following
triggers in the employee form:
- WHEN-NEW-FORM-INSTANCE at the form level
- PRE-QUERY trigger at the block level
Or
- in a WHEN-NEW-FORM-INSTANCE at the form level
- Default_where
- one_time_where
In
addition, create a parameter with the same name as the parameter
that
you are passing in the parameter list :emp_query.
The
application returns an error message that the parameter does not
exist.
The
following is an example of two triggers needed to do a query based on
the
value
passed in the parameter list when the form 'employee' is called:
WHEN-NEW-FORM-INSTANCE
in
called form at the form level
BEGIN
EXECUTE_QUERY;
END;
PRE-QUERY
in called form at the block level
/*
If this is a called form, then, set up the search criteria based /*
/*
on the parameter emp_query passed in the parameter list */
BEGIN
:deptno
:= :parameter.emp_query;
END;
WHEN-NEW-FORM-INSTANCE
with
Default_where
(or one_time_where)
in called form at the form level
BEGIN
IF
:PARAMETER. EMP_QUERY is null THEN
Enter_query;
ELSE
Set_Block_Property('EMP',DEFAULT_WHERE,
‘
DEPTNO = '||:PARAMETER.EMP_QUERY); ' AND ANNO
= '||:PARAMETER.ANNO);
execute_query;
end
if;
END;
-----
How
to pass parameters from one form to another form
using
a menu item instead of a regular push button.
TO
PASS PARAMETERS FROM FORM1 TO FORM2 USING THE MENU ITEM
- Create form1 e.g. based on xxx table.
We
will pass REG_QUERY as the parameter from form1 to form2.
2)
Create form2 with a button.
- Create a parameter under the object navigator of form2.
Let
us name that parameter "param1" and make the datatype
"Char" in order to match
REG_QUERY
datatype.
4)
Create a menu using menudefs.mmb and name it e.g. mymenu
5)
Create a menu item and name it "callform2".
6)
Under the property of the menu item "callform2", bring the
pl/sql editor
by
clicking on the "Menu Item Code". Type the following code:
DECLARE
param_list_id ParamList;
REG_QUERY VARCHAR2(50)
:= 'Miller';
BEGIN
IF
REG_QUERY IS NOT NULL THEN
param_list_id
:= GET_PARAMETER_LIST('TDOCUMENTO');
IF
NOT ID_NULL(param_list_id) THEN
DESTROY_PARAMETER_LIST('TDOCUMENTO');
END
IF;
param_list_id
:= CREATE_PARAMETER_LIST('TDOCUMENTO');
ADD_PARAMETER(param_list_id,
'REG_QUERY', TEXT_PARAMETER, REG_QUERY;
CALL_FORM
(‘form2’,HIDE,DO_REPLACE,NO_QUERY_ONLY,SHARE_LIBRARY_DATA,param_list_id);
DESTROY_PARAMETER_LIST(param_list_id);
END
IF;
END;
7)
Save the menu and compile it to create the mmx.
8)
Open the properties of the form level of form1 in order to assign
the menu.
Under
"Menu Module", type your menu that you have just modified
"mymenu".
9)
Under form2, create WHEN-NEW-FORM-INSTANCE trigger with the
following code:
BEGIN
MESSAGE(REG_QUERY);
MESSAGE(‘’);
END;
10)
Save form2 and compile it to create the fmx.
11)
Go back to your form1 and run it
For
instance, we would like to pass "Miller" as our parameter .
REG_QUERY
from
form1
to form2.
12)
Go to the menu item "callform2" in form1 and click on that
menu item in
order
to pass REG_QUERY parameter e.g. "Miller".
When
you get form2, that will display the parameter
that
was passed from form1.
3)
To Call Reports with a Parameter Form
How
to Access the Value of a Parameter in a report (run_report_object()
)
DECLARE
id_pl ParamList
:= NULL;
v_repid
REPORT_OBJECT;
param_list_name VARCHAR2(30)
:= 'para_lista';
v_rep
VARCHAR2(100);
NREPORT
VARCHAR2(30) := ‘myreport’;
MYWHERE VARCHAR2(300)
:= ‘’;
INTESTA_RP VARCHAR2(300)
:= ‘’;
BEGIN
v_repid := find_report_object(NREPORT );
Id_pl
:= GET_PARAMETER_LIST(param_list_name);
IF
NOT ID_NULL(Id_pl) THEN
DESTROY_PARAMETER_LIST(Id_pl);
END
IF;
id_PL
:= CREATE_PARAMETER_LIST(param_list_name);
--
ADD_PARAMETER(ID_PL,
'PARAMFORM', TEXT_PARAMETER, 'NO');
ADD_PARAMETER(ID_PL,
'MYWHERE', TEXT_PARAMETER, MYWHERE);
ADD_PARAMETER(ID_PL,
'INTESTA_RP', TEXT_PARAMETER, INTESTA_RP);
V_REP
:= run_report_object(v_repid,id_PL);
In
addition, create a parameters with the same name as the parameter
that
you are passing in the parameter list: MYWHERE
and INTESTA_RP.
In
Report builder
Open
up the Property Inspector of user parameter MYWHERE and specify the
following:
Name:
MYWHERE
Datatype:
CHAR
Width: 300
Initial value: AND DEPTNO = ‘10’ (It can be any valid value)
Width: 300
Initial value: AND DEPTNO = ‘10’ (It can be any valid value)
Same
for user parameter INTESTA_RP
HOW
TO PASS PARAMETER WITH SINGLE QUOTE FROM FORMS TO REPORTS
How
do you pass a parameter that contains single quotes
from
Oracle Forms to Oracle Reports?
Example
-------
How
do you store the parameter 'test', not test, in the database?
Hence,
in order for the report to work, the parameter must appear
in
the Parameter Form as 'test'.
Use
sets of 3 single quotes around the string literal.
Example
-------
'''test'''
This
appears as 'test' in the Parameter Form.
----
ORA-907
or ORA-12802 when using quote in the parameter
A
report has to be used in web application. It has a parameter that
includes
quote
signs like this : 'value1','value2',...,'valueN'
Following
problems occurred running it as web application :
1.
when using single quotes for the list of initial values:
->
ora-907
2.
when using no quotes at all for the list of initial values:
->
ORA-12801 & ORA-1462
3.
when using double quotes for each value in the list of initial
values:
->
no initial values given in Parameterform
4.
The only way to get it working in web reports only:
add
double quotes in front and at the end of the parameter list
Other
one possible workaround is to use following trigger :
Reports Trigger
After Parameter Form)
function AfterPForm
return boolean is
begin
:my_empname:=chr(39)||:my_param||chr(39);
return (TRUE);
end;
By
implementing the code in the afterParameterForms trigger
":
my_param:=chr(39)||:my_empname||chr(39);"
adds quotes
and
which becomes finally double quotes
Another
In a Form which
will call a report with some parameters. You find that if the text
parameter contains "'", You will get the following errors
in the log of reports background engine.
REP-0159 : Syntax error on command line
Error Submitting report
You can't use that as it's an illegal character.
What you do is put a little code in so as to change all apostrophes into their MIME equivalent. This must also be done for the percent sign (%) and others which might be considered illegal in an URL. The percent sign is the escape character in MIME so putting it in an URL results in whatever else is after it to be considered an escape character in HEX which causes problems.
In short, replace all ' in your URL sent to the Report Server with %27. If you're wondering, the escape code for the percent sign is %25.
In the future, any other illegal characters, just replace the character with %hex where hex is the hex code for the ASCII character.
--HOW TO EMBED SINGLE QUOTE IN STRING
REP-0159 : Syntax error on command line
Error Submitting report
You can't use that as it's an illegal character.
What you do is put a little code in so as to change all apostrophes into their MIME equivalent. This must also be done for the percent sign (%) and others which might be considered illegal in an URL. The percent sign is the escape character in MIME so putting it in an URL results in whatever else is after it to be considered an escape character in HEX which causes problems.
In short, replace all ' in your URL sent to the Report Server with %27. If you're wondering, the escape code for the percent sign is %25.
In the future, any other illegal characters, just replace the character with %hex where hex is the hex code for the ASCII character.
--HOW TO EMBED SINGLE QUOTE IN STRING
How
do you embed single quotes ( ' ) into a character string?
How
do you concatenate a quote in SQL?
How
do you place quotes around a character string in a SQL query?
How
do you store an apostrophe into a character variable in PL/SQL?
Example
1
When
you issue either of the following SELECT statements:
SQL>
SELECT ' FROM dual;
SQL>
SELECT ''' FROM dual;
the
following error occurs:
ORA-01756:
quoted string not properly terminated
Example
2
When
you issue the following SELECT statement:
SQL>
SELECT ''character string in quotes'' FROM dual;
the
following error occurs:
ORA-00923:
FROM keyword not found where expected
Solution
Description:
To
create a single quote, concatenate CHR(39) to the string.
CONCATENATE
CHR(39) TO CREATE LITERAL SINGLE QUOTE
Example
1
---------
SQL>
SELECT 'test' || CHR(39) || 'case' result FROM dual;
RESULT
---------
test'case
Example
2
---------
SQL>
SELECT CHR(39) c FROM dual;
C
-
'
To
return the ASCII value of the single quote ( ' ):
SQL>
SELECT ASCII('''') FROM dual;
ASCII('''')
-----------
39
USE
2 SINGLE QUOTES TO CREATE 1 SINGLE QUOTE
Keep
the following two rules in mind:
1.
Enclose every character string in single quotes.
The
single quote is a string delimiter.
2.
Inside a string literal, use two consecutive single quotes
to
create a literal single quote.
Example
1
---------
6
single quotes: SELECT 'test' || '''''' || 'case' c FROM dual;
RESULT
test''case
8
single quotes: SELECT 'test' || '''''''' || 'case' c FROM
dual;
RESULT
test'''case
You
can also implement the above in the following way:
SELECT
'test''case' c FROM dual;
RESULT
test'case
SELECT
'test''''case' c FROM dual;
RESULT
test''case
Hence:
a.
To create a single quote, concatenate 4 single quotes: ''''
The
two single quotes in the middle define the single quote.
The
outside single quotes are the single quotes that must
surround
a string.
Example
2
---------
SQL>
SELECT '''' FROM dual;
'
-
'
Example
3
---------
SQL>
SELECT 'test' || '''' || 'case' result FROM dual;
RESULT
---------
test'case
b.
To place single quotes around a character string,
enclose
the character string within 3 single quotes: '''
At
the start of a character string:
the
first single quote defines the start of the character string;
it
is one of the two single quotes that surround the string.
The
second and third single quotes define the literal single quote.
At
the end of the character string:
the
first and second single quotes define the literal single quote.
The
third single quote closes the character string; it is the
other
single quote that surrounds the string.
Example
4
---------
SQL>
SELECT '''character string in quotes''' result FROM dual;
RESULT
'character
string in quotes'
More
examples:
Example
5
---------
SRW.DO_SQL('SELECT
DECODE(dname, ''NONE'', NULL, ''A'')
FROM
dept
WHERE
deptno = 10');
As
a result, this is the SELECT statement sent to the database:
SELECT
DECODE(dname, 'NONE', NULL, 'A')
FROM
dept
WHERE
deptno = 10
Example
6
---------
DECLARE
a
VARCHAR2(200);
q
CHAR(1) := '''';
BEGIN
a
:= '''this is a ' || q || 'quoted'' string' || q;
END;
String
"a" stores:
'this
is a 'quoted' string'
Built-in
used (From Oracle Forms
ONLine help)
PROCEDURE
CALL_FORM
(formmodule_name VARCHAR2,
display NUMBER,
switch_menu NUMBER,
query_mode NUMBER,
data_mode NUMBER,
paramlist_name VARCHAR2);
(formmodule_name VARCHAR2,
display NUMBER,
switch_menu NUMBER,
query_mode NUMBER,
data_mode NUMBER,
paramlist_name VARCHAR2);
Parameters
formmodule_name
The
name of the called form (must be enclosed in single quotes). Datatype
is VARCHAR2.
display
HIDE
(The
default.) Oracle Forms will hide the calling form before drawing the
called form.NO_HIDE
Oracle Forms will display the called form without hiding the calling
form.
switch_menu
NO_REPLACE
(The default.) Oracle Forms will keep the default menu module of the
calling form active for the called form.
DO_REPLACE
Oracle Forms will replace the default menu module of the calling form
with the default menu module of the called form.
query_mode
NO_QUERY_ONLY
(The default.) Oracle Forms will run the indicated form in normal
mode, allowing the end user to perform inserts, updates, and deletes
from within the called form.
QUERY_ONLY
Oracle Forms will run the indicated form in query-only mode, allowing
the end user to query, but not to insert, update, or delete records.
data_mode
NO_SHARE_LIBRARY_DATA
(The default.) At runtime, Oracle Forms will not share data between
forms that have identical libraries attached (at design
time).SHARE_LIBRARY_DATA
At runtime, Oracle Forms will share data between forms that have
identical libraries attached (at design time).
paramlist_id
The
unique ID Oracle Forms assigns when it creates the parameter list.
You can optionally include a parameter list as initial input to the
called form. Datatype is PARAMLIST.
paramlist_name
The
name you gave the parameter list object when you defined it. Datatype
is VARCHAR2.
PROCEDURE
OPEN_FORM
(formmodule_name VARCHAR2,
activate_mode NUMBER,
session_mode NUMBER,
data_mode NUMBER,
paramlist_id PARAMLIST);
(formmodule_name VARCHAR2,
activate_mode NUMBER,
session_mode NUMBER,
data_mode NUMBER,
paramlist_id PARAMLIST);
Parameters:
formmodule_name
The
name of the form to open. Datatype is VARCHAR2. Required
activate_mode
ACTIVATE
(The default.) Sets focus to the form to make it the active form in
the application.NO_ACTIVATE
Opens the form but does not set focus to the form. The current form
remains current.
session_mode
NO_SESSION
(The default.) Specifies that the opened form should share the same
database session as the current form. POST and COMMIT operations in
any form will cause posting, validation, and commit processing to
occur for all forms running in the same session.
SESSION Specifies that a new, separate database session should be created for the opened form.
SESSION Specifies that a new, separate database session should be created for the opened form.
data_mode
NO_SHARE_LIBRARY_DATA
(The default.) At runtime, Oracle Forms will not share data between
forms that have identical libraries attached (at design
time).
SHARE_LIBRARY_DATA At runtime, Oracle Forms will share data between forms that have identical libraries attached (at design time).
SHARE_LIBRARY_DATA At runtime, Oracle Forms will share data between forms that have identical libraries attached (at design time).
paramlist_name
The
name of a parameter list to be passed to the opened form. Datatype is
VARCHAR2.
paramlist_id
The
unique ID that Oracle Forms assigns to the parameter list at the time
it is created. Use the GET_PARAMETER_LIST function to return the ID
to a variable of type PARAMLIST.
PROCEDURE
NEW_FORM
(formmodule_name VARCHAR2,
rollback_mode NUMBER,
query_mode NUMBER,
data_mode NUMBER,
paramlist_name VARCHAR2);
(formmodule_name VARCHAR2,
rollback_mode NUMBER,
query_mode NUMBER,
data_mode NUMBER,
paramlist_name VARCHAR2);
Parameters
formmodule_name
Then
name of the called form (must be enclosed in single quotes). Datatype
is VARCHAR2.
rollback_mode
TO_SAVEPOINT
(The
default.) Oracle Forms will roll back all uncommitted changes
(including posted changes) to the current form's
savepoint.
NO_ROLLBACK Oracle Forms will exit the current form without rolling back to a savepoint. You can leave the top level form without performing a rollback, which means that you retain any locks across a NEW_FORM operation. These locks can also occur when invoking Oracle Forms from an external 3GL program. The locks are still in effect when you regain control from Oracle Forms.
FULL_ROLLBACK Oracle Forms rolls back all uncommitted changes (including posted changes) that were made during the current Runform session. You cannot specify a FULL_ROLLBACK from a form that is running in post-only mode. (Post-only mode can occur when your form issues a call to another form while unposted records exist in the calling form. To avoid losing the locks issued by the calling form, Oracle Forms prevents any commit processing in the called form.)
NO_ROLLBACK Oracle Forms will exit the current form without rolling back to a savepoint. You can leave the top level form without performing a rollback, which means that you retain any locks across a NEW_FORM operation. These locks can also occur when invoking Oracle Forms from an external 3GL program. The locks are still in effect when you regain control from Oracle Forms.
FULL_ROLLBACK Oracle Forms rolls back all uncommitted changes (including posted changes) that were made during the current Runform session. You cannot specify a FULL_ROLLBACK from a form that is running in post-only mode. (Post-only mode can occur when your form issues a call to another form while unposted records exist in the calling form. To avoid losing the locks issued by the calling form, Oracle Forms prevents any commit processing in the called form.)
query_mode
NO_QUERY_ONLY
(The default.) Runs the indicated form normally, allowing the end
user to perform inserts, updates, and deletes in the
form.
QUERY_ONLY Runs the indicated form in query-only mode; end users can query records, but cannot perform inserts, updates or deletes.
QUERY_ONLY Runs the indicated form in query-only mode; end users can query records, but cannot perform inserts, updates or deletes.
data_mode
NO_SHARE_LIBRARY_DATA
(The default.) At runtime, Oracle Forms will not share data between
forms that have identical libraries attached (at design
time).
SHARE_LIBRARY_DATA At runtime, Oracle Forms will share data between forms that have identical libraries attached (at design time).
SHARE_LIBRARY_DATA At runtime, Oracle Forms will share data between forms that have identical libraries attached (at design time).
paramlist_id
The
unique ID Oracle Forms assigns when it creates the parameter list.
Specify a parameter list when you want to pass parameters from the
calling form to the new form. Datatype is PARAMLIST. A parameter list
passed to a form via NEW_FORM cannot contain parameters of type
DATA_PARAMETER (a pointer to record group).
paramlist_name
The
name you gave the parameter list object when you defined it. Datatype
is VARCHAR2. A parameter list passed to a form via NEW_FORM cannot
contain parameters of type DATA_PARAMETER (a pointer to record
group).
FUNCTION
RUN_REPORT_OBJECT
(report_name VARCHAR2,
paramlist_id PARAMLIST);
(report_name VARCHAR2,
paramlist_id PARAMLIST);
Returns
VARCHAR2
Parameters
report_id
Specifies
the unique ID of the report to be run. You can get the report ID for
a particular report using the Built-in FIND_REPORT_OBJECT
report_name
The
name of the report object to run.
paramlist_name
The
name you gave the parameter list object when you defined it. Datatype
is VARCHAR2.
paramlist_id
The
unique ID Oracle Forms assigns when it creates the parameter list.
Datatype is PARAMLIST.
No comments:
Post a Comment