Can some one give me a explanation about how and when should use syscursor in oracle?

Question

Explanation with example of syscursor.

When to use syscursor in plsql procedure? I have go through websites but didn't get how and when to use this.

Answer 1

As far as I know there is nothing like "syscursor" in Oracle. May be you are referring to SYS_REFCURSOR

The following url should help you in understanding how and when to use it.

https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets

Answer 2

SYS_REFCURSOR are used by PL/SQL procedures to return recordsets.

Example - lets get all table details for a given schema and return through sys_refcursor

CREATE OR REPLACE PROCEDURE GET_TABLE_DETAILS(schemaName    IN VARCHAR2,
                                              table_details OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN table_details FOR
      select table_name, column_name, data_type from ALL_TAB_COLUMNS where OWNER = schemaName;
    END GET_TABLE_DETAILS;

Here table_details out parameter will contain the result data of the select query, it can be retrieved as below.

DECLARE
  table_details_cursor  SYS_REFCURSOR;
  tab_name   ALL_TAB_COLUMNS.table_name%TYPE;
  col_name   ALL_TAB_COLUMNS.column_name%TYPE;
  data_type  ALL_TAB_COLUMNS.data_type%TYPE;
BEGIN
  GET_TABLE_DETAILS (schemaName    => 'DUMMY',
                     table_details => table_details_cursor);

  LOOP 
    FETCH table_details_cursor
    INTO  tab_name, col_name, data_type;
    EXIT WHEN table_details_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(tab_name || ' | ' || col_name || ' | ' || data_type);
  END LOOP;
  CLOSE table_details_cursor;
END;

However, you need to get through the Oracle documentation for detailed explanation - Oracle - Cursors Documenatation