Development

pySyncOracleStandby – Simple sync service for Oracle manual standby

XTended Oracle SQL - Thu, 2021-01-07 05:55

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

Categories: Development

Format SQL or PL/SQL directly in Oracle database

XTended Oracle SQL - Tue, 2020-12-22 16:59

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 Oracle

You 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 permissions

Then 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

Categories: Development

Funny friday Oracle SQL quiz: query running N seconds

XTended Oracle SQL - Fri, 2020-12-11 06:37

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution

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 &gt; :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag&gt;0
    and t.hsecs&gt;=v.start_hsecs
)
select delta
from v
where flag+rownum&lt;=0;

[collapse]
SQL&gt; var N number
SQL&gt; exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL&gt; 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;
Categories: Development

Pretzel Logic

Greg Pavlik - Sat, 2020-10-17 18:12

 

The Island

Greg Pavlik - Tue, 2020-09-15 23:19

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

XTended Oracle SQL - Tue, 2020-09-15 07:38

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
Categories: Development

Sacred Forests

Greg Pavlik - Mon, 2020-07-27 12:28
An acquaintance sent this article on the small forest preserves in Ethiopia. The video is less than 10 minutes and well worth watching. The pictures in many ways tell thousands of words. Interesting to me: many of the visuals remind me of parts of north and central California where the trees and shrubs were removed to make way for cattle grazing - the visual effects I think are best captured by the late great radical novelist Edward Abbey's description of a "cow-burnt west". Deforestation in Ethiopia was also driven by agriculture to a large extent as well.

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

XTended Oracle SQL - Sun, 2020-06-28 07:42

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:

Categories: Development

Oracle Legend Bryn Llewellyn on Distributed, Cloud-Native Databases

Gerger Consulting - Tue, 2020-06-23 13:12
Bryn Llewellyn has run PL/SQL and Edition-Based Redefinition at Oracle for more than a decade and we are incredibly happy to have him as our next speaker!



Cloud-native databases are coming out of age. They are open source and high performance. They are built on the shared nothing architecture, have linear write scalibility, low read latency and built-in fault tolerance. And now they are even compatibale with PostgreSQL.

Attend the webinar by the Oracle legend Bryn Llewellyn and learn how distributed databases can help you in your daily work.
Categories: Development

Modern Times

Greg Pavlik - Sun, 2020-06-21 17:18
I’ve found myself, quite unintentionally, immersed in modernism recently. I had been previously spending a lot of time on Renaissance era music and art, so I don’t have a good explanation as to how I got from there to here. But taking stock of things, I was: reading Fernando Pessoa’s Book of Disquiet, listening to a strange melange of Iannis Xenakis, Holly Herndon, Pink Floyd’s The Wall, and looking closely at a series of paintings by Makoto Fujimura. Pretty much the only active exception I could come up with was znamenny chant recordings. None of these works necessarily relate and I’m not sure I can explain the reason for this clustering outside of coincidence.

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

Greg Pavlik - Wed, 2020-06-03 09:43
One of the themes that traveled from Greek philosophy through until the unfolding of modernity was the neoplatonic notion of "the One". A simple unity in which all "transcendentals" - beauty, truth, goodness - both originate and in some sense coalesce. In its patristic and medieval development, these transcendentals were "en-hypostasized" or made present in persons - the idea of the Trinity, where a communion of persons exist in perfect love, perfect peace and mutual self-offering: most importantly, a perfect unity in difference. All cultures have their formative myths and this particular myth made its mark on a broad swath of humanity over the centuries - though I think in ways that usually obscured its underlying meaning (unfortunately).

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

Gerger Consulting - Tue, 2020-06-02 13:03
Apache Kafka is a product that should be in every IT professional's toolbox.

Attend the webinar by developer advocate Ricardo Ferreira and learn how to use Kafka in your daily work


About the Webinar:

