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