Oracle SQL -- (Procedure of function) that returns entire column-CodePudding

For security purposes, I want to have an entity that returns an entire column value.

Example of an entity: stored-procedure, function, table-valued function.

Example:

 CREATE OR REPLACE FUNCTION simpleSelect  RETURN VARCHAR2 AS
        output1   VARCHAR2(100);
    BEGIN
       Select  (col1 ) INTO output1 from SCHEMA1.TABLE1;     
       RETURN output1;
  END
  ;

The above gives the following error:

[Error] Execution (17: 9): ORA-01422: exact fetch returns more than requested number of rows
ORA-06512:

What would be the syntax to create the entity and also to call the entity?

CodePudding user response：

Here's a simple option; see if it helps. It uses Oracle built-in type which lets you store strings; another, for numbers, is sys.odcinumberlist (can't tell what datatype is your col1 so I picked one of them). Benefit? You don't even have to create your own type!

SQL> create or replace function simpleselect
  2    return sys.odcivarchar2list
  3  is
  4    retval sys.odcivarchar2list;
  5  begin
  6    select ename
  7      bulk collect into retval
  8      from emp;
  9    return retval;
 10  end;
 11  /

Function created.

What is the result?

SQL> select * from table(simpleselect);

COLUMN_VALUE
--------------------------------------------------------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL>

CodePudding user response：

Option 1: Use a cursor in a procedure:

CREATE PROCEDURE simpleSelect
(
  o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor FOR
    Select col1 from SCHEMA1.TABLE1;
END;
/

Option 2: Use a pipelined function:

CREATE TYPE string_list IS TABLE OF VARCHAR2(100);

CREATE FUNCTION simpleSelect
  RETURN string_list PIPELINED
IS
  v_cursor SYS_REFCURSOR;
  v_value  SCHEMA1.TABLE1.COL1%TYPE;
BEGIN
  OPEN v_cursor FOR
    Select col1 from SCHEMA1.TABLE1;

  LOOP
    FETCH v_cursor INTO v_value;
    EXIT WHEN v_cursor%NOTFOUND;
    PIPE ROW (v_value);
  END LOOP;

  CLOSE v_cursor;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    CLOSE v_cursor;
END;
/

Option 3: Use a (non-pipelined) function:

CREATE TYPE string_list IS TABLE OF VARCHAR2(100);

CREATE FUNCTION simpleSelect
  RETURN string_list
IS
  v_values string_list;
BEGIN
  SELECT col1
  BULK COLLECT INTO v_values
  FROM   SCHEMA1.TABLE1;

  RETURN v_values;
END;
/

Note: While this may be short to type, if there are a large number of rows then you will create a huge data structure in memory and is likely to result in performance issues; it may be better to stream the data to a third-party application using a pipelined function or a cursor.

fiddle