By user2228135


2016-01-09 21:36:41 8 Comments

I am currently learning PL/SQL so i am still a newbie. Assume that you have a production database, which you connect to using Oracle SQL developer. You have ONLY READ privilges to that databases. Therefore you cannot create or edit any tables.

My question is, if i have a big list of IDs, which i have to join with a table in that database, how can i do that?

Obviously, I can load the IDs onto a temporary table and then do a join, but that would be really tedious as i have only READ privileges. Hardcoding the IDs is not an option also, because the list is too big.

And also note that, i know the concept of TEMPORARY tables. But unfortunately, i also don't have privileges to create those.

Is there any solution in SQL developer where i can load the list of IDs, to match with the table in the database?

3 comments

@EvilTeach 2016-01-09 23:04:18

Your friendly dba can map a directory for you to use, that will let you plop your file in there, and treat it as a table. Then basically you join with the file-as-table. Ask your DBA about EXTERNAL_TABLES.

@user2228135 2016-01-09 23:33:00

My DBA is not friendly :(

@EvilTeach 2016-01-10 04:51:01

You should work on that. Developers and DBAs should be part of a team.

@MT0 2016-01-09 22:40:30

Use a collection

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 10000 );

  FOR i IN 1 .. 10000 LOOP
    -- Populate the collection.
    your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 );
  END LOOP;

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;

Or doing the same thing via java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
    public static void main(String args[]){
        try{
            Class.forName("oracle.jdbc.OracleDriver");

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

            String[] ids = { "1", "2", "3" };

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con);

            PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( :your_collection ) c ON t.id = c.COLUMN_VALUE");

            // Passing an array to the procedure - 
            ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) );
            ResultSet cursor = st.executeQuery();

            while ( cursor.next() )
            {
                int id = cursor.getInt(1);
                double column1 = cursor.getDouble(2);
                double column2 = cursor.getDouble(3);

                System.out.println( String.format( "Id: %5d", id ) );
                System.out.println( String.format( "  Column1: %s", column1 ) );
                System.out.println( String.format( "  Column2: %s", column2 ) );
            }
        } catch(ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
    }
}

@user2228135 2016-01-09 23:13:44

The java solution looks interesting! How can i execute this code? I have to use an IDE like eclipse for that? Or can i use SQL Developer?

@MT0 2016-01-09 23:17:27

Compile it and run it from the command line as per any simple Java program. (You could use an IDE if you want but it is not necessary.) The PL/SQL snippet at the top can be run in SQL Developer or SQL/Plus.

@user2228135 2016-01-09 23:30:00

Ok i understood the java solution. But i still want to try the first solution. I tried executing but im getting the following error : SP2-0552: Bind Variable "CURSOR" is not declared.

@MT0 2016-01-09 23:37:07

In SQL developer, you need to run it as a script (Button with the green arrow with the page behind it or press F5) so that the VARIABLE cursor REFCURSOR; line is processed as well as the anonymous PL/SQL block. If you run it as a normal statement (Button with the large green arrow or pressing CTRL-Enter) then it will just run a single statement.

@MT0 2017-01-30 14:40:23

If you create a user-defined table-type (i.e. CREATE TYPE string_list AS TABLE OF VARCHAR2(4000);) and pass this in then the query can be simplified to SELECT * FROM your_table WHERE id MEMBER OF :your_collection. But you need the UDT as it won't work for VARRAYs, which is what the SYS.ODCI*LIST types are defined as.

@Nathan Loyer 2018-10-25 18:23:26

Looks like ArrayDescriptor is deprecated. Here's how to get around it: stackoverflow.com/questions/33347134/…

@Alexei 2016-01-09 21:46:13

You can try doing the search by constructing a query like this:

SELECT * FROM YourTable WHERE ID IN (Id1, Id2, ...., Idn)

This is limited to 1000 Ids, but it can be circumvented using this little trick shown here.

@user2228135 2016-01-09 21:56:24

I don't think this will work if you have a big list. I can always split the list and run the query several times, but this would really take a lot of time. Thanks anyway mate.

@Alexei 2016-01-09 22:00:26

I do not see any other solution since you do not specify any other client (Java, .NET). By using a client application, an alternative is to fetch all IDs from database (if the record number is not beyond millions) and do the match on the client, but I do not think it's a viable alternative.

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

46 Answered Questions

7 Answered Questions

29 Answered Questions

[SOLVED] How to select the nth row in a SQL database table?

8 Answered Questions

[SOLVED] How does database indexing work?

17 Answered Questions

5 Answered Questions

1 Answered Questions

How to create a temporary table in ORACLE with READ ONLY access

2 Answered Questions

[SOLVED] connecting to remote oracle database in SQL

2 Answered Questions

Sponsored Content