Cursor finds records in SQLPlus, but not in procedure

Cursor finds records in SQLPlus, but not in procedure

Post by Kenny Y » Sun, 31 Dec 1899 09:00:00



Cursor finds records in SQLPlus, but not in procedure

I  have a packaged procedure that runs fine on database A. But when I
installed it on database B, a cursor in it find no records. When I debug
it in Procedure Builder client, everything works. When I run the same
procedure in Procedure Builder over the same connection on the server,
in non-debug mode, I got no records, no errors.

Here is the result of running the procedure:

N: no records
Y: found records

  Database A                                   Dabase B       (both A
and B are 8.1.5)
--------------------------------------------------------
Client  Server   SQLPlus        Client  Server   SQLPlus
--------------------------------------------------------
  Y        Y            Y                  Y        N            N
--------------------------------------------------------

The cursor has a unioned select query. I run one of the select statement
on SQLPlus on both A and B with expected records.

The databases have assumably the same schemas. I log in as the schema
owner in all cases.

What would cause the discrepancy?

  kun.yu.vcf
< 1K Download
 
 
 

1. Oracle sqlplus error: sp1<lang>.msb not found

I know this problem has been posted in the past, however, as yet I have
been unable to resolve it with any of the suggested fixes I've come
across.  I've run out of ideas so any suggestions would be appreciated.

Here are the details:  I have installed Oracle 8.1.6 on a Solaris 7
machine successfully.  When trying to start sqlplus I get the following
error:

Message File sp1<lang>.msb not found.
Error 6 Initializing SQL*PLUS

My profile contains following settings.  After sourcing the profile,
the env shows these settings have been set.

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/8.1.6
export ORACLE_HOME

ORACLE_DOC=$ORACLE_HOME/doc
export ORACLE_DOC

ORACLE_SID=DEMO
export ORACLE_SID

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORA_NLS33

NLS_LANG=american_america.US7ASCII
export NLS_LANG

LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

CLASS_PATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/networ
k/jlib:$ORACLE_HOME/JRE
export CLASS_PATH

PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/ccs/bin:/usr/openwin/bin:/usr/l
ocal/bin:/usr/ucb:/etc:.
export PATH

TNS_ADMIN=$ORACLE_HOME/network/admin/
export TNS_ADMIN

ORAENV_ASK=NO
. /usr/local/bin/oraenv

Sent via Deja.com
http://www.deja.com/

2. Q: Is it possible to read the transaction log?

3. PROBLEMS ON STORED PROCEDURES (Procedure not found)

4. 4GL WITH HOLD PROBLEMS

5. Procedure cursor inside a procedure cursor

6. Error at runtime but not in IDE

7. How can I find whether CURSOR exists or not

8. - Major automotive website -

9. Cursor on INSERTED not found

10. Cursor not found

11. finding results NOT IN another cursor

12. Query (with Cursor) returning different record sets does not work in DTS

13. Cursor not picking up all records