Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: OCI Array Binding

    Hello. I'm trying to insert an array of values in to a table using OCI, but I can't quite figure out how to do it. Please help.

    This code tries to add 2 strings to the same column with one execute statement. The column, "ColA", is of CHAR(1024). The execute function gives back a PLS-00382: expression is of wrong type.

    thanks
    Brian

    Code:
    #include "stdafx.h"
    #include "OCITest.h"
    #ifdef _DEBUG
    #define new DEBUG_NEW
    #endif
    
    #include <oci.h>
    #define BUFFERSIZE 1024
    // The one and only application object
    
    CWinApp theApp;
    
    using namespace std;
    
    void checkerr(OCIError *errhp, sword status)
    {
    	text errbuf[512];
    	sb4 errcode = 0;
    
    	switch (status)
    	{
    	case OCI_SUCCESS:
    		break;
    	case OCI_SUCCESS_WITH_INFO:
    		(void) _tprintf(_T("Error - OCI_SUCCESS_WITH_INFO\n"));
    		break;
    	case OCI_NEED_DATA:
    		(void) _tprintf(_T("Error - OCI_NEED_DATA\n"));
    		break;
    	case OCI_NO_DATA:
    		(void) _tprintf(_T("Error - OCI_NODATA\n"));
    		break;
    	case OCI_ERROR:
    		(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
    			errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    		(void) _tprintf(_T("Error - %s\n"), errbuf);
    		break;
    	case OCI_INVALID_HANDLE:
    		(void) _tprintf(_T("Error - OCI_INVALID_HANDLE\n"));
    		break;
    	case OCI_STILL_EXECUTING:
    		(void) _tprintf(_T("Error - OCI_STILL_EXECUTE\n"));
    		break;
    	case OCI_CONTINUE:
    		(void) _tprintf(_T("Error - OCI_CONTINUE\n"));
    		break;
    	default:
    		break;
    	}
    }
    
    
    
    int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
    {
    	int nRetCode = 0;
    
    	// initialize MFC and print and error on failure
    	if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))
    	{
    		// TODO: change error code to suit your needs
    		_tprintf(_T("Fatal Error: MFC initialization failed\n"));
    		nRetCode = 1;
    	}
    	else
    	{
    		OCIEnv*     ociEnvH = NULL;
    		OCIServer*  ociServH = NULL;
    		OCIError*   ociErrH = NULL;
    		OCISvcCtx*  ociSvcH = NULL;
    		OCISession* ociSessH = NULL;
    		OCIStmt*    ociStmtH = NULL;
    		OCIBind*     ociBindHs[2];
    				
    		// Create environmental handle
    		OCIEnvCreate(&ociEnvH, OCI_UTF16, 0,0,0,0,0,0);
    
    		// Create server handle
    		OCIHandleAlloc(ociEnvH, (dvoid**) &ociServH, OCI_HTYPE_SERVER, 0, 0);
    
    		// Create error handle
    		OCIHandleAlloc(ociEnvH, (dvoid**) &ociErrH, OCI_HTYPE_ERROR, 0, 0);
    
    		// Create a server contact
    		OCIServerAttach(ociServH, ociErrH, (text*)_T("ORA92"), _tcslen(_T("ORA92")) * sizeof(TCHAR), OCI_DEFAULT);
    
    		// Create a service handle
    		OCIHandleAlloc(ociEnvH, (dvoid**) &ociSvcH, OCI_HTYPE_SVCCTX, 0, 0);
    
    		// Link the server to the service handle
    		OCIAttrSet(ociSvcH, OCI_HTYPE_SVCCTX, ociServH, 0, OCI_ATTR_SERVER, ociErrH);
    
    		// Create user session
    		OCIHandleAlloc (ociEnvH, (dvoid **)&ociSessH, OCI_HTYPE_SESSION, 0, 0);
    
    		// Set user login name
    		OCIAttrSet(ociSessH, OCI_HTYPE_SESSION, (text*)_T("sys"), _tcslen(_T("sys")) * sizeof(TCHAR), OCI_ATTR_USERNAME, ociErrH);
    
    		// Set user password
    		OCIAttrSet(ociSessH, OCI_HTYPE_SESSION, (text*)_T("metsys"), _tcslen(_T("metsys")) * sizeof(TCHAR), OCI_ATTR_PASSWORD, ociErrH);
    
    		// Begin user session
    		checkerr(ociErrH, OCISessionBegin(ociSvcH, ociErrH, ociSessH, OCI_CRED_RDBMS, OCI_SYSDBA));
    
    		// Link the user session to the service context
    		OCIAttrSet(ociSvcH, OCI_HTYPE_SVCCTX, ociSessH, 0, OCI_ATTR_SESSION, ociErrH);
    
    		// Create statement
    		checkerr(ociErrH, OCIHandleAlloc(ociEnvH, (dvoid**)&ociStmtH, OCI_HTYPE_STMT, 0, 0));
    	
    		// Prepare statement
    		TCHAR* sqlString = _T("begin\
    							   INSERT INTO ed.keke(colA) VALUES (:valA);\
    							   commit;\
    							   end;");
    		int sqlStringSz = _tcslen(sqlString) * sizeof(TCHAR);
    		checkerr(ociErrH, OCIStmtPrepare(ociStmtH, ociErrH, (text*)sqlString, sqlStringSz, OCI_NTV_SYNTAX, OCI_DEFAULT));
    
    		// Bind parameters
    		ub4 numberOfElements = 2;
    		int stringLength = _tcslen(_T("colA-1")) * sizeof(TCHAR);
    		sb2 indicatorArray[2] = {stringLength + sizeof(TCHAR), stringLength + sizeof(TCHAR)};
    		ub2 colADataLengths[2] = {stringLength, stringLength};
    		ub2 colAReturnCodes[2] = {0, 0};
    		TCHAR colAData[2][BUFFERSIZE] = {_T("colA-1"), _T("colA-2")};
    		
    		colAData[0][0] = sqlStringSz;
    		checkerr(ociErrH, OCIBindByName(ociStmtH, &ociBindHs[0], ociErrH, (text*)_T("valA"), _tcslen(_T("valA")) * sizeof(TCHAR),
    			          colAData, sizeof(colAData[0]), SQLT_STR, indicatorArray, colADataLengths, 
    					  colAReturnCodes, 2, &numberOfElements, OCI_DEFAULT));
    		
    		// Execute the statement
    		checkerr(ociErrH, OCIStmtExecute(ociSvcH, ociStmtH, ociErrH, 1, 0, 0, 0, OCI_DEFAULT));
    
    	return 0;
    
    		
    	}
    
    	return nRetCode;
    }

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, I can't help you.

    However, if you ever decide to change from OCI to OCCI, then I may be of some precious help.

    Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •