passing ADO recordset to Oracle stored procedure

• • Reply

  17 years ago

  by learning_4_ever

  learning_4_ever

  New York, U S A, United States

  Joined 17 years ago

  Hi,
      Can we pass a recordset from a VB application to a Stored Procedure
  written in Oralce. If any one has any idea about this please respond.
  I am using ADO 2.7, VB6 and Oracle 9i.
  

  
  Thank you in advance
  Mike
  

  
  

  Report abuse
• • Reply

  17 years ago

  by bevardj

  bevardj

  , United States

  Joined 17 years ago

  Not to my knowledge however, you can pass a delimited string and use a procedure to convert it into a cursor.
  Here is the code to create the package. This will return the cursor which you can loop through inside of your stored procedure.
  

  
  Hope this helps!
  

  
  

  
  

  
  CREATE OR REPLACE PACKAGE PkgStringparse AS
  TYPE varchar2table IS TABLE OF VARCHAR2(32767) INDEX BY BINARYINTEGER;
  PROCEDURE SPDELIMITER2TABLE
    ( strInput IN  VARCHAR2
    , tblOutput       OUT varchar2table
    , intTableSize     OUT INTEGER
    , strDelimiter       IN  VARCHAR2 DEFAULT ','
    );
  END PkgStringparse;
  /
  

  
  CREATE OR REPLACE PACKAGE BODY PkgStringparse AS
  PROCEDURE SPDELIMITER2TABLE
    ( strInput IN  VARCHAR2
    , tblOutput       OUT varchar2table
    , intTableSize     OUT INTEGER
    , strDelimiter       IN  VARCHAR2 DEFAULT ','
    )
  IS
    strProcess   VARCHAR2(32767) := strInput;
    intRcTableSize  PLSINTEGER := 1;
    tblRcOutput    varchar2table;
    intDelimPos PLSINTEGER := INSTR(strInput, strDelimiter);
    intDelimLen PLSINTEGER := LENGTH(strDelimiter);
  BEGIN
  NULL;
    WHILE intDelimPos > 0
    LOOP
      tblRcOutput(intRcTableSize) := SUBSTR(strProcess,1,intDelimPos-1);
      strProcess := SUBSTR(strProcess,intDelimPos+intDelimLen);
      intRcTableSize := intRcTableSize+1;
      intDelimPos := INSTR(strProcess, strDelimiter);
    END LOOP;
    tblRcOutput(intRcTableSize) := strProcess;
    tblOutput := tblRcOutput;
    intTableSize := intRcTableSize;
  END SPDELIMITER2TABLE;
  END Pkg_Stringparse;
  /
  

  Report abuse