Link to home
Start Free TrialLog in
Avatar of bhavanisharansingh
bhavanisharansinghFlag for India

asked on

Provider does not support PL/SQL stored procedures/functions with RECORD or TABLE arguments.

Hi,
I have 1 ASP page that searches some records in Database, by calling a stored procedure.
Stored procedure returns a PLSQL table.
Stored proc is as follows
Procedure pr_search_acronym (p_i_acronym varchar2, p_i_category integer DEFAULT NULL, p_i_user varchar2, p_i_result OUT acronym_record1 )
IS
acronym_start_alphabet char(1);
acronym_table_name char(15);
acronym_result acronym_record;
sql_str varchar2(1000);
BEGIN
       /* Finding the table name/
         acronym_start_alphabet := SUBSTR(p_i_acronym,1,1);
         acronym_table_name := 'TBLA_ACRONYM_' || acronym_start_alphabet;
      
       sql_str := 'SELECT * FROM ' || acronym_table_name
                          || ' WHERE ACRONYM LIKE :1 ' ;
                    
       OPEN acronym_result FOR sql_str USING p_i_acronym;
       FETCH acronym_result BULK COLLECT INTO p_i_result;
END pr_search_acronym;


ASP Code is as follows
<!-- #include file="adovbs.inc" -->
<%
Dim sql
acronym = "MP"
set conn = server.CreateObject("adodb.connection")                  
conn.Open ("Provider=msdaora;User ID=starbox;password =1234;Data source=rhine9i;PLSQLRSet=1")
set cmd = server.CreateObject ("ADODB.Command")
Dim rs
Set Cmd.ActiveConnection = conn
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 3                     
'Calling the stored procedure using the package name
Cmd.CommandText = "{call starbox.pk_acronym.pr_search_acronym(?,?,?,{resultset 100, p_i_result})}"
Cmd.CommandType = 1
Cmd.Parameters.Append Cmd.CreateParameter ("p_i_acronym",adVarChar, adParamInput,15)
Cmd("p_i_acronym") = acronym
Cmd.Parameters.Append Cmd.CreateParameter ("p_i_category",adInteger, adParamInput)
Cmd("p_i_category") = 1
Cmd.Parameters.Append Cmd.CreateParameter ("p_i_user",adVarChar, adParamInput,30)
Cmd("p_i_user") = "ankit_upadhyay"
' Executing the stored procedure
Set rs.Source = Cmd                  
rs.open
%>


But on exceution of ASP page, i am getting following error.
Microsoft OLE DB Provider for Oracle (0x80004005)
Provider does not support PL/SQL stored procedures/functions with RECORD or TABLE arguments.

Please help.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial