Hi @atvanst,

What kind of tasks are the users performing when they get this error? There is a bug which throws similar errors for quick reports and attachments. It is fixed in UPD 7 via patch ID 150450. 

Hope this helps!

They get it as soon as they start IFS just opening various windows or running some IFS standard reports. It has worked fine before for these users but these errors started to occur yesterday. Have not heared that there where issues with any quick reports or attachements.

Hi @atvanst,

Have you solved your issue ? How ?
Have some message : 
DBMS_SQL access denied when transforming connections FOR target view …

  best regards 
    Jean-Luc

Hi, We restarted our MWS’s and seems issue went away, at least for now.

/Anna

We received this error today.  It appears to have affected attachments and business reporter.  We’re unsure of the root cause, but do have a server backup appliance that potentially caused access issues to the database and/or the repository for the IFS docs (ours is setup as a file share on our network).  All seems to be working at the moment, and we are on version 10 update 12.

The error appeared in the Oracle alert logs and referenced a trace file, but that didn’t seem to have any helpful info.

We are planning to more closely monitor our server backups to see if the problem re-appears.  Anyone have any other updates/ideas?

Hello,

One of our customers has the same problem: 

Did you manage to solve it?

Thank you for your help! 

I wish I could say we fixed the issue.  Unfortunately, it seems to be one of those problems that went away on its own and I can’t explain.  I’m still suspicious of our server backups causing some temporary unavailability of the shared repository for the attachments, but so far have been unable to conclusively point to a specific cause.

The answer to this is based on this info from Oracle doc:

Preventing Malicious or Accidental Access of Open Cursor Numbers

An error, ORA-29471, is raised when any DBMS_SQL subprogram is called with a cursor number that does not denote an open cursor. When the error is raised, an alert is issued to the alert log and DBMS_SQL becomes inoperable for the life of the session.

If the actual value for the cursor number in a call to the IS_OPEN Function denotes a cursor currently open in the session, the return value is TRUE. If the actual value is NULL, then the return value is FALSE. Otherwise, this raises an ORA-29471 error.

As of App10/UPD18 there is at least one place in the program that may try to close a cursor which is not opened (as happened in our case) Quick_Report_API.Validate_SQL_Expression___, we fixed it ourselves, using the technique in #1 below.
There might be other cases like that.

The key part is what is bold above.

So after this happens, those parts of code, anywhere else in the system,  which use DBMS_SQL, will throw an exception from that Oracle session. Other Oracle sessions will be fine.
App server restart will solve this, until next improper usage of DBMS_SQL.

More info form Oracle support below, in #3 containing a permanent - although insecure, so not recommended - solution.

1. Change code to protect all close cursor operations with the following is_open check.

if dbms_sql.is_open(mycursor) then
DBMS_SQL.CLOSE_CURSOR(mycursor);
end if;

-or-

2.  Implement and use the new parameter security_level with dbms_sql.open_cursor which applies fine-grained control to the security of the opened cursor. Level 0 allows all dbms_sql operations on the cursor without any security checks. Document 556301.1 contains more details.

dbms_sql.open_cursor (
SECURITY_LEVEL in integer)
return integer;

-or-

3. To completely turn off the security features in dbms_sql, you can set the following event.  However this is NOT a recommended solution.

alter system set "_dbms_sql_security_level" = 384 scope=spfile;
+ DB restart

Has IFS provided any bug correction to this issue or what is the IFS recommendation for this issue?

Hi @kjro ,

This error "DBMS_SQL SECURITY ERROR: ORA-29471" is a generic Oracle issue which will be thrown when trying to close a cursor that is already closed using dbms_sql.close_cursor.  @jasahu has explained it in his comment.

Oracle has provided solutions for this issue in their official documentation. Since this is a DB issue, it has to be handled from DB level with the help of a Database Administrator. Please refer the below documents when resolving this issue.

ORA-29471 Error When Executing dbms_sql.close_cursor ( Doc ID 760560.1 )

Ora-29471: Dbms_sql Access Denied" From Software Package After Upgrade ( Doc ID 556301.1 )

Hope it helps.

Thank you,

Aswin.

@Aswin Shadhujan Are you then recommending to set the hidden oracle parameter _dbms_sql_security_level? And if that’s the case where in the IFS documentation is this stated or is it set in the oracel database template?

This is the solution from the doc id you mentioned, and are you then actually recommending step 3 that isn’t recommended by Oracle, and step 1 and 2 points to a code change. I still think it needs a bug correction from IFS

1. Change code to protect all close cursor operations with the following is_open check.
if dbms_sql.is_open(mycursor) then
DBMS_SQL.CLOSE_CURSOR(mycursor);
end if;

-or-

2.  Implement and use the new parameter security_level with dbms_sql.open_cursor which applies fine-grained control to the security of the opened cursor. Level 0 allows all dbms_sql operations on the cursor without any security checks. Document 556301.1 contains more details.

dbms_sql.open_cursor (
SECURITY_LEVEL in integer)
return integer;

-or-

3. To completely turn off the security features in dbms_sql, you can set the following event.  However this is NOT a recommended solution.

alter system set "_dbms_sql_security_level" = 384 scope=spfile;

I have not reported it to IFS because we needed an quick fix (and from IFS we could not expect that) as this bug can have major consequences , once it occurs the only workaround was to restart App Server.

So we fixed it locally in QuickReport-Cust.plsql:
 

@Overtake Core
FUNCTION Validate_SQL_Expression___ (
   validate_status_      OUT VARCHAR2,   
   stmt_                 IN  CLOB,
   is_import_validation_ IN  BOOLEAN DEFAULT FALSE) RETURN VARCHAR2
IS
BEGIN

$TEXTSEARCH   
      WHEN OTHERS THEN
         dbms_sql.close_cursor(cursor_);
         validate_status_ := 'INVALID';
$TEXTREPLACE
      WHEN OTHERS THEN
         --(+) 230215  JASAHU  C_U01-3 (START)
         IF dbms_sql.is_open(cursor_) THEN
         --(+) 230215  JASAHU  C_U01-3 (FINISH)
            dbms_sql.close_cursor(cursor_);
         --(+) 230215  JASAHU  C_U01-3 (START)
         END IF;
         --(+) 230215  JASAHU  C_U01-3 (FINISH)
         validate_status_ := 'INVALID';
$TEXTEND   
   
END Validate_SQL_Expression___;
 

Hi.

I completely agree that this is a coding bug in IFS Apps. It is in general dangerous to play with the hidden parameters. 

See ORA-29471 DBMS_SQL SECURITY ERROR (anuroopchalladba.blogspot.com), especially the last comment:

When setting _dbms_sql_security_level with 384 (0x180), it means:
no security checks(level 0), no scan protection (0x80), and no security enforcement in open_cursor (0x100)

Setting this parameter to 384 in fact also disables security enforcement for open_cursor. Do we really want that?

Hi

Error handling still not corrected but it seems that patch 163601 from UPD17 masks problem.