bhavanisharansingh
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 .connectio n")
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_sear ch_acronym (?,?,?,{re sultset 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.
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
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
rs.CursorType = 3
'Calling the stored procedure using the package name
Cmd.CommandText = "{call starbox.pk_acronym.pr_sear
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.