Stored Procedure to return specific rows plsql

Question

Say I have a table as:

 create table product
(
  prod_code number(3),
  prod_name varchar2(20),
  prod_dept varchar2(20)
);

I want to write a procedure which takes in two parameters:

param_1 = col_name
param_2 = col_value

The procedure needs to check if the column exists and if it does then returns the row(s) with the values equal to param_2.

Example:

Say the procedure name is getproddetails and the parameters passed are like this:

execute getproddetails('prod_id', 40)

When I pass these two values then it should return the entire row with prod_id=40.

I can check weather the column exists or not but how to return the entire row(s) based on the values?

Any help would be great. I have written the following code but it doesn't work:

create or replace procedure getproductdetails(p_colname varchar2, p_value number)
is
  v_column_exists number :=0;
  cursor c1 is select * from product where p_colname = p_value;
  myrow c1%rowtype;
    BEGIN
    select count(*) into v_column_exists
    from user_tab_cols
    where column_name = p_colname and table_name = 'PRODUCT';

   if(v_column_exists > 0) then
     open c1;
      loop
        fetch c1 into myrow;
        exit when c1%notfound;
        dbms_output.put_line(myrow.prod_name || ' ' || myrow.prod_cost);
      end loop;
    else
      dbms_output.put_line('Column Not Found');
    end if;
   end getproductdetails;

Note that I am displaying only 2 columns but there are like 8 to 9 columns.

Why are you needing to do this? Is it for homework or something? This seems like an odd requirement for real world applications (although odd != impossible!) — Boneist, Jul 12 '17 at 07:28
Not exactly HW but just trying to learn. How to work with the values which can be number or varchar. If it cannot be done using both datatypes then atleast how to do it with number datatype as in the param_2 is a Number. — Karan Gupta, Jul 12 '17 at 07:31
In general, it's a bad idea to try and have a procedure that generalises the code - it makes things complicated, as now you have to deal with data conversion (what if the value is a number or a date?) as you have realised. Instead, it's far better to know what your application wants and code for specifics - e.g. have a procedure that returns the entire row for a given emp_id, and have a procedure that returns the rows when passed in the emp_name, etc. What you're after is do-able, but you have to ask yourself if it really makes sense to do it like that. — Boneist, Jul 12 '17 at 07:37
See [this article](https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/) on why generalisation is not necessarily a good thing, especially when taken to the extreme! — Boneist, Jul 12 '17 at 07:38
I do understand why it's a bad idea but just to know it can be done or not. What really is the requirement is passing the column name and the value which is going to definitive either a varchar or a number. So, how can I do it with a number? Like example 1 in the above question — Karan Gupta, Jul 12 '17 at 07:40
You'd have to check for whether the column is a number or not via user_tab_columns — Boneist, Jul 12 '17 at 07:48
OK yeah I checked that. Its done. The main problem is how to check for the particular value as in the param_2 value passed? — Karan Gupta, Jul 12 '17 at 07:51
I have pasted my code here: https://pastebin.com/nWfQRGH1 But this does not give the desired result. The cursor :`cursor c1 is select * from product where p_colname = p_value;` does not work but I need to check this condition. How? — Karan Gupta, Jul 12 '17 at 08:05
Unclear, please post code directly by editing your original post. Read [ask] — J. Chomel, Jul 12 '17 at 09:05

J. Chomel · Accepted Answer · 2017-07-12T12:03:52.010

1

It can work if you set param_2 as VARCHAR2; use implicit conversion.

Here is what I would do, using REF_CURSOR:

create or replace procedure getempdetails (
   pcol_name in varchar2
 , pcol_value in varchar2
 , pres_cursor out sys_refcursor
) is 
begin
  open pres_cursor for 'SELECT emp_id, emp_name, emp_dept FROM emp WHERE '
  ||pcol_name||'='''||pcol_value||'''';
end;
/

Edit: more elegant with the use of `:pcol_name`;

but I could not find how to make it work with the implicit conversion.

create or replace procedure getempdetails (
   pcol_name in varchar2
 , pcol_value in varchar2
 , pres_cursor out sys_refcursor
) is 
begin
   OPEN pres_cursor 
    FOR 'SELECT emp_id, emp_name, emp_dept FROM emp WHERE :pcol_name='''||pcol_value||''''
  USING pcol_name;
end;

/

Then use the cursor that way:

SQL>  insert into emp values(1, 'John', 'IT');

1 row created.

SQL> variable r refcursor;
SQL> exec  getempdetails('emp_id', '1', :r);

PL/SQL procedure successfully completed.

SQL>  print r
         1 John                 IT

SQL>

Search a little more if you want to use this cursor inside further PL/SQL. E.g. great examples like here or here.

NB: Basically, in PL/SQL, ' escapes '. So if you want a ' inside a string in PL/SQL, you write ' I want a quote there -> '' <- '

edited Jul 12 '17 at 12:03

answered Jul 12 '17 at 08:47

J. Chomel

8,193
15
41
69

You are calling the procedure with 3 parameters. Please check my code which I have posted in previous comments. – Karan Gupta Jul 12 '17 at 08:52
@KaranGupta, You have to edit your question to avoid such misunderstandings. I'll wait further notice. – J. Chomel Jul 12 '17 at 09:05
Your code works perfectly fine but please enlighten me about the "||" operator used. Thanks for the answer anyway. – Karan Gupta Jul 12 '17 at 09:32
Thanks, `||` is concatenation operation. – J. Chomel Jul 12 '17 at 09:46
Yeah I know it is concatenation but I don't understand how and why it is used here? The single quotes makes it more confusing. – Karan Gupta Jul 12 '17 at 09:48
The `||` and `''` are necessary to make the query dynamically dependent on your parameters. If you want to avoid this, then use the `:param` technique. I edited for a more elegant solution. – J. Chomel Jul 12 '17 at 11:43
Yeah that's ok. Could you please tell me where I can read about this "dynamically dependent query". I will be able to get a better understanding then. – Karan Gupta Jul 12 '17 at 11:53
You should look examples of the use of `EXECUTE IMMEDIATE`. You'll learn how to build queries that are only checked at execution time, so be careful with them, it require good exception handling. – J. Chomel Jul 12 '17 at 12:00
That's great, thanks a lot. I know where to look now. – Karan Gupta Jul 12 '17 at 12:01

score 0 · Answer 2 · answered Jul 12 '17 at 07:40

0

how to return the entire row(s) based on the values?

You can create a out parameter and it can be of the type of the table, like:

Procedure gettemplates( columnname in varchar2, value in varchar2, entirerowreturn out yourtable%rowtype) is ...

answered Jul 12 '17 at 07:40

Leandro Franciscato

26
5

That wouldn't help if there were multiple rows to be returned. Instead, I'd go with a ref cursor. – Boneist Jul 12 '17 at 07:48

Stored Procedure to return specific rows plsql

2 Answers2

Edit: more elegant with the use of :pcol_name;

Edit: more elegant with the use of `:pcol_name`;