How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?

by

You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):

CREATE OR REPLACE FUNCTION get_allitems
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT * FROM allitems;
  RETURN my_cursor;
END get_allitems;

This will return the cursor.

Make sure not to put your SELECT-String into quotes in PL/SQL when possible. Putting it in strings means that it can not be checked at compile time, and that it has to be parsed whenever you use it.

If you really need to use dynamic SQL you can put your query in single quotes:

  OPEN my_cursor FOR 'SELECT * FROM allitems';

This string has to be parsed whenever the function is called, which will usually be slower and hides errors in your query until runtime.

Make sure to use bind-variables where possible to avoid hard parses:

  OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;

More Related Contents:

  1. What is the replacement of cursor foreach in oracle pl/sql?
  2. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  3. Printing the value of a variable in SQL Developer
  4. PL/SQL, how to escape single quote in a string?
  5. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  6. Passing an array of data as an input parameter to an Oracle procedure
  7. Convert comma separated string to array in PL/SQL
  8. UTL_FILE.FOPEN() procedure not accepting path for directory?
  9. Oracle stored procedure with parameters for IN clause
  10. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  11. Get resultset from oracle stored procedure
  12. Oracle PL/SQL – How to create a simple array variable?
  13. ways to avoid global temp tables in oracle
  14. Split function in oracle to comma separated values with automatic sequence
  15. check if “it’s a number” function in Oracle
  16. ORA-06508: PL/SQL: could not find program unit being called
  17. Number of rows affected by an UPDATE in PL/SQL
  18. Create Pivot view in SQL from a SQL table
  19. Why doesn’t PL/SQL respect privileges granted by Roles?
  20. Can We use threading in PL/SQL?
  21. Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
  22. What is the algorithm used by the ORA_HASH function?
  23. passing in table name as plsql parameter
  24. Reading clob line by line with pl\sql
  25. Cursor For Loop with dynamic SQL-Statement
  26. How to use parameters in a ‘where value in…’ clause?
  27. Why cannot I create triggers on objects owned by SYS?
  28. No more data to read from socket
  29. automating rollback script oracle
  30. How do I get column datatype in Oracle with PL-SQL with low privileges?

Leave a Comment