How can I return an error back to Access when executing a command to
load a file using SQLLOADER and a control file?

eg

Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"

(where execcmd is a function that executes the command)

colmkav wrote:
> How can I return an error back to Access when executing a command to
> load a file using SQLLOADER and a control file?
>
> eg
>
> Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"
>
> (where execcmd is a function that executes the command)

Microsoft Access? Why? In what version of Oracle?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Sorry i should explain that the code is in VBA as part of an Access
application but loading to an Oracle XE database


On 22 Jun, 17:07, DA Morgan <damor...@psoug.org> wrote:
> colmkav wrote:
> > How can I return an error back to Access when executing a command to
> > load a file using SQLLOADER and a control file?
>
> > eg
>
> > Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"
>
> > (where execcmd is a function that executes the command)
>
> Microsoft Access? Why? In what version of Oracle?
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

On Fri, 22 Jun 2007 08:03:05 -0700, colmkav <colmjkav@yahoo.co.uk>
wrote:

>How can I return an error back to Access when executing a command to
>load a file using SQLLOADER and a control file?
>
>eg
>
>Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"
>
>(where execcmd is a function that executes the command)

Not an Oracle question. Sqlloader returns a non-zero status, and it's
up to you.
And you don't disclose what execcmd is doing.
Apart from that, using an Access application to call VBA to call
SQLLDR to load an XE database: You are aware you can do this directly
in Access? If so: Why did you implement this drama?

--
Sybrand Bakker
Senior Oracle DBA

On 22 Jun, 18:11, sybra...@hccnet.nl wrote:
> On Fri, 22 Jun 2007 08:03:05 -0700, colmkav <colmj...@yahoo.co.uk>
> wrote:
>
> >How can I return an error back to Access when executing a command to
> >load a file using SQLLOADER and a control file?
>
> >eg
>
> >Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"


>
> >(where execcmd is a function that executes the command)
>
> Not an Oracle question. Sqlloader returns a non-zero status, and it's
> up to you.
> And you don't disclose what execcmd is doing.
> Apart from that, using an Access application to call VBA to call
> SQLLDR to load an XE database: You are aware you can do this directly
> in Access? If so: Why did you implement this drama?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Exec does the folliowing:

Public Sub ExecCmd(cmdLine$)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ReturnValue As Integer

' Initialize the STARTUPINFO structure:
start.cb = Len(start)

