In article <7eh7md$c9f$1_at_nnrp1.dejanews.com>,
yewpc_at_my-dejanews.com wrote:
Does anyone can answer this ?
TQ
> 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
> #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\
> 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
> )
> AS
> CURSOR Curs IS
> 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
> );
> END P_GetYPCPE;
> /
> CREATE OR REPLACE PACKAGE BODY P_GetYPCPE
> AS
> P_TDM_USER_ID VARCHAR2(32);
> P_TDM_VERSION VARCHAR2(32);
> pupramt NUMBER;
>
> 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
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Apr 12 1999 - 01:47:53 CDT
