<v.sorna_at_gmail.com> wrote in message
news:1169442723.657026.232560_at_a75g2000cwd.googlegroups.com...
> Thanks everyone for the replies.
> Following is the code i have written.I have to use OCI to make things
> done.
> Following is the code i have wriitten.I'm not sure if that is correct
> or missing out something.
> Connection is going on well and i'm able to collect the data from the
> flat file (which has single row of tab seperated values) into array .It
> goes well till the data is collected into direct path col array and
> while loading there is problem.Please help me out .
>
>
> // bulk_o.cpp : Defines the entry point for the console application.
> //
>
> #include "stdafx.h"
> #include <oci.h>
> #include<occi.h>
> extern "C" {
> # include<oci8dp.h>
> };
> #include <stdio.h>
> #include <ctype.h>
> #include <cctype>
> #include <vector>
> #include <xstring>
> #include <cstring>
> #include <oratypes.h>
> #include<string>
> # include<iostream>
>
>
> using namespace oracle::occi;
> using namespace std;
> int _tmain(int argc, _TCHAR* argv[])
> {
>
> char colDelim = '\t';
> int rowNum = 0;
> int colNum = 0;
> long buffer_size=917504;
> char inLine[1024];
> sword retcode;
> char *strcol;
> char *buf=NULL;
> char str[100][100];
> int col=1;
> OCIEnv *envp;
> OCIDirPathCtx *dpctx=NULL;
> sword error;
> char *tmp;
> char *strTableName="TEST";
> OCIError *errhp;
> OCIServer *srvhp;
> OCISvcCtx *svchp;
> OCIStmt *stmthp;
> OCISession *usrhp; /* user session handle */
> Environment* m_env ;
>
> m_env = Environment::createEnvironment();
> Connection* m_conn ;
> std::string cSQL = "INSERT INTO TEST (X,Y,Z) values
> ('mango',52,'grapes')";
> m_conn = m_env->createConnection("username","password","//url");
> Statement* stmt ;
> stmt = m_conn->createStatement(cSQL);
> stmt->setAutoCommit(true);
> envp = m_env->getOCIEnvironment();
> svchp = m_conn->getOCIServiceContext();
>
> //stmthp = stmt->getOCIStatement();
> error= OCIHandleAlloc((dvoid *) envp, (dvoid **) &errhp,
> (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0);
>
> //stmthp = stmt->getOCIStatement();
> /*update is commented **********************/
>
> //stmt->executeUpdate(cSQL);
> //m_conn->terminateStatement(stmt);
> // OCIStmtExecute(svchp,stmthp,errhp,(ub4)0,(ub4)0,(CONST
> OCISnapshot*)NULL,(OCISnapshot *)NULL,OCI_STMT_SCROLLABLE_READONLY);
>
> error = OCIHandleAlloc((dvoid *)envp, (dvoid
> **)&dpctx,OCI_HTYPE_DIRPATH_CTX, (size_t)0,(dvoid **)0);
> if(dpctx==NULL)
> cout<<"some thing\n";
> OCIDirPathColArray *dpca=NULL; /* direct path column array */
> OCIDirPathStream *dpstr=NULL;
> cout<<"dpctx "<<error<<endl;
> int ncol=3;
> OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX,
> (dvoid *)"test",
> (ub4)strlen((const char *)"test"),
> (ub4)OCI_ATTR_NAME, errhp);
> OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX,
> (dvoid *)&ncol,
> (ub4)0, (ub4)OCI_ATTR_NUM_COLS, errhp);
>
>
> OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid
> *)&buffer_size, (ub4)0, (ub4)OCI_ATTR_BUF_SIZE, errhp);
> char *vbuf;
> vbuf = (char *)malloc((size_t)buffer_size);
> if (vbuf != (char *)0)
> (void)setvbuf(stdin, vbuf, _IOFBF, (size_t)(buffer_size));
> //OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX, (dvoid
> *)OCI_DIRPATH_LOAD, (ub4)0, (ub4)OCI_ATTR_DIRPATH_MODE, errhp);
> OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX,
> (dvoid *)"mdbadmin",
> (ub4)strlen((const char *)"mdbadmin"),
> (ub4)OCI_ATTR_SCHEMA_NAME, errhp);
> OCIDirPathPrepare ( dpctx,svchp,errhp);
> OCIHandleAlloc((dvoid *)dpctx, (dvoid **)&dpca,
> (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
> (size_t)0, (dvoid **)0);
> cout<<"dpca "<<error<<endl;
>
>
>
>
> int nrow=1;
> /* direct path stream */
>
> OCIAttrSet(dpca, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
> &nrow, 0, OCI_ATTR_NUM_ROWS,
> errhp);
>
>
> OCIAttrSet(dpca, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
> &ncol, 0, OCI_ATTR_NUM_COLS,
> errhp);
> /****************IS THIS NESSARY?????/******************/
>
> OCIDescribe *hDescribe=NULL;
> char column[100][100]={"X","Y","Z"};
> char columntype[100][100]={"SQLT_CHR","SQLT_INT","SQLT_CHR"};
> int i,pos;
> for (i = 0, pos = 1;i < ncol;i++, pos++)
> {
> /* get parameter handle on the column */
> OCIDescribeAny(svchp, errhp, (dvoid*)column[i], strlen(column[i]),
> OCI_OTYPE_NAME, 0, OCI_PTYPE_COL, hDescribe);
> OCIParamGet((CONST dvoid *)column,
> (ub4)OCI_DTYPE_PARAM, errhp,
> (dvoid **)&hDescribe, pos);
>
> /* set external attributes on the column */
> /* column name */
> OCIAttrSet((dvoid *)hDescribe, (ub4)OCI_DTYPE_PARAM,
> (dvoid *)column[i],
> (ub4)strlen((const char *)column[i]),
> (ub4)OCI_ATTR_NAME, errhp);
>
> OCIAttrSet((dvoid *)hDescribe, (ub4)OCI_DTYPE_PARAM,
> (dvoid *)columntype[i], (ub4)0,
> (ub4)OCI_ATTR_DATA_TYPE, errhp);
> // OCIDescriptorFree((dvoid *) hDescribe, OCI_DTYPE_PARAM);
> }
>
> /****************IS THIS NESSARY?????/******************/
>
>
>
> FILE *inpFil = fopen("E:\\test.dat", "r");
>
> //while ( ! feof(inpFil) )
> //{
> fgets(inLine, 1000, inpFil);
>
> strcol= strtok (inLine,"\t");
> printf("%s",strcol);
> strcpy (str[0],strcol);
> printf("string array=%s\n",str[0]);
> while (strcol != NULL)
> {
> strcol = strtok (NULL, "\t");
> if (strcol != NULL)
> {
> strcpy (str[col],strcol);
> printf ("string array=%s\n",str[col]);
> printf("full string from file=%s\n",strcol);
> col++;
> }
> }
>
>
> /* Set entries in the column array to point to
> the input data value for each column */
> printf("before entryyyyyyyyyyyyyyyy");
>
> for ( colNum = 0; colNum < col; colNum++)
> {
> try
> {
> error = OCIDirPathColArrayEntrySet(dpca, errhp, rowNum,
> colNum,(unsigned char*)str[colNum],strlen((const char
> *)str[colNum]),OCI_DIRPATH_COL_PARTIAL);
> }
> catch(...)
> {
>
> printf("%d",error);
> text errbuf[512];
> /* text msgbuf[1024]="";
> int errcode1=0;
> OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
> &errcode1,
> msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
> printf("%s in the line %d\n",msgbuf,__LINE__);*/
> ub4 buflen;
> sb4 errcode;
> switch (retcode)
> {
> case OCI_SUCCESS_WITH_INFO:
> printf("Error - OCI_SUCCESS_WITH_INFO\n");
> OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
> errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
> printf("Error - %s\n", errbuf);
> break;
> case OCI_NEED_DATA:
> printf("Error - OCI_NEED_DATA\n");
> break;
> case OCI_NO_DATA:
> printf("Error - OCI_NO_DATA\n");
> break;
> case OCI_ERROR:
> OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
> errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
> printf("Error - %s\n", errbuf);
> break;
> case OCI_INVALID_HANDLE:
> printf("Error - OCI_INVALID_HANDLE\n");
> break;
> case OCI_STILL_EXECUTING:
> printf("Error - OCI_STILL_EXECUTING\n");
> break;
> case OCI_CONTINUE:
> printf("Error - OCI_CONTINUE\n");
> break;
> default:
> printf("Error - %d\n", error);
> break;
> }
>
> }
> }
>
>
> printf("after entryyyyyyyyyyyyyyyy");
> //(void) OCIDirPathColArrayReset(dpca, errhp);
> /* convert column array to direct path stream */
> error = OCIHandleAlloc((dvoid *)dpctx, (dvoid
> **)&dpstr,(ub4)OCI_HTYPE_DIRPATH_STREAM, (size_t)0,(dvoid **)0);
>
> cout<<"dpstr "<<error<<endl;
> text msgbuf3[1024]="";
> int errcode3=0;
> OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
> &errcode3,
> msgbuf3, (ub4) sizeof(msgbuf3), (ub4) OCI_HTYPE_ERROR);
> printf("%s in the line %d\n",msgbuf3,__LINE__);
>
> retcode = OCIDirPathColArrayToStream( dpca,dpctx, dpstr, errhp, 1, 0);
> printf("after stream entryyyyyyyyyyyyyyyy");
> //rowNum++; //col=1;
> //}
>
> /* Load the direct path stream */
> retcode = OCIDirPathLoadStream( dpctx, dpstr, errhp );
> printf("after load entryyyyyyyyyyyyyyyy");
> /*text msgbuf4[1024]="";
> OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
> &errcode3,
> msgbuf4, (ub4) sizeof(msgbuf4), (ub4) OCI_HTYPE_ERROR);
> printf("%s in the line %d\n",msgbuf4,__LINE__);
> */
> /* Invoke the direct path finishing function */
> try
> {
> retcode=OCIDirPathDataSave( dpctx, errhp,OCI_DIRPATH_DATASAVE_FINISH);
> }
> catch(...)
> {
>
> printf("%d",error);
> text errbuf[512];
> /* text msgbuf[1024]="";
> int errcode1=0;
> OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
> &errcode1,
> msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
> printf("%s in the line %d\n",msgbuf,__LINE__);*/
> ub4 buflen;
> sb4 errcode;
> switch (retcode)
> {
> case OCI_SUCCESS_WITH_INFO:
> printf("Error - OCI_SUCCESS_WITH_INFO\n");
> OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
> errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
> printf("Error - %s\n", errbuf);
> break;
> case OCI_NEED_DATA:
> printf("Error - OCI_NEED_DATA\n");
> break;
> case OCI_NO_DATA:
> printf("Error - OCI_NO_DATA\n");
> break;
> case OCI_ERROR:
> OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
> errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
> printf("Error - %s\n", errbuf);
> break;
> case OCI_INVALID_HANDLE:
> printf("Error - OCI_INVALID_HANDLE\n");
> break;
> case OCI_STILL_EXECUTING:
> printf("Error - OCI_STILL_EXECUTING\n");
> break;
> case OCI_CONTINUE:
> printf("Error - OCI_CONTINUE\n");
> break;
> default:
> printf("Error - %d\n", error);
> break;
> }
>
> }
> printf("after data path save entryyyyyyyyyyyyyyyy");
> // free up server data structures for the load.
> retcode = OCIDirPathFinish( dpctx, errhp );
>
> printf("finish %d",retcode);
> printf("after data path finish entryyyyyyyyyyyyyyyy");
> return 0;
> }
>
>
> Volker Hetzer wrote:
>> Mark D Powell schrieb:
>> >
>> > On Jan 19, 9:18 am, "What's in a namespace" <what..._at_xs4all.nl> wrote:
>> >> <v.so..._at_gmail.com> schreef in
>> >> berichtnews:1169211516.698151.152330_at_m58g2000cwm.googlegroups.com...
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>> Hi,
>> >>> I'm new to DPL/OCI/OCCI.I'm trying to load data from flat file into
>> >>> table.
>> >>> 1)Connection to db(tested by inserting rows programatically)
>> >>> 2)Reading a dat file in E:\\test.dat which has tab seperated values
>> >>> into double dim array
>> >>> 3)allocating direct path context handle and setting attributes
>> >>> 4) allocating direct path column array handle using OCIHandleAlloc
>> >>> 3)setting the array created as direct path column array (dpca)
>> >>> OCIDirPathColArrayEntrySet
>> >>> Now that at the point,direct path stream (dpstream) handle is to be
>> >>> allocated and used to convert dpca into dpstream, OCIHandleAlloc is
>> >>> failing with ORA-24316: illegal handle type .
>> >>> So from that point everything is failing.
>> >>> Pleasae help me out ,...Take a look at External Tables!- Hide quoted
>> >>> text -- Show quoted text -
>> >
>> >
>> > If the job is to be triggered from outside the database then why not
>> > just use sqlldr utility? If you want to trigger the task from within
>> > the database then use the mentioned External table feature (which is an
>> > interface to sqlldr).
>> I second that.
>> Unless you need to do other things in the same transaction (or session),
>> and staging tables are out of the question, sqlldr is IMHO the better
>> choice.
>> It helpfully takes care of the constraint disabling/enabling and is
>> probably
>> as fast as direct loads are going to get anyway.
>>
>> Lots of Greetings!
>> Volker
>> --
>> For email replies, please substitute the obvious.
>
So why again, are you not just using an external table for this process??
I am using the free version of SPAMfighter for private users.
It has removed 84 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!
Received on Mon Jan 22 2007 - 01:03:29 CST
