Re: OCI/ PL/SQL - Given a table with a large number of rows - how to implement "get next n rows" and "get prev n rows"

From: Mark Tomlinson <marktoml_at_gdi.net>
Date: Thu, 25 Jun 1998 11:46:35 GMT
Message-ID: <3598383f.141956072_at_newshost.us.oracle.com>

Here is one way to get the scrollable concept working using 7.x OCI, the ability to fetch in arrays is inherent in OCI.

/*
This is the most basic means of mimicking a scrollable cursor in OCI. We implement the oback function which uses the cda.rpc to place a re-executed cursor to the previous row in the return set. [Note: Performance on this would probably not be very optimal.] */

#include <stdio.h> 
[Quoted] #include <string.h> 
#include <malloc.h> 

extern "C" {
[Quoted] #include <ociapr.h>
}  

[Quoted] #define VARCHAR2_TYPE            1 
#define NUMBER_TYPE              2 
#define INT_TYPE                 3 
#define FLOAT_TYPE               4 
#define STRING_TYPE              5 
#define ROWID_TYPE              11 
#define DATE_TYPE               12 

 

Lda_Def lda;
Cda_Def cda;
ub1 hda[256];  

char* 	v_loc; 
char* 	v_dname; 
int		v_deptno;

short   i_loc;

short i_dname;
short i_deptno;

int oback(Cda_Def*);  

void main()
{

	v_loc = (char*)malloc(30);
	v_dname = (char*)malloc(30);

	if

(olog(&lda,hda,(text*)"scott/tiger_at_t:orlnt-5:ORCL",-1,0,-1,0,-1,OCI_LM_DEF))

{

		printf("Logon failed: %i\n", lda.rc); 
		return;
	}
 
	if (oopen(&cda,&lda,0,-1,-1,0,-1)) 


	{

		printf("Open failed: %i\n", lda.rc); 
		return;
	}
 
	if (oparse(&cda,(unsigned char*)"select deptno, dname, loc
from dept",-1,1,2))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}

	if (odefin(&cda,1,(unsigned
char*)&v_deptno,sizeof(int),INT_TYPE,
		-1,0,0,-1,-1,0,0))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}

	if (odefin(&cda,2,(unsigned
char*)v_dname,12,STRING_TYPE,-1,0,0,-1,-1,0,0))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
	
	if (odefin(&cda,3,(unsigned
char*)v_loc,12,STRING_TYPE,-1,0,0,-1,-1,0,0))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}

	if (oexfet(&cda,1,0,0))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
	printf("\n%i", v_deptno); 
 	printf("\n%s", v_dname); 
	printf("\n%s\n", v_loc); 

	if (ofetch(&cda))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
	printf("\n%i", v_deptno); 
 	printf("\n%s", v_dname); 
	printf("\n%s\n", v_loc); 

	if (oback(&cda))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
	printf("\n%i", v_deptno); 
 	printf("\n%s", v_dname); 
	printf("\n%s\n", v_loc); 

	if (ofetch(&cda))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
	printf("\n%i", v_deptno); 
 	printf("\n%s", v_dname); 
	printf("\n%s\n", v_loc); 

	if (oclose(&cda))


	{

		printf("Routine #%i failed with Oracle error
%i\n",cda.fc,cda.rc); 
		return;
	}
 
	if (ologof(&lda)) 


	{

		printf("Logoff failed: %i\n", lda.rc); 
		return;
	}

	return;  

}

int oback(Cda_Def* p_cda)
{

        unsigned int v_pos;

        v_pos = p_cda->rpc - 1;

	if (ocan(p_cda)) 
		return p_cda->rc;
	
	if (oexec(p_cda))
		return p_cda->rc;

	while (p_cda->rpc < v_pos)


	{

		if (ofetch(p_cda))
		return p_cda->rc;
	}

	return 0;

} Received on Thu Jun 25 1998 - 13:46:35 CEST