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.



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 !!!!

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


Troubleshooting WebSphere MQ when connecting through Oracle Database gateway


Many times, you have the infrastructure team Install and setup WebSphere MQ in your servers and then as an Architect when you try to move data across servers through these MQs you face a lot of issues.

Here I am not going to talk about troubleshooting any specific errors, however I will tell you the steps to do generic troubleshooting for a WebSphere MQ and Oracle connection.

This article assumes that you access WebSphere MQ through the gateway.

Main items:

  1. Check if the DB Link in the DB is pointing to the tns correctly
  2. Check the tnsnames.ora
  3. Check the listener.ora – 2 entries described below
  4. Check if the init<SID> has the correct entries
  5. Check if Oracle user on the server has access to the MQM group.

You must use the same SID value in the tnsnames.ora file, the listener.ora file, and the GATEWAY_SID environment variable in the gateway initialization file for each gateway instance being configured.

  1. Check if your DBLINK is configured to the correct tns_name_entry.

BY password] USING ‘tns_name_entry’;

You can look in the tnsnames.ora to verify if we are using the same name or not.

  1. The tnsnames.ora should have a entry like this.













The gateway has some of the same components as an Oracle database which includes

>>directory where the gateway software is installed

>>System identifier (SID)

>>and an Initialization file similar to the Oracle database initialization parameter file


The default SID values


— dg4mqs

This is the default SID that is used when the gateway resides on the same system

as the WebSphere MQ software.

— dg4mqc

This is the default SID that is used when the gateway resides on a different system

than the WebSphere MQ software. In this case, the gateway functions as a remote

WebSphere MQ client.

The gateway and the DB talk to each other using Oracle Net. You will need to configure both, the gateway and Oracle

database to have Oracle Net communication enabled, by configuring the files tnsnames.ora and listener.ora

The gateway initialization file would be like (init<sid>.ora). The initialization file must be available when the gateway is started.

During installation, a default initialization file is created in

$ORACLE_HOME/dg4mq/admin/init<sid>.ora, on UNIX based systems where sid is the default SID of dg4mqs or dg4mqc.

3. Two entries must be added to the listener.ora file:

  • A list of Oracle Net addresses for the Oracle Net Listener to listen on
  • The gateway process that the Oracle Net Listener should start in response to incoming connection requests

If you are using Oracle Net and the TCP/IP protocol adapter, then the syntax of an

entry in the listener.ora file is:









Entry for the Gateway in listener.ora










4. The following entries needs to be in the initialization file (init<SID>.ora)