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.
bhavanisharansinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if that is the error message, you will need to either find a provider alternative.
you might want to try the Provider=Oracle.Oledb  instead (might need to get installed first)...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.