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
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>
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:
CREATE FUNCTIONin Oracle creates a PL/SQL stored function. Stored functions in SQL Server use Transact-SQL. These are not the same thing and neither of them is standard SQL. Hence the syntax is not identical. You need to learn PL/SQL syntax in order to create PL/SQL functions.