Running PL/SQL Code Using SQL Developer

I have seen a few questions about running PL/SQL using SQL Developer and what to do when encountering the PL/SQL error message "wrong number or types of arguments in call to '||' "

Here is some code for you. Assume I am using the HR schema and the EMPLOYEES table in Oracle 9i or 10g or Express Edition. In this instance we'll create a Package, but the principle is the same for functions and procedures. I'll give you some code to play with. (We have this same example in one of the JDeveloper tutorials on OTN if you are a JDeveloper user.)

Using SQL Developer 1.0:

Step1. Create a new Object TYPE. Here is the code:

create or replace type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);

You can copy and paste the code into the SQL Worksheet or use the dialog for Types, in the Connections Navigator.

Step 2. Create the package spec. Here is the code:

create or replace package emp_fetcher as
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec;
END;

Step 3. Create the package body. Here is the code:

CREATE OR REPLACE PACKAGE BODY emp_fetcher AS
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec IS
emp_found employees % rowtype;
emp_rtn emp_rec;
BEGIN
SELECT *
INTO emp_found
FROM employees
WHERE employees.employee_id = emp_no;
emp_rtn := emp_rec(emp_found.employee_id,
emp_found.last_name,
emp_found.job_id,
emp_found.manager_id,
emp_found.hire_date,
emp_found.salary,
emp_found.commission_pct,
emp_found.department_id);
RETURN emp_rtn;
END;
END;

Step4.
Now we are ready!
You can either select the package spec in the Navigator and use the context menu to run it.


or you can switch to the PL/SQL editor and run the Package from there.


Step 5. Now this is where we are headed. Oracle SQL Developer (and JDeveloper, if you are interested) creates an anonymous block for you. It provides a place to add "IN" parameters and the DBMS_OUTPUT for "OUT" parameters.

Here is the window before you add the parameters:


You can see the EMP_NO is a NULL. Update this with the employee number as required.
Note the DBMS_OUTPUT statement is commented out. Remove the comments and specify which of the values in the record you are interested in. The example I have given is "LAST_NAME", but if you refer to the record type we created, "SALARY" would also do, as would "hire_date" .

This is where you'd run into the error specified above. If you uncomment the DBMS_OUTPUT command, you must then pass the correct value.

Have fun with your PL/SQL.