oracle - no data in dbms_output when fetched via DBMS_SQL -
i'm trying retrieving first name, last name , salary of employees via dbms_sql package. below code executed no details printed.
excepted output:
king artur 15000
................
jimmy fallon 16000
current output:
pl/sql procedure completed.
declare sh integer ; output binary_integer; -- salary number(10) := 10000; l_firstname varchar2(50); l_lastname varchar2(50); l_salary number(10); begin -- sh := dbms_sql.open_cursor; dbms_sql.parse(sh,'select first_name,last_name,salary employees salary > :salary_value',dbms_sql.native); dbms_sql.bind_variable(sh,':salary_value',salary); dbms_sql.define_column(sh,1,l_firstname, 30); dbms_sql.define_column(sh,2,l_lastname, 30); dbms_sql.define_column(sh,3,l_salary); output := dbms_sql.execute(sh); <<dbms_sql_loop>> loop exit when dbms_sql.fetch_rows(sh) = 0; /* ||retrieve data || */ dbms_output.put_line(l_firstname ||' '||l_lastname||' ' || l_salary); dbms_sql.define_column(sh,1,l_firstname, 30); dbms_sql.define_column(sh,2,l_lastname, 30); dbms_sql.define_column(sh,3,l_salary); end loop dbms_sql_loop; dbms_sql.close_cursor(sh); end; /
i using dbms_sql.define_colum instead of dbms_sql.column_value. replace , working fine now.
Comments
Post a Comment