max open_cursor

[BJCooperIT]

I am writing PL/SQL scripts with dynamic SQL for a data conversion project. While testing, I exceeded the maximum number of open cursors. I was only loading 1415 rows and the max parameter is currently set to 650 (Oracle 8.0.5 database). I e-mailed the support DBAs and asked to have the maximum increased. They, in turn, have e-mailed me back (with a hint of attitude) demanding to know why I need it increased. This leads me to two sets of questions.

1. What is the impact of increasing the parameter to a larger number or to the max? Does it require more disk space? More memory? Why would they object to the increase?

2. I wrote a function to ease my code for executing dynamic sql as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace
function F_EXECUTE_SQL (P_SQL in varchar2) return varchar2
is
/*============================*/
/* Execute an SQL text string */
/*============================*/
C_SUCCESS varchar2(10) := 'SUCCESS';
C_FAILURE varchar2(10) := 'FAILURE';
V_CURSOR_ID integer;
V_ROWS integer;

begin

/*==============================================*/
/* Open a cursor workarea - returns a cursor ID */
/*==============================================*/
V_CURSOR_ID := dbms_sql.open_cursor;

/*================================*/
/* Parse the statement for errors */
/*================================*/
dbms_sql.parse(V_CURSOR_ID, P_SQL, dbms_sql.native);

/*=======================*/
/* Execute the statement */
/*=======================*/
V_ROWS := dbms_sql.execute(V_CURSOR_ID);

/*==================*/
/* Close the cursor */
/*==================*/
dbms_sql.close_cursor(V_CURSOR_ID);

return(C_SUCCESS);

exception
when NO_DATA_FOUND then return(C_FAILURE);
when OTHERS then return(C_FAILURE);
end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am puzzled as to why I am exceeding the max. I call this function to drop a table, create a table, and create an index. I then use UTL_FILE to read my flat file and each row insert makes a call to this function. Does this leave a cursor open each time it is executed even though I explicitly close it? Would the result be different if I were in 8.1.7 using EXECUTE IMMEDIATE?

Thanks in advance for your help and insight.
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[BJCooperIT]

Doing a little more research I found this web site:

http://www.jamesbelton.co.uk/html/quick_tips.html

That says:
"In a Forms environment, set your OPEN_CURSORS parameter artificially high. Forms can be pretty cursor intensive and to exceed the maximum number of open cursors specified in the INIT.ORA file is a common show-stopper, especially if you follow the examples Oracle give you. There is no overhead on having OPEN_CURSORS set high, so crank it up over the 600 mark (you can suggest your code-writers use cursors efficiently in their code too!!)."
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[AOLEURO]

Hi BJcooperIT


Increasing OPEN_CURSORS do not generate overhead, but then Oracle handles more cursors it is the Library Cache in Shared Pool that must be big enough to hold cursors. The value is PER session.

Some applications like a high value.

You set the value in PFILE.

You use GetHitRatio and PinHitRatio to monitor cursors.

You can see a reply I made on thread thread186-498701

Are you sure you do not make recursive calls to the function or other functions (perhaps indirectly from other codes) – then you have a lot of open cursors before hitting the max. cursors.

See also CURSOR_SHARING and CURSOR_SPACE_FOR_TIME.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[BJCooperIT]

I don't think I have recursive code (* uses F_EXECUTE_SQL).
Pseudo code is:

01. Drop* table if input parameter specifies to do so
02. If table does not exist create* it and create* index
03. Open input flat file using UTL_FILE
04. Open error output flat file using UTL_FILE
05. Read input flat file until end-of-file
06. Extract comma delimited fields into variables
07. Validate fields for compatible content
08. If data is not compatible, write record to error file
and display record and error using DBMS_OUTPUT
09. If data IS compatible insert* into table
10. Loop back to #05
11. Close input flat file
12. Close output error file

The check to insure data is compatible is a select against the ALL_TAB_COLUMNS table that get the data format, width and precision. It also uses another function which has been widely discussed here lately called:

function F_IS_IT_NUMERIC (P_TEXT in varchar2)
return varchar2 is
/*====================================================*/
/* Determine if a text field is numeric - returns Y/N */
/*====================================================*/
V_NUMBER number;
begin
V_NUMBER := P_TEXT;
return('Y');
exception
when VALUE_ERROR then return('N');
when OTHERS then return('N');

Do you see a problem with this approach?
Thanks! (select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[AOLEURO]

Hi

I see 3 problems:

1. In the function F_EXECUTE_SQL you need to CHECK for Open Cursor in the EXCEPTION clause. If you hit a error then you do NOT close you cursor before you exit the function. It is your problem - I hope

2. In your description no. 2 - you create Table and Indexes. I would create indexes at the end of all - because you only insert rows and it is faster to build index after insert of rows - and insert of rows is faster without indexes.

3. Yep - I would not use the exception to trap non-numeric values - why not use a function like:

REPLACE (TRANSLATE (column_name, '0123456789', '1111111111'),'1') IS NULL

DECODE(TRANSLATE(column_name,'0123456789','1111111111'),SubStr('1111111111',1,length(column_name)),to_number(column_name),NULL) IS NULL

Just some ideas Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[BJCooperIT]

Thanks for the feedback Allan.

1. In the function F_EXECUTE_SQL you need to CHECK for Open Cursor in the EXCEPTION clause.

I will add this.


2. In your description no. 2 - you create Table and Indexes. I would create indexes at the end ...

I agree it would be faster but I am using the index to prevent the loading of duplicate keys.


3. Yep - I would not use the exception to trap non-numeric values ...

Since I am receiving the data in text strings and use it many times it seemed prudent to create one piece of reusable code that would handle all numeric fields regardless of width, precision, or sign. Example:
V_YN1 := F_IS_IT_NUMERIC(123.4);
V_YN2 := F_IS_IT_NUMERIC(34873498703);
V_YN3 := F_IS_IT_NUMERIC(.5);

I guess I will have to wait to see if bumping the max cursors will solve my problem. Providing, of course, that the DBAs agree to my request. I was hoping to keep my design so that it would only commit if there were no data errors. For right now I have placed a commit behind each insert and that circumvents the max cursors error.
(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000