Generate Insert and Update SQL statements from Excel sheet is easy but sometimes seems very boring and tedious job, if table is having many columns and you need to do this for many Excel sheets to import the data into Oracle. Because there is also need to put lot of quotes and ampersands (&) and if you will skip any double quote or single quote then the statement will give error.

So I tried to create a SQL Query in Oracle to generate these Insert and Update statements for Excel sheet and once the statement will be generated, just paste the statement into Excel sheet cell and your statement is created.

You just need to change the table name in the query and run the SQL query into the schema where that table exists.

Below is the example to generate Insert statement for Excel:

Suppose you have data in Excel sheet like this for EMP table and you want to import this data into the Emp table.

Generate insert and update statements from Excel sheet to import data

For this just execute the below query in your schema where the table exists, for this I used Scott schema and change the table name which is highlighted with yellow to your table name. You can also change the Excel row number which is also highlighted below as 2 if your Excel data is starting from any other row.
SELECT    '='
       || CHR (34)
       || 'Insert into '
       || 'EMP'
       || '('
       || listagg (column_name, ',') WITHIN GROUP (ORDER BY r)
       || ') Values ('
       || listagg (
                CHR (39)
             || CHR (34)
             || '&'
             || excel_col
             || '2&'
             || CHR (34)
             || CHR (39),
             ',')
          WITHIN GROUP (ORDER BY r)
       || ');'||chr(34)
  FROM (  SELECT column_name,
                 CASE
                    WHEN r > 26 AND r <= 52 THEN 'A'
                    WHEN r > 52 AND r <= 78 THEN 'B'
                    ELSE ''
                 END
                 || SUBSTR (
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       R,
                       1)
                    EXCEL_COL,
                 table_name,
                 r
            FROM (  SELECT column_name, table_name, ROWNUM R
                      FROM user_tab_cols
                     WHERE table_name = 'EMP'
                  ORDER BY column_id)
        ORDER BY r);
And the statement will be generated like this below:

="Insert into EMP(DEPTNO,COMM,SAL,HIREDATE,MGR,JOB,ENAME,EMPNO) Values ('"&A2&"','"&B2&"','"&C2&"','"&D2&"','"&E2&"','"&F2&"','"&G2&"','"&H2&"');"

Just paste this statement into the Excel sheet cell and pull the fill handle to create statement for all the rows. And now your insert statements with data are ready something like this below:

INSERT INTO EMP (DEPTNO,
                 COMM,
                 SAL,
                 HIREDATE,
                 MGR,
                 JOB,
                 ENAME,
                 EMPNO)
     VALUES ('7369',
             'SMITH11V9',
             'CLERK',
             '7698',
             '21jun1990',
             '800',
             '',
             '20');

Same is for Update statement below, just change the table name which is highlighted and run the query and your Update statement is ready for Excel.
select '='
       || CHR (34)
       || 'Update '
       || 'EMP'
       || ' Set ' || listagg(col, ', ') within group (order by r) || ' where '||listagg(col, 'and ') within group (order by r) ||';'||chr(34) from (
SELECT    column_name
       || ' = '
       || CHR (39)
       || CHR (34)
       || '&'
       || excel_col
       || '2&'
       || CHR (34)
       || CHR (39)
       col, r
  FROM (  SELECT column_name,
                 CASE
                    WHEN r > 26 AND r <= 52 THEN 'A'
                    WHEN r > 52 AND r <= 78 THEN 'B'
                    ELSE ''
                 END
                 || SUBSTR (
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       R,
                       1)
                    EXCEL_COL,
                 table_name,
                 r
            FROM (  SELECT column_name, table_name, ROWNUM R
                      FROM user_tab_cols
                     WHERE table_name = 'EMP'
                  ORDER BY column_id)
        ORDER BY r));
After running the above query the update statement will be generated like below:

="Update EMP Set EMPNO = '"&A2&"', ENAME = '"&B2&"', JOB = '"&C2&"', MGR = '"&D2&"', HIREDATE = '"&E2&"', SAL = '"&F2&"', COMM = '"&G2&"', DEPTNO = '"&H2&"' where EMPNO = '"&A2&"'and ENAME = '"&B2&"'and JOB = '"&C2&"'and MGR = '"&D2&"'and HIREDATE = '"&E2&"'and SAL = '"&F2&"'and COMM = '"&G2&"'and DEPTNO = '"&H2&"';"

And after when you will paste the above statement into Excel it will become like this below:

UPDATE EMP
   SET EMPNO = '7369',
       ENAME = 'SMITH11V9',
       JOB = 'CLERK',
       MGR = '7698',
       HIREDATE = '21jun1990',
       SAL = '800',
       COMM = '',
       DEPTNO = '20'
 WHERE     EMPNO = '7369'
       AND ENAME = 'SMITH11V9'
       AND JOB = 'CLERK'
       AND MGR = '7698'
       AND HIREDATE = '21jun1990'
       AND SAL = '800'
       AND COMM = ''
       AND DEPTNO = '20';

You might need to modify the where clause because I used all the columns as criteria.

This utility SQL helped me and I think this will be helpful for you also. Thanks.

I have been to Hong Kong several times and I am very much familiar with most of the places in Hong Kong. But this time I got some new experience when I forgot my one bag in bus A41 which was from Airport to Shatin Central Bus Stop.

