using OCI to call a ORACLE stored function

Please support our C++ advertiser: Programming Forums - DaniWeb Sister Site
Reply

•
•
Join Date: Oct 2004
Posts: 3
Reputation: Genar Codina is an unknown quantity at this point 
Solved Threads: 0
Genar Codina Genar Codina is offline Offline
Newbie Poster

using OCI to call a ORACLE stored function

 
0
  #1
Feb 21st, 2005
I am trying to call a stored function (which makes basically a select query)from a VC++ program using OCI.
The description of the stored function is:

C++ Syntax (Toggle Plain Text) 
  1. FUNCTION patrick.P_DICTIONARY_FOR_RISK_GROUP RETURNS REF CURSOR
  2. Argument Name                  Type                    In/Out Default?
  3.  ----------------  	     -------------                ---------------
  4.  V_RISKGROUP              VARCHAR2                         IN
  5.  V_DICTIONARY            VARCHAR2                         IN
  6.  V_MARKETDATA           VARCHAR2                         IN
  7.  V_MODE                     VARCHAR2                         IN
  8.  V_DATE                      DATE                                IN
and with the returned value REF CURSOR, it could be possible to get all the rows returned from the function.

The problem is that, so far I could not get these rows. The function that I have written to get the rows is:

C++ Syntax (Toggle Plain Text) 
  1. bool DBRiskOracleMgr::LoadRiskGroupScenarios(cGrid<cVariant>& pgrd_Output,const string& pstr_Proc,const string& pstr_Portfolio,
  2. 							            const string& pstr_Scenario,const string& pstr_MarketData,const string& pstr_Mode,
  3.                                         DBRiskDate& dbRiskDate,const string& pstr_Currency,const bool pb_LogSQL)
  4. {
  5.         OCIError*           pOciError;
  6.         char*                   pConnectChar    = "VERTEXD1";
  7.         char*                   pUsernameChar   = "miau";
  8.         char*                   pPasswordChar   = "miau";
  9.         int                       answer;
  10.         OCIStmt*           pOciStatement;
  11.         char*                  sqlCharArray1 = "CALL VERTEX_RISK_ENGINE_CONFIG.CONFIG_CONTEXT_API.SET_CONTEXT('PC_OPTIONS_3D_UAT')";"BEGIN :success := patrick.P_DICTIONARY_FOR_RISK_GROUP('CHF_JPY_BASIS_OPTION','PRICE','COB',NULL,'14-Feb-2005'); END;";
  12.         char*                  sqlCharArray2 = "BEGIN\n :result := P_DICTIONARY_FOR_RISK_GROUP('CHF_JPY_BASIS_OPTION','PRICE','COB',NULL,'14-Feb-2005');\nEND;";
  13.         char                    szid[200];
  14.         char                    ename[200];
  15.         OCIEnv*            g_pOciEnvironment = NULL;
  16.         OCIServer*        g_pOciServer = NULL;
  17.         OCISession*       g_pOciSession = NULL;
  18.         OCISvcCtx*       g_pOciServiceContext = NULL;
  19.         sb2*                    pIndicator=0;
  20.         sb2*                    pIndicator2=0;
  21.         sb2*                    pIndicator3=0;
  22.         OCIDefine*         pOciDefine;
  23.         OCIDefine*         pOciDefine2;
  24.         OCIBind*            pBind;
  25.         OCIStmt*            cursor;
  26.         string              slog;
  27.  
  28.         answer = OCIInitialize(OCI_THREADED, NULL, NULL, NULL, NULL);
  29.         answer = OCIEnvInit(&g_pOciEnvironment, OCI_DEFAULT, 0, NULL);
  30.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&pOciError, OCI_HTYPE_ERROR, 0, NULL);
  31.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciSession, OCI_HTYPE_SESSION, 0, NULL);
  32.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciServer, OCI_HTYPE_SERVER, 0, NULL);
  33.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciServiceContext, OCI_HTYPE_SVCCTX, 0, NULL);
  34.         answer = OCIServerAttach(g_pOciServer, pOciError, (unsigned char *)pConnectChar, strlen(pConnectChar),
  35.                                  OCI_DEFAULT);
  36.         answer = OCIAttrSet(g_pOciSession, OCI_HTYPE_SESSION, (unsigned char *)pUsernameChar, strlen(pUsernameChar),
  37.                             OCI_ATTR_USERNAME, pOciError);
  38.         answer = OCIAttrSet(g_pOciSession, OCI_HTYPE_SESSION, (unsigned char *)pPasswordChar, strlen(pPasswordChar),
  39.                             OCI_ATTR_PASSWORD, pOciError);
  40.         answer = OCIAttrSet(g_pOciServiceContext, OCI_HTYPE_SVCCTX, g_pOciServer, 0, OCI_ATTR_SERVER, pOciError);
  41.         answer = OCIAttrSet(g_pOciServiceContext, OCI_HTYPE_SVCCTX, g_pOciSession, 0, OCI_ATTR_SESSION, pOciError);
  42.         answer = OCISessionBegin(g_pOciServiceContext, pOciError, g_pOciSession, OCI_CRED_RDBMS, OCI_DEFAULT);
  43.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&pOciStatement),  OCI_HTYPE_STMT, 0, NULL);
  44.         answer = OCIStmtPrepare(pOciStatement, pOciError, (unsigned char *)sqlCharArray1, strlen(sqlCharArray1),
  45.                                 OCI_NTV_SYNTAX, OCI_DEFAULT);
  46.         answer = OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, 1, 0, NULL, NULL,
  47.                                 OCI_DEFAULT);
  48.         writeToLog(sqlCharArray1);
  49.         if (answer != 0)
  50.         {
  51.             writeToLog("Bad1");
  52.             return false;
  53.         }
  54.         answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&cursor), OCI_HTYPE_STMT, 0, NULL);
  55.  
  56.         answer = OCIStmtPrepare(pOciStatement, pOciError, (unsigned char *)sqlCharArray2, strlen(sqlCharArray2),
  57.                                 OCI_NTV_SYNTAX, OCI_DEFAULT);
  58.         answer = OCIBindByPos(pOciStatement,&pBind, pOciError, 1, &cursor, 0,SQLT_RSET,
  59.                                   pIndicator2, 0,NULL, 0,0,OCI_DEFAULT);
  60.  
  61.         answer = OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, 1, 0, NULL, NULL,
  62.                                 OCI_DEFAULT);
  63.  
  64.         writeToLog(sqlCharArray2);
  65.         if (answer != 0)
  66.         {
  67.             writeToLog("Bad2");
  68.             return false;
  69.         }
  70.         answer = OCIDefineByPos(cursor,&pOciDefine, pOciError,2,szid,sizeof(szid),
  71.                                 SQLT_STR,pIndicator, 0, 0,OCI_DEFAULT);
  72.         answer = OCIDefineByPos(cursor,&pOciDefine2, pOciError,1,ename,40,
  73.                                 SQLT_STR,pIndicator3, 0, 0,OCI_DEFAULT);
  74.  
  75.         while ((answer = OCIStmtFetch(cursor,pOciError, 1,OCI_FETCH_NEXT,OCI_DEFAULT)) == 0)
  76.         {
  77.                 slog = string(szid) + string(ename);
  78.                 writeToLog(slog);
  79.         }
  80.         answer = OCIHandleFree(pOciError, OCI_HTYPE_ERROR);
  81.     return true;
  82. }

Any suggestion will be useful.
Thanks.
Last edited by alc6379; Feb 23rd, 2005 at 4:47 pm. Reason: added [code] tags
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the C++ Forum


Views: 6960 | Replies: 0
Thread Tools Search this Thread



Tag cloud for C++
6 add api array arrays beginner binary bitmap c++ c/c++ calculator char class classes code compile compiler console conversion convert count data delete desktop directshow dll encryption error file forms fstream function functions game getline givemetehcodez google graph homeworkhelper iamthwee ifstream input int integer java lazy lib linkedlist linux loop looping loops map math matrix memory microsoft newbie news node number output parameter pointer problem program programming project proxy python random read recursion recursive reference return sort string strings struct studio system template templates test text tree unix url variable vector video visual visualstudio win32 windows winsock word wordfrequency wxwidgets
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC