CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Vishal Mishr
Vishal Mishr

Reputation: 207

Table Valued function in Sql developer

I have Created a table valued function in Sql server 2012 which works fine but when i applied same code on same table but with Oracle Sql developer it shows syntax error

Function in SQL Server

Create Function fx_Mfr

(
@Mfr varchar
)

returns table

as

Return

Select * from Manufacturer

where Mfr = @Mfr

Upvotes: 1

Views: 985

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22427

Or using implicit statement results - if you're on Oracle 12c or higher version Database. This feature was added to make migrating your code from SQL Server easier.

CREATE OR REPLACE PROCEDURE fx (par_deptno IN NUMBER)
AS
  l_cursor_1 SYS_REFCURSOR;
BEGIN
      OPEN l_cursor_1 FOR
      SELECT department_id, first_name, last_name, job_id, salary 
      FROM   employees
      WHERE  department_id = par_deptno;

    DBMS_SQL.RETURN_RESULT(l_cursor_1);
END;
/

And then execute the program

DECLARE
  PAR_DEPTNO NUMBER;
BEGIN
  PAR_DEPTNO := 100;

  FX(
    PAR_DEPTNO => PAR_DEPTNO
  );
END;

The output is auto-magically returned from the database:

enter image description here

Examples/docs on Oracle-Base

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

One option is to return ref cursor, such as in this example (which, kind of, simulates what you have in MS SQL Server):

SQL> create or replace function fx (par_deptno in number)
  2    return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for
  7      select deptno, ename, job, sal
  8      from emp
  9      where deptno = par_deptno;
 10    return rc;
 11  end;
 12  /

Function created.

SQL> select fx(10) from dual;

FX(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO ENAME      JOB              SAL
---------- ---------- --------- ----------
        10 CLARK      MANAGER         2450
        10 KING       PRESIDENT      10000
        10 MILLER     CLERK           1300


SQL>

Upvotes: 1

Related Questions