' Start the shelled application:
ReturnValue = CreateProcessA(0&, cmdLine$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

' Wait for the shelled application to finish:
Do
ReturnValue = WaitForSingleObject(proc.hProcess, 0)
DoEvents
Loop Until ReturnValue <> 258

ReturnValue = CloseHandle(proc.hProcess)
End Sub

On Fri, 22 Jun 2007 09:25:28 -0700, colmkav <colmjkav@yahoo.co.uk>
wrote:

>On 22 Jun, 18:11, sybra...@hccnet.nl wrote:
>> On Fri, 22 Jun 2007 08:03:05 -0700, colmkav <colmj...@yahoo.co.uk>
>> wrote:
>>
>> >How can I return an error back to Access when executing a command to
>> >load a file using SQLLOADER and a control file?
>>
>> >eg
>>
>> >Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"
>
>
>>
>> >(where execcmd is a function that executes the command)
>>
>> Not an Oracle question. Sqlloader returns a non-zero status, and it's
>> up to you.
>> And you don't disclose what execcmd is doing.
>> Apart from that, using an Access application to call VBA to call
>> SQLLDR to load an XE database: You are aware you can do this directly
>> in Access? If so: Why did you implement this drama?
>>
>> --
>> Sybrand Bakker
>> Senior Oracle DBA
>
>Exec does the folliowing:
>
>Public Sub ExecCmd(cmdLine$)
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ReturnValue As Integer
>
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
>
> ' Start the shelled application:
> ReturnValue = CreateProcessA(0&, cmdLine$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
>
> ' Wait for the shelled application to finish:
> Do
> ReturnValue = WaitForSingleObject(proc.hProcess, 0)
> DoEvents
> Loop Until ReturnValue <> 258
>
> ReturnValue = CloseHandle(proc.hProcess)
>End Sub

So, as I said, NOT an Oracle question.
You have 2 options
- either do it *properly* in Access
(by properly I mean: as you are already in Access, use ODBC to connect
the destination table to Access, and write a query to insert the file.
ODBC has both a text driver and an Oracle driver. Your sqlldr kludge
will set up extra connections to the database,and it will need to
spawn threads)
- visit a Mickeysoft related newsgroup (or read the docs) to find out
what WaitForSingleObject does with a process returning with a non-zero
value.
Your issue has NOTHING to do with Oracle.
When you car runs out of gasoline, you aren't asking for assistance
here either, are you?

--
Sybrand Bakker
Senior Oracle DBA

On 23 Jun, 09:36, sybra...@hccnet.nl wrote:
> On Fri, 22 Jun 2007 09:25:28 -0700, colmkav <colmj...@yahoo.co.uk>
> wrote:
>
>
>
>
>
> >On 22 Jun, 18:11, sybra...@hccnet.nl wrote:
> >> On Fri, 22 Jun 2007 08:03:05 -0700, colmkav <colmj...@yahoo.co.uk>
> >> wrote:
>
> >> >How can I return an error back to Access when executing a command to
> >> >load a file using SQLLOADER and a control file?
>
> >> >eg
>
> >> >Execcmd "SQLLDR EQRISK/eq control=Instrument.ctl"
>
> >> >(where execcmd is a function that executes the command)
>
> >> Not an Oracle question. Sqlloader returns a non-zero status, and it's
> >> up to you.
> >> And you don't disclose what execcmd is doing.
> >> Apart from that, using an Access application to call VBA to call
> >> SQLLDR to load an XE database: You are aware you can do this directly
> >> in Access? If so: Why did you implement this drama?
>
> >> --
> >> Sybrand Bakker
> >> Senior Oracle DBA
>
> >Exec does the folliowing:
>
> >Public Sub ExecCmd(cmdLine$)
> > Dim proc As PROCESS_INFORMATION
> > Dim start As STARTUPINFO
> > Dim ReturnValue As Integer
>
> > ' Initialize the STARTUPINFO structure:
> > start.cb = Len(start)
>
> > ' Start the shelled application:
> > ReturnValue = CreateProcessA(0&, cmdLine$, 0&, 0&, 1&, _
> > NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
>
> > ' Wait for the shelled application to finish:
> > Do
> > ReturnValue = WaitForSingleObject(proc.hProcess, 0)
> > DoEvents
> > Loop Until ReturnValue <> 258
>
> > ReturnValue = CloseHandle(proc.hProcess)
> >End Sub
>
> So, as I said, NOT an Oracle question.
> You have 2 options
> - either do it *properly* in Access
> (by properly I mean: as you are already in Access, use ODBC to connect
> the destination table to Access, and write a query to insert the file.
> ODBC has both a text driver and an Oracle driver. Your sqlldr kludge
> will set up extra connections to the database,and it will need to
> spawn threads)
> - visit a Mickeysoft related newsgroup (or read the docs) to find out
> what WaitForSingleObject does with a process returning with a non-zero
> value.
> Your issue has NOTHING to do with Oracle.
> When you car runs out of gasoline, you aren't asking for assistance
> here either, are you?
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

It is a question that is a cross between Oracle and Access. I have put
a similar question on the Oracle site too. I am replacing code that
uses the ODBC linked table method because this is too slow. My method
is much quicker - the only problem I have is that SQLLDR does not
return an error when no records are uploaded.


Nothing you have said relates to this question.

On Mon, 25 Jun 2007 03:13:35 -0700, colmkav <colmjkav@yahoo.co.uk>
wrote:

> the only problem I have is that SQLLDR does not
>return an error when no records are uploaded.
>
>

Incorrect, the only problem is you require mindreading.
You *never* specified the version you are using and you *never*
specified the contents of the ctl file. Do you think sqlldr should
return a non-zero status when all records don't qualify and are
rightfully ignored? Don
't think so
>Nothing you have said relates to this question.
Nothing you have said has enabled anyone to provide a solution. You
are wasting people's time.

Sybrand Bakker
Senior Oracle DBA