ORA-01000: maximum open cursors exceeded

This ORA error message can occur in different situations. One such situation arises due to a silly mistake when PLSQL invokes a Java method which accesses database.

Let us see how we can reproduce the issue.

1. Create a stored procedure of language Java.

create or replace and compile java source named DBUser as
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
public class DBUser {
    public static String getUser()
    {
        String sql =
            "SELECT user FROM dual ";           
        String myUser="myUser";
        try {
            Connection conn =  DriverManager.getConnection("jdbc:default:connection:");
            for (int i = 0; i< 10000; i++) {
                PreparedStatement pstmt = conn.prepareStatement(sql);
                ResultSet rset = pstmt.executeQuery();
                rset.next();
                myUser = rset.getString(1);
                rset.close();
                /*pstmt.close();*/
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return myUser;
    }
}
Please note that PreparedStatement is not explicitly closed.

2. Create a PLSQL function which invokes the Java stored procedure.

create or replace function getDBUser return varchar2
AS LANGUAGE JAVA NAME  'DBUser.getUser()  return oracle.sql.String';

3. Execute the PLSQL procedure in a loop

select getDBUser from dual;

The error message will be reported when the for loops iteration crosses the number of allowed open cursors.The number of allowed open cursors in the system can be checked with the below SQL.

 select value from v$parameter where name = 'open_cursors';


Thus, if the issue is encountered, it is a good idea to check if there are any Java methods which misses the explicit close statements on resultSet, PreparedStatement  etc.. . This is often overlooked by inexperienced Java developers (or PLSQL developers writing one off Java code).

Oracle Time and Labor Layouts - Changing Prompts

One of the simpler requirements in OTL layout customizations is to change the prompt and other labels appearing on the layout. The following steps guides to achieve this.

Sample Requirement

In the timecard entry layout, change the prompt for Expenditure Type from "Type" to "Exp Type"

Steps

1. Identify the name of the layout to be modified. You can use the below SQL to identify the name of the layout.
Oracle Time and Labor: Identify Layout



2. Once you have the layout name, execute the below SQL which will display all components in the layout.

SELECT comp.component_name

      ,comp.region_code

      ,attr.name attribute_name

      ,attr.attribute_label_long   attribute_prompt

      ,region.attribute_label_long region_item_prompt

FROM   hxc_layout_components comp

      ,hxc_layouts_vl        lay

      ,ak_region_items_vl    region

      ,ak_attributes_vl      attr

WHERE  lay.layout_id = comp.layout_id

AND    attr.attribute_code(+) = comp.attribute_code

AND    region.region_code = comp.region_code

AND    region.attribute_code = comp.attribute_code

AND    lay.layout_name = '&Layout'

ORDER  BY comp.sequence;

 3. Identify the component from the result set. This can be done easily since the label appearing on the front end will be listed either as ATTRIBUTE_PROMPT or as REGION_ITEM_PROMPT.

4. Navigate to "AK Developer" responsibility and navigate to "Define Regions" page.

5. Query up the Region Code from the result set on the screen.

6. Open "Region Items" page and query for the attribute code from the result set

7. Scroll to "Long Label" field and change the prompt to new value. Save the record.

Verify Results

Navigate to the page again to verify the results.

Warning

The region is usually shared with other layouts. If you want to change only one layout, then it will be better to create a custom attribute and customize the required layout to use the custom attribute, instead of the standard layout