Calling Oracle Procedure from ASP

[meenakshidhar]

Hi All,
I want to call oracle procedure from my ASP page in which i m passing the value of empno and want to display empname,job etc..from the table...my code is given below...

=========================================================
Dim strSearchName
Dim objConnection
Dim objCommand
Dim objRecordset

strSearchName = 7369

Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=INDV;Persist Security Info=True"
.Open
End With

Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection = objConnection
.CommandText = "{call proc_meenu1(" & _
"?, {resultset 100, empno})}"
.CommandType = 1
.Parameters(0).Value = strSearchName
Set objRecordset = .Execute()
End With

Response.Write objRecordset.Fields("EmpName")
Response.Write objRecordset.Fields("job")
=========================================================

urgent help needed...

Regards,
Meenakshi Dhar

 

[meenakshidhar]

hi again...

My stored procedure is given below...
==========================================================
CREATE OR REPLACE procedure proc_meenu1(v_empno emp.empno%TYPE)
IS
CURSOR c1(v_empno emp.empno%TYPE) IS
SELECT * FROM EMP
WHERE empno = v_empno;
BEGIN
FOR I IN C1(v_empno)
LOOP
Dbms_Output.PUT_LINE(i.empno || ' ' || i.ename);
END LOOP;
END;
==========================================================

 

[DotNetGnat]

the example found in this link should help you out...

http://www.learnasp.com/learn/FAQOraclestoredproc.asp

-DNG

 

[RhythmAddict112]

Here is another example...

<%

  '*****BUILD COMPLETE NAME FOR CALLING IBA ORACLE PACKAGE**************************** 
  '*NOTE THIS IS FORMAT NEEDED TO EXECUTE ORACLE PACKAGE/PROCEDURE USING ADO COMMAND OBJECT 
  '*NewBatch2 is the name of the stored proc
  '*IMPORTANT: YOU MUST HAVE A QUESTION MARK FOR EACH PARAMETER - IN AND OUT
  strOraclePkgSQL = "{call NewBatch2.NewBatch2(?,?,?,?,?,?,?)}" 

  '*****CREATE YOUR ADO CONNECTION OBJECT************** 
  Set objConn = Server.CreateObject("ADODB.Connection") 
  objConn.ConnectionString = "Provider=MSDAORA;Data Source=@#$@.world;Persist Security Info=True;user id=!@#;password=%@#%@;"
  objConn.CursorLocation = 3 '* adUseClient 
  objConn.Open 

  '****CREATE ADO COMMAND OBJECT AND SET PARAMETERS ****************************** 
  Set objCommand = Server.CreateObject("ADODB.Command") 
  with objCommand 
     set .ActiveConnection = objConn 
     .CommandText = strOraclePkgSQL 
     .CommandType = 1  '***adCmdText   
   
     '*****SET ALL THE INPUT/OUTPUT PARAMETERS FOR ORACLE PROCEDURE IN THE ADO 
     '*****COMMAND OBJECT USING THE CREATEPARAMETER AND APPEND METHODS*********** 
     '*THE FOLLOWING INDICATES THE PARAMETERS FOR *CREATEPARAMETER* METHOD:**** 
     '*****1ST PARAMETER - NAME OF STORED PROCEDURE PARAMETER 
     '*****2ND PARAMETER - DATATYPE OF PARAMETER  200=VARCHAR,3=INTEGER,14=DECIMAL 
     '*****3RD PARAMETER - DIRECTION OF PARAMETER  1=INPUT,2=OUTPUT,3=INPUT/OUTPUT 
     '*****4TH PARAMETER - LENGTH OF PARAMETER 
     '*****5TH PARAMETER - VALUE OF INPUT PARAMETER 
     '****************************************************************************** 
     '*****These are all of my input params 1 - 6

     set objPar1 = .CreateParameter("Dt",200,1,11,"17-SEP-2004") 
     .Parameters.Append objPar1     
               
     set objPar2 = .CreateParameter("glbRegion",200,1,2,"NY") 
     .Parameters.Append objPar2     
     
     set objPar3 = .CreateParameter("glbUser",200,1,6,"greenf") 
     .Parameters.Append objPar3         

     set objPar4 = .CreateParameter("cBillSys",200,1,40,"I") 
     .Parameters.Append objPar4     

     set objPar5 = .CreateParameter("strPromoCodes",200,1,1," ") 
     .Parameters.Append objPar5     
   
     set objPar6 = .CreateParameter("BatchID",200,1,4,"8540") 
     .Parameters.Append objPar6         
           
     '***SET OUTPUT PARAMETER************************************************* 
     set objPar7 = .CreateParameter("nID_OUT",3,2,7,0) '***OUTPUT PARAMETER 
     .Parameters.Append objPar7 
     
  end with     
     
  objCommand.execute 
   
  '****GRAB THE RETURN CODE 
  intIDout = objCommand("nID_OUT") 
   
  response.write "Your output code:" & intIDout 

%>

All hail the INTERWEB!

 

[RhythmAddict112]

Oh, my example is for a stored proc with IN and OUT params...You don't need to do all that if you've only got IN params. However, it looks like you've got both IN and OUT. Let me know how it works out, I struggled a lot with this when I started workin with oracle and ASP, but the template above is what I always use now and it works flawlessly...

All hail the INTERWEB!

 

[meenakshidhar]

well, i tried this code but not able to retrieve the records in recordset...

===========================================================
Dim objConnection
Dim objCommand
Dim objRecordset


Set objConnection = Server.CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=INDV;Persist Security Info=True"
.CursorLocation = 3
.Open
End With


'Define call to stored procedure to return all data
strSQL = "{call packperson.allperson({resultset 10000, empno, ename, job})}"

'Command Object for all data
Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection = objConnection
.CommandText = strSQL
.CommandType = 1
End With

'Setup Recordset Object
Set objRecordset = Server.CreateObject("ADODB.Recordset")
With objRecordset
.CursorType = adOpenStatic
.LockType = 1
End With

'All data was requested, so set recordset object to appropriate source
Set objRecordset.Source = objCommand
'Open the recordset
objRecordset.Open


While Not objRecordset.EOF

Response.Write objRecordset(0) & "<br>"
Response.Write objRecordset(1) & "<br>"
Response.Write objRecordset(2) & "<br>"

objRecordset.MoveNext
Wend

============================================================

but got an error at this line
objRecordset.Open ---???

error is-
"An exception of type microsoft OLEDB Provider for Oracle: 'unspecified error' was not handled"

My stored procedure--
=============================================================
CREATE OR REPLACE PACKAGE PACKPERSON
IS
PROCEDURE AllPerson;
END;

CREATE OR REPLACE PACKAGE BODY PACKPERSON IS
PROCEDURE AllPerson
IS
CURSOR c1 IS
SELECT empno, ename, job
FROM emp
ORDER BY empno;


BEGIN
FOR c IN c1 LOOP
Dbms_Output.put_line(c.empno || ' ' || c.ename);
END LOOP;
END;
END;

============================================================

don't know what's the exact problem...


Regards,
Meenakshi Dhar

 

[RhythmAddict112]

Meenakshi,
You do not need to open a recordSet. Plug your params into the code I have provided in my previous posts. Also, I assume your stored proc works without issue in SQL Plus?

All hail the INTERWEB!