Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-00604: error occurred at recursive SQL level 1

Highlighted

ORA-00604: error occurred at recursive SQL level 1

Hello all,

everytime I try to run a sql script with ksc_local_exit I get the following error:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION

However started from the servers command line the script runs with no error.

I have this problem on all kind of ksc_local_exec with sqlplus involed.

Any ideas?

Regards
Volker

9 REPLIES
Erik Cole
Honored Contributor

Re: ORA-00604: error occurred at recursive SQL level 1

Volker,

Can you post your command?

Re: ORA-00604: error occurred at recursive SQL level 1

Hi,

the command is part of the ootb Request Type Detail Report. Nothing is changed.

command:
ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @./scripts/kcrtrpt_request_type_detail_help "[AS.REPORT_DIR]" "[RP.FILENAME]" "[P.P_FROM_ITYPE]" "[P.P_TO_ITYPE]" "[P.P_SHOW_PARMS]" "[P.P_SHOW_STATUSES]" "[P.P_SHOW_RULES]" "[P.P_SHOW_COMMANDS]" "[P.P_SHOW_DEP]" "[P.P_EXPAND_SC]" "[P.P_SHOW_USER_ACCESS]" "[P.P_SHOW_WORKFLOW]" "[P.P_SHOW_HELP_CONTENT]" "[P.P_SHOW_DISPLAY_COL]"

Command in report log:
/app/wilma/oracle/1020/product/10.2.0/bin/sqlplus PPM/#!#*****#!#@WILMA @./scripts/kcrtrpt_request_type_detail_help "/app/wilma/home/ppm/reports/" "rep_30822" "Anforderung" "Anforderung" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "N" "Y"


Complete report log:
CL Local Exec

Quellbefehl: Run Report

/app/wilma/oracle/1020/product/10.2.0/bin/sqlplus PPM/#!#*****#!#@WILMA @./scripts/kcrtrpt_request_type_detail_help "/app/wilma/home/ppm/reports/" "rep_30822" "Anforderung" "Anforderung" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "N" "Y"

SQL*Plus: Release 10.2.0.4.0 - Production on Do Jan 29 14:14:07 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION


Prozess zerstört.


Zeitüberschreitung bei Befehlsausführung...

Enter user-name:


The german phrase "Prozess zerstört" means Process destroyed.
Zeitüberschreibung bei Befehlsausführung:
Timeout while command execution.

But the problem is not related to a special report. It is everytime when we try to run sqlplus.

Regards
Volker
Erik Cole
Honored Contributor

Re: ORA-00604: error occurred at recursive SQL level 1

But you can use SqlPlus successfully from the PPM server via command line?

Do you have any environment variable for NLS_LANG on the PPM server?

Re: ORA-00604: error occurred at recursive SQL level 1

Hi,
yes I can run sqlplus from the ppm server comamnd line.
There are NLS_LANG settings.
And I found the NLS_LANG settings within ppm differs from the NLS_LANG settings of the command line.
I have no idea how to change the NLS_LANG settings within ppm.

Regards
Volker
Erik Cole
Honored Contributor

Re: ORA-00604: error occurred at recursive SQL level 1

Volker,

When you run sqlplus from the command line are you logging in as the same user that PPM uses?

What are the NLS_LANG on the DB?

What server OS? If Unix/Solaris, do you have any NLS parameters in the .env or .profile?

Re: ORA-00604: error occurred at recursive SQL level 1

Hello,

the command line user is the same user as for ppm.
OS is SuSE Linux.
The NLS_LANG of the user is AMERICAN_GERMANY.WE8ISO8859P15
It is set with the .profile

However when I run ksc_local_exec env I get NLS_LANG=GERMAN_GERMANY.UTF8
The result of
select * from v$nls_parameters is GERMAN_GERMANY.UTF8 as well.

Regards
Volker

Re: ORA-00604: error occurred at recursive SQL level 1

However to make this thing absolute weird:
When I run select * from v$nls_parameters from SQLRUNNER I get:
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CHARACTERSET=WE8ISO8859P15
NLS_NCHAR_CHARACTERSET=UTF8

Regards
Volker
Erik Cole
Honored Contributor

Re: ORA-00604: error occurred at recursive SQL level 1

v$nls_parameters only shows the session (client) parameters. Try select * from NLS_DATABASE_PARAMETERS to see what the DB server is set to.

I think your issue is a mismatch between NLS settings, but we've pretty much come to the end of my knowledge on how to iron it all out to get everything working the way you need it. You might want to open a support case unless anyone else here has any ideas...?
Torsten Neumann
Super Collector

Re: ORA-00604: error occurred at recursive SQL level 1

Hi everybody,

it's been a while, but in my case a

unset NLS_DATE_FORMAT

in the .profile of the Linux user running the PPM server helped.

Regards,
Torsten
Kintana strikes back... ;-)
//Add this to "OnDomLoad" event