Development
pySyncOracleStandby – Simple sync service for Oracle manual standby
I created this simple service a couple of years ago. It’s pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync
Format SQL or PL/SQL directly in Oracle database
Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.
1. load appropriate java library into OracleYou may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:
loadjava -u login/pass@pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar
Just change your login credentials and dbname.
2. set java permissionsThen you need to grant required Java permissions:
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' ); exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' ); exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );3. create java functions
As you can see, in the code below you can specify own formatting options (function getFormat()). For example, formatting options from Trivadis: https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_advanced_format.xml
You can copy the code below, but it would be better to take latest code from https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter
CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS /* Imports */ import oracle.dbtools.app.Format; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.sql.BLOB; import oracle.sql.CLOB; import java.io.StringWriter; import java.io.PrintWriter; public class SQLFormatter { private static String getStackTrace(Exception e) { StringWriter writer = new StringWriter(); PrintWriter printWriter = new PrintWriter( writer ); e.printStackTrace( printWriter ); printWriter.flush(); return writer.toString(); } public static Format getFormat() { oracle.dbtools.app.Format format = new oracle.dbtools.app.Format(); format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged); format.options.put("kwCase", Format.Case.UPPER); format.options.put("idCase", Format.Case.NoCaseChange); // default: Format.Case.lower format.options.put("adjustCaseOnly", false); // default: false (set true to skip formatting) format.options.put("formatThreshold", 1); // default: 1 (disables deprecated post-processing logic) // Alignment format.options.put("alignTabColAliases", false); // default: true format.options.put("alignTypeDecl", true); format.options.put("alignNamedArgs", true); format.options.put("alignEquality", false); format.options.put("alignAssignments", true); // default: false format.options.put("alignRight", false); // default: false // Indentation format.options.put("identSpaces", 3); // default: 4 format.options.put("useTab", false); // Line Breaks format.options.put("breaksComma", Format.Breaks.Before); // default: Format.Breaks.After format.options.put("breaksProcArgs", false); format.options.put("breaksConcat", Format.Breaks.Before); format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before); format.options.put("breaksAfterSelect", true); // default: true format.options.put("commasPerLine", 1); // default: 5 format.options.put("breakOnSubqueries", true); format.options.put("breakAnsiiJoin", true); // default: false format.options.put("breakParenCondition", true); // default: false format.options.put("maxCharLineSize", 120); // default: 128 format.options.put("forceLinebreaksBeforeComment", false); // default: false format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep); // default: Format.BreaksX2.X2 format.options.put("flowControl", Format.FlowControl.IndentedActions); // White Space format.options.put("spaceAroundOperators", true); format.options.put("spaceAfterCommas", true); format.options.put("spaceAroundBrackets", Format.Space.Default); //format.options.put("formatProgramURL", "default"); return format; } public static String format(String str) { String res; try { //res = new Format().format(str); Format f = SQLFormatter.getFormat(); res = f.format(str); } catch (Exception e){ res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]"; } return res; } public static CLOB formatClob(oracle.sql.CLOB clob) throws SQLException { String str = clob.getSubString(1, (int) clob.length()); String res = SQLFormatter.format(str); Connection conn = DriverManager.getConnection("jdbc:default:connection:"); CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION); resClob.setString(1L, res); return resClob; } } /4. Create PL/SQL package for it
create or replace package SQLFormatter as FUNCTION Format(str in varchar2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String'; FUNCTION FormatClob(str in clob) RETURN CLOB AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB'; end; /
Now we can test it:
SQL> select SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual; QTEXT ---------------------------------------------------------------------------------------------------- SELECT 1 a , 2 /*123 */ b , 3 c , d FROM dual , dual d2 SQL> select sql_id,SQLFormatter.format(sql_text) qtext from v$sqlarea where rownum<3 and sql_text is not null; SQL_ID QTEXT ------------- ------------------------------------- fbbm59qban13m DELETE FROM idl_sb4$ WHERE obj# = :1 AND part = :2 AND version <> :3 1gfaj4z5hn1kf DELETE FROM dependency$ WHERE d_obj# = :1

Funny friday Oracle SQL quiz: query running N seconds
Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.
with v(start_hsecs, delta, flag) as ( select hsecs as start_hsecs, 0 as delta, 1 as flag from v$timer union all select v.start_hsecs, (t.hsecs-v.start_hsecs)/100 as delta, case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag from v, v$timer t where v.flag>0 and t.hsecs>=v.start_hsecs ) select delta from v where flag+rownum<=0;
SQL> var N number SQL> exec :N := 3 /* seconds */; PL/SQL procedure successfully completed. SQL> select... DELTA ---------- 3.01 1 row selected. Elapsed: 00:00:03.01
Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:
select count(*) from dual connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;
The end of the 19th century
The Island
What is guilt? Who is guilty? Is redemption possible? What is sanity? Do persons have a telos, a destiny, both or neither? Ostrov (The Island) asks and answers all these questions and more.
A film that improbably remains one of the best of this century: "reads" like a 19th century Russian novel; the bleakly stunning visual setting is worth the time to watch alone.
Simple function returning Parallel slave info
You can add also any information from v$rtsm_sql_plan_monitor if needed
create or replace function px_session_info return varchar2 parallel_enable as vSID int; res varchar2(30); begin vSID:=userenv('sid'); select to_char(s.server_group,'fm000') ||'-'||to_char(s.server_set,'fm0000') ||'-'||to_char(s.server#,'fm0000') ||'('||s.sid||','||s.degree||'/'||s.req_degree||')' into res from v$px_session s where s.sid=vSID; return res; exception when no_data_found then return 'no_parallel'; end; /
Simple example:
select--+ parallel px_session_info, count(*) from sys.obj$ group by px_session_info / PX_SESSION_INFO COUNT(*) ------------------------ -------- 001-0002-0001(630,2/2) 38298 001-0002-0002(743,2/2) 34706
Sacred Forests
Now these forests are occupied by a handful of eremites. Their lived experience in these patches of natural oasis lends toward a wisdom that we seem to have lost in our industrialized and bustling commercial existence: "“In this world nothing exists alone,” he said. “It’s interconnected. A beautiful tree cannot exist by itself. It needs other creatures. We live in this world by giving and taking. We give CO2 for trees, and they give us oxygen. If we prefer only the creatures we like and destroy others, we lose everything. Bear in mind that the thing you like is connected with so many other things. You should respect that co-existence.” As Alemayehu explained, biodiversity gives rise to a forest’s emergent properties. “If you go into a forest and say, ‘I have ten species, that’s all,’ you’re wrong. You have ten species plus their interactions. The interactions you don’t see: it’s a mystery. This is more than just summing up components, it’s beyond that. These emergent properties of a forest, all the flowering fruits—it’s so complicated and sophisticated. These interactions you cannot explain, really. You don’t see it.”"
In my mind I see these eremites like Zosima in the Brothers Karamzov: "Love to throw yourself on the earth and kiss it. Kiss the earth and love it with an unceasing, consuming love. Love all men, love everything. Seek that rapture and ecstasy. Water the earth with the tears of your joy and love those tears. Don’t be ashamed of that ecstasy, prize it, for it is a gift of God and a great one; it is not given to many but only to the elect." Of course I may be romanticizing these good people's experience in these forest patches - I've never been there and never met any of the eremites that do.
And yet, as the author notes: "The trees’ fate is bound to ours, and our fate to theirs. And trees are nothing if not tenacious." For these Ethiopians, at least, a tree is tied inextricably to their salvation. But isn't it true that for all of us the tree is a source of life and ought to be honored as such?
Android Oracle Client 2.0
I’ve just released new version of my Simple Android Oracle Client.
New features:
- Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20.
- SQL Templates: now you can save and load own script templates
- Server output (dbms_output)
- Export results as JSON, CSV and HTML files (long tap on results)
- Copy results to the Clipboard as JSON or CSV
I use it just for basic troubleshooting and small fixes, but, please, let me know if you need anything else.
Screenshots:
Oracle Legend Bryn Llewellyn on Distributed, Cloud-Native Databases
Modern Times
I think many times the term "modernism" is conflated with "contemporary" in casual use. But by "modernism" in this case I mean, first and foremost, a mode of artistic exploration that breaks with prior, established forms, be they “rules” or aesthetic norms, seeing them as having exhausted their capacity to express themselves. Of course, these also involve the introduction of new forms and rationalizations for those shifts - ways to capture meaning in a way that carries forward a fresh energy of its own (at least for a time), often with an inchoate nod to "progress". I suppose the most recent manifestation of modernism may be transhumanism, but this obsession with the form seemed to have pervaded so much of the 20th century - in painting the emergence of cubism to the obsessiveness with abstraction (which finally gave way to a resurgence of figurative painting), in literary theory the move from structuralism to post structuralism and the disintegration into deconstruction. Poetry as well: proto modernists like Emily Dickinson paved the way for not only "high modernists" like Eliot but a full range of form-experimental poets, from ee cummings to BH Fairchild. These were not always entirely positive developments - I’ll take Miles Davis’s Kind of Blue over Bitches Brew any day of the week. But then again, I’ll take Dostoevsky over Tolstoy 10 times out of 10. In some sense, we have to take these developments as they come and eventually sift the wheat from the chaff.
Which brings me back to Pessoa, one of the literary giants of the Portuguese language. His Book of Disquiet was a lifelong project, which features a series - a seemingly never ending series - of reflections by a number of "heteronym" personalities he developed. The paragraphs are often redundant and the themes seem to run on, making for a difficult book to read in long sittings. As a consequence I've been pecking away at it slowly. It becomes more difficult as time goes by for another reason: the postured aloofness to life seems sometimes fake, sometimes pretentious: more what one would expect from an 18 year old than a mature writer who has mastered his craft. And yet Pessoa himself seems at times to long for a return to immaturity: "My only regret is that I am not a child, for that would allow me to believe in my dreams and believe that I am not mad, which would allow me to distance my soul from all those who surround me."
But still, the writing at times is simply gorgeous. There's not so much beauty in what Pessoa says as in how he says it. He retains completely the form of language, but deliberately evacuates the novel of its structure. What we are left with are in some sense "micro-essays" that sometimes connect and at other times disassociate. Taken as words that invoke meaning, they are often depressing, sometimes nonsensical. Taken as words that invoke feeling - a feeling of language arranged to be something more than just words - they can be spectacular.
The tension between the words as meaning and words as expression is impossible to escape: "Nothing satisfies me, nothing consoles me, everything—whether or not it has ever existed—satiates me. I neither want my soul nor wish to renounce it. I desire what I do not desire and renounce what I do not have. I can be neither nothing nor everything: I’m just the bridge between what I do not have and what I do not want.” What does one make of this when considered as creed? Unlikely anything positive. Yet this pericope is rendered in a particularly dreamy sort of way that infects the reader when immersed in the dream-like narrative in which it is situated. It's almost inescapable.
Few novels have made me pause for such extended periods of time to ponder not so much what the author has to say but how he says it. It's like a kind of poetry rendered without a poem.
---
A nod to New Directions Publishing, by the way, for making this project happen. Their edition of Disquiet I suspect will be seen as definitive for some time.
Unity and Difference
Now I have always identified with this comment of Dostoevsky: "I will tell you that I am a child of this century, a child of disbelief and doubt. I am that today and will remain so until the grave": sometimes more strongly than others. But myths are not about what we believe is "real" at any point in time. The meaning of these symbols I think says something for all of us today - particularly in the United States: that the essence of humanity may be best realized in a unity in difference that can only be realized through self-offering love. In political terms we are all citizens of one country and our obligation as a society is to care for each other. This much ought to be obvious - we cannot exclude one race, one economic class, one geography, one party, from mutual care. The whole point of our systems, in fact, ought to be to realize, however imperfectly, some level of that mutual care, of mutual up-building and mutual support.
That isn't happening today. Too often this we are engaged in the opposite - mutual tearing down and avoiding our responsibilities to each other. I wish there was a magic fix for this: it clearly has been a problem that has plagued our history for a long, long time. The one suggestion I can make is to find a way to reach out across boundaries with care on a day by day basis. It may seem like a person cannot make a difference. No individual drop of rain thinks it is responsible for the flood.
Introduction to Apache Kafka
Machine Learning for Oracle Professionals
How to Improve Oracle Performance with NVM
- - Proof points, best practices, and guidelines for achieving peak performance for Oracle workloads with NVMe and Storage Class Memory.
- - Maintaining high availability through disasters with Oracle Extended RAC (demo)
- - Achieving amazing data reduction and storage efficiency for Oracle databases.
Register at: http://www.prohuddle.com/webinars/yarondar/oracle_performance.php
Centrally Managed Users in Oracle (MS Active Directory)
This webinar is sponsored by Gitora, the source control tool for the Oracle database.
Practical Oracle SQL Webinar by ACE Director Kim Berg Hansan
In this webinar, Kim will present several SQL techniques, taken from his new book Practical Oracle SQL, and show you how you can apply them in real life scenarios.
- Tree Calculations with Recursion (Recursive subquery factoring)
- Functions Defined Within SQL (Functions in the WITH clause)
- Answering Top-N Questions (Analytic ranking functions)
- Rolling Sums to Forecast Reaching Minimums (Analytic window clause, recursive subquery factoring, model clause)
- Merging Date Ranges (Row pattern matching MATCH_RECOGNIZE)
Oracle Database + Git = Gitora 4
We are happy to announce the new version of Gitora, the version control tool for the Oracle Database.
Gitora 4 enables you to manage your entire Oracle code base with Git. New features in Gitora 4 include:
- Push to and pull from repositories in GitHub, GitLab, Bitbucket etc...
- Simplified workflow and vastly improved working directory structure
- SQL Developer Extension (Coming soon.)
PL/SQL functions and statement level consistency
You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.
Simple example:
create table test as select level a, level b from dual connect by level<=10; create or replace function f1(a int) return int as res int; begin select b into res from test t where t.a=f1.a; dbms_lock.sleep(1); return res; end; /
As you can see we created simple PL/SQL function that returns the result of the query select b from test where a=:input_var
But lets check what will it return if another session changes data in the table:
-- session 2: begin for i in 1..30 loop update test set b=b+1; commit; dbms_lock.sleep(1); end loop; end; / -- session 1: SQL> select t.*, f1(a) func from test t; A B FUNC ---------- ---------- ---------- 1 1 1 2 2 3 3 3 5 4 4 7 5 5 9 6 6 11 7 7 13 8 8 15 9 9 17 10 10 19 10 rows selected.
As you can see we got inconsistent results in the column FUNC, but we can easily fix it using OPERATORs:
CREATE OPERATOR f1_op BINDING (INT) RETURN INT USING F1;
Lets revert changes back and check our query with new operator now:
--session 1: SQL> update test set b=a; 10 rows updated. SQL> commit; Commit complete. -- session 2: begin for i in 1..30 loop update test set b=b+1; commit; dbms_lock.sleep(1); end loop; end; / -- session 1: SQL> select t.*, f1(a) func, f1_op(a) op from test t; A B FUNC OP ---------- ---------- ---------- ---------- 1 2 2 2 2 3 5 3 3 4 8 4 4 5 11 5 5 6 14 6 6 7 17 7 7 8 20 8 8 9 23 9 9 10 26 10 10 11 29 11 10 rows selected.
As you can see values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.
Important update of ORDS... release 19.4
In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:
Performance of REST APIs
The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.
We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.
After setting the following properties in default.xml:
You can access SQL Developer Web through:
http(s)://your_server:your_port/ords/sql-developer
You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'DIMI',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'dimi',
p_auto_rest_auth => FALSE);
commit;
END;
/
This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):
SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:
You find also more information in the ORDS 19.4 documentation.
Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.
Really nice release!
Pages
