Answer Posted / rahul poptani

Function returns only 1 value
BUT you can return multiple value Indireclty..
1) By returning an result set -

CREATE OR REPLACE FUNCTION GET_DEPT_INFO (P_DEPTNO IN NUMBER) RETURN SYS_REFCURSOR
AS
V_RC SYS_REFCURSOR;
BEGIN
OPEN V_RC FOR SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO = P_DEPTNO;
RETURN V_RC;
END;

SQL> VAR RESULTSET REFCURSOR;
SQL> EXEC :RESULTSET := GET_DEPT_INFO(10)

PL/SQL procedure successfully completed.
SQL> PRINT :RESULTSET

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK

2)PROVIDING OUT MODE PARAMETERS IN FUNCTION (RARELY USED)

SQL> CREATE OR REPLACE FUNCTION OUTMODE_EXAMPLE (P_DEPTNO NUMBER, P_DNAME OUT VARCHAR2)
2 RETURN NUMBER
3 AS
4 BEGIN
5 SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO = P_DEPTNO;
6 RETURN NULL;
7 END;
8 /

Function created.

SQL> DECLARE
2 V_DNAME DEPT.DNAME%TYPE;
3 V NUMBER;
4 BEGIN
5 V := OUTMODE_EXAMPLE(10,V_DNAME);
6 DBMS_OUTPUT.PUT_LINE(V_DNAME);
7 END;
8 /
ACCOUNTING

PL/SQL procedure successfully completed.

What is type and rowtype in pl sql?

968

---

Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?

1041

---

Can a view be mutating? If yes, then how?

963

---

What is sql analyzer?

962

---

what is the difference between char_length and length? : Sql dba

993

---

What are the two types of exceptions in pl/sql?

969

---

Explain mutating table error.

1132

---

What is the execution plan in sql?

975

---

What is foreign key in sql with example?

892

---

how to create a new table by selecting rows from another table in mysql? : Sql dba

1011

---

List out the acid properties and explain?

905

---

how to enter numeric values as hex numbers? : Sql dba

1007

---

Why is there a need for sqlcode and sqlerrm variables?

999

---

Is sqlite free?

907

---

What is scope of pl sql developer in future?

952

---