| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Procedure VS Package
What I want to do is calling a stored procedure to return multiple rows
retrieval through OCI.
If my OCI program use Stored Procedure for multiple rows retrieval I will get
infinite loop. (just the first record keep returning).
If my OCI program make use of Package for multiple rows retrieval then it work
find. Anything wrong with you coding ?
Below is my OCI program, Table, Stored procedure and Package. I just change stored procedure name to package name in OCI program to test.
/* OCI program */
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <oratypes.h> #include <ocidfn.h> #include <ocidem.h> #include <ociapr.h> #define V7_LNGFLG 2 #define NO_PARSE_DEFER 0 #define PARSE_DEFER 1
Lda_Def lda;
Cda_Def cda;
ub1 hda[256];
dvoid oci_error(void);
dvoid do_fetch(boolean);
main(argc, argv)
sword argc;
text **argv;
{
text username[40] = "mbs_dvl";
text password[10] = "mbs_dvl";
text database[10] = "REL3";
text dbname[32];
sb2 ind_db;
ub2 retl_db, retc_db;
int sqlmode;
text userid[32];
double upramt;
if (argc > 1)
sqlmode = TRUE;
else
sqlmode = FALSE;
printf ("Connecting to oracle database ...");
if (orlon(&lda, hda, username, -1, password, -1, 0))
{
printf ("\nCannot login as %s/%s. Program terminate.\n",
username, password);
exit(1);
}
if (oopen(&cda, &lda, 0, -1, -1, 0, -1))
{
printf ("\nCannot open cursor. Program terminate.\n");
exit(1);
}
printf (" Done !\n");
/* Select which database connected */
memset (&dbname,'\0', sizeof(dbname));
if (oparse(&cda, (text *)"select GLOBAL_NAME from global_name", -1,0,2))
{
oci_error();
return;
}
if (odefin(&cda, 1, dbname, (sword) sizeof(dbname), VARCHAR2_TYPE, -1,
&ind_db, (text *)0, -1, -1, &retl_db, &retc_db))
oci_error();
oexfet(&cda, 1, 0, 0);
if (cda.rc != 0)
{
oci_error();
return;
}
if (cda.rpc > 0)
printf ("Database connected is %s\n", dbname);
/* End of retrieving database connected */
do_fetch(sqlmode);
if (oclose(&cda))
{
printf ("Error closing cursor !\n");
return -1;
}
if (ologof(&lda))
{
printf ("Error logging off !\n");
return -1;
}
exit (1);
dvoid do_fetch(int sqlmode)
{
text call_fetch[256];
double upramt;
int rv;
text user_id[32];
text tdm_user[32];
text tdm_version[32];
sb2 upramt_ind;
ub2 upramt_len, upramt_rcode;
sb2 ind_user;
ub2 retl_user, retc_user;
sword i;
if (sqlmode == TRUE)
{
printf ("Using Sql Mode\n");
strcpy((char *)call_fetch, "select TDM_User_ID from TB_YPC
where upramt = :upramt AND TDM_USER_ID = :tdm_user AND TDM_VERSION =
:tdm_version");
}
else
{
printf ("Using SP Mode\n");
strcpy ((char *)call_fetch, "begin\n\
P_GETYPCPE.SP_GETYPCPE(:upramt, :user_id,
:tdm_user, :tdm_version);\n\
end;");
}
if (oparse(&cda, call_fetch, -1, PARSE_DEFER, V7_LNGFLG))
{
oci_error();
return;
}
if (obndrv(&cda, (text *) ":upramt", -1, (ub1 *) &upramt, (sword)sizeof
(double), FLOAT_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
if (sqlmode == TRUE)
{
if (odefin(&cda, 1, user_id, 32, STRING_TYPE, -1, &ind_user,
(text *)0, -1, -1, &retl_user, &retc_user))
{
oci_error();
return;
}
}
else
{
if (obndrv(&cda, (text *) ":user_id", -1, (ub1 *) user_id, 32,
STRING_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
}
if (obndrv(&cda, (text *) ":tdm_user", -1, (ub1 *) tdm_user, -1,
VARCHAR2_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
if (obndrv(&cda, (text *) ":tdm_version", -1, (ub1 *) tdm_version, -1,
VARCHAR2_TYPE, -1, (sb2 *) 0, (text *) 0, -1, -1))
{
oci_error();
return;
}
printf ("Enter Upper Amount :");
scanf ("%lf", &upramt);
fflush (stdin);
printf ("Enter TDM User Id :");
scanf ("%s", tdm_user);
fflush (stdin);
printf ("Enter TDM Version :");
scanf ("%s", tdm_version);
fflush (stdin);
printf ("Upper Amount : %lf, TDM User : %s, TDM Version : %s\n",
upramt, tdm_user, tdm_version);
for (;;)
{
if (oexec(&cda))
{
printf ("OCI error\n");
oci_error();
return;
}
if (cda.rc != 0)
return;
if (cda.rpc > 0)
{
printf("print output \n");
for (i = 0; i < cda.rpc; i++)
{
printf ("User id is %s\n", user_id);
printf("\n");
}
}
else
break;
}
return;
dvoid oci_error(void)
{
text msg[900];
sword rv;
rv = oerhms(&lda, cda.rc, msg, (sword) sizeof(msg));
printf ("\n\n%.*s", rv, msg);
printf ("Processing OCI Function %s\n", oci_func_tab[(int) cda.fc]);
return;
/* Stored Procedure */
CREATE OR REPLACE PROCEDURE SP_GETYPCPE
( iupramt IN number ,oUsrId out varchar2 ,itdm_user_id in varchar2 ,itdm_version in varchar2 )
SELECT user_id FROM TB_ypc WHERE upramt >= iupramt and tdm_user_id = itdm_user_id and tdm_version = itdm_version;
BEGIN
IF NOT Curs%ISOPEN THEN OPEN Curs; END IF; FETCH Curs INTO oUsrId; IF Curs%NOTFOUND THEN CLOSE Curs; RAISE_APPLICATION_ERROR(-20000, 'Record Not Found.'); END IF; IF Curs%ISOPEN THEN CLOSE Curs; END IF;
END SP_GETYPCPE;
/
/* Package */
CREATE OR REPLACE PACKAGE P_GetYPCPE
AS
PROCEDURE SP_GetYPCPE
(
iupramt IN number ,oUsrId out varchar2 ,itdm_user_id in varchar2 ,itdm_version in varchar2
P_TDM_USER_ID VARCHAR2(32); P_TDM_VERSION VARCHAR2(32); pupramt NUMBER; CURSOR Curs0
SELECT user_id FROM TB_ypc WHERE upramt >= pupramt and tdm_user_id = p_tdm_user_id and tdm_version = p_tdm_version;
PROCEDURE SP_GetYPCPE
(
iupramt IN number ,oUsrId out varchar2 ,itdm_user_id in varchar2 ,itdm_version in varchar2
)
AS
BEGIN
P_TDM_USER_ID := itdm_user_id; P_TDM_VERSION := itdm_version; pupramt := iupramt; IF NOT Curs0%ISOPEN THEN OPEN Curs0; END IF; FETCH Curs0 INTO oUsrId; IF Curs0%NOTFOUND THEN CLOSE Curs0; RAISE_APPLICATION_ERROR(-20000, 'Record Not Found.'); END IF;
END SP_GetYPCPE;
END P_GetYPCPE;
/
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 07 1999 - 22:27:15 CDT
 |
 |