OraFAQ Forum: SQL & PL/SQL » Native Dynamic Sql

Home » SQL & PL/SQL » SQL & PL/SQL » Native Dynamic Sql (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Native Dynamic Sql [message #574715]

Tue, 15 January 2013 06:59

sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD

Junior Member

when I query select count(*) from scott.emp;
it is showing 14 records but when i run below pl/sql block
it is showing o records.

why is it so.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> declare
2 cur integer;
3 rc integer;
4 begin
5 cur := dbms_sql.open_cursor;
6 dbms_sql.parse(cur,'select * from scott.emp',dbms_sql.native);
7 rc := dbms_sql.execute(cur);
8 dbms_output.put_line(rc);
9 dbms_sql.close_cursor(cur);
10 dbms_output.put_line(rc);
11 end;
12
13
14 /
0
0

PL/SQL procedure successfully completed.

Thanks for help in advance.

Report message to a moderator

Re: Native Dynamic Sql [message #574719 is a reply to message #574715]

Tue, 15 January 2013 07:51

cookiemonster
Messages: 9152
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?
You should read the documentation on oracle packages before using them. It clearly states that the return value for the execute function is only valid for insert,update and delete - so not select.
You need to use execute_and_fetch.

Report message to a moderator