what say the documentation about parameters ? https://docs.oracle.com/database/121/LNPLS/formal_parameter.htm#GUID-5BA8E033-96B9-439A-A4FC-4844FEC14AD8__CJAJDGCC
First: try create procedure data ( nm in varchar2(20))
Second: a procedure or function or package is pl/sql.
You need a BEGIN / END block and you need a construct where you can hold result of your select.
Something like this
create procedure data ( nm in varchar2(20)) as declare result_construct work%rowtype; begin select * into result_construct from work where name = nm; end; /
This may work, i can't test here at home.
But it will only work if name is unique and you get only 1 row as the result.
With 0 or 2 or more rows you get an exception. Better you use a cursor for such a select.
What do you want to achieve with this procedure ?
As KayK mentioned, your example you're testing is full of errors but first a few notes
- Avoid using Oracle keywords as names of your code (i.e. procedures/functions/packages/tables/views, etc...)
- The slash (aka /) is needed after your code when in the SQL* Plus command line to compile the procedure.
- The following errors in your code are:
- The syntax for parameter definition in a procedure/function is "parameter name, parameter mode [optional but by default is IN], parameter data type]" You have the parameter mode first
- Then the minimum is a BEGIN followed by code, then END. You do not have a BEGIN
- What KayK put is also incorrect in that you only use the DECLARE if you are writing an "un-named procedure" (aka anonymous PL/SQL block)
- Your SELECT statement needs to "capture" the result set correctly. As KayK mentioned, you need the "INTO" clause and code to capture 1 row or if more than 1 an array or an exception handler.
with a real database at hand my example has to look like this:
create or replace procedure prc_data ( nm in varchar2 ) as result_construct emp%rowtype; begin select * into result_construct from emp where ename = nm; end; / Procedure created. < scott:op57@unxsy078 > show err No errors.
If you do not put the Slash (/), you will not leave
create or replace PROCEDURE test_choc (p_ename in varchar2) IS
CURSOR showx is
FOR rec IN showx LOOP
Copy and paste this code, on line 12 to exit, place /
I already told the OP about the slash.