Best object for returning a dataset

dorianpc

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?

Karthick2003

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

Performing SQL Operations from PL/SQL

Unknown

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

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.

Unknown

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

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;

Karthick2003

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

Performing SQL Operations from PL/SQL

dorianpc

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.

Unknown

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

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.