Explain how procedures and functions are called in a PL/SQL block ?

Explain how procedures and functions are called in a PL/SQL
block ?

Question Posted / guest

3 Answers
9891 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Explain how procedures and functions are called in a PL/SQL block ?..

Answer / tulsi

PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);

Is This Answer Correct ?

3 Yes

0 No

Explain how procedures and functions are called in a PL/SQL block ?..

Answer / p.rajasekar

CREATE OR REPLACE PACKAGE PersistPkg AS
–– Type which holds an array of book ISBN's
TYPE t_BookTable IS TABLE OF books.isbn%TYPE
INDEX BY BINARY_INTEGER;
–– Maximum number of rows to return each time.
v_MaxRows NUMBER := 4;
–– Returns up to v_MaxRows ISBN's
PROCEDURE ReadBooks(p_BookTable OUT t_BookTable,
p_NumRows OUT NUMBER);
END PersistPkg;
/
CREATE OR REPLACE PACKAGE BODY PersistPkg AS
–– Query against books. Since this is global to the package
–– body, it will remain past a database call.
CURSOR c_BasicBooks IS
SELECT isbn
FROM BOOKS
WHERE category = 'Oracle Basics'
ORDER BY title;
PROCEDURE ReadBooks(p_BookTable OUT t_BookTable,
p_NumRows OUT NUMBER) IS
v_Done BOOLEAN := FALSE;
v_NumRows NUMBER := 1;
BEGIN
IF NOT c_BasicBooks%ISOPEN THEN
–– First open the cursor
OPEN c_BasicBooks;
END IF;
–– Cursor is open, so we can fetch up to v_MaxRows
WHILE NOT v_Done LOOP
FETCH c_BasicBooks INTO p_BookTable(v_NumRows);
IF c_BasicBooks%NOTFOUND THEN
Chapter 9: Using Procedures, Functions, and Packages 407
–– No more data, so we're finished.
CLOSE c_BasicBooks;
v_Done := TRUE;
ELSE
v_NumRows := v_NumRows + 1;
IF v_NumRows > v_MaxRows THEN
v_Done := TRUE;
END IF;
END IF;
END LOOP;
–– Return the actual number of rows fetched.
p_NumRows := v_NumRows - 1;
END ReadBooks;
END PersistPkg;
/
PersistPkg.ReadBooks will select from the c_BasicBooks
cursor.
Since this cursor is declared at the package level (not
inside ReadBooks), it will
remain past a call to ReadBooks. We can call
PersistPkg.ReadBooks with
the following block:

DECLARE
v_BookTable PersistPkg.t_BookTable;
v_NumRows NUMBER := PersistPkg.v_MaxRows;
v_Title books.title%TYPE;
BEGIN
PersistPkg.ReadBooks(v_BookTable, v_NumRows);
DBMS_OUTPUT.PUT_LINE(' Fetched ' || v_NumRows || ' rows:');
FOR v_Count IN 1..v_NumRows LOOP
SELECT title
INTO v_Title
FROM books
WHERE isbn = v_BookTable(v_Count);
DBMS_OUTPUT.PUT_LINE(v_Title);
END LOOP;
END;

Is This Answer Correct ?

1 Yes

0 No

Explain how procedures and functions are called in a PL/SQL block ?..

Answer / nishi

PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments);

Is This Answer Correct ?

0 Yes

0 No

Post New Answer