ADO & Oracle- please help me......

Results 1 to 3 of 3

Thread: ADO & Oracle- please help me......

  1. #1
    Ashish Agnihotri Guest

    Default ADO & Oracle- please help me......

    Hi everybody :<BR><BR>I am doing a project which is on MS platform (VB/ASP/Site Server) with Oracle as database.<BR><BR>My stored procedures ( which are encapsulated in a package) are called from VB COM object. I am having problem with those stored proecedures which return multiple record sets.<BR><BR>I am also having problem with testing PL/SQL block.<BR>I am pasting simple code... please help me.....<BR><BR>create or replace package netshop as<BR> type coy_id1t is table of Number(9)<BR> index by binary_integer;<BR> procedure nsCoy_GetCoyIDfrCode<BR> (coy_code2 IN varchar2,<BR> coy_id out coy_id1t);<BR>end netshop;<BR>/<BR><BR>package created.<BR><BR>create or replace package body netshop<BR> as<BR> PROCEDURE nsCoy_GetCoyIDfrCode<BR>(<BR> coy_code2 IN varchar2,<BR> coy_id OUT coy_id1t)<BR> is<BR> CURSOR MYCUR IS<BR> SELECT coy_id<BR> FROM ns_coy<BR> WHERE coy_code=coy_code2 AND<BR> del_ind=&#039N&#039;<BR> percount number default 1;<BR> BEGIN<BR> FOR nscoy IN MYCUR<BR> LOOP<BR> COY_ID(percount):=nscoy.COY_ID;<BR> percount:=percount+1;<BR> END LOOP;<BR> END;<BR>END NETSHOP;<BR>/<BR><BR>package body created.<BR><BR>NOW I TRY AND RUN THIS PROCEDURE WITH INPUT PARAMETER AS &#039HUBACCT&#039... AND IT GIVES FOLLOWING ERROR :<BR><BR> declare<BR>type coy_id1t is table of Number(9)<BR> index by binary_integer;<BR>coy_id coy_id1t;<BR>begin<BR>netshop.nscoy_getcoyidfrcode (&#039HUBACCT&#039,netshop.coy_id);<BR>END;<BR>/<BR><BR>ERROR :<BR>at line 1:<BR>ORA-6550: line 6, column 48:<BR>PLS-0302: component &#039COY_ID&#039 must be declared<BR>ORA-6550: line 6, column 1:<BR>PL/SQL: Statement ignored<BR><BR>many thanks<BR>ashish<BR><BR><BR>

  2. #2
    avinash pendse Guest

    Default RE: ADO & Oracle- please help me......

    When multiple recordsets are generated, you need to fetch one recordset at a time until no more recordsets are available. The NextRecordset method of the Recordset object allows you to fetch subsequent recordsets. If no more recordsets are available, the returned Recordset object is set to Nothing. Generally, you write code that tests whether a Recordset object is set to Nothing as the test condition for exiting the “multiple recordset” loop.<BR><BR>The example shows how to fetch multiple recordsets from a stored procedure using the NextRecordset method of the Recordset object. <BR><BR>The stored procedure syntax is:<BR><BR>DROP PROC myNextproc<BR><BR>GO<BR><BR>CREATE PROC myNextproc AS<BR><BR>SELECT * FROM titles<BR><BR>SELECT * FROM publishers<BR><BR>GO<BR><BR> <BR><BR>The stored procedure generates two result sets: one for the result of SELECT * FROM titles and the other for the result of SELECT * FROM publishers.<BR><BR>The ADO code syntax is:<BR><BR>Dim cmd As New ADODB.Command<BR><BR>Dim rs As ADODB.Recordset <BR><BR> <BR><BR>cn.Provider = "sqloledb"<BR><BR>cn.Properties("Data Source") = "MyServerName"<BR><BR>cn.Properties("Initial Catalog") = "pubs"<BR><BR>cn.Properties("user ID") = "sa"<BR><BR>cn.Properties("password") = ""<BR><BR>cn.Open<BR><BR> <BR><BR>Cmd.CommandText = "myNextProc"<BR><BR>Cmd.CommandType = adCmdStoredProc<BR><BR> <BR><BR>Set rs = Cmd.Execute<BR><BR>While Not rs Is Nothing<BR><BR> If (Not rs.EOF) Then<BR><BR> Debug.Print rs(0)<BR><BR> End If<BR><BR> Set rs = rs.NextRecordset<BR><BR>Wend<BR><BR> <BR><BR>After the myNextProc stored procedure is executed, a Recordset object is created. Because there are two result sets generated by the myNextProc stored procedure, each Recordset object can be retrieved by using the NextRecordset method. The Recordset object rs is simply reused for each recordset.<BR><BR>Note : This is a extract from MSDN . Hope it helps you.

  3. #3
    ashish agnihotri Guest

    Default RE: ADO & Oracle- please help me......

    Hi !<BR><BR>thanks for the effort.... but my database is Oracle8 and also we are having stored procedures wrapped in packages..<BR><BR>can you help on this ....<BR><BR>thanks again<BR>ashish<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts