PL/SQL, DBMS_SQL can not display data

Question

I'm new in DBMS_SQL. I'm trying to fetch 1st row from cursor, but nothing is displayed. What I did wrong? Here's a code:

CREATE OR REPLACE PROCEDURE TEST_SQL 
IS 
     id_var             NUMBER; 
     name_var           VARCHAR2(30); 
     sal_var      Number; 
     source_cursor      INTEGER; 
     countp             INTEGER; 
  BEGIN 


     source_cursor := dbms_sql.open_cursor; 
     DBMS_SQL.PARSE(source_cursor, 
         'SELECT employee_id, first_name, salary FROM employees where department_id = 50', 
          DBMS_SQL.NATIVE); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, sal_var); 
     countp := DBMS_SQL.EXECUTE(source_cursor); 


  DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 3, sal_var); 

 dbms_output.put_line('id_var: '||id_var ||', name_var: '||name_var ||', sal_var: '|| sal_var);


  END; 
/

And output is:

PROCEDURE TEST_SQL compiled
anonymous block completed
id_var: , name_var: , sal_var:

EDIT: school mistake - forgot about Fetch_rows -.- Everything works!

Updated answer. Output is as above. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit — Michał M
– Michał M, Commented Aug 5, 2016 at 9:32
Looks strange bt a silly question , do you really have any record for department_id = 50 in your table. Also, y are you using oracle defined functions for displaying when you can easily do it using a simple cursors. — XING
– XING, Commented Aug 5, 2016 at 9:34
Even if you would have printed your counter, it would had shown you that cursor is fetching no records.. — XING
– XING, Commented Aug 5, 2016 at 9:43

Alex Poole · Accepted Answer · 2016-08-05 09:43:36Z

1

You aren't fetching any data; you need to do:

DBMS_SQL.FETCH_ROWS(source_cursor);

So this works:

CREATE OR REPLACE PROCEDURE TEST_SQL 
IS 
     id_var             NUMBER; 
     name_var           VARCHAR2(30); 
     sal_var      Number; 
     source_cursor      INTEGER; 
     countp             INTEGER; 
  BEGIN 

     source_cursor := dbms_sql.open_cursor; 
     DBMS_SQL.PARSE(source_cursor, 
         'SELECT employee_id, first_name, salary FROM employees where department_id = 50', 
          DBMS_SQL.NATIVE); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, sal_var); 
     countp := DBMS_SQL.EXECUTE(source_cursor); 

     countp := DBMS_SQL.FETCH_ROWS(source_cursor);

     DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); 
     DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
     DBMS_SQL.COLUMN_VALUE(source_cursor, 3, sal_var); 

     dbms_output.put_line('id_var: '||id_var ||', name_var: '||name_var ||', sal_var: '|| sal_var);

  END; 
/

set serveroutput on
exec test_sql;

PL/SQL procedure successfully completed.

id_var: 120, name_var: Matthew, sal_var: 8000

Or as Tony Andrews pointed out, combine the two steps with EXECUTE_AND_FETCH.

Of course, you probably want to do the fetch in a loop, as this will only fetch and display the first row returned by the query.

edited Aug 5, 2016 at 9:43

answered Aug 5, 2016 at 9:39

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tony Andrews Over a year ago

I was about to post similar, but mentioning DBMS_SQL.EXECUTE_AND_FETCH, which is an alternative. +1, as we're not supposed to say any more.

Michał M Over a year ago

Alex - Yes, I've already resolve it. I've edited my post. But thanks!

Collectives™ on Stack Overflow

PL/SQL, DBMS_SQL can not display data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related