Creating procedure with dynamic SQL

Question

Here are the instructions:

A company wants to allow customers to do product search by selecting a product name or description, and then typing a search term. Using native dynamic SQL, create a procedure name SEARCH_SP that returns a product name, description, and price base on users’ search criteria. The procedure needs handle multiple rows being returned.

Here is the code I have so far.

CREATE OR REPLACE PROCEDURE search_sp (product_name IN VARCHAR2,
                                        description IN VARCHAR2,
                                        price_based IN NUMBER             
                                        )
AS

BEGIN
    SELECT customer.product.name, customer.description, customer.price
    FROM dbo.customer
    WHERE customer.description = @SEARCH.customer.product.name = @SEARCH
END;
/

EXECUTE IMMEDIATE plsql_block
    USING IN OUT new_product_name, new_description, new_price_based;
    
END;
/

I'm getting compilation errors and more. Any help or suggestions will be greatly appreciated.

Answer 1

Dynamic SQL? What for? To make your life more miserable than it should be? What's wrong with a straight SQL?

I'd suggest a function. Why? You're supposed to return the result. If it is a procedure, it has to have an OUT parameter. If that's so, then it's a function.

Here's how I'd do it; see how it works, use it (and improve) if you want. I don't think I'll get involved into anything dynamic here, sorry.

---

As I don't have your tables, I'll use Scott's sample schema. Here's data I'm interested in:

SQL> select d.dname, e.ename, e.job, e.sal
  2    from dept d join emp e on e.deptno = d.deptno
  3    order by d.dname, e.job, e.sal;

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     MILLER     CLERK           1300
ACCOUNTING     CLARK      MANAGER         2450
ACCOUNTING     KING       PRESIDENT       5000
RESEARCH       SCOTT      ANALYST         3000
RESEARCH       FORD       ANALYST         3000
RESEARCH       SMITH      CLERK            800
RESEARCH       ADAMS      CLERK           1100
RESEARCH       JONES      MANAGER         2975
SALES          JAMES      CLERK            950
SALES          BLAKE      MANAGER         2850
SALES          MARTIN     SALESMAN        1250   --> for testing, I'll fetch SALESMEN
SALES          WARD       SALESMAN        1250   --> who earn 1250 USD (it's probably USD)
SALES          TURNER     SALESMAN        1500
SALES          ALLEN      SALESMAN        1600

14 rows selected.

Code that searches through it looks like this:

SQL> create or replace function search_sp
  2    (par_dname in varchar2,
  3     par_job   in varchar2,
  4     par_sal   in number
  5    )
  6    return sys_refcursor
  7  is
  8    rc sys_refcursor;
  9  begin
 10    open rc for
 11      select d.dname, e.ename, e.job, e.sal
 12        from dept d join emp e on e.deptno = d.deptno
 13        where (d.dname = par_dname or par_dname is null)
 14          and (e.job   = par_job   or par_job   is null)
 15          and (e.sal   = par_sal   or par_sal   is null);
 16    return rc;
 17  end;
 18  /

Function created.

Let's test it:

SQL> select search_sp(null, 'SALESMAN', 1250) from dual;

SEARCH_SP(NULL,'SALE
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
SALES          WARD       SALESMAN        1250
SALES          MARTIN     SALESMAN        1250


SQL>

Looks OK to me.