-
Insert into cursor
Hi,
In one of our procedure, we opened a curosr (say C1) to fetch records from few tables. Then we write all the code to refain the rows and insert into a table (say t2). Another cursor (say C2) is written to fetch the records from the t2 and then cursor (C2) is recturned to Java.
My qerry - Is it possible to insert records directly into the Cursor C2?
Thank u all.
-
Hi
well you can use a refrence cursor to return the result set directly to the java programme..something like this..
create or replace package demo
as
type cursorType is ref cursor;
end;
create or replace procedure listemp( p_cursor in out demo.cursorType )
as
begin
open p_cursor for select ename, empno from emp order by ename;
end;
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class javacur
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@orcl:1521:oracle8";
String query = "begin :1 := listemp; end;";
Connection conn;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "scott", "tiger");
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
while (rset.next ())
System.out.println( rset.getString (1) );
cstmt.close();
}
}
try this..but i did not understand your question rightly why are you using a c1 to insert into a temp table ?
regards
Hrishy
-
Oops, I think my question is confusing.what I mean actually is,
Is possible to fetch records from a cursor (say C1) into another cursor (say C2).
Thanks.
-
This might help..
Code:
SQL> DECLARE
2
3 TYPE tDatum IS TABLE OF DATE;
4 TYPE tNumbers IS TABLE OF NUMBER;
5 TYPE tChars IS TABLE OF VARCHAR2(30);
6
7 dtDate tDatum := tDatum();
8 nNum tNumbers := tNumbers();
9 sString tChars := tChars();
10
11 n_idx NUMBER;
12
13 BEGIN
14 SELECT SYSDATE, object_name, object_id
15 BULK COLLECT INTO dtDate, sString, nNum
16 FROM ALL_OBJECTS
17 WHERE ROWNUM < 11;
18
19 n_idx := dtDate.FIRST;
20
21 WHILE n_idx IS NOT NULL LOOP
22 DBMS_OUTPUT.PUT_LINE('Id - ' || nNum(n_idx)
23 || ' Name - '||sString(n_idx)
24 || ' DateTime - '||to_char(dtDate(n_idx),'DD.MM.YY HH24:MI:SS'));
25 n_idx := nNum.NEXT(n_idx);
26 END LOOP;
27
28 END;
29 /
Id - 89 Name - ACCESS$ DateTime - 23.01.03 12:58:59
Id - 1283 Name - ALL_ALL_TABLES DateTime - 23.01.03 12:58:59
Id - 1244 Name - ALL_ARGUMENTS DateTime - 23.01.03 12:58:59
Id - 1506 Name - ALL_ASSOCIATIONS DateTime - 23.01.03 12:58:59
Id - 1162 Name - ALL_CATALOG DateTime - 23.01.03 12:58:59
Id - 1169 Name - ALL_CLUSTERS DateTime - 23.01.03 12:58:59
Id - 1359 Name - ALL_CLUSTER_HASH_EXPRESSIONS DateTime - 23.01.03 12:58:59
Id - 1978 Name - ALL_COLL_TYPES DateTime - 23.01.03 12:58:59
Id - 1179 Name - ALL_COL_COMMENTS DateTime - 23.01.03 12:58:59
Id - 1185 Name - ALL_COL_PRIVS DateTime - 23.01.03 12:58:59
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
Here in the example, I have fetched complete data into bind variables (BULK COLLECT). Later I have simply displayed those variables in a while loop. I guess this might help you..
Consider first BULK COLLECT is your C1
AND WHILE LOOP is your C2
May be you can do something similar..
HTH
Sameer