#16 "can't find PSPCMPROG record" multiple environments

[Marcus Cuda]

Hello,

Great project! I've been using multiple copies of Decode to backup multiple environments and it has been working wonderfully. I finally gotten around to setting up a single configuration using multiple environment suffixes. However, when I run decode, I get this error:
SEVERE: ?? can't find PSPCMPROG record
java.sql.SQLException: ?? can't find PSPCMPROG record

Turning up the logging, the SQL statement that causes error does not return any data on the secondary environment (the one with the suffix). If I run the statement on the primary environment, it returns a row. It seems that decode is running the statement against the wrong database. Running decode against one environment (either one), no problem.

running decode against Oracle 11, using

java -classpath ./bin:ojdbc6.jar  -Djava.util.logging.config.file=logger.properties decodepcode.Controller ProcessToFile custom

DecodePC.properties:
# DEV
user=sysadm
password=xxxxx
dbowner=sysadm
driverClass=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@dev.xxxx.xxx:1521/DEV
outdir=./code/dev

# PROD
processPROD=ProcessToFile
userPROD=readonly
passwordPROD=xxxxx
dbownerPROD=sysadm
driverClassPROD=oracle.jdbc.OracleDriver
urlPROD=jdbc:oracle:thin:@prod.xxx.xx:1521/PROD
outdirPROD=./code/prod

Any idea what I'm doing wrong to cause the problem?

Thank you,
Marcus

[Erik H]

Not really sure what goes wrong. When you turned up the logging, did you do that by changing the level in logger.properties to 'ALL'? If so, you should have had an SQL statement in your log file starting with
select LASTUPDDTTM, LASTUPDOPRID from ...
Please run that statement as the 'readonly' user in PROD.

[Marcus Cuda]

When it is run on PROD, it returns no rows. On DEV, it returns 1 row. That item doesn't exist on PROD, but does on DEV. So those results are expected. That is why I was wondering if the code was hitting the wrong database.

Thanks,
Marcus

SQL:select LASTUPDDTTM, LASTUPDOPRID from sysadm.PSPCMPROG pc where OBJECTVALUE1 = 'KU_SSR_PT_FX' and OBJECTVALUE2 = 'TmplFix' and OBJECTVALUE3 = 'GBL' and OBJECTVALUE4 = 'default' and OBJECTVALUE5 = '1900-01-01' and OBJECTVALUE6 = 'Step02' and OBJECTVALUE7 = 'OnExecute' and PROGSEQ = 0

[Erik H]

Hold on - I think I found it. Something goes wrong if you use a recent version of ptools (with PSPCMTXT) and you have some pcode in one environment which does not exist in all of your environments.
I'll prepare a new version.

[Erik H]

Possible fix in new version 0.55

[Erik H]

And just a suggestion: try the ProcessToSVN or ProcessToGit options. It's so much more useful to have everything in a version control system - you just schedule the tool to run automatically, and there are wonderful tools for both Subversion and Git to do comparisons or to retrieve old versions.

[Marcus Cuda]

great, I'll test it in a bit.

And just a suggestion: try the ProcessToSVN or ProcessToGit options
I do store the code in git, ProcessToFile works better for my workflow.

Thanks.

[Marcus Cuda]

got this error:
SEVERE: ORA-01000: maximum open cursors exceeded

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:876)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1498)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
    at decodepcode.JDBCPeopleCodeContainer.<init>(JDBCPeopleCodeContainer.java:164)
    at decodepcode.Controller.makeAndProcessContainers(Controller.java:162)
    at decodepcode.Controller.makeAndProcessContainers(Controller.java:111)
    at decodepcode.Controller.writeCustomizedPPC(Controller.java:593)
    at decodepcode.Controller.main(Controller.java:816)

[Erik H]

Hmm. Maybe I should have tested it (which is a bit tricky because I currently have access to a single environment only). Shouldn't be all that difficult to find the SQL statement that remains open, though.

[Erik H]

Try again - there's a new file (same file name, still not tested, but with an added st.close() ).

[Marcus Cuda]

tested with a four environment property file and it worked perfectly. Thanks!!