Development

Version Control for Oracle Webinar Video

Gerger Consulting - Wed, 2021-02-24 05:34

 

On February 16th, we hosted a webinar about Gitora 5. Many thanks to everyone who attended. The recording of the webinar is below.

There were a few questions we received several times during the webinar. We'd like to reiterate our answers below:

How does Gitora work with GitLab, GitHub or similar services...?
Gitora repos work with these services just like any other local Git repo does. From the point of GitLab, GitHub or similar services, Gitora repos are just local repos that you can push to these services.

How can I create a merge/pull request with Gitora?
Merge/Pull request is not a Git feature. It is a GitHub/GitLab feature. You can continue creating merge/pull requests on these services using the same workflow you use for any other local repo. To create a merge/pull request for a Gitora repo, first push your repo to GitHub/GitLab using the Gitora web app (or use Git directly). Next, go to your GitHub/GitLab account and create your merge/pull request.

How can I work on two different projects with different deadlines in the same development database and send only code related to one project to the test database?
You can't. Gitora cannot do magic. :-) There will never be a tool that will help you to do this because it is impossible. What Gitora empowers you to do is to have multiple databases per project. Then you can work on the same logical objects in different databases and use Gitora to merge code bases.
You can watch a full workflow and detailed explanation of this process at this link.

Categories: Development

Source Control Management for the Oracle Database

Gerger Consulting - Thu, 2021-02-11 03:28

Managing the source code of objects in the Oracle database and moving them between databases has been a problem for many years.

DBA's and database developers did not enjoy the benefits of prominent source control software such as Git and have been largely left behind.

Gitora is a unique tool that bridges the Oracle database and Git and enables DBA's and database developers to benefit from Git's capabilities in managing database objects.

On February 16th we are hosting a webinar to show you how you can use Gitora to manage your database objects.

In this webinar we'll cover the following topics:

- How to use Git for database object DDL statements
- How to use Gitora to move code between databases
- How to implement modern development workflows using Gitora
- How to use Pluggable Databases for a more productive development environment

You can sign up at this link.

Categories: Development

Gitora 5 Source Control for Oracle is Out

Gerger Consulting - Wed, 2021-01-27 05:14

We are very excited to announce that Gitora 5 is now available for download.

Gitora 5 is a huge step forward for version management of database objects.

Gitora 5 has many new features. Below are just six of our favorites:

  • A Brand New User Interface and Application
  • One Gitora Installation to Manage Any Number of Databases
  • Gitora Now Manages Tables
  • No-login development experience
  • A Comprehensive Developer Privileges Management Module
  • Full API To Completely Automate Your Workflow

Let’s go over these new features briefly:

A Brand New User Interface and Application

We redesigned every screen in Gitora from the ground up. The new design gives Gitora 5 a beautiful modern look with large components, easy to read, fresh colors and updated icons. 

We also rebuilt the application with Java. This reduced Gitora’s database footprint by more than 95% making it much easier to install and manage.

One Gitora Installation to Manage Any Number of Databases

This was probably one of the most requested features. Previously, if you wanted to manage a database with Gitora, you needed a new Gitora installation. This is no longer the case.

Thanks to Gitora 5’s new architecture, users can now manage any number of databases with Gitora 5 from a single application.

This makes moving code and switching between databases much easier. Maintenance and upgrade tasks are also much simpler because there is only one application to manage.

Moreover, we made installing Gitora much easier. No more running hundreds of database scripts or changing database settings. Once you create a database user for Gitora, the application will automatically install its database objects.

Gitora Now Manages Tables

Tables are now first class citizens in Gitora. You can manage every table’s DDL in Git repos along with its constraints and indexes, in its own file with its complete history, automatically.

No-login Development Experience

Previously, signing in to Gitora required an extra step for database developers. Every time they opened a database session, they had to execute one line of code to sign in to Gitora. Gitora 5 can be configured to recognize selected database users as valid Gitora users. This way, Gitora 5 becomes completely transparent during regular development activities.

A Comprehensive Developer Privileges Management Module

Gitora 5 Enterprise comes with a sophisticated authorization module with which you can manage who can edit which database object and perform which Git operation. You can define privileges for actions such as check out, commit, pull, push etc… at database level and at repo level. You can even restrict access to certain objects to specific developers.

Full API To Completely Automate Your Workflow

Last but not least, Gitora 5 Enterprise comes with a set of API’s so that you can fully automate your workflow. The API’s are easy to use and support the restrictions you specify in the authorization module.

These are our top six features but there are many more. Here is just a few of them:

  • A new and improved diff screen
  • A new way to track every DDL you execute in the database
  • Table DDL generation that is master-detail relationship aware
  • Support for sequences, materialized views

