Have you ever encountered error "Implementation Restriction: 'XX.XX': Cannot directly access remote package variable or cursor" while compiling oracle forms? If yes read below to find a method of creating a generic way of assigning values to variables from oracle forms.
Here in this article we will see how to avoid the error "Cannot directly access remote package variable or cursor" from Oracle forms through an example.
We have created a package for test purposes. The name of the package is testpck. Assume it has a lot of variables, say some 200 and you need to assign values to each of the variables from front-end. One way is to create a procedure which assigns values using a rowtype variable. But in this approach we would be writing unnecessary code to attain the logic.
In this example listed below we have used only one variable in the package specification. The variable name is l_variable. For simplicity of discussion I am restricting the variables of type VARCHAR2. This can be further enhanced to provide a logic for all of the datatypes.
Find below the code for package specification:
create or replace package testpck as
l_variable varchar2(255);
procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2);
end;
/
Find below the code for package body:
create or replace package body testpck as
procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2) as
l_sql varchar2(4000);
begin
l_sql := 'begin testpck.'||l_variable_name ||':='||chr(39)||l_variable_value||chr(39)||'; end;';
execute immediate l_sql;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end;
/
Now to test this procedure:
begin
testpck.l_set_variable_value('l_variable',
'variables value');
end;
/
That's all. Now you can use this procedure to assign value to any of the variables present in the package specification. If an invalid variable name is passed then the following error will pop out:
ORA-06550: line 1, column 15:
PLS-00302: component 'L_VARIABLE1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Program Global Area (PGA)
A program global area (PGA) is a memory region that stores the data and control information for the server processes. Each Server process has a non-shared memory created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. Broadly speaking, PGA contains a private SQL area and a Session memory area.
A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area.
Note that the Location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
---The above text is an excerpt from:
Oracle 10g Grid & Real Application Clusters
Oracle 10g Grid Computing with RAC
ISBN 0-9744355-4-6
by Mike Ault, Madhu Tumma
All days in a year - Query
Here is the query to find all dates and the days in a current year:
select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)
Note: You must be running 10g to get the desired output. For a non 10g version see below the query:
select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)
Note: You must be running 10g to get the desired output. For a non 10g version see below the query:
select mydate,to_char(mydate,'Day') from(
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)
The above query assumes that all_objects returns at least 365 records.
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)
The above query assumes that all_objects returns at least 365 records.
Traverse through all items in a form
The simple technique of traversing through all items in a form is demonstrated in this article. See below logic for the traversing:
001 start program
002 get first block name;
003 get last block name;
004 go to first block name;
005 assign value of first block name to current block;
006 start loop
007 get first item;
008 get last item;
009 if the loop is running for first time then assign current item's value as first item;
010 print item name;
011
012 if last item is true and current block is not last block
013 go to next block;
014 assign current block's value as new block's name;
015 else if last item is true and current block is last block
016 exit loop;
017 else if current item is not last item
018 go to next item;
019 assign current item's value as new item's name;
020 end if;
021 end loop;
022 end program;
001 start program
002 get first block name;
003 get last block name;
004 go to first block name;
005 assign value of first block name to current block;
006 start loop
007 get first item;
008 get last item;
009 if the loop is running for first time then assign current item's value as first item;
010 print item name;
011
012 if last item is true and current block is not last block
013 go to next block;
014 assign current block's value as new block's name;
015 else if last item is true and current block is last block
016 exit loop;
017 else if current item is not last item
018 go to next item;
019 assign current item's value as new item's name;
020 end if;
021 end loop;
022 end program;
Dynamic Wrapping of Procedures
In this topic we will discuss the all new dynamic wrapping from inside a PL/SQL block. This new feature is also born with Oracle Database 10g Release 2 version.
What is WRAP utility?
The wrap is a command line utility that wraps or encrypts the contents of a PL/SQL source file. The syntax for using this is:
wrap iname=input_file [oname=output_file]
Pass the source file name to iname parameter and specify the output file name (file to store the encrypted contents) in the oname parameter.
For Example:
wrap iname=myfunction.sql oname=myfunction.pld
If the oname parameter is not specified the wrap utility creates a wrapped file with same name as source file, but with extension as .pld
The new way of Wrapping
Starting with Oracle Database 10g Release 2, the wrapping gets a new shape. Two functions has been added in the DBMS_DDL package for wrapping.
DBMS_DDL.WRAP
The syntax of the WRAP procedure is:
DBMS_DDL.WRAP(
ddl VARCHAR2,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN VARCHAR2;
DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2S;
DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2A;
For source which is less than the 32K size they can be defined as a VARCHAR2. For source greater than this size use the data types of DBMS_SQL.VARCHAR2S or DBMS_SQL.VARCHAR2A can be defined. They are collection variables.
Example of DBMS_DDL.WRAP procedure:
DECLARE
l_source VARCHAR2(32767);
l_wrapped VARCHAR2(32767);
BEGIN
l_source := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source := l_source || ' BEGIN ';
l_source := l_source ||' RETURN sysdate;';
l_source := l_source ||' END get_date;';
l_wrapped := DBMS_DDL.WRAP(ddl => l_source);
EXECUTE IMMEDIATE l_wrapped;
END;
The above example creates a function get_date in runtime as wrapped function.
For source size greater than 32 K, use the overloaded procedures of DBMS_DDL.WRAP. For source with size greater than 32 K the example is slightly modified as below:
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrapped DBMS_SQL.VARCHAR2A;
BEGIN
l_source(1) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(2) := 'BEGIN ';
l_source(3) := 'RETURN sysdate;';
l_source(4) := 'END get_date;';
l_wrapped := DBMS_DDL.WRAP(l_source, 1, l_source.count);
EXECUTE IMMEDIATE l_wrapped;
END;
The second and third parameters to WRAP procedure is lower bound and upper bound of collection variables. To restrict the header and footer section of the collections you can use the parameters as shown in the below variant using DBMS_SQL.VARCHAR2S:
DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';
l_wrapped := DBMS_DDL.WRAP(l_source, 2, 5);
EXECUTE IMMEDIATE l_wrapped;
END;
DBMS_DDL.CREATE_WRAPPED
The syntax of the CREATE_WRAPPED is same as that of WRAP procedure. See below the example of the CREATE_WRAPPED function:
DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';
l_wrapped := DBMS_DDL.CREATE_WRAPPED(l_source, 2, 5);
EXECUTE IMMEDIATE l_wrapped;
END;
The above example demonstrates two things. One is usage of CREATE_WRAPPED and another is the usage of only using part from the collection variables.
More references can be found at:
From Oracle site
From Oracle-Base site
From Puget Sound Oracle Users Group (PSOUG)
And from Google
What is WRAP utility?
The wrap is a command line utility that wraps or encrypts the contents of a PL/SQL source file. The syntax for using this is:
wrap iname=input_file [oname=output_file]
Pass the source file name to iname parameter and specify the output file name (file to store the encrypted contents) in the oname parameter.
For Example:
wrap iname=myfunction.sql oname=myfunction.pld
If the oname parameter is not specified the wrap utility creates a wrapped file with same name as source file, but with extension as .pld
The new way of Wrapping
Starting with Oracle Database 10g Release 2, the wrapping gets a new shape. Two functions has been added in the DBMS_DDL package for wrapping.
- DBMS_DDL.WRAP
- DBMS_DDL.CREATE_WRAPPED
DBMS_DDL.WRAP
The syntax of the WRAP procedure is:
DBMS_DDL.WRAP(
ddl VARCHAR2,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN VARCHAR2;
DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2S;
DBMS_DDL.WRAP(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub PLS_INTEGER)
RETURN DBMS_SQL.VARCHAR2A;
For source which is less than the 32K size they can be defined as a VARCHAR2. For source greater than this size use the data types of DBMS_SQL.VARCHAR2S or DBMS_SQL.VARCHAR2A can be defined. They are collection variables.
Example of DBMS_DDL.WRAP procedure:
DECLARE
l_source VARCHAR2(32767);
l_wrapped VARCHAR2(32767);
BEGIN
l_source := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source := l_source || ' BEGIN ';
l_source := l_source ||' RETURN sysdate;';
l_source := l_source ||' END get_date;';
l_wrapped := DBMS_DDL.WRAP(ddl => l_source);
EXECUTE IMMEDIATE l_wrapped;
END;
The above example creates a function get_date in runtime as wrapped function.
For source size greater than 32 K, use the overloaded procedures of DBMS_DDL.WRAP. For source with size greater than 32 K the example is slightly modified as below:
DECLARE
l_source DBMS_SQL.VARCHAR2A;
l_wrapped DBMS_SQL.VARCHAR2A;
BEGIN
l_source(1) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(2) := 'BEGIN ';
l_source(3) := 'RETURN sysdate;';
l_source(4) := 'END get_date;';
l_wrapped := DBMS_DDL.WRAP(l_source, 1, l_source.count);
EXECUTE IMMEDIATE l_wrapped;
END;
The second and third parameters to WRAP procedure is lower bound and upper bound of collection variables. To restrict the header and footer section of the collections you can use the parameters as shown in the below variant using DBMS_SQL.VARCHAR2S:
DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';
l_wrapped := DBMS_DDL.WRAP(l_source, 2, 5);
EXECUTE IMMEDIATE l_wrapped;
END;
DBMS_DDL.CREATE_WRAPPED
The syntax of the CREATE_WRAPPED is same as that of WRAP procedure. See below the example of the CREATE_WRAPPED function:
DECLARE
l_source DBMS_SQL.VARCHAR2S;
l_wrapped DBMS_SQL.VARCHAR2S;
BEGIN
l_source(1) := 'Some header comments go here';
l_source(2) := 'CREATE OR REPLACE FUNCTION get_date IS ';
l_source(3) := 'BEGIN ';
l_source(4) := 'RETURN sysdate;';
l_source(5) := 'END get_date;';
l_source(6) := 'Some footer comments go here';
l_wrapped := DBMS_DDL.CREATE_WRAPPED(l_source, 2, 5);
EXECUTE IMMEDIATE l_wrapped;
END;
The above example demonstrates two things. One is usage of CREATE_WRAPPED and another is the usage of only using part from the collection variables.
More references can be found at:
From Oracle site
From Oracle-Base site
From Puget Sound Oracle Users Group (PSOUG)
And from Google
My experiences with LOG ERRORS clause
Oracle database 10g Release 2 comes with lot of features. In this article we will see one of the most awaited features that will cheer up at least some of the faces.
The LOG ERRORS clause is the topic for the article. This clause is available to all DML statements.
What is the clause doing?
Well simply stating this clause will skip errors encountered in middle of DML operation, and insert all those errors onto another table which we mention.
What is the benefit of the clause?
Consider a situation where 1000 records are being loaded using a insert into table_name select field_names from another_table_name statement. Say for example the insert succeeds for 999 records and for the last record it gives a error say unique constraint error. Oracle will rollback all the records inserted till that point. With this clause you will be able to insert the other 999 records and only the one record that had errors will be skipped.
What is the syntax?
The syntax of the clause is very simple:
{Any_DML_Statement}
LOG ERRORS INTO {log_table_name}
REJECT LIMIT {record_count}
For Example:
INSERT INTO b_table
SELECT *
FROM a_table
LOG ERRORS INTO error_table
REJECT LIMIT 200;
Now my experience with LOG ERRORS:
I wanted to experiment how easy is to get going with LOG ERRORS clause. Believe me, this is the first time I am going to use it.
I created two tables:
create table oldtable(field1 number);
create table newtable(field1 number primary key);
insert into oldtable values(1);
insert into oldtable values(2);
insert into oldtable values(3);
insert into oldtable values(3);
commit;
select * from oldtable;
FIELD1
1
2
3
3
Now i tried without the LOG ERRORS clause:
insert into newtable select * from oldtable;
I got the following error:
ORA:00001: unique constraint(MYUSER.SYS_C00297183) violated.
Now I have to create a log_table. I did this by calling dbms_errlog.create_error_log procedure.
exec dbms_errlog.create_error_log('newtable','err_new_table');
Now I am ready for using the LOG ERRORS clause.
insert into newtable
select * from oldtable
log errors into err_new_table
reject limit UNLIMITED;
It said, 3 rows created.
Note that oldtable contains 4 records but only 3 records where inserted; the other record were rejected due to unique constraint errors.
To see the errors logged use the following query:
select ora_err_number$, ora_err_mesg$, field1
from err_new_table;
ORA_ ORA_ERR_MESG$ FIELD1
---------------------------------------------------------------
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated 3
The value 3 in FIELD1 was already inserted into newtable. The second row with value 3 was rejected through this statement.
This is really wonderful. There was no problems in getting configured.
NOTE: This clause was introduced only in Oracle database 10g Release 2.
Read more about this on Oracle
The LOG ERRORS clause is the topic for the article. This clause is available to all DML statements.
What is the clause doing?
Well simply stating this clause will skip errors encountered in middle of DML operation, and insert all those errors onto another table which we mention.
What is the benefit of the clause?
Consider a situation where 1000 records are being loaded using a insert into table_name select field_names from another_table_name statement. Say for example the insert succeeds for 999 records and for the last record it gives a error say unique constraint error. Oracle will rollback all the records inserted till that point. With this clause you will be able to insert the other 999 records and only the one record that had errors will be skipped.
What is the syntax?
The syntax of the clause is very simple:
{Any_DML_Statement}
LOG ERRORS INTO {log_table_name}
REJECT LIMIT {record_count}
For Example:
INSERT INTO b_table
SELECT *
FROM a_table
LOG ERRORS INTO error_table
REJECT LIMIT 200;
Now my experience with LOG ERRORS:
I wanted to experiment how easy is to get going with LOG ERRORS clause. Believe me, this is the first time I am going to use it.
I created two tables:
create table oldtable(field1 number);
create table newtable(field1 number primary key);
insert into oldtable values(1);
insert into oldtable values(2);
insert into oldtable values(3);
insert into oldtable values(3);
commit;
select * from oldtable;
FIELD1
1
2
3
3
Now i tried without the LOG ERRORS clause:
insert into newtable select * from oldtable;
I got the following error:
ORA:00001: unique constraint(MYUSER.SYS_C00297183) violated.
Now I have to create a log_table. I did this by calling dbms_errlog.create_error_log procedure.
exec dbms_errlog.create_error_log('newtable','err_new_table');
Now I am ready for using the LOG ERRORS clause.
insert into newtable
select * from oldtable
log errors into err_new_table
reject limit UNLIMITED;
It said, 3 rows created.
Note that oldtable contains 4 records but only 3 records where inserted; the other record were rejected due to unique constraint errors.
To see the errors logged use the following query:
select ora_err_number$, ora_err_mesg$, field1
from err_new_table;
ORA_ ORA_ERR_MESG$ FIELD1
---------------------------------------------------------------
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated 3
The value 3 in FIELD1 was already inserted into newtable. The second row with value 3 was rejected through this statement.
This is really wonderful. There was no problems in getting configured.
NOTE: This clause was introduced only in Oracle database 10g Release 2.
Read more about this on Oracle
Does your application relies heavily on DUAL?
If the answer to the question is yes, and your application is on a networked or more particularly in internet, think twice. Because the normal DUAL table if referenced may cause lot of logical I/O to happen. Read below a tip from Mark.J.Bobak as appeared in ORACLE MAGAZINE (September/October 2004).
Mark suggests to create our own DUAL table as an Index Organized Table, rather than a normal heap table. He thinks that it will reduce logical I/Os significantly. The definition of the DUAL table can be like:
CREATE TABLE MYDUAL(
DUMMY VARCHAR2(1)
PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;
He sees benefit of avoiding overhead of a trigger and still protect the table from having more than one row. Note that the primary key protects against more than one row containing 'X' and the check constraint protects against any rows containing something other than 'X' which results in exactly one row containing 'X'.
Mark suggests to create our own DUAL table as an Index Organized Table, rather than a normal heap table. He thinks that it will reduce logical I/Os significantly. The definition of the DUAL table can be like:
CREATE TABLE MYDUAL(
DUMMY VARCHAR2(1)
PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;
He sees benefit of avoiding overhead of a trigger and still protect the table from having more than one row. Note that the primary key protects against more than one row containing 'X' and the check constraint protects against any rows containing something other than 'X' which results in exactly one row containing 'X'.
Oracle HTTP Server
The Oracle HTTP Server(OHS) provides key infrastructure for serving the Internet's HTTP protocol. OHS is used to return responses for both process to process and human generated requests from browsers. Key aspects of OHS are its technology, its serving of both static and dynamic content and its integration with both Oracle and non-Oracle products.
Technology - OHS is based on the proven, open source technology of both Apache 1.3 and Apache 2.0. OHS versions based on Apache 2.0 now provide the ability to accommodate the newest version of the Internet Protocol, IPv6. OHS based on Apache 2.0 is available as a standalone product off the Oracle Application Server 10g Companion CD. In addition OHS now provides, via the open source product mod_security, an application firewall capability.
Static and Dynamic Content - OHS serves static content directly or via standard interfaces such as WebDAV standard. Great flexibility is provided in dynamic content generation and many languages, such as Java, C/C++, Perl, PHP and PLSQL are provided for content generation.
Integration - While OHS has standalone deployment options, it can also be deployed in a highly integrated manner with Oracle clustering, monitoring, Single Sign On or Web Caching technology. In addition, Oracle offers plug-ins (Proxy, OC4J, and OSSO) for integration of the Oracle Application Server with non-Oracle HTTP Servers. These plug-ins are available off the Oracle Application Server 10g Companion CD.
Retrieved from: http://www.oracle.com/technology/products/ias/ohs/index.html
FAQ on Oracle HTTP Server (OHS)
The answer for this question took me and Google hours. And finally I got the answer:
What is OHS?
Oracle HTTP Server is often called Oracle HTTP Server.
What version of Apache is Oracle HTTP Server based on?
Two versions. Apache version 1.3.34 for OHS based on Apache 1.3 and Apache version 2.0.55 for OHS based on Apache 2.0.
Are Apache modules, not provided by Oracle, supported when integrated with OHS?
Oracle only provides support for modules included in the Oracle distribution. Oracle does not support modules obtained from any other source, including the Apache Software Foundation. However, OHS will still be supported when non-Oracle provided modules are included. If Oracle Support suspects that a non-Oracle provided module is contributing to a reported problem, customers may be requested to reproduce the problem without that module being included.
What is Oracle’s policy on fixing security bugs found in OHS?
Oracle’s policy and process for fixing security vulnerabilities can be found on Oracle’s SecAlert OTN page.
Is Oracle HTTP Server available as a standalone product too?
Yes. A new ‘Web Server and Process Management’ installation option let you install just the Oracle HTTP Server based on Apache 1.3 (with Oracle Process Manager and Notification Server). Also, Oracle HTTP Server based on Apache 2.0 is available to be installed as a standalone product off the Oracle Application Server 10g (10.1.3) Companion CD.
Is Apache v2.0 version of OHS supported with this release?
Yes. However, OHS based on Apache 2.0 is only supported in a standalone deployment version. It has the same functionality as OHS based on Apache 1.3 except for the following:
• IPv6 is supported in OHS based on Apache 2.0 but not in OHS based on Apache 1.3
• mod_oradav is not supported in OHS based on Apache 2.0
• mod_dms is not supported in OHS based on Apache 2.0
• mod_plsql is not supported in OHS based on Apache 2.0
Does OHS need to run as root?
No. OHS needs to run as root only when users wants to use port less than 1024. If this will never be the case, then they can run OHS as the user that installed Oracle Application Server rather than root. In order to do this, perform the following steps:
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
8 If you are using mod_osso, re-register mod_osso
9 Exit root
10 Restart OHS
Can I compress output from OHS (ex. gzip)?
In general, the recommendation is to use Web Cache for this purpose. There are other freeware modules (for example, mod_gzip) that may be plugged in for this purpose - but their use is not supported.
Why do I see a warning about the use of EAPI when starting OHS with a non-Oracle provided module?
Oracle HTTP Server is compiled with EAPI (Extended API) support. The EAPI is an extension to the Apache module API provided by mod_ssl (see http://www.modssl.org). If you see a message similar to the following example when starting OHS with a non-Oracle provided module it means that the module being loaded was not compiled with the EAPI.
[Mon Oct 31 12:11:37 2005] [warn] Loaded DSO libexec/mod_python.so uses plain Apache 1.3 API, this module might crash under EAPI! (please recompile it with -DEAPI)
In most cases, this warning message can safely be ignored. To eliminate the error message, the module can be recompiled using the -DEAPI compile time option and either the Apache header files provided with OHS in $ORACLE_HOME/Apache/Apache/include or the header files from a generic Apache instance that includes modssl and the corresponding EAPI additions.
Can a standalone OHS based on Apache 2.0 communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. Standalone OHS based on Apache 2.0 can be configured to communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 2 ‘Configuring Standalone Oracle HTTP Server with Oracle Application Server’ of Oracle HTTP Server Standalone Administrator’s Guide Based on Apache 2.0 for details on how to configure it.
Can an OHS of Oracle Application Server 10g Release 2 (10.1.2) be used to route requests to Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. You can configure an OHS of Oracle Application Server 10g Release 2 (10.1.2) middle-tier instance to communicate with Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 6 ‘Reconfiguring Application Server Instances’ of Oracle Application Server Administrator’s Guide 10g Release 3 (10.1.3) for details on how to configure it.
What is mod_oc4j?
Mod_oc4j is the load balancer for the requests going to the Oracle Application Server Container for J2EE (OC4J) Instances in Oracle Application Server. It is an OHS module that provides routing between OHS and OC4J. The Oracle Process Manager and Notification Server (OPMN) component of Oracle Application Server keeps mod_oc4j aware of the status of different OC4J processes - thus, mod_oc4j routes only to the processes that are up and running. Mod_oc4j also understands the concepts of Oracle Application Server Cluster and OC4J groups, and routes accordingly to provide as much transparent failover as possible.
Does mod_oc4j work with web servers other than OHS?
Yes. Mod_oc4j is available as a plug-in too, called OC4J Plug-in, to work with non-Oracle web servers too including IIS, iPlanet, and generic Apache.
What are the different routing/load balancing algorithms?
Mod_oc4j provides three distinct kinds of routing: (a) round robin, (b) random and (c) metric based. The effective performance of round robin and random algorithms is the same. The latter, metric based routing, is based on OC4J process informing mod_oc4j of a metric based on its internal resource availability (ex. connection pools). Mod_oc4j then uses this metric to make routing decisions.
These load balancing/routing algorithms also have a flavor - affinity based. In this mode (it is the default mode), these algorithms will always route to the local node, except in cases when no process is available on the local node. The random and round robin algorithms have an extra flavor - weight based. In case of weight based, mod_oc4j distributes requests according to the routing weight configured for each host. Refer to Oracle HTTP Server Administrator’s Guide for more details on load balancing algorithms.
Can mod_oc4j talk to OC4J using SSL?
Yes, the AJP communication between mod_oc4j and OC4J processes can now be over AJP/SSL. Previously, this was in the clear text. Also, the SSL negotiation does not happen each time the two need to talk - resulting in less performance impact.
There are no Oc4jMount directives in my mod_oc4j.conf file, how does mod_oc4j know where to route the requests?
In previous releases of Oracle Application Server (version 10.1.2 and earlier), OC4J mount points were statically configured in mod_oc4j.conf file. Thus, when a user deployed or un-deployed an application, mod_oc4j.conf file was updated and OHS restarted.
In Oracle Application Server 10g Release 3 (10.1.3), OC4Js announce their mount-point(s) in the notifications they send out and mod_oc4j dynamically adjusts its routing table using this information. This eliminates the need for static mount point configuration and enables mod_oc4j to update its mount point configuration dynamically (without restarting OHS).
Can I still use the old static mount point configuration?
Yes. Although dynamic mount point creation is enabled by default, you do have the option of continuing to use statically configured mount points. You can configure this by setting a new directive called Oc4jRoutingMode to ‘Static’ in mod_oc4j.conf file. Directive Oc4jRoutingMode specifies the routing behavior and can take one of the following values:
• Dynamic – This specifies that the new dynamic routing functionality is used and any old style routing configuration is ignored.
• Static – This informs mod_OC4J to use 9.0.4/10.1.2 style routing configuration (where mount points are explicitly listed). Dynamic routing is not used.
• DynamicOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the dynamically specified mount points.
• StaticOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the statically specified mount points.
Is it possible to dump/view the list of current mount points mod_oc4j is using?
Yes, you can dump/view the in-memory mod_oc4j routing table contents by going to the following URL:
http://localhost:/oc4j-status
The information you will find here includes configured load balancing algorithm, routing mode, routing id, application name, context and the OC4J process(s) the application requests are routing to.
The URI for this pages is configurable, and by default is located in the dms.conf file in $ORACLE_HOME/Apache/Apache/conf directory. The default configuration only allows it to be accessible from the localhost (127.0.0.1) Virtual Host. The configuration snippet looks like this:
Oc4jSet StatusUri /oc4j-status
You can place this snippet anywhere in the httpd.conf if you want to see that status page under the default server, instead of only accessing it from the localhost Virtual Host. If you do that, the URL to dump/view routing table will be:
http://:/oc4j-status
What is routing ID?
The routing ID specifies a routing relationship between OC4Js and OHSs. In other words, an OHS routes to every OC4J that it shares a routing ID with. Every OC4J is assigned a routing ID, similarly each OHS is assigned one or more routing IDs to route to.
OPMN passes the routing ID to OC4J as a system property and to OHS as an environment variable when it is started. OC4J adds this routing ID to the ONS notifications it publishes. OHS listens for notifications from OC4J. When an OHS sees the first notification from an OC4J containing a routing ID on its list, it begins routing to it.
The addition of routing IDs and mount point discovery in Oracle Application Server 10g Release 3 (10.1.3) version of OHS allows mod_OC4J to dynamically discover all aspects of OC4J routing.
Should I configure routing ID for OHS in both mod_oc4j.conf and opmn.xml files?
No. Out of the box, OHS is configured to pick up its routing ID from opmn.xml file. Though it is possible to configure routing IDs for OHS in both opmn.xml and directly in mod_oc4j.conf file, but if OHS is configured with routing-id in both places, it considers it an error and fails to start. So routing IDs for OHS should either be configured in opmn.xml (specified as module data under element or under element of OHS) or mod_oc4j.conf but not at both places.
Source: http://www.oracle.com/technology/products/ias/ohs/htdocs/ohs-1013-faq.pdf
What is OHS?
Oracle HTTP Server is often called Oracle HTTP Server.
What version of Apache is Oracle HTTP Server based on?
Two versions. Apache version 1.3.34 for OHS based on Apache 1.3 and Apache version 2.0.55 for OHS based on Apache 2.0.
Are Apache modules, not provided by Oracle, supported when integrated with OHS?
Oracle only provides support for modules included in the Oracle distribution. Oracle does not support modules obtained from any other source, including the Apache Software Foundation. However, OHS will still be supported when non-Oracle provided modules are included. If Oracle Support suspects that a non-Oracle provided module is contributing to a reported problem, customers may be requested to reproduce the problem without that module being included.
What is Oracle’s policy on fixing security bugs found in OHS?
Oracle’s policy and process for fixing security vulnerabilities can be found on Oracle’s SecAlert OTN page.
Is Oracle HTTP Server available as a standalone product too?
Yes. A new ‘Web Server and Process Management’ installation option let you install just the Oracle HTTP Server based on Apache 1.3 (with Oracle Process Manager and Notification Server). Also, Oracle HTTP Server based on Apache 2.0 is available to be installed as a standalone product off the Oracle Application Server 10g (10.1.3) Companion CD.
Is Apache v2.0 version of OHS supported with this release?
Yes. However, OHS based on Apache 2.0 is only supported in a standalone deployment version. It has the same functionality as OHS based on Apache 1.3 except for the following:
• IPv6 is supported in OHS based on Apache 2.0 but not in OHS based on Apache 1.3
• mod_oradav is not supported in OHS based on Apache 2.0
• mod_dms is not supported in OHS based on Apache 2.0
• mod_plsql is not supported in OHS based on Apache 2.0
Does OHS need to run as root?
No. OHS needs to run as root only when users wants to use port less than 1024. If this will never be the case, then they can run OHS as the user that installed Oracle Application Server rather than root. In order to do this, perform the following steps:
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
8 If you are using mod_osso, re-register mod_osso
9 Exit root
10 Restart OHS
Can I compress output from OHS (ex. gzip)?
In general, the recommendation is to use Web Cache for this purpose. There are other freeware modules (for example, mod_gzip) that may be plugged in for this purpose - but their use is not supported.
Why do I see a warning about the use of EAPI when starting OHS with a non-Oracle provided module?
Oracle HTTP Server is compiled with EAPI (Extended API) support. The EAPI is an extension to the Apache module API provided by mod_ssl (see http://www.modssl.org). If you see a message similar to the following example when starting OHS with a non-Oracle provided module it means that the module being loaded was not compiled with the EAPI.
[Mon Oct 31 12:11:37 2005] [warn] Loaded DSO libexec/mod_python.so uses plain Apache 1.3 API, this module might crash under EAPI! (please recompile it with -DEAPI)
In most cases, this warning message can safely be ignored. To eliminate the error message, the module can be recompiled using the -DEAPI compile time option and either the Apache header files provided with OHS in $ORACLE_HOME/Apache/Apache/include or the header files from a generic Apache instance that includes modssl and the corresponding EAPI additions.
Can a standalone OHS based on Apache 2.0 communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. Standalone OHS based on Apache 2.0 can be configured to communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 2 ‘Configuring Standalone Oracle HTTP Server with Oracle Application Server’ of Oracle HTTP Server Standalone Administrator’s Guide Based on Apache 2.0 for details on how to configure it.
Can an OHS of Oracle Application Server 10g Release 2 (10.1.2) be used to route requests to Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. You can configure an OHS of Oracle Application Server 10g Release 2 (10.1.2) middle-tier instance to communicate with Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 6 ‘Reconfiguring Application Server Instances’ of Oracle Application Server Administrator’s Guide 10g Release 3 (10.1.3) for details on how to configure it.
What is mod_oc4j?
Mod_oc4j is the load balancer for the requests going to the Oracle Application Server Container for J2EE (OC4J) Instances in Oracle Application Server. It is an OHS module that provides routing between OHS and OC4J. The Oracle Process Manager and Notification Server (OPMN) component of Oracle Application Server keeps mod_oc4j aware of the status of different OC4J processes - thus, mod_oc4j routes only to the processes that are up and running. Mod_oc4j also understands the concepts of Oracle Application Server Cluster and OC4J groups, and routes accordingly to provide as much transparent failover as possible.
Does mod_oc4j work with web servers other than OHS?
Yes. Mod_oc4j is available as a plug-in too, called OC4J Plug-in, to work with non-Oracle web servers too including IIS, iPlanet, and generic Apache.
What are the different routing/load balancing algorithms?
Mod_oc4j provides three distinct kinds of routing: (a) round robin, (b) random and (c) metric based. The effective performance of round robin and random algorithms is the same. The latter, metric based routing, is based on OC4J process informing mod_oc4j of a metric based on its internal resource availability (ex. connection pools). Mod_oc4j then uses this metric to make routing decisions.
These load balancing/routing algorithms also have a flavor - affinity based. In this mode (it is the default mode), these algorithms will always route to the local node, except in cases when no process is available on the local node. The random and round robin algorithms have an extra flavor - weight based. In case of weight based, mod_oc4j distributes requests according to the routing weight configured for each host. Refer to Oracle HTTP Server Administrator’s Guide for more details on load balancing algorithms.
Can mod_oc4j talk to OC4J using SSL?
Yes, the AJP communication between mod_oc4j and OC4J processes can now be over AJP/SSL. Previously, this was in the clear text. Also, the SSL negotiation does not happen each time the two need to talk - resulting in less performance impact.
There are no Oc4jMount directives in my mod_oc4j.conf file, how does mod_oc4j know where to route the requests?
In previous releases of Oracle Application Server (version 10.1.2 and earlier), OC4J mount points were statically configured in mod_oc4j.conf file. Thus, when a user deployed or un-deployed an application, mod_oc4j.conf file was updated and OHS restarted.
In Oracle Application Server 10g Release 3 (10.1.3), OC4Js announce their mount-point(s) in the notifications they send out and mod_oc4j dynamically adjusts its routing table using this information. This eliminates the need for static mount point configuration and enables mod_oc4j to update its mount point configuration dynamically (without restarting OHS).
Can I still use the old static mount point configuration?
Yes. Although dynamic mount point creation is enabled by default, you do have the option of continuing to use statically configured mount points. You can configure this by setting a new directive called Oc4jRoutingMode to ‘Static’ in mod_oc4j.conf file. Directive Oc4jRoutingMode specifies the routing behavior and can take one of the following values:
• Dynamic – This specifies that the new dynamic routing functionality is used and any old style routing configuration is ignored.
• Static – This informs mod_OC4J to use 9.0.4/10.1.2 style routing configuration (where mount points are explicitly listed). Dynamic routing is not used.
• DynamicOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the dynamically specified mount points.
• StaticOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the statically specified mount points.
Is it possible to dump/view the list of current mount points mod_oc4j is using?
Yes, you can dump/view the in-memory mod_oc4j routing table contents by going to the following URL:
http://localhost:
The information you will find here includes configured load balancing algorithm, routing mode, routing id, application name, context and the OC4J process(s) the application requests are routing to.
The URI for this pages is configurable, and by default is located in the dms.conf file in $ORACLE_HOME/Apache/Apache/conf directory. The default configuration only allows it to be accessible from the localhost (127.0.0.1) Virtual Host. The configuration snippet looks like this:
Oc4jSet StatusUri /oc4j-status
You can place this snippet anywhere in the httpd.conf if you want to see that status page under the default server, instead of only accessing it from the localhost Virtual Host. If you do that, the URL to dump/view routing table will be:
http://
What is routing ID?
The routing ID specifies a routing relationship between OC4Js and OHSs. In other words, an OHS routes to every OC4J that it shares a routing ID with. Every OC4J is assigned a routing ID, similarly each OHS is assigned one or more routing IDs to route to.
OPMN passes the routing ID to OC4J as a system property and to OHS as an environment variable when it is started. OC4J adds this routing ID to the ONS notifications it publishes. OHS listens for notifications from OC4J. When an OHS sees the first notification from an OC4J containing a routing ID on its list, it begins routing to it.
The addition of routing IDs and mount point discovery in Oracle Application Server 10g Release 3 (10.1.3) version of OHS allows mod_OC4J to dynamically discover all aspects of OC4J routing.
Should I configure routing ID for OHS in both mod_oc4j.conf and opmn.xml files?
No. Out of the box, OHS is configured to pick up its routing ID from opmn.xml file. Though it is possible to configure routing IDs for OHS in both opmn.xml and directly in mod_oc4j.conf file, but if OHS is configured with routing-id in both places, it considers it an error and fails to start. So routing IDs for OHS should either be configured in opmn.xml (specified as module data under
Source: http://www.oracle.com/technology/products/ias/ohs/htdocs/ohs-1013-faq.pdf
Explain Plan
* Explain Plan is a statement that lets you to have execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.
* A Plan table holds execution plans generated by Execute plan statement. Typical name is plan_table but any name can be given for a plan table.
* utlxplan.sql file in $ORACLE_HOME/rdbms/admin contains script to create plan table.
* Privileges required for Explain Plan:
-> INSERT privilege for Explain Plan
-> EXECUTE privilege for statement execution
-> SELECT privilege on underlying table/view
Syntax:
EXPLAIN PLAN
[SET STATEMENT_ID = {string in single quotes}]
[INTO{plan table name}]
FOR
{SQL statement};
Output extraction:
select id, parent_id, LPAD('',LEVEL-1)||
operation ||' '||options operation, object_name
from plan_table
where statement_id = '&statement_id'
start with id=0
and statement_id = '&statement_id'
connect by prior id = parent_id
and statement_id = '&statement_id';
Explain Plan limitations:
The real plan that gets used may differ from what Explain Plan tells due to:
1. Optimizer stats, cursor sharing, bind variable peeking, dynamic instance parameters makes plans less stable.
2. Explain Plan does not peek at bind variables.
3. Explain Plan does not check library cache to see if the statement has already been parsed.
4. Explain Plan does not work for some queries
ORA-22905 : cannot access rows from a non-nested table item.
* A Plan table holds execution plans generated by Execute plan statement. Typical name is plan_table but any name can be given for a plan table.
* utlxplan.sql file in $ORACLE_HOME/rdbms/admin contains script to create plan table.
* Privileges required for Explain Plan:
-> INSERT privilege for Explain Plan
-> EXECUTE privilege for statement execution
-> SELECT privilege on underlying table/view
Syntax:
EXPLAIN PLAN
[SET STATEMENT_ID = {string in single quotes}
[INTO
FOR
Output extraction:
select id, parent_id, LPAD('',LEVEL-1)||
operation ||' '||options operation, object_name
from plan_table
where statement_id = '&statement_id'
start with id=0
and statement_id = '&statement_id'
connect by prior id = parent_id
and statement_id = '&statement_id';
Explain Plan limitations:
The real plan that gets used may differ from what Explain Plan tells due to:
1. Optimizer stats, cursor sharing, bind variable peeking, dynamic instance parameters makes plans less stable.
2. Explain Plan does not peek at bind variables.
3. Explain Plan does not check library cache to see if the statement has already been parsed.
4. Explain Plan does not work for some queries
ORA-22905 : cannot access rows from a non-nested table item.
An XML trick - CSV format for any table
One more trick with xml.
I want to get a semi-column separated format without having to specify the columns
alter session set nls_date_format='YYYY-MM-DD';
Session altered.
select regexp_replace(column_value,' *<[^>]*>[^>]*>’,';’)
from table(xmlsequence(cursor(select * from emp)));
;7369;SMITH;CLERK;7902;1980-12-17;800;20;
;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30;
;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30;
;7566;JONES;MANAGER;7839;1981-04-02;2975;20;
;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30;
;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30;
;7782;CLARK;MANAGER;7839;1981-06-09;2450;10;
;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20;
;7839;KING;PRESIDENT;1981-11-17;5000;10;
;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30;
;7876;ADAMS;CLERK;7788;1987-05-23;1100;20;
;7900;JAMES;CLERK;7698;1981-12-03;950;30;
;7902;FORD;ANALYST;7566;1981-12-03;3000;20;
;7934;MILLER;CLERK;7782;1982-01-23;1300;10;
Courtesy:
http://laurentschneider.com/wordpress/2007/05/
csv-format-with-select.html
Usage of check constraints - A good example
In this blog I will illustrate usage of CHECK constraint. A CHECK constraint is used to check for the right value before a value is inserted into a table.
In our situation, we have a table where a candidate's information is stored. We are going to validate the value for Children based on the Marital status using a CHECK constraint. The logic of the same is like if the candidate is married the children field can have value of 0 or more. If he is single, the field will only accept value of 0.
We will create the table Personal_Details as follows:
CREATE TABLE PERSONAL_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));
This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.
In our situation, we have a table where a candidate's information is stored. We are going to validate the value for Children based on the Marital status using a CHECK constraint. The logic of the same is like if the candidate is married the children field can have value of 0 or more. If he is single, the field will only accept value of 0.
We will create the table Personal_Details as follows:
CREATE TABLE PERSONAL_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));
This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.
Subscribe to:
Posts
(
Atom
)