Oracle procedure compiling successfully but show errors

Question

Using Oracle SQL Developer I created a simple procedure. The procedure compiles successfully, but when I type the command:

execute CMPPROJECTPROCSELECT();

BEGIN CMPPROJECTPROCSELECT(); END;

I get the following errors:

Error starting at line : 1 in command -
execute CMPPROJECTPROCSELECT()
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CMPPROJECTPROCSELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Error starting at line : 2 in command -
BEGIN CMPPROJECTPROCSELECT(); END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CMPPROJECTPROCSELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Please help me to solve this. I know it's a small error. Also I have specified the data types, declarations of names correctly.

My procedure code is

CREATE OR REPLACE PROCEDURE CMPPROJECTPROCSELECT( 
  p_projectname IN VARCHAR2,
  p_description OUT VARCHAR2)
IS
BEGIN
  SELECT DESCRIPTION
    INTO p_description
    FROM CMPPROJECT
   WHERE PROJECTNAME = p_projectname;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_description:= NULL;
    COMMIT;
END CMPPROJECTPROCSELECT;

Answer 1

execute CMPPROJECTPROCSELECT();
BEGIN CMPPROJECTPROCSELECT();
 END;

1. EXECUTE is SQL*Plus command.
2. You are not passing the required parameters to the procedure. You have declared two parameters for your procedure:

p_projectname IN VARCHAR2,
p_description OUT VARCHAR2

So, you need to declare the required parameters and then pass it to the procedure:

DECLARE
  proj_desc VARCHAR2(2000);
BEGIN
  CMPPROJECTPROCSELECT('project_name', proj_desc);
  -- use the OUT value of proj_desc later
END;
/ 

On a side note, you do not need COMMIT at all. It is required to permanently commit a DML and has nothing to do with a SELECT ..INTO clause.

SELECT DESCRIPTION INTO p_description FROM CMPPROJECT WHERE PROJECTNAME = p_projectname;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_description:= NULL;
COMMIT;  -- You don't need COMMIT at all

---

UPDATE A working demonstration:

In PL/SQL:

SQL> CREATE OR REPLACE PROCEDURE get_emp(
  2      p_ename IN VARCHAR2,
  3      p_job OUT VARCHAR2)
  4  IS
  5  BEGIN
  6    SELECT job INTO p_job FROM emp WHERE ename = p_ename;
  7  END;
  8  /

Procedure created.

SQL> sho err
No errors.
SQL> set serveroutput on
SQL> DECLARE
  2    job VARCHAR2(20);
  3  BEGIN
  4    get_emp('SCOTT',JOB);
  5    DBMS_OUTPUT.PUT_LINE('The output is '||job);
  6  END;
  7  /
The output is ANALYST

PL/SQL procedure successfully completed.

In SQL*Plus:

SQL> VARIABLE JOB VARCHAR2(20);
SQL> EXECUTE get_emp('SCOTT', :JOB);

PL/SQL procedure successfully completed.

SQL> PRINT JOB;

JOB
--------------------------------
ANALYST