Gitora 5 comes with the features you are already familiar with:

  • Create Git repos with any of your database code objects such as Packages, Views, Types, Triggers etc…
  • Gitora doesn’t miss a change. Capture every change in these objects automatically and commit them to Git with a single click.
  • Perform Git operations such as reset, branch, merge, pull etc.. These operations will update your database objects automatically.
  • Push your repos to any cloud service such as GitHub, GitLab etc…

Gitora is a unique tool that integrates Git to the Oracle Database. It enables you to use Git just like a Java, JavaScript or C# developer.  

Gitora 5 is by far our biggest and most important release since releasing the first version of Gitora in 2015. Gitora 5 is an indispensable tool to improve developer productivity, implement modern development workflows and cut delivery times significantly.

Try Gitora 5 today.

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

Need to extend Oracle APEX? Welcome Plug-ins Pro!

Dimitri Gielis - Wed, 2020-09-02 09:22
After APEX Office Print (AOP), APEX Media Extension (AME), and APEX Project Eye (APE), we launched our newest product and service, Plug-ins Pro, in June.


Since 2004 I've used Oracle Application Express (APEX) and I just love it. With every new release of APEX things become easier and new features get added going from new item types like switches to rich components like charts. But the web is moving fast, and customers want more and more which means you need to go outside of what APEX provides. The main building blocks of APEX are the Oracle database, SQL, PL/SQL, HTML, CSS, and JavaScript. You can use any of those technologies to extend Oracle APEX with custom components. This is a real benefit of APEX, you can go from Low Code to High Code in no time. Really, there's no limit... only your imagination and skills.

Over time I've created many extensions for APEX, going from building reusable PL/SQL packages to integrating some JavaScript library. The issue with building those one-off customizations is that you have to maintain them. Where plain Oracle APEX applications stand the test of time very well, for example, an app built in 2006 still runs today, even after upgrading to the latest Oracle APEX version. Whenever I used custom code, especially JavaScript, it was less obvious to keep that current, and it would be one of the first things that break. Next to that, you make the code work specifically for a project, and in the next project you need something similar, so you copy the solution and make changes specific for that project. It's always in the context of a project that things are built, never looking at the bigger picture.

As the Oracle APEX team identified the need to extend APEX and wanted to provide a way that makes it more reusable and easier to maintain between APEX applications, they introduced plug-ins in APEX 4.0, this is now 10 years ago!

The result was that the awesome APEX community started to create and share plug-ins. Most people offer those plug-ins as open-source. Initially, there was an APEX plug-ins website maintained by Itium that had a catalog of all APEX plug-ins, but today you find the plug-ins catalog on apex.world.


There are many great plug-ins from people like Bartosz Ostrowski, Daniel Hochleitner, and Ronny Weiß to name a few, but there are also many plug-ins now that are built for a specific purpose but not maintained any longer. If you included one of those plug-ins in your app, you are on your own. But even if it's still maintained, you can't expect the author to add new features to the plug-in when you need them or when you upgrade the APEX version and it breaks. 

Creating plug-ins and maintaining plug-ins takes a lot of time, especially when you want to think about the bigger picture and make them so good that they can be used in many different projects, they are fully documented and you have some sample code, so it's easy to include in any project, even when it's been a while since you last looked at it.

And this is how Plug-ins Pro was born... we want to take away the pain of having to build and maintain APEX plug-ins. These are not just plug-ins, those are rock-solid, state-of-the-art, professionally built plug-ins that behave just like native Oracle APEX components. They are accessible, translatable, universal theme ready, and most importantly fully documented, with many samples on how to use them AND supported! If you have an issue or you want an extension, you know who to go to.

With AOP we made printing and exporting of data easy in the Oracle database and APEX. With Plug-ins Pro we want to make extending the Oracle database and APEX easy!



Once I knew we wanted to bring high-quality, supported plug-ins to the APEX community, I knew we would need the best people to bring them to life.  Bartosz Ostrowski joined the team in January of this year and began creating our awesome plug-ins.  When we launched Plug-ins Pro we started with 3 highly demanded extensions for Oracle APEX:
And we didn't go light on those plug-ins... the client-side validation plugin for example took over 400h to build!

We started from a requirements document:


We built the plug-in, tested it in on the different APEX versions (starting with 18.1), and finished off by creating extensive documentation and a full-fledged sample application



The goal of Plug-ins Pro is to make you successful, so you can turn your ideas into reality with Oracle APEX and the Database.

Our goal is not to have all the possible plug-ins, instead, we want to create plug-ins that make a difference, and that would take a lot of time to build yourself. We have some plug-ins in the pipeline, some I'm super excited about and can't wait to share with you :) but just like with APEX Office Print (AOP), we especially want to listen to you, what are you searching for, and what would make a difference for you.

