javakid1993 javakid1993 - 1 month ago 5
SQL Question

Sorting an Oracle table inside a stored procedure

I have a stored procedure which I am running from a .sql file which takes in inputs from the users and runs the procedure. The procedure runs many queries and inserts a row of values into the table. In the .sql file I have I would like to order the table by a certain column after I finish running the queries. Currently I have:

DECLARE
NAMEPARAM VARCHAR2(200);
VERSIONPARAM VARCHAR2(200);
STARTDATE DATE;
ENDDATE DATE;

BEGIN
NAMEPARAM := '&1';
VERSIONPARAM := '&2';
STARTDATE := '&3';
ENDDATE := '&4';

PROCEDURE(NAMEPARAM, VERSIONPARAM, STARTDATE, ENDDATE);
COMMIT;

Select * from TABLE_NAME
ORDER BY COLUMN_NAME ASC;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101,SQLERRM);
END;
/


However It throws the error:


PLS-00428: an INTO clause is expected in this SELECT statement


I dont know why it needs an into clause. Help?

Edit:
To clarify I don't want to see an output of a sorted table I would like the table itself to be updated and sorted by the column in ascending order.

Answer

If you put a SELECT statement in a PL/SQL block, you need to do something with the results. If you're expecting exactly one row, you'd do a SELECT INTO a local variable. Assuming that you are expecting multiple rows, you could write a FOR loop that iterates over the rows or you could BULK COLLECT the rows into a PL/SQL collection. You could also open a SYS_REFCURSOR but since there is no way to return that from an anonymous PL/SQL block, that seems unlikely to be what you want.

My guess is that you want the SELECT statement to be outside the PL/SQL block and you want whatever tool you are using to execute the .sql script to run the PL/SQL block to populate the table and then run the SELECT statement, writing the results to whatever file/ console you are spooling output to.

Comments