Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 21st, 2005, 10:58 AM
Registered User
 
Join Date: Jun 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Oracle SP's in VBA

Hi all,
I am trying to use two oracle stored procedures by creating a cmd object in VBA. I am getting errors about expressions of wrong type. I did an example in TOAD and it worked fine. I just can't seem to set up my cmd parameters right. Here is my VB code.

Private Sub cmdReceipt_Click()
    Set cmd = New ADODB.Command
       With cmd
            Set prm = cmd.CreateParameter("RetVal", adBoolean, adParamReturnValue) 'Return value for SP
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_CO", adVarChar, adParamInput, 200, RecCo)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_PURCHASE_ORDER", adVarChar, adParamInput, 200, PurchaseOrder)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_ITEM", adVarChar, adParamInput, 200, RecItem)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_QTY", adInteger, adParamInput, , Qty)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_REC_DATE", adDate, adParamInput, , RecDate)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_WAYBILL", adVarChar, adParamInput, 200, RecWaybill)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_SERIAL_NUMBER", adVarChar, adParamInput, 200, RecSerialNumber)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_NUMBER", adVarChar, adParamInput, 200, RecLotNumber)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_SELL", adDate, adParamInput, , LotSell)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_LOT_EXPIRE", adDate, adParamInput, , LotExpire)
            .Parameters.Append prm
            Set prm = cmd.CreateParameter("P_MSG", adVarChar, adParamOutput, 200, RecMsg)
            .Parameters.Append prm
            .ActiveConnection = MIConn
            .CommandType = adCmdStoredProc
            .CommandText = "PO_RECEIPT"
        End With
            cmd.Execute


            If cmd.Parameters("Retval").Value Then
                MsgBox ("Success")
            Else
                MsgBox ("Failed")
            End If

End Sub


Here is the basis of the SP:

CREATE OR REPLACE function po_receipt(p_co in varchar2,
                       p_purchase_order in varchar2,
                       p_item in varchar2,
                       p_qty in number,
                       p_rec_date in date,
                       p_waybill in varchar2,
                      p_serial_number in varchar2,
                      p_lot_number in varchar2,
                      p_lot_sell in date,
                      p_lot_expire in date,
                       p_msg out varchar2) return Boolean
IS
  ct number;
    v_co varchar2(5) := nvl(p_co,'100');
    v_sql_code number;
    v_stock_qty number;
    v_po_receive_key number;
    v_line_key number;
    v_waybill varchar2(30);



This is how i got it to work thru SQL Editor:

DECLARE
  RetVal Boolean;
  P_CO VARCHAR2(200);
  P_PURCHASE_ORDER VARCHAR2(200);
  P_ITEM VARCHAR2(200);
  P_QTY NUMBER;
  P_REC_DATE DATE;
  P_WAYBILL VARCHAR2(200);
  P_SERIAL_NUMBER VARCHAR2(200);
  P_LOT_NUMBER VARCHAR2(200);
  P_LOT_SELL DATE;
  P_LOT_EXPIRE DATE;
  P_MSG VARCHAR2(200);

BEGIN
  P_CO := '100';
  P_PURCHASE_ORDER := '1900';
  P_ITEM := 'WATER';
  P_QTY := 2;
  P_REC_DATE := '01-MAR-05';
  P_WAYBILL := NULL;
  P_SERIAL_NUMBER := NULL;
  P_LOT_NUMBER := NULL;
  P_LOT_SELL := NULL;
  P_LOT_EXPIRE := NULL;
  P_MSG := NULL;

  RetVal := PO_RECEIPT ( P_CO, P_PURCHASE_ORDER, P_ITEM, P_QTY, P_REC_DATE, P_WAYBILL, P_SERIAL_NUMBER, P_LOT_NUMBER, P_LOT_SELL, P_LOT_EXPIRE, P_MSG );
  COMMIT;
END;

There is probably some syntax difference. Can someone help me out??


 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
call oracle function using oracle link server vl SQL Server 2000 1 July 12th, 2007 08:19 AM
drop Oracle table from VBA excel reipereira VB Databases Basics 0 February 2nd, 2006 01:01 PM
Access VBA with Oracle connection pankaj_daga Access 0 September 7th, 2004 08:48 AM
Access VBA problem with Oracle database pankaj_daga Oracle 0 September 7th, 2004 08:47 AM
Error 265946 when using OLEDB with Oracle from VBA Ciarano Excel VBA 9 July 29th, 2004 05:00 PM



All times are GMT -4. The time now is 11:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.