The use of distributed streaming platforms is becoming increasingly popular among developers, but have you ever wondered why?
Part Pub/Sub messaging system, partly distributed storage, partly CEP-type event processing engine, the usage of this type of technology brings a whole new perspective on how developers capture, store, and process events. This talk will explain what distributed streaming platforms are and how it can be a game changer for modern data architectures. We'll discuss the road in IT that led to the need of this type of platform, the current state of Apache Kafka, as well as scenarios where this technology can be implemented.


About the Presenter:
Ricardo is a Developer Advocate at Confluent, the company founded by the original co-creators of Apache Kafka. He has over 22 years of experience working with software engineering and specializes in service-oriented architecture, big data, cloud, and serverless architecture. Prior to Confluent, he worked for other vendors, such as Oracle, Red Hat, and IONA Technologies, as well as several consulting firms. While not working he enjoys grilling steaks on his backyard with his family and friends, where he gets the chance to talk about anything that is not IT related. Currently, he lives in Raleigh, North Carolina, with his wife, and son.
Categories: Development

Machine Learning for Oracle Professionals

Gerger Consulting - Tue, 2020-05-26 17:00

In this webinar, Oracle ACE Director Craig Shallahamer will introduce you to the world of applied Machine Learning from an Oracle Professional (DBA/Developer/Manager) perspective. This includes understanding what ML is, why use it and why now.

He will demonstrate how to create an automated anomalous performance detection system. He'll use industry standard Python with its ML libraries and Jupyter Notebooks. You will be able to download and do everything he does in this webinar!


If you have ever wondered how ML can be applied in an IT environment, you don't want to miss this webinar.



Categories: Development

How to Improve Oracle Performance with NVM

Gerger Consulting - Mon, 2020-05-18 13:26
Non-Volatile Memory hardware is a technology that every Oracle customer must know about. Attend the webinar by Yaron Dar and find out how you can improve the performance of yoru Oracle database with NVM.

Covered topics:
  • - 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


Categories: Development

Centrally Managed Users in Oracle (MS Active Directory)

Gerger Consulting - Tue, 2020-05-05 18:09
Attend the webinar by Oracle ACE Simon Pane and learn how to integrate Microsoft Active Directory with the Oracle database.

About the Webinar
This presentation details the advantages of using Centrally Managed Users (CMU) over the previous technologies, covers the installation/set-up process, and gives practical examples and use cases for organizations to leverage and benefit from the simplified integration of database user management (authentication and authorization) with Active Directory (AD).
An exciting new feature of Oracle Database 18c and 19c is "Centrally Managed Users" (CMU). This, for the first time, allows for simple and complete user management integration with Active Directory. No additional licenses required!
Previously, Oracle Enterprise User Security (EUS) and Oracle Unified Directory (OUD) was needed as an intermediate component bridging the Oracle Database to Active Directory. While EUS/OUD is still present and does offer some additional features and benefits, it's no longer required for database-AD user integration and management.

This webinar is sponsored by Gitora, the source control tool for the Oracle database.
Categories: Development

Practical Oracle SQL Webinar by ACE Director Kim Berg Hansan

Gerger Consulting - Wed, 2020-04-08 14:26
ACE Director Kim Berg Hansen is the author of the book "Practical Oracle SQL, Mastering the Full Power of Oracle Database".



Kim Berg Hansen is known to present complicated SQL features in a very accessible way to developers so that they can apply these features in their daily work.

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.

Kim will cover the following topics:
  • 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)
Categories: Development

Oracle Database + Git = Gitora 4

Gerger Consulting - Thu, 2020-04-02 15:17

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.)


Categories: Development

PL/SQL functions and statement level consistency

XTended Oracle SQL - Sun, 2019-12-29 18:40

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.

Categories: Development

Important update of ORDS... release 19.4

Dimitri Gielis - Thu, 2019-12-26 06:45
A few days ago Oracle released Oracle REST Data Services (ORDS) 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!
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development