Re: cursors question

In article <300217f3.0206242054.77f48a2c_at_posting.google.com>, muthy69_at_yahoo.com says...
>
>I had written the following SQL statement in a file called stat.sql
>
>select name,value
>from v$mystat m,v$statname s
>where m.statistic# in(2,3,179,180,181)
>and m.statistic#=s.statistic#
>
>Can you please tell me the difference between the following 2
>procedures
>

because when you run a script

SQL> @script

sqlplus will convienently set the client info/application and module fields in v$session for you. When I run a script I have called showsql for example, I see:

USERNAME SID_SERIAL STATUS MODULE ACTION --------------- --------------- ---------- --------------- --------------- OPS$TKYTE '7,15' ACTIVE 01@ showsql.sql

it is showing me the level of the current script (if a script runs a script, it'll incrment that). It is showing me the name of the script. Useful when running a long running script with scripts in it -- to see where you are.

When you do the "get" -- all that did was fill a buffer in the sqlplus client. When you run it -- it just submitted the buffer -- as if you typed it in by hand. Since it is not running a script, it'll not do the dbma_application_info stuff.

If you like, issue

SQL> set appinfo off

>1)
>SQL>@stat
>
>NAME VALUE
>------------------------------------------------ ----------
>opened cursors cumulative 133
>opened cursors current 1
>parse count (total) 139
>parse count (hard) 13
>execute count 167
>
>
>SQL>@stat
>
>NAME VALUE
>------------------------------------------------ ----------
>opened cursors cumulative 136
>opened cursors current 1
>parse count (total) 142
>parse count (hard) 13
>execute count 170
>
>2)
>SQL>get stat
>1 select name,value
>2 from v$mystat m,v$statname s
>3 where m.statistic# in(2,3,179,180,181)
>4 and m.statistic#=s.statistic#
>SQL>/
>NAME VALUE
>------------------------------------------------ ----------
>opened cursors cumulative 138
>opened cursors current 1
>parse count (total) 144
>parse count (hard) 13
>execute count 172
>
>SQL>get stat
>1 select name,value
>2 from v$mystat m,v$statname s
>3 where m.statistic# in(2,3,179,180,181)
>4 and m.statistic#=s.statistic#
>SQL>/
>NAME VALUE
>------------------------------------------------ ----------
>opened cursors cumulative 139
>opened cursors current 1
>parse count (total) 145
>parse count (hard) 13
>execute count 173
>
>
>You can see that SQL*PLUS is taking 3 cursors for each @stat
>i turned on tracing and saw that it is because it is calling
>DBMS_APPLICATION_INFO.SET_MODULE procedure.
>Why is it calling that procedure when i say @stat and why not when i
>say get the stat file and execute it?
>Thanks in advance for any help

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp