Home » SQL & PL/SQL » SQL & PL/SQL » Retrun a single row based on a row index specified as a function parameter
Retrun a single row based on a row index specified as a function parameter [message #38132] Fri, 22 March 2002 22:07 Go to next message
Doug Van Auken
Messages: 2
Registered: March 2002
Junior Member
I have been writing stored procedures in SQL Server that are based on the following pattern:

CREATE PROCEDURE [[dbo]].[[spGetRow]] (
@index int = 1
)
AS

declare @id nvarchar(60)
declare @field1 nvarchar(60)
declare @field2 nvarchar(60)

DECLARE rows CURSOR STATIC
FOR
SELECT ID, field1, field2 from table1

open rows

FETCH ABSOLUTE @index FROM rows INTO @id, @field1, @field2

SELECT @id ID, @field1 FIELD1, @field2 FIELD2, @@cursor_rows as ROW_COUNT

CLOSE rows
DEALLOCATE rows
GO

This pattern allows me to retrieve a single row at a specifed row index from a result set. This has proven very beneficial in a web context, because it allows the user to page through a record set very easily. The alternative is to return a complete recordset, then have the ASP page loop through the recordset until the client side cursor reaches the desired index. Obviosly, the first approach is a lot more efficient.

Question: How do I adapt this pattern to work in PL/SQL?
Re: Retrun a single row based on a row index specified as a function parameter [message #38143 is a reply to message #38132] Mon, 25 March 2002 05:50 Go to previous messageGo to next message
Alex Mazur
Messages: 17
Registered: March 2002
Junior Member
If i understood you correctly it should be something like that:

create or replace procedure spGetRow(
indx in number,
out_id out nvarchar2,
out_field1 out nvarchar2,
out_field2 out nvarchar2 )
is
cursor cur(rid number) is
select ID, field1, field2 from table1
where rownum <= rid
minus
select ID, field1, field2 from table1
where rownum < rid;
begin
open cur(indx);
fetch cur into out_id, out_field1, out_field2
close cur;
end spGetRow;
Re: Retrun a single row based on a row index specified as a function parameter [message #38148 is a reply to message #38143] Mon, 25 March 2002 19:16 Go to previous messageGo to next message
Doug Van Auken
Messages: 2
Registered: March 2002
Junior Member
Ok, now I'm trying to run this in SQL* Plus, but I'm not able to because I'm getting a message that says: "Procedure created with compilation errors."

This is how table1 is setup:

SQL> create table table1
2 (
3 id nvarchar2(255),
4 field1 nvarchar2(255),
5 field2 nvarchar2(255)
6 );

Table created.

This is how the procedure was input:

create or replace procedure spGetRow(
indx in number,
out_id out nvarchar2,
out_field1 out nvarchar2,
out_field2 out nvarchar2
)
is
cursor cur(rid number) is
select id, field1, field2 from table1 where rownum <= rid
minus
select id, field1, field2 from table1 where rownum < rid;
begin
open cur(indx);
fetch cur into out_id, out_field1, out_field2
close cur;
end spGetRow;
/

This is my error message, again:

Warning: Procedure created with compilation errors.

One other question on this example: How would this work on the client -- either in JDBC or ADO. As it is now, I think the syntax on the client could get ugly (instead of just three fields, there could be 10 fields and they might all be of different types). This is how I want to be able to call the procedure or function (using ADO in this example):

dim rst as new ADODB.connection
set rst = cnn.execute("spGetRow " & n)

Where "n" is the index of the nth row.
Re: Retrun a single row based on a row index specified as a function parameter [message #38155 is a reply to message #38148] Tue, 26 March 2002 03:59 Go to previous message
Alex Mazur
Messages: 17
Registered: March 2002
Junior Member
Sorry, man! I forgot to put ";" after fetch in my
example. This is why it didn't compile. If you don't
like using lot of arguments in call of this thing you
can rewrite it in package form:

CREATE OR REPLACE
PACKAGE Tpackage is
type retCur is Ref Cursor;
function spGetRow( indx in number ) return retCur;
end tPackage;
/

CREATE OR REPLACE
PACKAGE BODY Tpackage is
function spGetRow( indx in number ) return retCur
is
retCurVar retCur;
begin
open retCurVar for
select id, field1, field2 from table1 where rownum <= indx
minus
select id, field1, field2 from table1 where rownum < indx;

return retCurVar;
end spGetRow;
begin
null;
end tPackage;
/

In this case JDBC client will be something like this:

CallableStatement call =
conn.prepareCall ("{ ? = call tpackage.spGetRow(?)}");

call.registerOutParameter (1, OracleTypes.CURSOR);
call.setInteger (2, 5);
call.execute ();
ResultSet rset = (ResultSet)call.getObject(1);

// Dump the cursor
while (rset.next ())
{ System.out.println (rset.getString(1));
System.out.println (rset.getString(2));
System.out.println (rset.getString(3));
}

rset.close();

...

Unfortunately, i've never worked with nchar Oracle types but i don't think they could possibly cause any
problem.

Good luck!
Previous Topic: Not Even Really Started
Next Topic: change to a different line using chr(10) when "select"
Goto Forum:
  


Current Time: Thu Mar 28 12:30:28 CDT 2024