Moving BLOB data from to Apex on Autonomous Database

Moving BLOB data from to Apex on Autonomous Database

The below steps would work for less data may  be 1-2Gb.

Step 1: Go to Data Workshop as show below

Step 2: Once inside Data workshop, you XML to unload the data to your desktop.

Step 3: if you want you can zip the file if the file size is huge. In these cases it will zip to 60-80%, which is really good.

Step 4: Go to the new Apex where you want to upload the file.

Step 5: Under your workspace, choose “Shared Components”

Step 6: Goto “Static Application Files”

Step 7: Upload your zip file here

Step 8: Once uploaded you will be able to see your file in

select * from apex_application_files

this will confirm that the file has been uploaded in the Apex folder.

Step 9: create procedure as shown below

    create or replace procedure load_xml_to_existing_table(
    p_xml_content blob,
    p_table_name  varchar2,
    p_table_owner varchar2 default sys_context('userenv','current_user' ))
    l_clob                    clob;

    l_ins_ctx                 dbms_xmlstore.ctxType;
    l_rows                    number;
    l_date_format             varchar2(255); 

    l_nls_date_format         varchar2(255);
    l_nls_timestamp_format    varchar2(255);
    l_nls_timestamp_tz_format varchar2(255);

    c_nls_date_format         varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss."000"';
    c_nls_timestamp_format    varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss.FF3';
    c_nls_timestamp_tz_format varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss.FF3 TZR';

    procedure convert_to_clob is
        l_dest_offset  number := 1;
        l_src_offset   number := 1;
        l_lang_context number := dbms_lob.default_lang_ctx;
        l_warning      number;
        dbms_lob.createtemporary ( l_clob, true );

            dest_lob      => l_clob,
            src_blob      => p_xml_content,
            amount        => dbms_lob.lobmaxsize,
            dest_offset   => l_dest_offset,
            src_offset    => l_src_offset,
            blob_csid     => nls_charset_id('AL32UTF8'),
            lang_context  => l_lang_context,
            warning       => l_warning );

    end convert_to_clob;


    for c1 in (
        select parameter, value
          from nls_session_parameters
         where parameter in ('NLS_DATE_FORMAT',
        if c1.parameter = 'NLS_DATE_FORMAT'            then 
            l_nls_date_format         := c1.value;
        elsif c1.parameter = 'NLS_TIMESTAMP_FORMAT'    then 
            l_nls_timestamp_format    := c1.value;
        elsif c1.parameter = 'NLS_TIMESTAMP_TZ_FORMAT' then 
            l_nls_timestamp_tz_format := c1.value;
        end if;
    end loop;

    dbms_session.set_nls( 'NLS_DATE_FORMAT',         
                          '''' || c_nls_date_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_FORMAT',    
                          '''' || c_nls_timestamp_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_TZ_FORMAT', 
                          '''' || c_nls_timestamp_tz_format || '''');

    l_ins_ctx := dbms_xmlstore.newContext( 
                                   dbms_assert.enquote_name( p_table_owner ) || '.' || 
                                   dbms_assert.enquote_name( p_table_name ) );

    dbms_xmlstore.setrowtag( l_ins_ctx, 'ROW' );
    l_rows := dbms_xmlstore.insertxml( l_ins_ctx, l_clob );
    dbms_xmlstore.closecontext( l_ins_ctx );

    dbms_session.set_nls( 'NLS_DATE_FORMAT',         
                          '''' || l_nls_date_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_FORMAT',    
                          '''' || l_nls_timestamp_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_TZ_FORMAT', 
                          '''' || l_nls_timestamp_tz_format || '''');

    if dbms_lob.istemporary( l_clob ) = 1 then
        dbms_lob.freetemporary( l_clob );
    end if;
end load_xml_to_existing_table;

    l_blob blob;
    select blob_content
      into l_blob
      from apex_application_files
     where name = :PX_XML_FILE;

        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );

Step 10: Initiate the procedure using the below code. Pass your file name and the table in which you want to insert the data.

    l_blob blob;
    select blob_content
      into l_blob
      from apex_application_files
     where name = :PX_XML_FILE;

        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );

Step 11: Query the table to see if the load has completed. Finish.



Human Tendency to make one sided comparisons

A few months ago, I was talking to a friend over a beer and mid-conversation with him, I remember thinking:

“Wow, this guy is so freaking awesome”

This person is a Data Scientist, running his own company, so basically does amazing work, solving real world data problems, amplifying them and providing future insights (I love it !!) and on top of that “shit loads of money”.

I remember feeling jealous, that I wasn’t nearly as close to him professionally. I wished I could be a Data Scientist with my own startup.

Over the conversation, we ended up talking a bit about Health in general.

It turns out, he has some major health concerns – he’s got Diabetes, has frequent back aches, doesn’t exercise, no walking/jogging and mostly spends his time what he boasts as partying and hanging out with his friends.

Now, the purpose of this article is not to judge the person for not focusing on his health; it’s to point out the fundamental human tendency to make one-sided comparisons.

We look at the positive aspects of people – the things we like about them: some are cooler than you, some have more muscle, some beat you in all fronts, and we compare their strengths to our weaknesses: I’m not as cool, I’m not as rich, I’m not as fit…

Let me be blunt: sometimes we are RIGHT – sometimes we’re not doing well in that aspect of life at all. In that case, it makes perfect sense to take a pause and fix that part of ourselves.

But often, you’re doing fine in that area, and these differences are just a result of different priorities, interests and different motivations.

The grass is always greener on the other side.

When you make these half comparisons, the grass often looks greener on the other side.

You’re comparing someone’s best aspect or number 1 priority to something that’s only of average importance to you.

If I compare my finances with many people here in Sydney, I’d lose, big time. There are so many millionaires here, and I’m not even close to that.

If I compare my body to a bodybuilder, I’d feel small. Some of them have more muscle in their legs than I have in my entire body.

This is where shortsightedness comes in. We get dazzled by the part of other people we feel amazed by (or the ones we feel that we lack), and would happily exchange it with them.

The grass often isn’t actually greener on the other side. It only looks greener because you choose to look at the parts of it that is greener.

If you take a bodybuilder as an example: he’s strong, fit, and good looking. You’re good looking too, but not as godlike or as muscular as him.

But would you exchange lives with him?

The point I’m getting to is, you need to look at the complete picture.

If you only see a popular kid and an unpopular kid, you will no doubt conclude that the popular kid was ahead of the game.

Now, this is not to say that all people are equal because everyone has different strengths and weaknesses.

Some people are better than others, either because they were born that way (IQ, temperament, etc.) or they worked for it.

If someone is richer, younger, healthier, happier, and more connected than you – and achieved it themselves – hell yeah he’s better and the grass is legitimately greener on the other side – (you should take some notes from him!)

(In fact, you should take some notes from everyone who’s worked to be great at something – after all, despite his lack of youth, Buffett can teach everyone quite a bit about acquisitions, the bodybuilder can give you some tips on your form even though he may not know much about anything else.)

But, in most cases, it’s fake greenery – the result of the myopic vision of your mind, where you only see the glory and not the sacrifice, only see the pros and not the cons, and only see the part of land where the grass is watered well and ignore the regions where it’s cracking out of dryness.

Comparisons with other people are inherently biased.

From the above, we can reasonably conclude that it’s not possible for us to compare ourselves with others because we don’t know their lives that well.

We know the things they’re doing well in – where they’re kicking ass, but we don’t know where they’re failing, the challenges they’re facing, or what things they’ve neglected for a while (those parts of them are much less visible and they don’t talk about them as much).

Because you don’t know the darker sides of their life, you cannot make a complete comparison.

There’s nothing wrong with being inspired by other people, but…

You can only compare you to yourself from the past.

Your only true comparison you can make is with yourself – 1, 5, 10 years ago.

This is because, with your own self, you see the full picture.

Where were you one year ago and where are you now?

If you were deeply in debt last year and now your net worth is $0 (i.e., debt-free) – you’ve done well. You’ve grown.

The real metric is what you have divided by what you started with. Are you growing? And are you happy with the rate of growth?

At the end of the day, you can only work with what you have, so it’s only fair to measure progress with yourself.

The next time you start comparing, ask yourselves this question: “Would you exchange lives with that person?”. If the answer is No, don’t be jealous, well if the answer is Yes, then instead of being jealous, be inspired and motivated (and start taking notes).

Copy user from an existing user account (Oracle)

Connect to target database and execute with DBA privileges

Example for user “USER1” – Make sure to put the username in uppercase.


set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000

undefine User_in_Uppercase;

set linesize 1000
set long 2000000000
select (case
when ((select count() from dba_users where username = ‘&&User_in_Uppercase’ and profile <> ‘DEFAULT’) > 0) then chr(10)||’ — Note: Profile’||(select dbms_metadata.get_ddl(‘PROFILE’, u.profile) AS ddl from dba_users u where u.username = ‘&User_in_Uppercase’) else to_clob (chr(10)||’ — Note: Default profile, no need to create!’) end ) from dual UNION ALL select (case when ((select count()
from dba_users
where username = ‘&User_in_Uppercase’) > 0)
then ‘ — Note: Create user statement’||dbms_metadata.get_ddl (‘USER’, ‘&User_in_Uppercase’)
else to_clob (chr(10)||’ — Note: User not found!’)
end ) Extracted_DDL from dual
select (case
when ((select count() from dba_ts_quotas where username = ‘&User_in_Uppercase’) > 0) then ‘ — Note: TBS quota’||dbms_metadata.get_granted_ddl( ‘TABLESPACE_QUOTA’, ‘&User_in_Uppercase’) else to_clob (chr(10)||’ — Note: No TS Quotas found!’) end ) from dual UNION ALL select (case when ((select count()
from dba_role_privs
where grantee = ‘&User_in_Uppercase’) > 0)
then ‘ — Note: Roles’||dbms_metadata.get_granted_ddl (‘ROLE_GRANT’, ‘&User_in_Uppercase’)
else to_clob (chr(10)||’ — Note: No granted Roles found!’)
end ) from dual
select (case
when ((select count() from V$PWFILE_USERS where username = ‘&User_in_Uppercase’ and SYSDBA=’TRUE’) > 0) then ‘ — Note: sysdba’||chr(10)||to_clob (‘ GRANT SYSDBA TO ‘||'”‘||’&User_in_Uppercase’||'”‘||’;’) else to_clob (chr(10)||’ — Note: No sysdba administrative Privilege found!’) end ) from dual UNION ALL select (case when ((select count()
from dba_sys_privs
where grantee = ‘&User_in_Uppercase’) > 0)
then ‘ — Note: System Privileges’||dbms_metadata.get_granted_ddl (‘SYSTEM_GRANT’, ‘&User_in_Uppercase’)
else to_clob (chr(10)||’ — Note: No System Privileges found!’)
end ) from dual
select (case
when ((select count(*)
from dba_tab_privs
where grantee = ‘&User_in_Uppercase’) > 0)
then ‘ — Note: Object Privileges’||dbms_metadata.get_granted_ddl (‘OBJECT_GRANT’, ‘&User_in_Uppercase’)
else to_clob (chr(10)||’ — Note: No Object Privileges found!’)
end ) from dual

Use the output of the above script to create your new user. All Done !!!!

Feedback after Meditation

I can see a lot of people on social media telling everybody else that meditation is awesome and they are loving it. They claim that you can see the positive results of meditation on their minds and body. I wanted to start meditation or at least give it a try. I was searching through content on the web and came across this video on YouTube, which looked simple and straight forward for beginners.

The video explains that we should meditate for at least a month before we can see the results. To keep myself accountable, I will post here that I am starting meditation today i.e. 18-Jan-2020. I will provide my feedback after a month and let you know how I feel.

All the best 🙂


Angry customers/feedback, Who/What are they?

Team across the spectrum should realize that “Angry customers/feedbacks” are a goldmine for you and for your team.

They are providing feedback, what your

a) Development Team

b) Quality Team

