Recordsets from a Oracle Procedure 1

[Dynapen]

Here's my question. I have a SQL Server database that I am converting to Oracle 9i to power my ASP web site. Most of the objects in the SQL database are Stored Procedures used by the server to return large recordsets based off of a set of parameters that are submitted.

My question is, can I convert these to Oracle Procedures and get the same result. I know I can get a result set out of a view, but every thing I have seen says I cant' get one out of a procedure. I know that I can create output variables, but I would need those variables to store a array, to hold the entire record set. Also, I will have up to 30-40 outputs for a particular procedure, and I don't want to have to define each and everyone becuase I have almost 150 procedures to convert.

Final part is that I need to be able to hand a field name to the database as a parameter, and have that parameter be used as part of the order by clause. Otherwise I have to hard code the select statement for each different way I want to sort a particular report.

Is a view the easiest way to do this, or can this realistically be done in a procedure.

 

[Hehenka]

This is from

http://asktom.oracle.com/

You Asked

Hi all, I am facing a seriuos problem while transferring DB from SQL Server to Oracle 8i:
In SQL Server I have a store procedure such as very simple in SQL Server)
CREATE PROCEDURE [usp_get_Announcements]
(@AnnouncementID int,
@Columns nvarchar(200))
AS

Declare @SQL nvarchar(500)

Set @SQL = 'SELECT ' + @Columns + ' FROM Announcements WHERE AnnouncementID = '
+ STR(@AnnouncementID)

Exec (@SQL)

Return @@RowCount

I want to transfer it to Oracle 8i, but I cann't execute Dynamic PL/SQL because @Columns is an any column in table that I want SELECT. Addition,In Oracle I want to RETURN a cursor that is a recordset such as SQL Server.Please help me. Thanks and Regards


and we said...

create or replace package my_pkg
as
type rc is ref cursor;
procedure usp_get_announements( p_announcement_id in int,
p_columns in varchar2,
p_cursor in out rc );
end;
/

create or replace package body my_pkg
as

procedure usp_get_announements( p_announcement_id in int,
p_columns in varchar2,
p_cursor in out rc )
as
begin
open p_cursor for
'select ' || p_columns || from announcements where announcementid = :x'
using p_announcements;
end;

end;
/

See

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:528893984337

for why bind variables are crucial.

See

http://osi.oracle.com/~tkyte/ResultSets/index.html

for full examples of ref cursors (how to return a result set in Oracle)

See

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:286816015990

for why you want to never use a stored procedure/function but always want to use packages.
--------------------------------------

I hope this will help you.

Helena

 

[Dynapen]

I know that I can use the refcursor to parse through it multiple times. And I know that I can put a array in to each output variable that I build. What I wanted was to be able to return one continuous recodset, without having to make a second request for it.

Unfortunately I already found the answer at OTN. And you can't return a recordset from Oracle, like you can in SQL Server or Sybase. Oracles' method is great if you have smaller queries that you are running, or if you to do some different things to teh values from teh tables after you retrieve them. But for building simple report type functions, Oracles method is slow, long to build, and confusing to use. For my money, I would stay with SQL Server becuase it doesn't tie your hands on this like Oracle does. If you want a traditional recordset in Oracle, build a view. Procedures are for Data manipulation only......

Just my two cents.

 

[sem]

What do you mean by "to parse through it multiple times"?
You DO may get refcursor variable from your procedure and FETCH from it as you need. Do you need something else?
And of course it's a bit strange to expect of Oracle to return MS object directly to your ASP . If your world is stricly limited by Windows only, the idea to use one vendor software is quite reasonable. As for tying your hands - Oracle doesn't do this. Just recognize that you know nothing about it ...

 

[Dynapen]

sem-

Oracle does tie my hands. What is the advantage of having to do repeated Do and Fetch statements? Even Oracle itself doesn't recommend looping through the DO and FETCH statements to get the recordset. Their recommended plan is to store the results in a table and then read them from there. That means I have to execute two different commands against the DB, jsut to get a resultset. I have 1 query with over 200 different fields that I need to return. That means that I have to build and call 200 different Output variables, just to get back the results of a select statement. If you can explain to me how Oracle's way of returning values from a procedure is better, then I would love to hear it. The money's gone, the brain is shot.....but the liquor we still got.

 

[sem]

You DO NOT NEED to fetch the result into 200 variables, just declare 1 of your cursor%rowtype. You may also fetch your result set in one shot using BULK COLLECT. Have you ever heard about such features? Does you frontend tool support pl/sql tables?
Client/server architecture was primarily designed to reduce network traffic so retutning a large amount of data if quite rare event. It's NORMALLY for this architecture to get results as you REALLY need them. At present time this is some kind of limitation. But did you answer YES on my questions ?

 

[Dynapen]

No, I hadn't heard about features like the Bulk Collect. Nor did I really understand the Fetch commands that they talked about. But it was Oracle itself that didn't say anything about the Bulk Collect. Most of what I have on this is from a article that I pulled off of the OTN, that specifically compares MS SQL and Sybase procedures with Oracle procedures, and it mentions nothign about Bulk Collect and even less about how cursor and rowtype work.
As far as the frontend tool, I am using the OEM to do the database work, and all the work on the web side is going directly through the OLEDB using PL/SQL. I do have some experience with basic PL/SQL, but again, in ths case getting a large amount of data from the server is exactly what I need. That will be it's primary function. It will be writing out reports across between 5-25 fields that can be up to 400,000 records long.

But with these extra steps that you are talking about, with the cursor and rowtype, am I really saving any amount of time over just building a view? The money's gone, the brain is shot.....but the liquor we still got.

 

[sem]

Building a view does not help you a lot, because looping through cursor based on a view does not signeficantly differ from looping through cursor based on a more complex select statement (of course until the security is not an issue). In both cases you should fetch from cursor in a loop.

ROWTYPE is used to declare records. You may declare your record type by specifying its fields explicitly or using some existing object (table, view, cursor) as an example.
Once declared the variable of this type may be referenced by "natural" dot notation: variable.attribute.

REF CURSOR is just some kind of pointer. You may fetch from it into some variable. As you fetch from cursor the pointer moves through the result set (still located on server) and assigns the data retrieved to your host variable. So you do not need to pass all 200 field select statement. Just define your variable and call your procedure to make assignment. Hehenka's example is quite exhaustive.

As far as I know Oracle OLEDB implementation supports REF CURSOR as a sourse for recordset. I have no recent experience with it but I'm sure you may play with fetch size or other parameters to improve performance. You may find manuals on

http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/win.901/a90171/intro.htm

BTW if you're on 8i+ you may use its native feature to generate XML directly from select statement.





P.S.

As for guilty Oracle - you've read 1 article from OTN and expect it to contain everything you need. Why don't you look for the answer in ASP manuals?

 

[spenglerr]

Sorry,

but when you receive an Oracle database ordered by technet for developing or if you bought it you will get a CD-ROM with 600 MB documentation.

Everything about the Cursors is written there. Also everything about this topics can be found in additional books by Oracle Press / O'Reilly.

When I read dynapen's last statement I got only one thought:
rtfm. The problem I have with Oracle is that it has so many features and such a huge amount of documentation that you need at least 4 years for getting everything into your mind. To say that Oracle is not giving enough information is impossible. It's just that you didn't look for it.

To OTN:

If you are perfoming a search there the normal default is to search only the discussion groups and forums. So please take the next time the time to "refine your search " by using also the manuals, which are included in OTN also!!!
You will find lots and lots more about how cursor, ref cursor and bulk collect work.

 

[Dynapen]

Here's the thing. I will agree that there is alot of Oracle info out there. But if it is that hard to look through (and I did include the manuals on my OTN search) then what is the point of having it. ASP manuals don't have anything about how to get the data to work right on the database side, just how to get it all to work on the ASP side after you connect to the database. If the database object doesn't do what you want, then the ASP book is no help.

Also, in regards to building a view over a procedure. This goes back to what spenglerr said about truly needing 4 years to get all of the Oracle stuff handled. I don't have that much time. Sem-

You make it sound like it is no big deal to do all of this. If that's the case, then how about a example so that I can begin to see what you are talking about. Becuase I don't know how I will be able to reference a part of a variable output by a procedure, when all i get back is the recordset object. This will have to be handled by the ASP, and the ASP book should help with that, but I need the database object first.

But still, no one has answered the second part of the question. Can I pass a field name to the procedure, as a parameter, and then execute the order by clause of my select statement against the value of that parameter.

field_var in varchar2

Select * from test
order by field_var;

I have tried but can't get it to work. Without this working, then I will have to take the conversion time for every 1 of 100 SP and multiple by the number of sort possiblities I have to work with, which could be up to 17 on a single SP. The money's gone, the brain is shot.....but the liquor we still got.

 

[sem]

1. You may create SELECT statement dynamically and pass field name to your VB procedure, returning recordset, rather then passing field name to some stored procedure. Hope you know VB better then me.

2. You may build stored procedure:

create or replace package dynamic_rc as
type rc is ref cursor;
procedure get_rc(pStatement in varchar2, pResult in out rc);
end;

create or replace package body dynamic_rc as
procedure get_rc(pStatement in varchar2, pResult in out rc)
is
begin
open pResult for pStatement;
end;
end;

You may pass any valid select statement to this procedure as pStatement and get the result set via pResult. For your specific order by case you may hardcode your select statement (without such clause) and pass only ORDER BY clause, concatinating it with the basic query.

To read how to use this procedure follow the link below and find Example: Setting the Custom Property PLSQLRSet.

http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/win.901/a90171/using.htm

P.S. The thing we know nothing about may be better then we think of it.

 

[spenglerr]

Hi dynapen,

did you give Oracle's Migration Workbench a try? You get it right with the Oracle distribution, but you will have to install some plugins then afterwards to cover all databases you need. It works quite well. When I tried it with ACCESS and SQL-SERVER databases it translated a lot of the routines and VB-scripts to Oracle Procedures and VB direct working with the Oracle. Of course not everything worked perfect, but starting from your top thread-question this would be the best starting point and then continue working on the details only.

I still do not understand why it is not possible to give the data back using CursorVariables containing your recordsets and then work put these results into Arrays and request them in VB. Actually I agree completely with sems posts. Actually you put your parameters to a PL/SQL-procedure or package defined there as IN and get the large recordset or some large recordsets back in Form of CursorVariables. This is excactly what you did with SQL-Server and it works also with Oracle.

I told it will take 4 years, but ok I am working as well as a DBA then as developer. I am working on SOLARIS, HPUX, Windows NT/98/2000. My Jobs are Tuning, Administration, Coding, Design, Interfaces(a lot of XML nowadays). Configuration together with IIS, Apache ,.....

There is a good point from sem in the last post. I also had the meaning following the discussion that you don't know dynamic sql-statements and that you are trying to cope everything on the middle tier using VB instead of porting the basic calculations and everything to the database to reduce the outcoming results as much as possible. You can dynamically build up queries also by parameters within PL/SQL. And also combine Recordsets by the UNION operator.

 

[Dynapen]

Spenglerr-

Can you give me some examples. Becuase from what I am finding here, even to use the cursor varialbes and return the values as a array, I still have to build a nested table somewhere. Which at that point I still lose that speed advantage over going to a view.

And while yes, I do have a limited understand of Dynamic-SQL, I do understand the concept. If you can pass field names to a oracle procedure as a parameter, and have it proceses them as part of the overall select statement, then I would love to see a example, becuase I can't find one anywhere. Even a simple example will do.

But remember. I am not using VB. The only tools i have are VBScript and Javascript. Not quite as robust as the VB or Java.

I will gladly admit that I am a little over my head here, but I need a example of what you are talking about so that I can put it into my database and try to learn. No one has been able to provide anything like that yet. The money's gone, the brain is shot.....but the liquor we still got.

 

[spenglerr]

Sorry for the delay,

small example: I have 6 tables with pre-grouped page impressions for the WebServer logfiles, containing parameters to identify which content is most interesting for our users.

Our CEOs and partners can select the lookup-parameters on a web page and get result sets grouped to get a peak-analysis or a topic-analysis.

I build up the

col_stm (select column clause)
frm_stm (from clause)
whr_stm (where clause)
grp_stm (group_by clause)

then I concat these in the correct order to form the query (qry_stm) and then I use dynamic SQL to get the record-set into a CURSOR VARIABLE returning it from the database to the application. (By this way only the final result is sent to the web-server reducing network traffic and page build up speed, also the database is much quicker with grouping algorithms than an application). Of course you will have to define your cursor variable first!


CREATE OR REPLACE PACKAGE pk_cursor
AS
TYPE allgCursor IS REF CURSOR;
END pk_cursor;
/




CREATE OR REPLACE PROCEDURE stat_report (
c_table IN OUT pk_cursor.allgCursor
,p_ArtAuswahl IN VARCHAR2
,p_ZeitWahl IN VARCHAR2
,p_Datum IN VARCHAR2 := NULL
,p_Vergleich IN VARCHAR2 := NULL
,p_Mandant IN VARCHAR2 := NULL
,p_Rubrik IN VARCHAR2 := NULL
,p_Thema IN VARCHAR2 := NULL
,r_Message OUT VARCHAR2
)
AS
-- r_Message VARCHAR2(200);
v_ArtText VARCHAR2(25);
qry_stm VARCHAR2(1000);
col_stm VARCHAR2(100);
grp_stm VARCHAR2(100);
whr_stm VARCHAR2(300);
frm_stm VARCHAR2(100);
BEGIN
IF (p_ArtAuswahl = '0') THEN
-- First set the text for the PI-headline
v_ArtText := 'zeitlichen Verlauf';
-- Now generate the selected column-/group- and from-definitions
IF (RTRIM(p_Zeitwahl) = 'Wochen') THEN
col_stm := ' SELECT pi_week||pi_year PI_TIME, sum(pi_counts) PI_COUNTS';
grp_stm := ' GROUP BY pi_year, pi_week ';
frm_stm := ' FROM pi_per_weak ';
ELSE
col_stm := ' SELECT pi_time PI_TIME, sum(pi_counts) PI_COUNTS';
grp_stm := ' GROUP BY pi_time ';
IF (RTRIM(p_Zeitwahl) = 'Monate') THEN
frm_stm := ' FROM pi_per_month ';
ELSIF (RTRIM(p_Zeitwahl) = 'Tage') THEN
frm_stm := ' FROM pi_per_day ';
ELSIF (RTRIM(p_Zeitwahl) = 'Stunden') THEN
frm_stm := ' FROM pi_per_hour ';
ELSE
r_Message := 'kein gültiger Zeitraum für zeitlichen Verlauf! ';
RETURN;
END IF;
END IF;
-- add the mandant-definition to the where clause
IF (p_Mandant IS NULL OR p_Mandant = '0') THEN
whr_stm := '';
ELSE
whr_stm := 'WHERE client = '|| p_Mandant;
END IF;
IF (p_Rubrik IS NULL OR p_Rubrik = '') THEN
whr_stm := whr_stm || '';
ELSE
IF (INSTR(whr_stm,'WHERE',1) > 0 ) THEN
whr_stm := whr_stm || ' AND rubrik = ''' || p_Rubrik || '''';
ELSE
whr_stm := ' WHERE rubrik = ''' || p_Rubrik || '''';
END IF;
END IF;
IF (p_Thema IS NULL OR p_Thema = '') THEN
whr_stm := whr_stm || '';
ELSE
IF (p_Rubrik = 'Urlaub/Freizeit') THEN
whr_stm := ' AND uf_category = ''' || p_Thema || '''';
ELSIF (p_Rubrik = 'Handel/Wirtschaft') THEN
whr_stm := ' AND uv_category = ''' || p_Thema || '''';
ELSIF (p_Rubrik = 'Veranstaltungen') THEN
whr_stm := ' AND eventcategory = ' || p_Thema;
ELSIF (p_Rubrik = 'Lebenslagen') THEN
whr_stm := ' AND ls_category = ' || p_Thema;
ELSIF (p_Rubrik = 'Behörden') THEN
whr_stm := ' AND auth_category = ' || p_Thema;
ELSIF (p_Rubrik = 'Nachrichten') THEN
whr_stm := ' AND newscategory = ' || p_Thema;
END IF;
END IF;
qry_stm := col_stm || frm_stm || whr_stm || grp_stm;
OPEN c_table FOR qry_stm;
ELSIF (p_ArtAuswahl = '1') THEN
-- First set the text for the PI-headline
v_ArtText := 'inhaltlichen Vergleich';
-- Now generate the selected column-/group- and from-definitions
IF (RTRIM(p_Zeitwahl) = 'Wochen') THEN
frm_stm := ' FROM pi_per_weak ';
whr_stm := ' WHERE pi_week = ' || SUBSTR(p_Datum,1,INSTR(p_datum,',',1)-1) ||
' AND pi_year = ' || SUBSTR(p_Datum,INSTR(p_datum,',',1)+1,4);
ELSE
IF (RTRIM(p_Zeitwahl) = 'Jahr') THEN
frm_stm := ' FROM pi_per_month ';
whr_stm := ' WHERE TO_CHAR(pi_time,''yyyy'') = ''' || p_Datum || '''';
ELSIF (RTRIM(p_Zeitwahl) = 'Monate') THEN
frm_stm := ' FROM pi_per_month ';
whr_stm := ' WHERE TO_CHAR(pi_time,''mm.yyyy'') = ''' || p_Datum || '''';
ELSIF (RTRIM(p_Zeitwahl) = 'Tage') THEN
frm_stm := ' FROM pi_per_day ';
whr_stm := ' WHERE TO_CHAR(pi_time,''dd.mm.yyyy'') = ''' || p_Datum || '''';
ELSE
r_Message := 'kein gültiger Zeitraum für einen Vergleich! ';
RETURN;
END IF;
END IF;
IF (p_Vergleich IS NULL) THEN
r_Message := 'keine inhaltliche Größe für den Vergleich definiert! ';
RETURN;
ELSIF (p_Vergleich = 'Mandant') THEN
frm_stm := frm_stm || 'a, vmb_mandant b';
whr_stm := whr_stm || ' AND a.client = b.mand_id ';
col_stm := ' SELECT b.mand_name PI_VGL, sum(a.pi_counts) PI_COUNTS';
grp_stm := ' GROUP BY b.mand_name ';
qry_stm := col_stm || frm_stm || whr_stm || grp_stm;
OPEN c_table FOR qry_stm;
ELSIF (p_Vergleich = 'Referrer') THEN
grp_stm := ' GROUP BY referrer ';
frm_stm := 'FROM (SELECT referrer, sum(pi_counts) AS pi_counts ' ||
' FROM pi_per_referrer ' || whr_stm || grp_stm || ') ';
whr_stm := ' WHERE rownum < 21 ';
col_stm := ' SELECT referrer PI_VGL, pi_counts PI_COUNTS';
qry_stm := col_stm || frm_stm || whr_stm ;
OPEN c_table FOR qry_stm;
ELSIF (p_Vergleich = 'Domain') THEN
grp_stm := ' GROUP BY domain ';
frm_stm := 'FROM (SELECT domain, sum(pi_counts) AS pi_counts ' ||
' FROM pi_per_domain ' || whr_stm || grp_stm || ') ';
whr_stm := ' WHERE rownum < 21 ';
col_stm := ' SELECT domain PI_VGL, pi_counts PI_COUNTS';
qry_stm := col_stm || frm_stm || whr_stm ;
OPEN c_table FOR qry_stm;
ELSIF (p_Vergleich = 'Rubrik') THEN
IF (p_Mandant = '0' OR p_Mandant IS NULL) THEN
whr_stm := whr_stm;
ELSE
whr_stm := whr_stm || ' AND client = ' || p_Mandant;
END IF;
col_stm := ' SELECT rubrik PI_VGL, sum(a.pi_counts) PI_COUNTS';
grp_stm := ' GROUP BY rubrik ';
qry_stm := col_stm || frm_stm || whr_stm || grp_stm;
OPEN c_table FOR qry_stm;
END IF;
ELSE
r_Message := 'keine gültige Statistik-Art gewählt! ';
RETURN;
END IF;
r_Message := 'Die Abfrage ergibt einen ' ||
v_ArtText || ' über ' ||
p_Zeitwahl || ' ' || p_Datum || ' für ' ||
p_Mandant || ', ' || p_Rubrik || ', ' ||
p_Thema ;
EXCEPTION
WHEN OTHERS
THEN
r_Message := 'Die gewählten Parameter ergeben keine gültige Auswahl';
END stat_report;
/