Oracle SQL Developer: find methods in packages/functions/procedures

I work in a team with senior developers. We create awefull brilliant functionality 馃槈 but … where is it? Do you recognise this?

Well next query when you run it in Oracle SQL Developer it may help you as it did me:

select prc.owner
|| prc.owner
|| ':' || prc.object_type
|| ':' || prc.object_name
|| ':' || decode(prc.procedure_name, null, 1
,( select min(line) + 1 as line
from all_source
where regexp_like(text,'^\s*(procedure|function)\s+'||prc.procedure_name||'\W','im')
and owner = prc.owner
and name = prc.object_name
and type = prc.object_type
) -- line
||':'||0 -- column
||':'||prc.owner || '.' || prc.object_name || nullif('.' || prc.procedure_name,'.')
||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' as "Jump to it"
from all_procedures prc
where (:rx_Search is null
or regexp_like(prc.object_name || chr(30) || prc.procedure_name, :rx_Search,'i')
and owner in ('ALF','CRM','HWS','KCS','SRG')
order by owner
,prc.procedure_name nulls first
,prc.overload nulls first

Oracle formatted hex dump of a string

Recently I ran into the problem that some regexp_substr was not giving the result that I expected. It turned out that when I tested my code, the line-ending looked like Windows CRLF, but internally it was only and LF. When I copied the test string data from SQL Developer into my script, it was converted automatically and without notice. Grgrgrgr! So I was wondering how to be able to inspect the data that was subject of the regexp_substr. Searches lead me to RAWTOHEX(UTL_RAW.CAST_TO_RAW(yourstringhere)). But I did not like the looooong output. So I wrote my own function :-).

I finally was satisfied with the HEX_DUMP function that gave me this output of the first 300 characters of聽Lipsum (with some new lines added).

0001: 4c 6f 72 65 6d 20 69 70 73 75 6d 20 64 6f 6c 6f = Lorem ipsum dolo
0017: 72 20 73 69 74 20 61 6d 65 74 2c 20 63 6f 6e 73 = r sit amet, cons
0033: 65 63 74 65 74 75 72 20 61 64 69 70 69 73 63 69 = ectetur adipisci
0049: 6e 67 20 0a 65 6c 69 74 2e 20 56 69 76 61 6d 75 = ng 路elit. Vivamu
0065: 73 20 6d 6f 6c 65 73 74 69 65 20 64 69 61 6d 20 = s molestie diam
0081: 69 64 20 63 6f 6e 73 65 71 75 61 74 20 76 65 68 = id consequat veh
0097: 69 63 75 6c 61 2e 0a 4d 61 65 63 65 6e 61 73 20 = icula.路Maecenas
0113: 76 65 6e 65 6e 61 74 69 73 20 73 65 6d 20 76 69 = venenatis sem vi
0129: 74 61 65 20 73 65 6d 20 6c 75 63 74 75 73 2c 20 = tae sem luctus,
0145: 61 20 63 6f 6d 6d 6f 64 6f 0a 61 75 67 75 65 20 = a commodo路augue
0161: 6c 75 63 74 75 73 2e 20 56 69 76 61 6d 75 73 20 = luctus. Vivamus
0177: 74 65 6d 70 6f 72 20 62 6c 61 6e 64 69 74 20 6c = tempor blandit l
0193: 6f 72 65 6d 2c 20 61 63 0a 70 75 6c 76 69 6e 61 = orem, ac路pulvina
0209: 72 20 6c 65 63 74 75 73 20 74 65 6d 70 6f 72 20 = r lectus tempor
0225: 71 75 69 73 2e 20 53 75 73 70 65 6e 64 69 73 73 = quis. Suspendiss
0241: 65 20 64 75 69 0a 73 61 70 69 65 6e 2c 20 74 69 = e dui路sapien, ti
0257: 6e 63 69 64 75 6e 74 20 65 75 20 72 69 73 75 73 = ncidunt eu risus
0273: 20 69 64 2c 20 70 65 6c 6c 65 6e 74 65 73 71 75 =聽 id, pellentesqu
0289: 65 20 63 6f 6e 67 75 65 0a 73 65 64 2e 20<聽聽聽聽聽 = e congue路sed.


You can download the source from my_utl_hex.

Jump from log immediate to the Oracle PL/SQL source

How many times did you find a log message from your Oracle application and would like to jump immediately to the PL/SQL source in the database? Well, you can with Oracle SQL Developer! This is how.

It needs some preparation. You need to register with each message that you log where that message was initiated. The simplest way to do this is to pass the so-called “Predefined Inquiry Directives” $$PLSQL_UNIT and the $$PLSQL_LINE聽 with each call to your (autonomous transaction) log method (see the PL/SQL Language reference).

A more sophisticated technique is by using the WHO_CALLED_ME procedure from the OWA_UTIL package (see the PL/SQL Packages documentation). If you call that in your log method before any other method, it will return you the database object owner, the database object name and the line number in that database object. So you won’t have to worry about saving those items with each call: it is done automatically once you have programmed it in your log method.

Once you have these object owner, name and the line in your log message, you can create a query (or report) in SQL Developer that contains a link to that source line.聽 You can find the way how to do this at聽Navigate to a specific line of PLSQL code by Jeff Smith. Or search the internet for “SQLDEV:LINK”. The link column looks something like this:

 ||':'||(calling_from_line + 1)
 ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' as Link2Object

Retrieve the object type from the ALL_OBJECTS view. You only need to query for the object types PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE and TRIGGER. (maybe TYPE BODY too).

A note on OWA_UTIL’s WHO_CALLED_ME procedure. I believe it originates from the famous Ask Tom pages (聽the initial version? ), but is now included in the the OWA_UTIL package. Unfortunately with triggers there is something weird going on. The data returned from WHO_CALLED_ME is using the output of聽DBMS_UTILITY.FORMAT_CALL_STACK. But the format that is put on that call stack for triggers is rather different then for packages, functions and procedures. So you might construct your own “where_was_i_called_from” function that supplies you with a proper string or collection.

Happy debugging!



PS: you may refrain from using WHO_CALLED_ME since it has this flaw with triggers. Also the full parsing may be not needed at the time the log message is saved. You only need to save the first entry of the call stack. This is the statement to extract the first line:



Where to put the comma?

I spend most of my work programming Oracle SQL and PL/SQL. Ofcourse much time is consumed by maintaining existing code. Here it is essential the code is readable and many times I start with reformatting the code. Put it in a format that I like. This refactoring also helps me in understanding the code, it’s structure. So the reformatting is not wasted time. On the contrary!

But many times I note that my preferences differ from most(?) tools or programmers. For instance the immense important question聽馃槈 :聽聽Where to put the comma?

I find, in a continued statement the comma should always be聽at the beginning of a line and never at the end of the prior line.

Why? Simple! Just ask yourself the question: to what part of the code does the comma belong to? It is a聽character that indicates the next part is separated from the prior part. If there would be no next part, then there would also be no聽 comma!

Other advantages of the comma in the beginning:

  • If you would have to add a parameter value to a list, you only have to insert a new line, start with the comma聽and code the value. Would you have your comma’s at the end of聽lines, you would have to go to the end of the line, code the comma, insert a new line and only then code the value.
  • The “comma-at-the-end” style also has the disadvantage that when you compare two versions of a piece of code, you also get the聽 lines where only the comma was added; with the comma at the begin, you get only the inserted lines!
  • Often I generate code from some SQL statement. It’s much easier to add a comma at the begin of each new line, then figuring out if the last line has been reached …
select decode(rownum,1,' ',',') || column_name as stmt_part
from user_tab_columns
where table_name = upper(:table_name)
  • If I have to comment a line, I do not need to bother about whether I have to remove the “continuation comma” at the end of the prior line. And when I un-comment the line again… but that comma back.
  • You immediately see that the line is part of the statements on prior lines(s).
  • It eases the method of “vertical code reading”;聽 you need less time to read it because you don’t have to read the whole line, scroll horizontally or move your eyes to the end of the line (of these ever increasing monitors)

Code is not natural language. Or didn’t you notice the different punctuation: Statements ending with a semicolon and not a period? The indendation? Color coding? Camel case?Aligning statement parts? Brackets, braces and curly braces? Abbreviations? …

Let’s make it more easy and efficient to understand and maintain!

APEX: Who is doing what?

Ever wanted to know what a user in doing in Oracle APEX? Which pages he visited in his current session? What the values of his session variables are at this very moment? It’s possible! (I’m writing this while using APEX 5.1, but the info is also available in APEX 04.02 so the path to the option in APEX Development聽might differ somewhat from your APEX version.)

Where are Dashboards?

In the first place you can find a lot of information in the Oracle APEX Development environment. Go to “Monitor Activity” and then select “Active Sessions”.

Or open de “Dashboards”, Click on “Activities” and click on the Arrow right of “Recent Logins”.

The data shown in these APEX pages is accessible thru views that exist in the APEX_nnnnnn schema’s. Be aware that some of these views have an implied selection of a so-called security_group_id which is retrieved at runtime, so trying to see the data from SqlDeveloper will only work

  1. if you have the proper authorisations
  2. if you are logged-in as the user of the parsing schema.

If the parsing schema user has sufficient privileges you can ofcource access the data from APEX SQL Workshop when logging in to the proper workspace.

And the views you should look for are:

  • apex_050100.apex_workspace_access_log
    Shows all the login info of sessions that are still available in the running APEX-engine. These might not be all active sessions! Oracle APEX has it’s own rules for cleaning up sessions, and ofcourse you can use time-out and possible your own cleaning-up.
    This view does not聽contain an APEX session id.
  • apex_050100.apex_workspace_sessions
    Shows the current sessions and contains an APEX聽session id, which is important to be able to see the session variables. It also contains the session’s NLS settings like the territory.
  • apex_050100.wwv_flow_data_view
    This is the view where you can see in detail all the session variables of each session still available in the APEX runtime. For sake of security you should add a WHERE condition on the security_group_id, for instance聽 by limiting the rows to those where security_group_id =聽v(‘FLOW_SECURITY_GROUP_ID’);

These views should give you sufficient data to link it to your own user tables so you can add application info like full names, email and phonenumber in case you have to contact a certain user.

Regarding the implementation: Since the views you are referencing are APEX-owned views, you should consider building your own application-owned views on top of these APEX-views and only using those application-owned views. Then in case聽you have to upgrade to a new APEX version聽you only have to change these three application-owned views.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy