Return cursor results from anonymous block

Question

I have the following SELECT which I want to change into an anonymous block (need to use an anonymous block as doing it within Java and do not have access to created stored functionality) in order to remove the use of the literal into a bind variable:

SELECT IL.LAT_NUM, IL.LNGTD_NUM
FROM ITEM_LOCATION IL
WHERE IL.ITEM_ID = 294341;

I have two anonymous blocks created but am unable to find how to return the values created in both:

1)

DECLARE
    itemID number;
    latitude number;
    longitude number;
BEGIN
    itemID := 294341;

    SELECT 
        IL.LAT_NUM,
        IL.LNGTD_NUM,
    INTO
        latitude,
        longitude,
    FROM 
        ITEM_LOCATION IL
    WHERE 
        IL.ITEM_ID = itemID ;   
END;

2)

DECLARE
    TYPE t_ref_cursor IS REF CURSOR;
    c_cursor  t_ref_cursor;
    itemID number;
    latitude   ITEM_LOCATION.LAT_NUM%TYPE;
    longitude   ITEM_LOCATION.LNGTD_NUM%TYPE;
BEGIN
    itemID := 294341;

    OPEN c_cursor FOR
        SELECT 
            IL.LAT_NUM,
            IL.LNGTD_NUM, 
        FROM 
            ITEM_LOCATION IL
        WHERE 
            IL.ITEM_ID = itemID ; 
    CLOSE c_cursor; 
END;

Does anyone know how either/both of these blocks can return as if it were the SELECT above?

Answer 1

I want to change into an anonymous block ... in order to remove the use of the literal into a bind variable

Why do you think you need to use an anonymous block to use a bind variable? And both of your blocks still have the value 294341 hard-coded anyway; your select is using a bind variable within the block but is generating a new block every time really much of an improvement on what you had?

As @haki said many hours ago, you cam just use a prepared statement with a bind variable:

PreparedStatement pStmt = conn.prepareStatement(
    "SELECT IL.LAT_NUM, IL.LNGTD_NUM :
    + "FROM ITEM_LOCATION IL "
    + "WHERE IL.ITEM_ID = ?");
pStmt.setInt(1, 294341);

... and then execute the query and process the result set as you presumably are already. (From your reply to haki's comment you seem to be confusing a prepared statement - which is a Java/JDBC construct - with a stored procedure in the database).

Here's a simple standalone example against the demo HR schema's EMP table:

import java.sql.; import java.text.; import oracle.jdbc.*; import oracle.jdbc.pool.OracleDataSource;

public class JamesGallagher
{
    public static void main(String args[]) throws SQLException
    {
        Connection conn;
        OracleDataSource ds = new OracleDataSource();
        ds.setURL("jdbc:oracle:thin:scott/oracle@127.0.0.1:1521:orcl");
        conn = ds.getConnection();

        PreparedStatement pStmt = conn.prepareStatement(
            "select ename, sal from emp where sal > ?");
        pStmt.setInt(1, Integer.parseInt(args[0]));

        ResultSet rs = pStmt.executeQuery();

        while (rs.next())
        {
            System.out.println(rs.getString(1) + ": " + rs.getInt(2));
        }

        try { rs.close(); } catch ( Exception ex ) {}
        try { pStmt.close(); } catch ( Exception ex ) {}
        try { conn.close(); } catch ( Exception ex ) {}
        conn = null;
    }
}

I can compile that with javac JamesGallagher.java and execute with java JamesGallagher 1500 and it prints the results based on the bound value:

ALLEN: 1600
JONES: 2975
BLAKE: 2850
CLARK: 2450
SCOTT: 3000
KING: 5000
FORD: 3000