You can upgrade your previous installation of Oracle Apex 4.x to 5.x in just few steps.

First download the Oracle Apex 5.x from the Oracle.com with the following link:


After downloading the zip file extract to your local drive and from the command prompt start the SQLPLUS from the directory you extracted the Apex zip file.

Suppose you have extracted zip in D:\software\apex folder, then make it the current directory from command prompt:

D:\>cd software\apex\

Then start the sqlplus and give the SYS credintials to logon as following:

D:\Software\Apex>sqlplus /nolog

SQL*Plus: Release Production on Thu Aug 27 16:36:43 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

After connected run the following script as given in the example below:

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

This will upgrade your existing Apex installation and after the completion of up-gradation of Oracle Apex you must run the following script to install the images into database for the new version, else it will give you the configuration related error when you will try to access the Apex.

SQL> @apex_epg_config.sql /software -- this is the folder in which you extracted the zip file.

Up-gradation is complete and now you can run the Apex 5 successfully.

Upgrade Oracle Apex from 4 to version 5

A procedure is given below to search any text string in all tables of current schema in Oracle.

Create the following procedure in your schema:

CREATE OR REPLACE PROCEDURE search_text (i_search_string IN VARCHAR2)
   CURSOR c_search
      SELECT    'Select '''
             || table_name
             || ''', '
             || SUBSTR (REPLACE (fields, ',', '||'' ''||'), 1, 4000)
             || ' From '
             || table_name
             || ' Where instr(lower('
             || REPLACE (fields, ',', '||')
             || '), '
             || ''''
             || LOWER (i_search_string)
             || ''''
             || ') > 0 '
        FROM (  SELECT wm_concat (column_name) fields, table_name
                  FROM (  SELECT table_name, column_name
                            FROM user_tab_cols
                           WHERE data_type = 'VARCHAR2'
                        ORDER BY table_name, column_id)
              GROUP BY table_name);

   TYPE t_rec_data IS RECORD (
      table_name   VARCHAR2 (100),
      v_data       VARCHAR2 (4000)

   TYPE rec_data IS TABLE OF t_rec_data
                       INDEX BY BINARY_INTEGER;

   v_rec_data     rec_data;
   i              NUMBER := 0;
   DBMS_OUTPUT.enable (200000);

   FOR c IN c_search
         EXECUTE IMMEDIATE c.sqlcmd
            INTO v_rec_data (i).table_name, v_rec_data (i).v_data;

         i := i + 1;
         WHEN OTHERS

 IF v_rec_data.COUNT > 0
      FOR j IN v_rec_data.FIRST .. v_rec_data.LAST
         DBMS_OUTPUT.put_line (
            v_rec_data (j).table_name || ' | ' || v_rec_data (j).v_data);
      END LOOP;
   END IF;

And then call it using the following:

set serveroutput on;

   search_text ('marketing'); -- where marketing is the string being searched

It will list out the table names and the row data containing that string.

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.