Forum Stats

  • 3,784,126 Users
  • 2,254,894 Discussions
  • 7,880,699 Comments

Discussions

Best object for returning a dataset

dorianpc
dorianpc Member Posts: 10
edited May 6, 2014 4:03AM in SQL & PL/SQL


Guys, I use a select statement very often that takes 2 text parameters. What is the most efficient oracle object I can use that will return a dataset taking 2 text parameters?

dorianpc

Best Answer

Answers

  • Guys, I use a select statement very often that takes 2 text parameters. What is the most efficient oracle object I can use that will return a dataset taking 2 text parameters?

    Return to 'who'?

    Why do you need an 'object'?

    Why can't you just return a result set?

    Give us an example of what you are trying to do.

  • dorianpc
    dorianpc Member Posts: 10

    sorry I am a newbie...i have a habit of calling everything in oracle a object 

    I do want to return a dataset from a query like this....select * from tableA where columnA like '%parameter1%' and columnB = 'parameter2'

    I run this query frequently and I wanted to build a function or something that takes those 2 parameters and would be effective.

  • Again - return to 'who'?

    You don't need a function. Just execute the query in sql plus and use substitution variables:

    SQL> set serveroutput on
    SQL> select * from emp where deptno = &a;
    Enter value for a: 20
    old   1: select * from emp where deptno = &a
    new   1: select * from emp where deptno = 20
    

    Put that query above in a file on your machine and then just execute the file in sql*plus

    SQL>@myFile
    

    Or use Oracle's FREE Sql Developer

    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html?ssSourceSiteId=otnpt

    You can easily create a custom 'report' that is just that query and it will prompt you for values when you execute it.


  • kendenny
    kendenny Member Posts: 1,269

    What's going to call this procedure? If it's a front end program in .NET or something similar, you can return the dataset in the form of a ref cursor. The procedure will have two input parameters and one output parameter. The output parameter will be defined as sys_refcursor

    CREATE OR REPLACE PROCEDURE get_t_data(param1  in varchar2

        ,param2  in varchar2

        ,results  out sys_refcursor)

    IS

    BEGIN

      OPEN results FOR

        SELECT * FROM tableA

         where columnA like '%'||param1||'%'

           and columnB = param2;

    END;


    dorianpc
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Accepted Answer

    The best tool to use to return result set to client from oracle is ref cursor.

    Performing SQL Operations from PL/SQL

    dorianpc
  • dorianpc
    dorianpc Member Posts: 10

    The only client I am using at this moment is SQL Developer and sometimes Toad (for some data analysis).  I just wanted to shorthand the select statement, since I call it a lot during my day.  So far, creating and using a procedure is the best route for me.

  • The only client I am using at this moment is SQL Developer and sometimes Toad (for some data analysis).  I just wanted to shorthand the select statement, since I call it a lot during my day.  So far, creating and using a procedure is the best route for me. 

    No - it isn't. Use a custom report in sql developer as I mentioned above:

    You can easily create a custom 'report' that is just that query and it will prompt you for values when you execute it.
    

    See 'Oracle SQL Developers Best Kept Secret: Reports' by Jeff Smith - Sql Dev PM

    http://www.thatjeffsmith.com/archive/2014/04/oracle-sql-developers-best-kept-secret-reports/

    The feature that everyone knows about yet does not use? That would be the reports.  
    
    I say that everyone knows about them because we throw them in your face..at least in terms of the user interface. It’s feature prominently next to where you see your database connections and objects.
    

    A simple report can do EVERYTHING you said you wanted to do and more.

    And you don't need to create an DB objects to use it on ANY database you have.

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    dorianpc wrote:
    
    The only client I am using at this moment is SQL Developer and sometimes Toad (for some data analysis).  I just wanted to shorthand the select statement, since I call it a lot during my day.  So far, creating and using a procedure is the best route for me.
    
    


    Did you consider creating a VIEW?

    create or replace view my_view
    as
    <your select statement>;
    

    Then on you can just do

    select * from my_view;

    I think that's the simplest thing to do.

This discussion has been closed.