Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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.
0
bhavanisharansingh
Asked:
bhavanisharansingh
1 Solution
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now