c) Product Owners

d) and thousands or millions of other customers could not provide (basically they just skipped it when they were irritated).

So next time welcome your angry customer/feedback, listen to the whole story and identify what’s broken and can be fixed.

Why are Database consultants so important


One of the most common mistakes that organizations/teams make is not hiring a Database Consultant at the start of the project.

You might think that this is such a silly mistake and people should not do it, however trust me, they don’t and they do it again and again.

At the start of the project most of the times the team just want to hire a UI expert and want to start asap. The UI person creates a webpage and the associated events and later realizes that he/she needs to store the data somewhere (wow!!!) and creates a table and this activity goes on for every page or click the UI person is creating.

This leads to a Database design which is neither scalable, compatible and you can just imagine the performance and latency issues that you will get into.


Database design is the foundation of a well-performing database.

Would you ever consider building a house without a blueprint? The foundation to your Applications, Interfaces and Systems is the database. To ensure that your database will be a good foundation for your applications and programs that rely on it, build it right in the beginning.

What Organizations need to do is to hire a Database consultant at the start of the project and let him design and decide the Data model and the Architecture. If there are budget constraints, at least get .5 FTE from some other project for some time and let him design the Database keeping in mind the environment you are working on. Trust me, this will go a long way for your application. Applications which are designed in such a manner can withstand latency, scalability, availability and compatibility issues in far better manner than others.