This is why on our website, you can submit ideas for new plug-ins and vote on already submitted ideas!


I will write some more blog posts on the plug-ins we offer, but if you already want to try them, head over to the Plug-ins Pro website and try them 1 month for free!


Last but not least... through September 30th, 2020, we are running an Early Adopter Special Offer. Show your support for Plug-ins Pro and help us get off the ground running!  Get all the details at plug-ins-pro.com.



Categories: Development

Free Oracle Cloud: Custom Domain Name (URL) with your own ORDS on the Webserver

Dimitri Gielis - Mon, 2020-08-31 16:19
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

One of the most popular posts in this series is how to Setup a web server on the Virtual Machine.

In that blog post we setup Nginx as our web server, and as a proxy in front of Oracle APEX running on the Autonomous Database Cloud, so we can serve our own domain name e.g. dgielis.com.

Since the release of ORDS 19.4.6 (and higher), Oracle supports that we run our own ORDS version in front of the Oracle Autonomous Database Cloud. This is really cool, for a couple of reasons:
  • We can tune ORDS. Oracle limits our Free ATP to 20 concurrent connections, having our own ORDS we can allow more simultaneous connections to our database by changing the config files.
  • We are able to create a reverse proxy on the same network as where ORDS is running, which will result in a more secure solution.
  • We can have multiple ORDS running against the same database.
  • We have more security options as we can choose where we run ORDS.
But before we go into the details on how to run ORDS on your own Compute VM and let it talk to the Autonomous Database Cloud, I also want to highlight a few consequences of doing this.
There are two main points of attention running your own ORDS in front of your APEX instance:
  • You are responsible to keep your own ORDS version the same as the on the Autonomous Database Cloud. Whenever Oracle upgrades ORDS, you have to upgrade your own ORDS too.
  • You have to update your images folder whenever a patch or upgrade is done for APEX.
Here's a small script I use to check the version of both Oracle APEX and ORDS:

select 'APEX' as product, version_no, api_compatibility, case when patch_applied = 'APPLIED' then (select listagg('Patch ' || to_char(patch_number) || ' (' || patch_version || ') installed on ' || installed_on, ', ') within group (order by installed_on) as patches from apex_patches) end as applied_patches from apex_release
union all
select 'ORDS' as product, ords.installed_version as version_no, null as api_compatibility, null as applied_patches from dual;

This results in:


You could automate the check, and in case something changed that it will email you for example.

Now, let's get started with the installation of ORDS on the same machine as our Nginx webserver.

# Log in as the opc user to the Compute VM and install Nginx and ORDS:
ssh 150.136.245.144 -l opc -i .ssh/oraclecloud

# connect as root
sudo su

# as a best practice, update all packages on Linux
yum update


# install Nginx webserver
yum install -y nginx


# load repo which holds ORDS
yum-config-manager --enable ol7_oci_included

# in case you get an error, add the repo first manually
/etc/yum.repos.d

vi oci-included-ol7.repo

[ol7_oci_included]
name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL7/oci/included/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

yum-config-manager --enable ol7_oci_included

# install ORDS
yum install -y ords

Let's also make sure we have the latest versions of SQLcl and the Oracle software and wallet.
Download SQLcl here and check this blog post about the software and wallet and let's make sure we can still connect from our compute instance to our Autonomous Database.

# uninstall old Oracle Instant client
yum remove oracle-instantclient18.5-tools.x86_64

# install latest Oracle Instant client
yum install oracle-instantclient19.8-basic.x86_64 

# connect as oracle 
su - oracle

# set environment variables
export PATH=/usr/lib/oracle/19.8/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.8/client64/lib/network/admin

# connect with SQLcl to see if we can connect from the VM to the DB
./sql admin@dbdimi_high


If for some reason something is not working any longer, follow the steps again in the blog post how to run sqlcl from the compute instance to ATP.

We first have to create an ORDS user we will connect at, and it won't be the normal ORDS_PUBLIC_USER as that is used by ATP itself. We will create an ORDS_PUBLIC_USER2 as specified in the Installing and Configuring Customer Managed ORDS on Autonomous Database documentation.

CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "changeme";

GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";

BEGIN
     ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
         p_user => 'ORDS_PUBLIC_USER2',
         p_proxy_enabled_schemas => TRUE);
END;
/


Now let's configure ORDS to connect to this user in the database:

# make a base64 string of the zip
mkdir /opt/oracle/ords/wallet
cd /opt/oracle/ords/wallet
cp /tmp/wallet_DBDIMI.zip .
base64 -w 0 wallet_DBDIMI.zip > wallet_DBDIMI.zip.b64
WALLET_BASE64=`cat wallet_DBDIMI.zip.b64`

