long.sql

[patrickjolliffe]

SQL> @help long

NAME DESCRIPTION USAGE

---

long.sql Display session long operations @long <filter_expression>
@long 1=1
@long username='SOE'

But in long.sql:
prompt Show session long operations from v$session_longops for sid &1
sid in (select sid from v$session where &1)

I'm guessing it should work as per the help, just fix has been lost in actual code.
Happy to add that if that's what is expected.
Btw presumably it would also be easy (and sensible) to default to 1=1 if argument is missing

[tanelpoder]

With the latest long.sql it should work? Perhaps I pushed the actual code too late - but "in my laptop it works"?

So when you run @long 1=1 or some expression, does it fail with an error or not report anything? Or what's the failure mode you're describing?

[tanelpoder]

Or were you referring to the inaccuracy in the prompt output

prompt Show session long operations from v$session_longops for sid &1

Feel free to send a PR :-)

And as a general topic, I wonder if more scripts should be standardized from @script 123 to @script sid=123 if they can easily filter by other fields too. I'm actually not sure - perhaps scripts that area 99.9% times used with just the SID as an argument, then it would be less typing (and fewer compatibility breaking changes to existing users). Feedback and thoughts welcome.

[patrickjolliffe]

Yes, it's the prompt output, sorry I should have been more clear. I was looking to implement PR, but got bogged down in trying to make it default to 1=1, seems it's not quite as easy as I thought :)

[jgebal]

Are you looking for option to have default vaues in sqlplus script parameters?
That could help:
https://www.oraclethoughts.com/sqlplus_/sqlplus-default-values-for-script-parameters/
I use some defaults in utPLSQL install scripts too.

[tanelpoder]

Thanks for the idea @jgebal - I have thought about it, but I think I won't do it in the foreseeable future as I would have to change a lot of scripts (most of them!) for consistency and now many people use my scripts via SQL Developer (and soon VSCode Oracle plugin) and I'm not sure if these engines handle the defaults well.

My path for easier usage is to document more scripts (and with better default argument examples) in help.sql and possibly in a rlwrap tab-completion extension too (PRs welcome!)

That way people can just copy paste the example "default usage" commands from help.sql and as an added bonus, have a history of exact commands - with their exact arguments in the SQLDev/commandline history too:

SQL> @help ashtop

NAME                      DESCRIPTION                                                  USAGE
------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------
ashtop.sql                Display top activity by grouping ASH columns                 @ash/ashtop <grouping_cols> <filters> <from_time> <to_time>
                                                                                       @ash/ashtop username,sql_opname,event2 1=1 sysdate-1/24 sysdate
                                                                                       @ash/ashtop sql_opname,event2,sql_plan_operation||chr(32)||sql_plan_options,objt 1=1 sysdate-1/24 sysdate

dashtop.sql               Display top activity by grouping ASH columns (based on       @ash/dashtop <grouping_cols> <filters> <from_time> <to_time>
                          DBA_HIST)                                                    @ash/dashtop username,sql_opname,event2 1=1 sysdate-1/24 sysdate
                                                                                       @ash/dashtop sql_opname,event2,sql_plan_operation||chr(32)||sql_plan_options,objt 1=1 sysdate-1 sysdate