The benefits of building it right are many. Just as you would never consider building a house without thoughtful design and planning, so should you approach database design. Imagine the building architect who is designing to your specifications. You’ll be asked questions like “How will you use this?”, “What are your future plans?”, “What do you want this to look like?”, “How do you want it to work?” Asking these same questions when you’re building a database will give you the same usability and “livability” that you’ll get out of a well-designed house.

Properly tuned and deployed Database designs will go a very long way toward ensuring your database environment is functioning at optimal levels. It also ensures that all your stakeholders your Technical architects, DBAs, UI team, Domain managers, users and even business intelligence and applications teams are aligned and working together efficiently.

Happy Designing !!!!

Making System tablespace Offline – Oracle

If you search the Oracle documentation it says that you cannot bring the system tablespace offline.
However, here is a way to do it —

– Each tablespace is assigned one or more datafile. You can bring the datafile offline.
In the below case, I have deleted the system datafile and trying to start the database.

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2182592 bytes
Variable Size 335544896 bytes
Database Buffers 725614592 bytes
Redo Buffers 5595136 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file


SQL> alter database datafile 1 offline;

Database altered.

and then you can bring it back up using the same datafile.

In my case, i have done a rman restore of the tablespace.

SQL> alter database open;
alter database open
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline


SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;

Database altered.


EM dbconsole not starting and giving NET HELPMSG 3547


Here are the steps you can use to check if you face problems with EM DB Consle

Database version : standard edition

EM DB Console not working.

1) check the status of dbconsole as follows

C:\WINDOWS\system32>emctl status dbconsole

2) Try to start the dbconsole

C:\WINDOWS\system32>emctl start dbconsole

if the dbconsole cannot be started, sometimes this problem may occur because SYSMAN and DBSNMP users are locked, so check their account status

C:\WINDOWS\system32>sqlplus / as sysdba

SQL> select username,account_status from dba_users;

—————————— ——————————–
OUTLN                                            OPEN
MGMT_VIEW                                 OPEN
SYS                                                   OPEN
SYSTEM                                           OPEN
DBSNMP                                         OPEN
SYSMAN                                          OPEN
DIP                                                   EXPIRED & LOCKED
ORACLE_OCM                                EXPIRED & LOCKED
APPQOSSYS                                    EXPIRED & LOCKED

so from above, its clear that both the user accounts are open

5) Check if agent is running or not

C:\WINDOWS\system32>emctl status agent

6) If the agent is not running try to start it

C:\WINDOWS\system32>emctl start agent

sometimes you get the message NET HELPMSG 2185

C:\WINDOWS\system32>NET HELPMSG 2185

The service name is invalid.

You tried to start a service that is not configured on this system.


Check the spelling of the service name or check the configuration information for the service using
the Services option from Server Manager.

Eventually recreate the repository please remember that recreating the repository will not impact your database functionality

before recreating, make sure you have the following information with you
Database hostname
Database SID
Listener port number
password for SYS user
password for DBSNMP user
password for SYSMAN user
also, it is important that DBSNMP and SYSMAN user’s account should be opened


C:\WINDOWS\system32>emca -config dbcontrol db -repos recreate -silent -DB_UNIQUE_NAME WINDEV -PORT 1521 -EM_HOME C:\oracle\11g\R2 -LISTENER LISTENER -SERVICE_NAME WINDEV -SID WINDEV -ORACLE_HOME C:\oracle\11g\R2 -HOST HP -LISTENER_OH C:\oracle\11g\R2 -DBCONTROL_HTTP_PORT 1159 -DBSNMP_PWD oracle -SYSMAN_PWD oracle -SYS_PWD oracle


AWSCLI installtions issues

In case you are trying to install awscli and using cygwin and getting errors.
One of the errors that I encountered was
aws –version
> C:\windows-style-path-to-anaconda\python.exe: can’t open file > ‘cygdrive/c/cygdrive-style-path-to-anaconda/Scripts/aws’:

The problem is that you have installed awscli in your other python installation, could be windows anaconda.
The installation has to be there in cygwin
The solution is to install python and pip inside cygwin: Use the below commands

pip uninstall awscli
install apt-cyg /bin
apt-cyg install python
pip install awscli