Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning forum!

Chris Hendy

Ranch Hand
+ Follow
since Mar 04, 2006
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Chris Hendy

Don't know why - still looking into it - but I get the same Oracle error as Santosh.

However if I alias the table it works ok



One thing to bear in mind, if one of your XMLTYPE values exceeds 4000 characters you will get a

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Try



instead.
The date datatype is stored in a proprietory internal format, but displayed according to the database initialization parameter NLS_DATE_FORMAT (which at least for me defaults to 'DD-MON-YY' and so chops off the hours/minutes/seconds). You can alter this either for your session using ALTER SESSION (see below), or system wide by changing the init paramter - but this requires a database bounce.

SQL> select sysdate from dual;

SYSDATE
---------
21-NOV-07

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
21-NOV-2007 02:56:49

SQL>
12 years ago
Try

select to_char(t.log_time,'DD-MON-YYYY HH24:MI:SS') log_time_full, t.*
from Table t
order by t.log_time desc;

and see what happens
12 years ago
Yes

12 years ago
If you are creating a stored function then you need



You don't need the DECLARE key word.

If it is a local function in an anonymous pl/sql block then you need a BEGIN and END to go with the DECLARE

12 years ago
Since you are using a prepared statement and setString I don't think you need the single quotes. They are only used for string literals.

"SELECT * FROM Course WHERE semester='?' AND courseNumber='?'"

should be

"SELECT * FROM Course WHERE semester=? AND courseNumber=?";

I just would start his Plan C before proceeding to Plan A --> Plan B if it were me.



Totally agree.
If you don't know SQL at all then start with Head First SQL, then Mastering Oracle SQL 2nd Edition.

If you do know another vendor dialect of SQL go straight to the second title mentioned.
12 years ago
Plan B.

This only works if your oracle user has alter session privilege, and you either have access to the user_dump_dest or if not can get the DBA to give you your trace file.

Since you can amend your own Java code, don't try to turn trace on from another session, turn it on in Java by executing the following SQL before your query on COURSE

alter session set tracefile_identifier = 'HIGGLEDY';

alter session set events '10046 trace name context forever, level 4';

The trace file will be named

<oracle_sid>_ora_<SPID>_higgledy.trc

so you don't need to know the SPID.

Plan C.

Can you post the part of your Java code concerned with the query, results and the logic for generating the message "The course that you have selected does not yet exist; please select another one" so we can look at it?
Try using sys.v_$SESSION instead of v$session, and sys.v_$PROCESS instead of v$process.

If that doesn't work then I fear Paul is correct and you don't have the privileges.

It may be time to start practising another real world Developer skill - wheedling the DBA. Either to temporarily grant them to you, or to do the tracing for you.
To find out what SQL statement is actually hitting the database from your Java app I suggest using extended SQL trace.

After your Java app has established a database connection, and before issuing the query, turn on level 4 extended trace for the Java apps session. Do this by getting the SID, SERIAL# and SPID of the Java connection from v$session

SELECT vs.SID, vs.SERIAL#, vp.SPID
FROM v$session vs, v$process vp
WHERE vs.username = '&java_user'
AND vs.paddr = vp.addr;

and turning trace on from a separate SQL*Plus session using

exec dbms_system.set_ev(&sid,&serial,10046,4,'')

Once the query has returned in Java, drop the connection and go look at the trace file in the user_dump_dest directory of the database server. Find this in SQL*Plus by typing

SHOW PARAMETER user_dump_dest

The SQL statement issued by the Java APP will be in a text file called
<oracle_sid>_ora_<SPID>.trc along with any bind variable values if you used a prepared statement.

PARSING IN CURSOR #1 len=50 dep=0 uid=32 oct=3 lid=32 tim=18446744071330715763 hv=2604616925 ad='1b57dc50'
select user_id from all_users where username = :b1
END OF STMT
PARSE #1:c=0,e=6713,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=0,tim=18446744071330715758
BINDS #1:
bind 0: dty=1 mxl=32(10) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=32 offset=0
bfp=06932354 bln=32 avl=05 flg=05
value="JAVAUSER"
EXEC #1:c=0,e=861,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=18446744071330717956
FETCH #1:c=0,e=58,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=3,tim=18446744071330718234

The bind values are displayed in the same order as they appear in the SQL statement.

Check out the following for a fuller explanation.

http://www.rittmanmead.com/work_stuff/extended_sql_trace_and_tkprof.htm

http://www.juliandyke.com/Diagnostics/Trace/EnablingTrace.html
As long as it is in SQL*PLUS you can also do the following:

SQL> set define off
SQL> insert into test values('/action/forwardfwdPage=fwdItem&program=item&clearCache=yes');

1 row created.

SQL> select * from test;

COL1
--------------------------------------------------------------------------------
/action/forwardfwdPage=fwdItem&program=item&clearCache=yes

SQL> set define on
12 years ago