# create the apex_pu.xml and defaults.xml configuration files for ORDS
cat << EOF > /opt/oracle/ords/config/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">ORDS_PUBLIC_USER2</entry>
  <entry key="db.password">!changeme</entry>
  <entry key="db.wallet.zip.service">dbdimi_low</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

cat << EOF > /opt/oracle/ords/config/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.enabled">true</entry>
  <entry key="jdbc.InitialLimit">10</entry>
  <entry key="jdbc.MaxLimit">20</entry>
</properties>
EOF


Let's start ORDS manually first, so we can see if there are any errors:

cd /opt/oracle/ords
java -jar ords.war standalone


Note: In case you get java.lang.OutOfMemoryError: Java heap space, you can fix that by doing: 
export JAVA_OPTIONS=-Xmx512M 
In July I got this error, when I tried again last week I didn't anymore.


Copy Oracle APEX images folder.

Download Oracle APEX and upload the zip file with your favorite SFTP program to your compute instance. E.g. I use Transmit on OSX or you can use wget or something else.

# Unzip the file in the /opt/oracle folder
unzip /tmp/apex_20.1_en.zip -d /opt/oracle/

You need to make sure your APEX Images folder is in sync with the version of APEX running on ATP.
Typically Oracle will apply some patches, so download from Oracle Support, the patch for Oracle APEX and download it to your Compute Instance:

# Unzip the file in the /tmp
unzip p30990551_2010_Generic.zip

# Overwrite the files from the original Oracle APEX folder
cp -rf /tmp/30990551/images /opt/oracle/apex

# Edit the standalone.properties to add the reference to the images folder
vi /opt/oracle/ords/config/ords/standalone/standalone.properties

# Add
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images


Restart ORDS again, and once we are sure all is working, let's start ORDS as a service and enable it to autostart on reboot:

# make sure you are root user
exit

# run ORDS service and enable with startup
systemctl start ords
systemctl enable ords


Next, we want to configure Nginx as a reverse proxy in front of ORDS.
ORDS by default is running on port 8080. You may want to read my previous blog post on how to get started with Nginx. I didn't configure the domain yet, but I just wanted to access the IP.

The different bit is :

vi /etc/nginx/conf.d/dgielis.com.conf

Add the following:

server {
    listen         80;
    listen         [::]:80;
    server_name    150.136.245.144;
    root           /usr/share/nginx/html/dgielis.com;
    index          index.html;
    try_files $uri /index.html;

 location /i/20.1.0.00.13/ {
  alias /opt/oracle/apex/images/;
 }

 location /ords/ {
  proxy_pass http://localhost:8080/ords/;
  proxy_redirect off;
  proxy_set_header Host $host;
  proxy_set_header X-Real-IP $remote_addr;
  proxy_set_header X-Forwarded-Proto  $scheme;
  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
 }

}

Next, you want to reload Nginx:

nginx -s reload

The first time I tried to access http://150.136.245.144/ords/ I got a bad gateway error.

Looking at the logs I saw what was going on: 

tail -30 /var/log/nginx/error.log


To fix the issue I did:

cat /var/log/audit/audit.log | grep nginx | grep denied | audit2allow -M mynginx
semodule -i mynginx.pp

And there we go... it worked! Now we can access Oracle APEX with our own ORDS :)



Note: instead of using Nginx, you can also configure a Load Balancer in the Oracle Cloud. 
Adrian did a nice blog post on how to do that.


There you go... now you can choose to have your preferred domain name (custom URL) with your own ORDS in the Oracle Cloud.

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?

More info on Oracle Always Free APEX upgrades

Dimitri Gielis - Sun, 2020-07-05 11:12
This post is a continuation of the series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

With the release of APEX 19.2 you couldn't really decide when Oracle would upgrade your APEX version in the Always Free Oracle Cloud. But this is now changed. From now on you can decide when Oracle upgrades your Oracle APEX version.

This is the email I received a few days ago:


If you do nothing your APEX instance will be upgraded as soon as possible, but you can decide to defer the update up to 45 days.

In order to check what the settings are on your instance, log in to your Oracle Cloud account.

Go to Autonomous Transaction Processing and click on your database, in my case DBDIMI. From there click on Tools and hit Open APEX: 

 
You might have a direct link to your Oracle APEX instance, you can click on that too.

Login to your INTERNAL workspace, with the ADMIN user:


On the right-hand side it will tell you when an update is being done for Oracle APEX:


By default, it will update automatically when Oracle APEX is made available for your platform.
But if you click on the gear icon you can change it:


Here I changed it to defer the update, up to 45 days this can be done before it performs the upgrade automatically.



You find some more frequently asked questions in this blog post
Categories: Development

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

Pages

Subscribe to Oracle FAQ aggregator - Development