Home » SQL & PL/SQL » SQL & PL/SQL » Calling stored procedure from excel
Calling stored procedure from excel [message #220771] Thu, 22 February 2007 00:40 Go to next message
Yarlee
Messages: 1
Registered: February 2007
Junior Member
I want to connect to database from excel and generate a sequence number. I wrote the following procedure

sub distb_id()

Dim OraSession As Object
Dim OraDatabase As Object

Const ORAPARM_INPUT = 1
Const ORAPARM_OUTPUT = 2
Const ORAPARM_BOTH = 3

Const ORATYPE_VARCHAR = 1
Const ORATYPE_NUMBER = 2


Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.dbopendatabase("3DVDRG03", "rgusr13/rgusr13", 0&)

OraDatabase.Parameters.Add ("po_i_var"), 520999, ORAPARM_INPUT
OraDatabase.Parameters("po_i_var").ServerType = ORATYPE_NUMBER
OraDatabase.Parameters.Add ("po_num_source_var"), "0907", ORAPARM_INPUT
OraDatabase.Parameters("po_num_source_var").ServerType = ORATYPE_VARCHAR
OraDatabase.Parameters.Add ("dc_no number"), 579, ORAPARM_INPUT
OraDatabase.Parameters("dc_no number").ServerType = ORATYPE_NUMBER
OraDatabase.Parameters.Add ("new_po_distb_i"), "", ORAPARM_OUTPUT
OraDatabase.Parameters("new_po_distb_i").ServerType = ORATYPE_VARCHAR
OraDatabase.DbExecuteSQL ("Begin DBCALLS.drc_gen_new_distribution_pkg.drc_new_dist_sp (:po_i_var, :po_num_source_var, :dc_no number, :new_po_distb_i); End;")

MsgBox (OraDatabase.Parameters("new_po_distb_i"))

OraDatabase.Parameters.Remove "po_i_var"
OraDatabase.Parameters.Remove "po_num_source_var"
OraDatabase.Parameters.Remove "dc_no number"
OraDatabase.Parameters.Remove "new_po_distb_i"
end sub
The description of package is as below (no access to look into the contents)

Parameter Type Mode Default?
------------------ ------ ---- --------
PO_I_IN NUMBER IN
PO_NUM_SOURCE_C_IN CHAR IN
DC_NUMBER_IN NUMBER IN
NEW_DISTB_I_OUT CHAR OUT

when I tried to run the procedure it is throwing the following error

run time error 440
sql execution error ,ora -06550:line 1 ,column 91:
PLS-00103 encountered the symbol "NUMBER" when expecting one of the following:
<,>......

Can any one help me in this

thanks in advance
Re: Calling stored procedure from excel [message #220774 is a reply to message #220771] Thu, 22 February 2007 01:47 Go to previous message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
"sql execution error ,ora -06550:line 1 ,column 91:
PLS-00103 encountered the symbol "NUMBER" when expecting one of the following: ..."

It seems to be an error in your oracle package(or stored procedure), so you should post also the Oracle code...
Previous Topic: INSERT BLOB IN ORACLE 10G
Next Topic: How to code a SQL for offset data from single table
Goto Forum:
  


Current Time: Sat Dec 10 18:45:01 CST 2016

Total time taken to generate the page: 0.08981 seconds