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

SELECT avg(length(a.col1.getStringVal())) FROM tab1 a;

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

SELECT avg(length(a.col1.getClobVal())) from tab1 a;

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>

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

Yes

CREATE OR REPLACE FUNCTION getStudentName(p_studentID IN student.studentID%TYPE) RETURN student.name%TYPE IS CURSOR c_student IS SELECT s.name FROM student s WHERE s.studentID = p_studentID; vc_name student.name%TYPE; BEGIN OPEN c_student; FETCH c_student INTO vc_name; CLOSE c_student; RETURN vc_name; END getStudentName; /

If you are creating a stored function then you need

CREATE OR REPLACE FUNCTION GET(firstName IN STUDENT.firstName%TYPE) return STUDENT%ROWTYPE IS record STUDENT%ROWTYPE; BEGIN select * into record from STUDENT where STUDENT.firstName = firstName; return record; END; /

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

DECLARE FUNCTION GET(firstName IN STUDENT.firstName%TYPE) return STUDENT%ROWTYPE IS record STUDENT%ROWTYPE; BEGIN select * into record from STUDENT where STUDENT.firstName = firstName; return record; END; BEGIN -- Program code including call to GET null; END; /

The developers of Amazons search engine must be wondering where they went wrong

Head First SQL

http://www.amazon.com/Head-First-SQL-Brain-Learners/dp/0596526849/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1195597769&sr=8-1

Mastering Oracle SQL 2nd Edition

http://www.amazon.com/Mastering-Oracle-SQL-Sanjay-Mishra/dp/0596006322/ref=sr_1_1?ie=UTF8&s=books&qid=1195597867&sr=1-1

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.

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

iSQL is a browser based version of SQL*Plus

http://www.oracle.com/technology/obe/obe10gdb/install/isqlplus/isqlplus.htm

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

Thanks.