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;

how do you tune the slow running queries in oracle db , explain the methodology

3221

---

Is it possible to create startup or shutdown trigger for on-schema?

1091

---

How do you update sql?

1036

---

What is data definition language?

1135

---

Is pl sql a programming language?

1042

---

tell us something about heap tables. : Sql dba

1142

---

What is dynamic query?

1034

---

Can cursors be part of a trigger body?

1692

---

What is bulk collect in pl sql?

1157

---

The select into statement is most often used to create backup copies of tables or for archiving records?

1115

---

What is sqlerrm?

983

---

What does 0 mean in sql?

1020

---

What is thread join () in threading?

1057

---

What are some emotional triggers?

1033

---

What is oracle and pl sql?

1159

---