Ask Anantha

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...

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 t...

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 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 The above query assumes that all_objects returns at least 365 records.

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;

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 DBMS_DDL.CREATE_WRAPPED The DBMS_DDL.WRAP function simply wraps the source given and returns the wrapped source code. It does no...

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_DM...

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'.

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 fle...

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 foun...

* 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 ...

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/ ...

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'...