| 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 -> Different between Stored Procedure & Package
I have coded a stored procedure and package the perform the same thing.
(Retrieve multiple rows from a table)
I call the Stored Procedure and Package using the same OCI program.
(by just changing the Procedure name or package name in my OCI program
calling portion)
The outcome is If I can using procedure, I get infinite loop.
If I can using package, then it work fine.
Can anyone explain why ?
below is the code of my OCI program, stored procedure and package.
#define V7_LNGFLG 2 #define NO_PARSE_DEFER 0 #define PARSE_DEFER 1 #define TRUE 1 #define FALSE 0
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\
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;
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;
/
CURSOR
Curs0
IS
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 28 1999 - 21:15:47 CDT
 |
 |