Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Stored Procedure

684479Mar 3 2009 — edited Mar 3 2009
I am new to Oracle with MS SQL Server background. It seems that MS SQL and Oracle handle data set operations differently in creating stored procedures; with a stored proc I am trying to create a denormalized table with a few large normalized Oracle tables which act as data dumps and store all kinds of historical information.

Instead of going deep with 40 million rows and very difficult to navigate, I am thinking about going horizontal with data denormalized for easy user access. With only 1 million unique customer records and their historical data in each row, business users can run queries more efficiently. However, in Oracle, I am not sure how ref cursors can be used and if the output can be selected into a permanent table. Here is the simplified code :


{color:#0000ff}Create or Replace PROCEDURE PROCEDURE1 AS
{color}
{color:#0000ff}Type rc IS REF CURSOR RETURN user.Table1%rowtype;
Cursor1 rc;
Cursor2 rc;
Cursor3 rc;

BEGIN

OPEN Cursor1 FOR
SELECT * FROM user.Table1
{color}


{color:#0000ff}END;

End PROCEDURE1;
{color}


While the above code compiles, I do not knwo how to output the whole dataset into a table. Can I open multiple cursors and hold all the rows from the historical tables and perform joins and other set based operations and return the result set from a cursor to a table? I thought about creating views but it seemed there would be too many intermidiate ones to be created. Will a stored proc or package be the best way to go or there are better solutions? Any help would be apprciated.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2009
Added on Mar 3 2009
6 comments
296 views