Hi,
I am trying to call a PLSQL stored procedure with in an excel macro code. But not getting any clue how to do it?
Procedure:
procedure test_excel( p_1 IN number, p_2 out varchar) as
begin
if p_1 = 1 then
p_2:= 'piyush';
else
p_2:='bhatt';
end if;
end;
Macro code:
Sub callProc()
Dim rs As Object, com As Object
Dim dbname As String
Dim username As String
Dim password As String
Dim param as String
dbname = Cells(1, 1)
username = Cells(2, 1)
password = Cells(3, 1)
Set cn = CreateObject("ADODB.Connection")
cn.Open ("Provider=OraOLEDB.Oracle.1;Password=" + password + ";Persist Security Info=True;User ID=" + username + ";Data Source=" + dbname)
Dim SQL
Set com = CreateObject("ADODB.Command")
Set com.ActiveConnection = cn
com.CommandText = "test_excel(1," + param + ")"
com.Execute
cells(6,1) = param
End Sub
It gives me ORA-00900 Invalid SQL statement error. But If I call a procedure with only IN parameters in similar way. It works for me. Please advice..!!
