CODEWITHSUNDEEP
plsqloracle11g
Thiyagu ATR
Thiyagu ATR

Reputation: 2264

How to print value from pl/sql cursor?

I've tried the following code for printing each or some detail based on the value I get from the cursor but I can't, can anybody help me?

set serveroutput on

declare
d_id number;
temp_tab VARRAY;
cursor c1 is select * from(select d.department_ID,            `enter code here`d.department_name,count(d.department_name) cnt from employees e,departments d where e.department_id=d.department_id group by d.department_ID, d.department_name) where cnt>=5;

begin
open c1;

for i in c1
loop
    select e.first_name,d.department_id,d.department_name into temp_tab from employees e,departments d where d.department_id=i.department_ID;
end loop;

close c1;
end;

Upvotes: 1

Views: 42148

Answers (2)

user2001117
user2001117

Reputation: 3777

Yes.If you want to print ant element value.Then select the element value into the variable.

Then use below one to print the element value :

dbms_output.put_line('Variable value= ' ||  Variable_name );

In your code you are using the collection varaiable and fetching value into collection. Then to print objects elements:

dbms_output.put_line('First Name :'||object_name.first_name);

If you are using the record variable in PLSQL.Then use the below method:then declare the record type variable then fetch into that variable then display variable.column,

DECLARE
  TYPE t_name IS RECORD(
     first_name employees.first_name%TYPE,
     last_name  employees.last_name%TYPE
  );
  r_name   t_name; -- name record
  n_emp_id employees.employee_id%TYPE := 200;
BEGIN
  SELECT first_name,
         last_name
  INTO r_name
  FROM employees
  WHERE employee_id = n_emp_id;
  -- print out the employee's name
  DBMS_OUTPUT.PUT_LINE(r_name.first_name || ',' || r_name.last_name );
END;

Upvotes: 3

Canburak Tümer
Canburak Tümer

Reputation: 1063

Create an Oracle Object with your fields (e.first_name,d.department_id,d.department_name) here is the link : http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjint.htm

Then select your query into this object. Lets say object name is my_object. Then print objects elements.

dbms_output.put_line('First Name :'||my_object.first_name||' Department: '||my_object.department_id||' Department Name: ' || my_object.department_name);

This should do what you ask for if I understand your question.

Upvotes: 0

Related Questions