Oracle Proc to SQL Reporting Services: character set name is not recognized

Question

A few months ago Oracle queries stopped working on our reporting services server after a service pack on the reporting server. I ended up having to grant permissions on Oracle directories to eliminate the first error and then renamed the NLS_LANG registry entry (effectively deleting it) to get rid of the second error. The old value was: AMERICAN_AMERICA.WE8MSWIN1252

Now we are moving some new reports into production which use Oracle stored procedures and they are getting this error.

   --- End of inner exception stack trace ---
w3wp!processing!8!7/27/2009-09:26:09:: e ERROR: An exception has occurred in data source 'CSUD3_RPTAPL'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'MYDATASET'. ---> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 57:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

I've created a test procedure that simply returns

select 'test' "field1" from dual;

It gets the same error. Tonight I will try setting NLS_LANG back to it's old value and see if that fixes the procedures - although it will break the regular oracle reports. If anyone has a clue what is going on, please enlighten me.

From what I've read, the client should be using the database default if the charset is not specified on the client.

Answer 1

The key was the first error message:

ORA-12705: Cannot access NLS data files or invalid environment specified.

Which led to this:

http://www.dba-oracle.com/t_ora_12705_error.htm

And then a closer examination of the folder permissions. I needed to grant perms to authenticated users on the ORACLE_HOME directory and subdirs.

Working now!