Answer Posted / 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;

What are the features of pl sql?

1058

---

What is procedure and function?

967

---

Why functions are used in sql?

983

---

a table has 2 classifications 1)liabilities 2)earnings this liabitity has 2 elements with 2 input values and the earnings have 2 elements with 2 input values i wrote a query so that my input is liability savings amount1 amount2 xxxx null xxxxxx 0 xxx1 null xxxxx1 0 null yyyy 0 yyyy null yyy1 0 yyy1 my problem is --when i developed a report(d2k) with this data my o/p is liabilities,amount1,savings,amount2 xxxx xxxxxx xxx1 xxxxx1 yyyy yyyy yyy1 yyy1 how could i move this savings,savings values 2 palces up. can any body provide me witha better solution

2254

---

How to use sql*plus built-in timers?

1182

---

Explain locks? : Transact sql

993

---

What is sap sql?

971

---

Can we join 3 tables in sql?

1025

---

How can you tell the difference between an index and a view?

925

---

Explain the methods used to protect source code of pl/sql.

1011

---

Can we rollback delete command?

979

---

How many types of indexes are there in sql?

977

---

What is a full join sql?

1051

---

How can triggers be used for the table auditing?

1064

---

How to get each name only once from an employee table?

1108

---