Solved

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

Posted on 2008-10-17
1
383 Views
Last Modified: 2009-02-06
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.
0
Comment
Question by:bhavanisharansingh
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now