Programming - Database
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Subject: [Oracle]Stored function
Date: 23 Jan 1999 12:58:15 GMT
Is it possible to return a resultSet from a stored
function? If so, how? What type of data structure
should I return?
Any pointers to online references are alway welcome.
tia.
From: Choon Leong <chuacl@comp.nus.edu.sg>
Date: 24 Jan 1999 04:40:05 GMT
It's possible, below is what I grabbed off the Usenet.
/*
* This sample shows how to call a PL/SQL function that opens
* a cursor and get the cursor back as a Java ResultSet.
*/
import java.sql.*;
import java.io.*;
// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;
class RefCursorExample {
public static void main (String args [])
throws SQLException, ClassNotFoundException {
// Load the driver
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci7:@", "scott", "tiger");
// Create the stored procedure
init (conn);
// Prepare a PL/SQL call
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");
// Find out all the SALES person
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "SALES");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1);
// Dump the cursor
while (rset.next ())
System.out.println (rset.getString ("ENAME"));
}
// Utility function to create the stored procedure
static void init (Connection conn)
throws SQLException {
Statement stmt = conn.createStatement ();
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;");
stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from emp where job = j; " +
" return rc; " +
" end; " +
"end java_refcursor;");
}
}
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Date: Sun, 24 Jan 1999 23:25:20 +0800
Anyone tried it? I have problem exexcuting it. Below is the error
..
I created the stored function using PL/SQL, so the error has nothing
to do with it. Problem comes when I try executing the function using
the java program.
tia.
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00905: object P386450.JAVA_REFCURSOR is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:631)
at
oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1221)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:657
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:770)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement
812)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:115
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
a:1187)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
java:108)
at RefCursorExample.main(RefCursorExample.java:36)
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Subject: [JDBC]Bulk-Loading?
Date: 5 Feb 1999 17:07:12 GMT
Is that any way to do Bulk-Loading to Oracle using JDBC?
Inserting the values one by one is far too slow. :(
From: Choon Leong <chuacl@comp.nus.edu.sg>
Date: 6 Feb 1999 02:26:15 GMT
Use sqlloader.
From: Prototype <ouhengli@comp.nus.edu.sg>
Subject: Taking readings...
Date: 6 Feb 1999 04:42:58 GMT
I got an application on a machine which access oracle on sununx.
If I want to take readings or time taken for this application
which have to be independent of network traffic, will accessing of oracle
on sununx caused it to be dependent?
If so, does porting to sununx solve the problem?
or change my code to disregard time taken to access oracle??
From: Lee Boon Peng <leebp@comp.nus.edu.sg>
Date: 6 Feb 1999 08:01:18 GMT
If you are going to upload or download data from the server over the network,
obviously this would be a major factor. The load on the machine running the
database server would again be another major factor. The users themselves
would make the Oracle database crawl.
If you are looking for meaningful benchmarks/times, well ... good luck.
From: Wang Wenqiang <wangwenq@comp.nus.edu.sg>
Subject: data truncate
Date: 6 Feb 1999 09:09:06 GMT
I am using jdbc to get data from Oracle Database. But if the query is
something like "select XXX, AVG(XXX) from .....", the average number I got
is quite a long real number, like "0.33333333333333333". How can I
truncate this result and get something like "0.33" in the previous case.
From: Choon Leong <chuacl@comp.nus.edu.sg>
Date: 8 Feb 1999 02:30:11 GMT
Use the function trunc to do it, eg
select trunc(avg(xxx),2) from ...
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Subject: [JDBC]TABLE_SCHEM
Date: Mon, 01 Feb 1999 09:03:22 +0800
In the documentation for java.sql.DatabaseMetaData
The method getTables states that the 2nd field it takes in is
2.TABLE_SCHEM String => table schema (may be null)
Does table schema refers to the account name we have in oracle?
I got this information off the Usenet.
"The schema maps to the DBMS user, so if there is a
DBMS user 'joe', then your call should return only
tables owned by joe."
I tried the above in my oracle account but it return me an empty
set. Any one tried the above method?
From: Balanced <tancheeh@comp.nus.edu.sg>
Date: 8 Feb 1999 01:38:54 GMT
Use your account name as schema name when calling getTables.
Alternatively, you can use "%" as a wildcard to retrieve all tables.
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Date: 8 Feb 1999 06:55:22 GMT
ResultSet rs = dbmd.getTables(null, "p386450","%",null);
if(rs == null)
System.out.println("is null");
while(rs.next()) {
System.out.println(rs.getString(3));
}
The following codes should return you the tables you have in Oracle. However,
I keep getting the resultset as null. No problem using % but I do not want all
the tables.
I'm using jdbc thin.
From: Balanced <tancheeh@comp.nus.edu.sg>
Date: 8 Feb 1999 11:34:32 GMT
>
> ResultSet rs = dbmd.getTables(null, "p386450","%",null);
^^^^^^^
IC the problem now. User uppercase: "P386450" (I had this problem before)
From: Lee Yeow Leong <leeyeowl@comp.nus.edu.sg>
Subject: [JDBC] Inserting Date fields
Date: 8 Feb 1999 07:21:50 GMT
Can we insert a date into an oracle Date field? I keep getting the
following error when I tried it. tia.
--------[ code snipplet ]-----------------------------------
java.sql.Date myDate = new java.sql.Date(1991, 12, 12);
stmt.executeUpdate("insert into COFFEES " +
"values( " + myDate + " )");
------------------------------------------------------------
--------[ output ]-----------------------------------------
sununx[03:16:57] ~/archive/examples
- java CreateCoffees
Connecting to Oracle
SQLException: ORA-00932: inconsistent datatypes
-----------------------------------------------------------