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:

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

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 11.2.0.1.0 Production on Thu Aug 27 16:36:43 2015

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

SQL> connect SYS AS SYSDBA
Enter password:
Connected.

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)
IS
   CURSOR c_search
   IS
      SELECT    'Select '''
             || table_name
             || ''', '
             || SUBSTR (REPLACE (fields, ',', '||'' ''||'), 1, 4000)
             || ' From '
             || table_name
             || ' Where instr(lower('
             || REPLACE (fields, ',', '||')
             || '), '
             || ''''
             || LOWER (i_search_string)
             || ''''
             || ') > 0 '
                sqlcmd
        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;
BEGIN
   DBMS_OUTPUT.enable (200000);

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

         i := i + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;

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

And then call it using the following:

set serveroutput on;

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

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.