Home » SQL & PL/SQL » SQL & PL/SQL » Native Dynamic Sql (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Native Dynamic Sql [message #574715] Tue, 15 January 2013 06:59 Go to next message
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.
Re: Native Dynamic Sql [message #574719 is a reply to message #574715] Tue, 15 January 2013 07:51 Go to previous messageGo to next message
cookiemonster
Messages: 10841
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.
Re: Native Dynamic Sql [message #574723 is a reply to message #574719] Tue, 15 January 2013 08:09 Go to previous messageGo to next message
sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD
Junior Member
Thanks for the clarification...
Re: Native Dynamic Sql [message #574724 is a reply to message #574715] Tue, 15 January 2013 08:11 Go to previous message
Michel Cadot
Messages: 58487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is there:

http://www.orafaq.com/forum/mv/msg/185734/574699/102589/#msg_574699

Regards
Michel
Previous Topic: dates question
Next Topic: Facing problem with a Query
Goto Forum:
  


Current Time: Tue Jul 22 14:36:55 CDT 2014

Total time taken to generate the page: 0.10022 seconds