Hi Ron,

>2. Copy the structure from the remote updateable view into a table in the VFP database, fill that with data (this step works perfectly), then append into the remote view. This blows up trying to add the first record, because it seems the remote view is waiting for the memo field values. The error message text is: "Connectivity error: [Oracle][ODBC]No data at execution values pending."

Sounds as if you have the hard part done.

I see two possible paths after you have the data in a VFP table or cursor.

1) Modify the existing remote view to work with correctly Oracle. I suspect that the Varchar2's are tripping you. I have had some success in the past with scoping these to binary memo in the view designer when running against Oracle 8.x.

2) Create a SQL insert statement using view parameters, precompile it, and scan the cursor, storing the values from the cursor to the view parms and issuing a SQLExec() at the bottom of each loop. Check with your DBA as to 'commit' intervals. I'm not sure how fast large Varchar2's will eat rollback segments, but I suspect that it would be easy to exceed the space available. You may need to commit after each row.

Be sure to limit the length of each character view parm to the space designated in the schema and to trim trailing spaces on the varchar2's.

See the example in the help file available from the SQLPrepare() documentation if you are not familar with using precompiled parameterized sql.


Although option 2 is a bit more work, it can be done without using a database container and, IMHO, debugging is much simpler, since you can use DEBUGOUT to export the SQL and monitor the values and types of the view parameters in the debugger.



Jim


>Hi,
>Question: How do I best get data from a text file, using a VFP program, into Oracle?
>Text file is variable length message log file.
>Oracle structure is as follows:
> date NOT null,
> char(6) NOT null,
> char(7) Default 'dfvalue' NOT null,
> char(6) Default 'novalu' NOT null,
> char(1) NOT null,
> char(10) Default 'valueisnil' NOT null,
> varchar2(1500) NOT null,
> varchar2(255) null,
> number null,
> through are all varchar2(4000) null
>Constraint is Primary Key
>Primary key is .
>The reason for this strange structure is that many of the messages differ only by content, which is contained in the various varchar2 columns. The majority of the messages will have no data at all in through .
>Using Oracle 8i with Oracle ODBC driver v8.01.06.00, and VFP6, what is the best way to get the data from VFP into Oracle?
>
>Here is what I have tried so far:
>1. Build dynamic SQL with an array, arRec() to hold each value for the INSERT ...(Fieldname1,Fieldname2...Fieldname25)VALUES(arRec(1),arRec(2)... arRec(25)) statement. This works great if the individual values are short strings without too many extraneous characters. I build a string called lcSQL which has the dynamically created SQL statement, then run it against the updateable remote view with &lcSQL. PROBLEM: If the message text is too long (many of the messages are tens of thousands of characters in length), or has quotes as part of the message, this approach does not work well for me.
>2. Copy the structure from the remote updateable view into a table in the VFP database, fill that with data (this step works perfectly), then append into the remote view. This blows up trying to add the first record, because it seems the remote view is waiting for the memo field values. The error message text is: "Connectivity error: [Oracle][ODBC]No data at execution values pending."
>3. Select the remote updateable view, SCATTER MEMO MEMVAR BLANK, fill the appropriate M. variables with data, make sure the view is selected, then INSERT INTO FROM MEMVAR. The error message text is: "Connectivity error: [Oracle][ODBC]No data at execution values pending."
>
>What do I need to be doing to get the data safely into Oracle?
>Any thoughts / suggestions would be much appreciated!
>Thanks,
> RonL