How I can improve my procedure to work successfully in a backend?

Question

I am trying to make a dynamic sql procedure for the backend in my courser project. I want to make sort by a specific column.

CREATE OR REPLACE PROCEDURE sort_by_column(p_column_name VARCHAR2, p_parameter VARCHAR2) IS
    v_stmt VARCHAR2(1000);
BEGIN
    v_stmt := '
               SELECT * 
               FROM CARS  
               ORDER BY ' || p_column_name || ' ' || p_parameter;
    EXECUTE IMMEDIATE v_stmt;
END;

And I am calling it like this:

EXECUTE sort_by_column('model', 'ASC');

It compiles successfully. But not shows anything. How I can improve my procedure so in backend can handle the data?

score 2 · Accepted Answer · answered May 01 '21 at 02:17

You need to change your procedure to return a cursor. Then when you call it you need to fetch from the cursor and display the results. For instance:

CREATE OR REPLACE FUNCTION sort_by_column(p_column_name VARCHAR2,
                                          p_parameter VARCHAR2)
  RETURN SYS_REFCURSOR
IS
    v_stmt  VARCHAR2(1000);
    csr     SYS_REFCURSOR;
BEGIN
    v_stmt := 'SELECT * 
               FROM CARS  
               ORDER BY ' || p_column_name || ' ' || p_parameter;
    OPEN csr FOR v_stmt;
    RETURN csr;
END;

CREATE OR REPLACE PROCEDURE fetch_and_print(p_column_name VARCHAR2,
                                            p_parameter VARCHAR2)
IS
  csr      SYS_REFCURSOR;
  rowCars  CARS%ROWTYPE;
BEGIN
  csr := sort_by_column(p_column_name, p_parameter);

  LOOP
    FETCH csr INTO rowCars;
    EXIT WHEN csr%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('MFG=' || rowCars.MFG ||
                         '  MODEL=' || rowCars.MODEL ||
                         '  COLOR=' || rowCars.COLOR);
  END LOOP;

  CLOSE csr;
END fetch_and_print;

EXECUTE fetch_and_print('model', 'ASC');

The DBMS_OUTPUT.PUT_LINE in fetch_and_print is just an example since I have no idea what fields are in your CARS table - you'll have to change it to print whatever fields are in your table.

How I can improve my procedure to work successfully in a backend?

1 Answers1