Returning procedure recordset to Access/Excel
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Returning procedure recordset to Access/Excel

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    Returning procedure recordset to Access/Excel

    I have recently been called in on a project to create a database abstraction layer. Moving all the SQL into packages for the applciation was easy, but now I've come to their hodge-podge of reporting methods. This includes Excel and Access. What I would like to do is move this SQL into packages as well. The problem is how to make these products recognize the resultset returned from a stored procedure without using code. I don't think it can be done, but I've been wrong once or twice before .

    So, is there any way in, say, an Access Query (using only the front end - not in code) to create a pass-through query that calls a procedure with a Ref Cursor and have it somehow be recognized as returning a recordset?

    This *used* to be possible using Microsoft's ODBC driver for Oracle and a bunch of array parameters, etc. But that was de-supported by MS back in 8i. (and was ugly-as-sin to boot) I need similar magic, but in 9i.

    Thanks

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    An object is a user defined type in 8i/9i/10g.
    There are 2 types:
    1 Object types - Analogous to a record or structure in a language such as PL/SQL or C, or a class in an object-oriented language such as C++ or Java. Consists of one or more attributes and a set of methods used to manipulate those attributes.

    2 Collections - Analogous to arrays, sets, bags, etc., from other languages such as C/C++, Java, or Smalltalk. Collections contain zero or more elements of a given datatype.

    Code:
    SQL>desc emp
     Name                             Null?    Type
     -------------------------------- -------- ----------------
     EMPLID                                    NUMBER
     NAME                                      VARCHAR2(30)
     DEPTID                                    NUMBER
    -- create user defined object type
    create or replace type EmpRecordType as object
       ( emplid number,      name   varchar2(30) ,      deptid number 
       )
    /
    -- create object table 
    create or replace type EmpTableType as table of EmpRecordType;
    /
    -- create a function
    create or replace function emp_function return EmpTableType
      as  I int := 1;
          emp_data empTableType := empTabletype();
      begin
          for crec in ( select emplid, name, deptid from emp) loop
              emp_data.extend;
              emp_data(I) := EmpRecordType( crec.emplid, 
                                                           crec.name, 
                                                           crec.deptid ) ; 
              I := I+1;
          end loop;
          return Emp_data;
      end;
    /
    
    select emplid, name, deptid 
      from TABLE ( cast( Emp_function() as EmpTableType ) )
    /
       EMPLID NAME                               DEPTID
    --------- ------------------           ----------
         1001 TAMIL                                  10
         1002 VEERA                                 20
         1003 RAJIV
    The CAST instructs oracle to treat the return set of rows from the PL/SQL function (Emp_function() ) as a collection type.

    Is that are you looking for?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Quote Originally Posted by tamilselvan
    Is that are you looking for?
    mmm....no

    I am looking at a standard stored procedure, say:
    Code:
    PROCEDURE GetEmp
       (
       inout_RefCur      IN   OUT   SYS_REFCURSOR
       )
    IS
    BEGIN
    
       OPEN
          inout_RefCur
       FOR
          SELECT
             EMPLID ,
             NAME   ,
             DEPTID
          FROM
             EMP   ;
    
    END GetEmp;
    What I want to know is how I can create a query in MS Access (through the front-end) that uses this proc and returns the resultset?

    Thanks,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You might want to research MS-Access pass-through query feature.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Quote Originally Posted by PAVB
    You might want to research MS-Access pass-through query feature.
    Yeah, I can use pass-throughs for DML procs with no problem. The problem is with returning a resultset. I need to convert Oracle's goofy Ref Cursor into something MS accepts as a resultset.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Mar 2006
    Posts
    74
    we've never found a way to do it, 5 years into use of access as a frontend to oracle, we still use VBA ADODB code to do it (I think.. not my dept)

    if youre reporting to excel, you can have someone who is .NET capable make a dll that will quite simply turn a stored procedure call into an Excel file..

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I havent realy used this but looks like it can do what you are asking for

    http://www.orafaq.com/faqoo4o.htm

    regards
    Hrishy

Posting Permissions

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


Click Here to Expand Forum to Full Width