Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor

Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor

---

Question:  In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.

I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.

Example:

parameter_1= "Chianti" parameter_2= "10" parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?

 

The output of my stored procedure must be a cursor.

Answer:  To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.

Let's take a look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

create table wine ( col1 varchar2(40),   col2 varchar2(40),   col3 varchar2(40) );

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an output parameter in our stored procedure.

create or replace PACKAGE winepkg IS    /* Define the REF CURSOR type. */    TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE; END winepkg;

/* the common method is like this */

create or replace PACKAGE wine_pkg

  AS 

       TYPE IBS_CURSOR IS REF CURSOR; 

END wine_pkg; 

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).

create or replace procedure find_wine2   (

col1_in in varchar2,    

col2_in in varchar2,    

col3_in in varchar2,   

 c1 out winepkg.wine_type

) 

as



BEGIN



   /* all columns were entered */    IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col1 = col1_in       and  wine.col2 = col2_in       and  wine.col3 = col3_in;



   /* col1 and col2 were entered */    ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col1 = col1_in       and  wine.col2 = col2_in;



   /* col1 and col3 were entered */    ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col1 = col1_in       and  wine.col3 = col3_in;



   /* col2 and col3 where entered */    ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col2 = col2_in       and  wine.col3 = col3_in;



   /* col1 was entered */    ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col1 = col1_in;



   /* col2 was entered */    ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col2 = col2_in;



   /* col3 was entered */    ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)    THEN       OPEN c1 FOR       select *       from wine       where wine.col3 = col3_in;



   END IF; END find_wine2;




protected void Button1_Click(object sender, EventArgs e)
{
string connString = "Data Source=joe;User ID=scott;Password=tiger;";
DataSet ds = new DataSet();
OracleConnection conn = new OracleConnection(connString);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
string c1 = "Oracle";
string c2 = "SQL";
string c3 = "scott";
cmd.CommandText = "find_wine2";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("col1_in", OracleType.NVarChar)).Direction = ParameterDirection.Input;
cmd.Parameters.Add(new OracleParameter("col2_in", OracleType.NVarChar)).Direction = ParameterDirection.Input;
cmd.Parameters.Add(new OracleParameter("col3_in", OracleType.NVarChar)).Direction = ParameterDirection.Input;
cmd.Parameters.Add(new OracleParameter("c1", OracleType.Cursor)).Direction = ParameterDirection.Output;
cmd.Parameters[0].Value = c1;
cmd.Parameters[1].Value = c2;
cmd.Parameters[2].Value = c3;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "wine");
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}