Calling oracle stored procedure from Excel - VBA

I have to execute an Oracle stored procedure from vba (Excel) with around 38 input parameters. The

stored procedure will insert some values in the destination table once that is executed. When it is executed through VBA the number of fields which is inserted is less than when it is executed directly from the backend (oracle).

For example it is creating around 17 fields of records while executing directly from the back end. (I have created a wrapp开发者_运维知识库er class in the back-end and passing the same parameter values in the back-end.). It is creating around 15 fields of records while executing from the excel VBA in the destination table.

Kindly let me know what could be the possible reasons for this.

Debugging our own code is hard enough, debugging someone else's code remotely is virtually impossible. What we can do is provide you with some guidance ti help you debug your code.

Basically the same code behaves in two different ways when called from different clients with the same parameters. The most likely explanation is that the VBA call is not passing the parameter values which you think it is - perhaps there is a misnamed cell or a implicit datatype conversion - but the only way to know is build some tracing into your code. This means simply writing debug messages from inside code.

The regrettably common way of doing this is using DBMS_OUTPUT, AKA The Devil's Debugger. This writes to the screen, which rules it out for your scenario because you want to call the procedure from VBA. So you can either write to a file using UTL_FILE or a LOG_TABLE. Using a log table is probably more of a performance hit than writing to an OS file, but it is easier to work with (at least if you're happier with SQL than sed and grep).

A crude tracing implementation woudl look something like this. Using AUTONOMOUS_TRANSACTION is optional but it guarantees trace records in the event that the main procedure hurls an exception; it also allows you to monitor the output in real time.

create or replace procedure your_proc 
    (p1 in number
     , p2 in varchar2
     ...
     , p38 in date)
is
    ....
    procedure write_log 
         (p_action in varchar2
          , p_add_text in varchar2 := null)
    is
        pragma autonomous_transaction;
    begin
        insert into log_table
            (id, ts, action, add_text)
        values
            (log_seqno.nextval, systimestamp, p_action, p_add_text);
        commit;
    end write_log ;
begin
    write_log ('YOUR_PROC::START', user);
    write_log ('YOUR_PROC::PARAM', 'P1='||to_char(p1));
    write_log ('YOUR_PROC::PARAM', 'P2='||p2);
    ....
    write_log ('YOUR_PROC::PARAM', 'P32='||to_char(p32));
    ...
    --  main body of procedure happens here
    ...
    write_log ('YOUR_PROC::END');
exception
    when some_exception then
        write_log ('YOUR_PROC::EXCEPTION='||some_exception);
        raise;
end your_proc;     

As well as recording the values of the paramters you will want to record stages in the program flow, especially noting loops and conditiona; switch (IF.. ELSE, CASE, etc).

Run the program twice, once from VBA, once from SQL*Plus. Then compare the two sets of output. Where they differ, that will be you answer.

My best guess is that the command you are sending from VBA is different than whatever you are sending when you run through SQLPlus or whatever. What I would do is have whatever VBA procedure you are running to call the Oracle Stored Procedure output the actual command it is sending. I've had silly issues where I was sending a command like: storedproc(p1, p2, p3) from one tool and storedproc(p1, p2, p3,p4) from another or storedproc(p1, p3, p2) because I was building my command incorrectly (or similar reasons). You also may be sending different values altogether.
Anyhow, if you can take the command VBA is sending and compare it to whatever command you are running 'manually', you should see the issue. (also, are you connecting as the same user in both cases?)