I was very tired due to connecting flight and I was carrying 3 bags with me and I took a bus A41 from Airport for Shatin Central Bus Station and when I reached Shatin Central station I just took my Laptop bag and main luggage bag and I forgot my 3rd bag in which some other important items were there.

After getting off I realized with 1 minute that I forgot my one bag in bus but in the mean time bus was already left.

I got little upset and thought that it is impossible to get that bag now but I hope that may be I get because this is Hong Kong.

Next day I just Googled about the A41 bus to get any contact information and I get the information that this bus belongs to Long Win Bus Limited company and the customer service number is 22612791.

Then I just contacted to the above number and gave the details to them that how it happened and about my bag that what color of bag and what was the contents of bag. After getting information from me the customer service person told me that they will contact me when they found the bag.

And simply next day I got the call from Long Win Bus company that they found my bag and told me to pick up from the Long Win Bus Terminus Airport and I got really happy and appreciated them for their good and reliable services.
shek o beach hong kong

Follow these just 5 simple steps to change an application title in Oracle Apex (Oracle Application Express).

1. In your application builder of Oracle Apex, click on the Edit Application Properties as shown below:

edit application property in Oracle Apex
2. Then click on User Interface Tab as shown below:

how to change application title in oracle apex

3. After that click on Logo Tab, then change the application name in Logo field, here you can specify an image also for the application title and then click on Apply Changes button to save the changes.

Change application title and logo in Oracle Apex


In this post you will learn to install Oracle Apex 5 on Oracle Database 12c pluggable database. Below is the step by step guide to install Oracle Application Express on Oracle 12c pluggable database.

1. Download the latest version of Oracle Apex 5 from the Oracle.com. You can download with the following link Oracle Application Express 5

2. After downloading extract the zip file into some location, for e.g. on windows D:\Software\Apex5

3. Then open the MS-DOS prompt and changed the directory to the extract location, eg.

CD D:\Software\Apex5\Apex

4. Then run the sqlplus as following:

sqlplus /nolog 

It is better to run sqlplus from the Oracle Database 12c Server location, you can copy the path from the shortcut available in Start Menu for Oracle Sql Plus.

5. Now logon using Sys account as following:

Conn
Enter User Name: sys as sysdba
Enter Password: yourpassword

6. After logon change the session to pluggable database with the following command. Change the YOURPDB12C with your pluggable database name:

ALTER SESSION SET CONTAINER = YOURPDB12C;
Note: If your pluggable database is down then run the Alter Database Open; command to start it.
You can remove if any prior Oracle Apex installation exists. To remove the apex run the following script.
@apxremov.sql
7. Now run the main script (apexins.sql) to install Oracle Apex, it will take some time.

@apexins.sql SYSAUX SYSAUX TEMP /i/

8. After completion the above script, create Apex Admin User and Password with the following script. It will ask you to enter admin username and password information:

@apxchpwd.sql

9. Configure Apex using apex_epg_config.sql by passing the path of your apex directory, where you extracted the zip file.

@apex_epg_config.sql D:\Software\Apex\Apex5.3

10. Unlock the Anonymous User.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

11. Set the HTTP Port, you can specify any depend on your system.

EXEC DBMS_XDB.SETHTTPPORT(8181);

12. Grant Connect priviledges, run all below scripts.

BEGIN
DBMS_EPG.set_dad_attribute (
dad_name => 'APEX',
attr_name => 'cgi-environment-list',
attr_value => 'REQUEST_PROTOCOL=https');
DBMS_EPG.set_dad_attribute (
dad_name => 'APEX',
attr_name => 'cgi-environment-list',
attr_value => 'HTTP_HOST=external-name:external-port');
commit;
END;
/

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', TRUE, 'connect');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to 'localhost' and give APEX_050000
-- the "connect" privilege if APEX_050000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to 'localhost'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
'ACL that lets users to connect to localhost',
'APEX_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'apex_050000',
principal_type => xs_acl.ptype_db));
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'localhost',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'apex_050000',
principal_type => xs_acl.ptype_db));
END;
/

Now your installation of Oracle Apex is complete, you can now open the Apex administration page with the following URL:

http://localhost:8181/apex/apex_admin


Note: Any time if you got disconnected from database during installation, just connect it again using sys account and give the following command to continue with the installation:

ALTER SESSION SET CONTAINER = YOURPDB12C;

How to install oracle apex on oracle 12c database

In windows if you list directory and files with DIR command in MS Dos prompt then you will get the list of files and directory with long names including spaces and when you want to change the directory with CD command to get into that directory then you may can get error.

Below is the screen shot if you give DIR command:

get short directory and file names in ms dos for windows

To get the short names for files and directories in 8.3 name format in windows give Dir command with X parameter e.g. Dir /x as shown below:

file and directory name in 8.3 format in windows

Like for directory Program Files (x86) the short name is Progra~2.

Below are the steps to add signature in Gmail account with logo or image.

1. Open your Gmail account and click on Settings as shown in image:
2. Then in General tab of Settings page scroll down and type the name and other information in Signature box and click on the image icon to add logo or an image to your signature as shown below:

How to add signature in gmail account with logo

3. Then the Add an image window will open and there you can upload an image from your computer or from Google Drive (My Drive) or from website URL, as shown below:

Upload logo or image in signature of gmail account

Your signature is now added with logo.

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.