SP call using ADO command object

[sravva]

I am calling a oracle stored procedure having one of column as "PL/SQL TABLE" through Visual basic code using ADO objects. I am getting wrong types passed error.
at the following code:
cmd.Parameters.Append cmd.CreateParameter("P_REPORT_CD", adVarchar, adParamInput, Len(sReportCd(0)), sReportCd(0)).

Please help me!!!

 

[fredericofonseca]

Try not to declare the parameters yourself, and let ADO do it for you. Then look at the parameters collection and see the type used.

So set them automatically use the following order.
Set adoInsertComm = New ADODB.Command
adoInsertComm.CommandType = adCmdStoredProc
adoInsertComm.CommandText = "sp_add_pais"
adoInsertComm.ActiveConnection = DBConn



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sravva]

Thanks fredericofonseca!!

When I am trying to see parameter collection, i am getting following error "Provider cannot derive parameter information and SetParameterInfo has not been called"

 

[fredericofonseca]

Oops!!.

After further investigation it seems that Oracle does not support this.

Explanation from Oracle


-----------

This bug relates to ADO .Refresh property which should be used only at the application design time and not in the production application.

The use of .Refresh property causes one or more round trips to the database per call.

Using this functionality in production applications can cause serious performance bottlenecks.

Based on these feedbacks, we had decided not to support this property.
----------

Can you supply a sample of that PS/SQL reduced to a mininum but still showing the error so that I can have a look at it?



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sravva]

Please find the code as below:

Dim cmd As ADODB.Command
Dim sAction As String, sPrintSchDate As String
Dim sWaesNo(0) As String

Set cmd = New ADODB.Command

cmd.CommandText = "rpt_core.pr_report_schedule"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = Conn

sAction = "U"
sWaesNo(0) = "123445"


cmd.Parameters.Append cmd.CreateParameter("P_DML_CODE", adVarChar, adParamInput, Len(sAction), sAction)
cmd.Parameters.Append cmd.CreateParameter("P_PRINT_SCHEDULE_DATE", adVarChar, adParamInput, Len(sPrintSchDate), sPrintSchDate)
cmd.Parameters.Append cmd.CreateParameter("P_RAI_COUNTRY_CD", adVarChar, adParamInput, Len(sCountryCd), sCountryCd)

cmd.Parameters.Append cmd.CreateParameter("P_WAES_NO", adVarchar, adParamInput,,sWaesNo(0)) - this one is defined as "PL/SQL TABLE" in the database stored procedure

cmd.Execute

I have tried couple of ways too using adArray, etc.

Thanks.

 

[fredericofonseca]

I was asking for striped version of the code of rpt_core.pr_report_schedule

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sravva]

Thanks!! Here is the snippet of the code...

TYPE wno IS TABLE OF report_schedule.wno%TYPE
INDEX BY BINARY_INTEGER;

TYPE vno IS TABLE OF report_schedule.vno%TYPE
INDEX BY BINARY_INTEGER;

TYPE repseqno IS TABLE OF report_schedule.rep_seq_no%TYPE
INDEX BY BINARY_INTEGER;

TYPE rptcode IS TABLE OF report_schedule.report_cd%TYPE
INDEX BY BINARY_INTEGER;

TYPE optattch IS TABLE OF report_schedule.optical_attachment_yn%TYPE
INDEX BY BINARY_INTEGER;



PROCEDURE pr_report_schedule (
p_dml_code IN VARCHAR2,
p_print_schedule_date IN VARCHAR2,
p_country_cd IN VARCHAR2,
p_wno IN rpt_core.wno,
p_report_cd IN rpt_core.rptcode,
p_optical_attachment_yn IN rpt_core.optattch,
p_v_no IN rpt_core.vno,
p_rep_seq_no IN rpt_core.repseqno




PROCEDURE pr_report_schedule (
p_dml_code IN VARCHAR2,
p_print_schedule_date IN VARCHAR2,
p_country_cd IN VARCHAR2,
p_wno IN rpt_core.wno,
p_report_cd IN rpt_core.rptcode,
p_optical_attachment_yn IN rpt_core.optattch,
p_v_no IN rpt_core.vno,
p_rep_seq_no IN rpt_core.repseqno
)
IS
BEGIN
** the main purpose of this procedure is to update flag column Processed_YN
END

 

[fredericofonseca]

Hum. Never worked like that.

The createparameter should use the same type as defined in field report_schedule.report_cd. Does it?

Also you mention that " the main purpose of this procedure is to update flag column Processed_YN". does this means that you receive the value on the "call procedure" and update a table with that or are you supposed to pass the value back to VB? If the last then the parameter needs to be OUT.

And for your specific problem I think you need to define it as Reference Cursor, e.g.
' cursor c1 is select ename from emp;
' type empCur is ref cursor return c1%ROWTYPE;

Adapt to your particular case.

PL/SQL Tables are normally output, but you have it defined as input. is this correct?



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sravva]

yes, PL/SQL Tables are defined as IN parameter here. There are 6 columns of these types. It is supposed to work as BULK updates for the modified columns. Passing arrays to the procedure. Is it possible doing with ADO? Are there any third party tools to do this.

Thanks.

 

[fredericofonseca]

After further reading it seems that
1- with ado only reference cursors can be used to support pl/sql tables.
2- To use the table as you intend you will need to use OO40 instead of ADO. This has full support for pl/sql tables.


If you use VB.NET then you can use ADO.NET with PL/SQL tables.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sravva]

Can I get a link to sample code for these.

Thanks for your valuable help.

 

[fredericofonseca]

You should have some supplied with your installation of Oracle.

They should also be available at

http://www.oracle.com

or forums.oracle.com. On the forums there is definitely some code. Search for PL/SQL and table



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com