DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Insert into cursor

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    India
    Posts
    22

    Question 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.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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 = "jdbcracle:thin:@orcl:1521racle8";

    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

  3. #3
    Join Date
    Nov 2002
    Location
    India
    Posts
    22

    Question

    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.

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width