For example:

SQL> DROP INDEX MYEMP_ENAME_INDX;

Index dropped.

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(LOWER(ENAME));

Index created.

SQL> SELECT * FROM MYEMP WHERE LOWER(ENAME) = 'ward';

Execution Plan
----------------------------------------------------------
Plan hash value: 2632457189

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


When we execute query with OR clause where OR is filtering the records from 2
different field. Oracle Database can not use index to filter the data.

For example:

SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO);

Index created.

SQL> CREATE INDEX MYEMP_HIREDATE_INDX ON MYEMP(HIREDATE);

Index created.

SQL> SET AUTOT TRACE
SQL> SELECT * FROM MYEMP WHERE EMPNO=7839 OR HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    38 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

As here we can see that we have indexes on EMPNO and HIREDATE of EMP table,
But the query with EMPNO=7839 OR HIREDATE > SYSDATE - 60 condition was not able
to use any index.

Workaround of this problem is to break this OR clause into UNION,
so that index can be used by the Oracle Database

Solution:

SQL> SELECT * FROM MYEMP WHERE EMPNO=7839
  UNION
  SELECT * FROM MYEMP WHERE HIREDATE > SYSDATE - 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 1491074982

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   1 |  SORT UNIQUE                  |                     |     2 |    76 |     6  (67)| 00:00:01 |
|   2 |   UNION-ALL                   |                     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MYEMP_EMPNO_INDX    |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYEMP               |     1 |    38 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | MYEMP_HIREDATE_INDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

sql developer formating for DB2

1) Use already set preferences  file :  

Use the suggested File to copy it into the local directory.

C:/....AppData\Roaming\SQL Developer\system4.1.3.20.78\o.sqldeveloper.12.2.0.20.78\product-preferences.xml

Copy this attached file and replace it in your user directory ( refer the above folder structure).

inside this xml file you can find the preferences called "Arul-SQL"


2) Otherwise, you can set this manually by the following way.

 

settings : After install the driver for db2

Go to tools

go to preferences

go to database

go to sql formatter

go to other vendors

select db2 formatter

choose profile( we can have different profiles for formatter) I have used with my name Arul

then click Edit

 

Find_string_inthe_schema

This particular code is used to find out the given value is present in which table :

create table table_row_count ( name varchar2(100));

declare
l_count number:=0;
v_sql   varchar2(4000);
v_string varchar2(100):='Sample101';
begin
for i in ( select * from user_tab_cols  where data_type in ('CHAR','VARCHAR2') and table_name not like '%BIN$%')
loop
v_sql := ' select distinct 1 from '||i.table_name  || ' where '||i.column_name||' like '
          ||''''||'%vinodcust/vinodcustuser3/outback21%'||'''';
  dbms_output.put_line ( v_sql);
begin
 execute immediate  v_sql into l_count;
 insert into table_row_count values ( i.table_name);
exception
when no_data_found then
 null;
 end;
end loop;
end;
/

select * from table_row_count;

drop table table_row_count;

AWR Report Generation in Sql prompt for the given time frame

--run this in sqlplus.

set pagesize 0
set linesize 121
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

spool C:\app\subramaniana\admin\LFINARUL\dpdump\awr.html

SELECT
   output 
FROM   
   TABLE
   (dbms_workload_repository.AWR_REPORT_HTML
      (
( SELECT dbid FROM v$database),
( SELECT inst_id FROM gv$instance ),
( select snap_id from DBA_HIST_SNAPSHOT 
  where begin_interval_time = ( select  max(begin_interval_time) from DBA_HIST_SNAPSHOT 
  where begin_interval_time<= to_timestamp('03-DEC-13 01.30.26.273' ,'DD-MON-RR HH24:MI:SS.FF') ) ),
( select snap_id from DBA_HIST_SNAPSHOT 
where end_interval_time = ( select  min(end_interval_time) from DBA_HIST_SNAPSHOT 
where end_interval_time >= to_timestamp('03-DEC-13 01.45.26.273' ,'DD-MON-RR HH24:MI:SS.FF')
and snap_id > (select snap_id from DBA_HIST_SNAPSHOT 
where begin_interval_time = ( select  max(begin_interval_time) from DBA_HIST_SNAPSHOT 
where begin_interval_time<= to_timestamp('03-DEC-13 01.30.26.273' ,'DD-MON-RR HH24:MI:SS.FF') )) ) ))
   );

Emi Calculator using AngularJS - With Button Functions

<!DOCTYPE html>
<html>
<style>
  table,
  th,
  td {
    border: 1px solid grey;
    border-collapse: collapse;
    padding: 5px;
  }

  table tr:nth-child(odd) {
    background-color: #f1f1f1;
  }

  table tr:nth-child(even) {
    background-color: #ffffff;
  }
</style>
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
<body>
  <div ng-app="myApp" ng-controller="myCtrl" ng-init="principle=100000;interest=14.5;tenor=10">
    Principal:&nbsp;&nbsp;&nbsp;&nbsp;
    <input type="number" ng-model="principle">
    <br> Interest:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <input type="number" ng-model="interest">
    <br> Tenor in months:
    <input type="number" ng-model="tenor">
    <br>
    <p>
      <button ng-click="calculateEMI()">CalculateEMI</button>
      <button ng-click="ClearReslult()">ClearResult</button>
      <button ng-click="RestoreDefault()">RestoreDefault</button>
    </p>
    <br>
    <table>
      <TR>
        <TH>Month
          <TH>EMI
            <TH>Interest
              <TH>Principal
                <tr ng-repeat="contact in contacts">
                  <td align='right'>{{contact.O}} </td>
                  <td align='right'>{{contact.e}} </td>
                  <td align='right'>{{contact.intPerMonth}} </td>
                  <td align='right'>{{contact.prinPermonth }} </td>
                </tr>
                <TR>
                  <TH colspan="2">Total
                    <td align='right'>{{totalIntt }} </td>
                    <td align='right'>{{totalAmtt }}</td>
    </table>
  </div>
  <script>
    var app = angular.module('myApp', []);
    app.controller('myCtrl', function($scope) {
      $scope.principle = 100000;
      $scope.interest = 14.5;
      $scope.tenor = 10;
      $scope.totalIntt = 0;
      $scope.totalAmtt = 0;
      $scope.contacts = [];
      $scope.calculateEMI = function() {
        var contactss = [];
        var R = ($scope.interest / 12) / 100;
        var P = $scope.principle;
        var n = $scope.tenor;
        var e = (P * R * (Math.pow((1 + R), n)) / ((Math.pow((1 + R), n)) - 1));
        e = Math.round(e);
        var totalInt = Math.round((e * n) - P);
        var totalAmt = Math.round((e * n));
        var intPerMonth = 0;
        var prinPermonth = 0;
        for (var i = 1; i <= $scope.tenor; i++) {
          intPerMonth = (P * R);
          P = ((P) - ((e) - (intPerMonth)));
          prinPermonth = Math.round((e) - intPerMonth);
          $scope.totalIntt = totalInt;
          $scope.totalAmtt = totalAmt;
          contactss.push({
            O: i,
            e: e,
            intPerMonth: Math.round(intPerMonth),
            prinPermonth: prinPermonth
          });
        }
        $scope.contacts = contactss;
      }
      $scope.ClearReslult = function() {
        $scope.principle = 0;
        $scope.interest = 0;
        $scope.tenor = 0;
        $scope.contacts = [];
        $scope.totalIntt = 0;
        $scope.totalAmtt = 0;
      }
      $scope.RestoreDefault = function() {
        $scope.principle = 100000;
        $scope.interest = 14.5;
        $scope.tenor = 10;
        $scope.totalIntt = 0;
        $scope.totalAmtt = 0;
        $scope.calculateEMI();
      }
    });
  </script>
</body>
</html>

Principal:    
Interest:      
Tenor in months: 
  

Month	EMI	Interest	Principal
1	10677	1208	9469
2	10677	1094	9583
3	10677	978	9699
4	10677	861	9816
5	10677	742	9935
6	10677	622	10055
7	10677	501	10176
8	10677	378	10299
9	10677	253	10424
10	10677	127	10550
Total		6770	106770

Emi Calculator using AngularJS - With Event Group

<!DOCTYPE html>
<html>
<style>
  table,
  th,
  td {
    border: 1px solid grey;
    border-collapse: collapse;
    padding: 5px;
  }
 
  table tr:nth-child(odd) {
    background-color: #f1f1f1;
  }
 
  table tr:nth-child(even) {
    background-color: #ffffff;
  }
</style>
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
<body>
  <div ng-app="myApp" ng-controller="myCtrl" ng-init="principle=100000;interest=14.5;tenor=10">
    Principal:
    <input type="number" ng-model="principle">
    <br> Interest:
    <input type="number" ng-model="interest">
    <br> Tenor in months:
    <input type="number" ng-model="tenor">
    <br>
    <br>
    <table>
      <TR>
        <TH>Month
          <TH>EMI
            <TH>Interest
              <TH>Principal
                <tr ng-repeat="contact in contacts">
                  <td align='right'>{{contact.O}} </td>
                  <td align='right'>{{contact.e}} </td>
                  <td align='right'>{{contact.intPerMonth}} </td>
                  <td align='right'>{{contact.prinPermonth }} </td>
                </tr>
                <TR>
                  <TH colspan="2">Total
                    <td align='right'>{{totalIntt }} </td>
                    <td align='right'>{{totalAmtt }}</td>
    </table>
  </div>
  <script>
    var app = angular.module('myApp', []);
    app.controller('myCtrl', function($scope) {
      $scope.principle = 100000;
      $scope.interest = 14.5;
      $scope.tenor = 10;
      $scope.totalIntt = 0;
      $scope.totalAmtt = 0;
      var contactss = [];
      var R = ($scope.interest / 12) / 100;
      var P = $scope.principle;
      var n = $scope.tenor;
      var e = (P * R * (Math.pow((1 + R), n)) / ((Math.pow((1 + R), n)) - 1));
      e = Math.round(e);
      var totalInt = Math.round((e * n) - P);
      var totalAmt = Math.round((e * n));
      var intPerMonth = 0;
      var prinPermonth = 0;
      for (var i = 1; i <= $scope.tenor; i++) {
        intPerMonth = (P * R);
        P = ((P) - ((e) - (intPerMonth)));
        prinPermonth = Math.round((e) - intPerMonth);
        $scope.totalIntt = totalInt;
        $scope.totalAmtt = totalAmt;
        contactss.push({
          O: i,
          e: e,
          intPerMonth: Math.round(intPerMonth),
          prinPermonth: prinPermonth
        });
      }
      $scope.contacts = contactss;
      $scope.$watch('[principle,interest,tenor]', function(newValue, oldValue) {
        var contactss = [];
        var R = ($scope.interest / 12) / 100;
        var P = $scope.principle;
        var n = $scope.tenor;
        var e = (P * R * (Math.pow((1 + R), n)) / ((Math.pow((1 + R), n)) - 1));
        e = Math.round(e);
        var totalInt = Math.round((e * n) - P);
        var totalAmt = Math.round((e * n));
        var intPerMonth = 0;
        var prinPermonth = 0;
        for (var i = 1; i <= $scope.tenor; i++) {
          intPerMonth = (P * R);
          P = ((P) - ((e) - (intPerMonth)));
          prinPermonth = Math.round((e) - intPerMonth);
          $scope.totalIntt = totalInt;
          $scope.totalAmtt = totalAmt;
          contactss.push({
            O: i,
            e: e,
            intPerMonth: Math.round(intPerMonth),
            prinPermonth: prinPermonth
          });
        }
        $scope.contacts = contactss;
      });

    });
  </script>
</body>
</html>

Result :
Principal: 
Interest: 
Tenor in months: 

Month	EMI	Interest	Principal
1	10677	1208	9469
2	10677	1094	9583
3	10677	978	9699
4	10677	861	9816
5	10677	742	9935
6	10677	622	10055
7	10677	501	10176
8	10677	378	10299
9	10677	253	10424
10	10677	127	10550
Total		6770	106770

Pipelined Functions in Oracle

USE : 

--------------------------

Pipelined functions are useful if there is a need for a data source other than a table in a select statement.

What is pipelined function :

--------------------------

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, 

saving memory and allowing subsequent processing to start before all the rows are generated.

Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function

as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there 

is no collection to return from the function.

 

  if you mean "pipelined functions" it would be the fact that you are streaming data from a cursor to a procedure process (the pipelined function) and then another process retrieves this. and you can skip all of the heinous IO that a global temporary table would likely incur by just streaming the data. 

  and the stream is nice because the pipelined function is constantly returning data to the client as it produces it - nice "pipeline" gets going instead of a series of monolithic steps that require large amounts of resources. This constant return is vary from tool to tool.

Demo :

--------------------------------------------------------------

Normal method of doing passing data to application.

--------------------------------------------------------------

Table functions are used to return PL/SQL collections that mimic tables.They can be queried like a regular table by using the TABLE function in the FROM clause.Regular table functions require collections to be fully populated before they are returned.Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. 

These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations.

Regular table functions require named row and table types to be created as database objects.

create table album_scalar_table  

(a_id VARCHAR2(10), 

flags VARCHAR2(1) 

) ;

 

CREATE OR REPLACE FUNCTION flags_etl_table 

RETURN varchar2   AS

v_a_id VARCHAR2(10);

v_a_flag VARCHAR2(10);

v_flg VARCHAR2(1);

BEGIN

LOOP

  FOR I IN 1..100000000000000000000000000000

  LOOP

  insert into album_scalar_table values  (I, I);

  END LOOP;

END LOOP;

RETURN 'A';

END;

/

declare

ret_val varchar(200);

begin

ret_val:=flags_etl_table;

end;

/

select * from album_scalar_table;

-- it should wait the completion of this function and transmite the data;

----------------------------------------------------------------------------------------------------------------------------

Normal method of doing passing data to application. with table functions : Table Functions

----------------------------------------------------------------------------------------------------------------------------

 

create or replace type album_scalar_type as object 

(a_id VARCHAR2(10), 

flags VARCHAR2(1) 

); 

create or replace type album_table_type as table of album_scalar_type; 

-- Build the table function itself.

CREATE OR REPLACE FUNCTION get_tab_tf  RETURN album_table_type AS

  l_tab  album_table_type := album_table_type();

BEGIN

  FOR i IN 1 .. 100000000000000000000000000000 LOOP

    l_tab.extend;

    l_tab(l_tab.last) := album_scalar_type(i, 1);

  END LOOP;

  RETURN l_tab;

END;

/

select * from table(get_tab_tf);

Note : both the case the the end select statement would wait the calling function to complete and return the rows .

--------------------------------------------------------------

pipelined method of doing passing data to application.

--------------------------------------------------------------

create or replace type album_scalar_type as object 

(a_id VARCHAR2(10), 

flags VARCHAR2(1) 

) 

create or replace type album_table_type as table of album_scalar_type; 

CREATE OR REPLACE FUNCTION flags_etl 

RETURN album_table_type PIPELINED AS

v_a_id VARCHAR2(10);

v_a_flag VARCHAR2(10);

v_flg VARCHAR2(1);

BEGIN

LOOP

  FOR I IN 1..100000000000000000000000000000 

  LOOP

  

  pipe row( album_scalar_type(I, 1) );

 

  END LOOP;

END LOOP;

RETURN;

END;

/

select * from table(flags_etl());

-- this will return the rows immediately when the return interval reaches, means that whenever the rows are getting inserted into the array , oracle automatically transfer the data. so this will avoid the I/O of sending the bulk data in one shot.

--------------------------------------------------------------------------

Demo -- will that entire loop getting executed ????

--------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION flags_etl 

RETURN album_table_type PIPELINED AS

v_a_id VARCHAR2(10);

v_a_flag VARCHAR2(10);

v_flg VARCHAR2(1);

BEGIN

LOOP

  FOR I IN 1..1000

  LOOP

      DBMS_LOCK.sleep(seconds =>.1);

  pipe row( album_scalar_type(I, 1) );

  insert into album_scalar_table values  (I, I);

  END LOOP;

END LOOP;

RETURN;

END;

/

select * from table(flags_etl())

where rownum <=20

-- result of error.. cannot perform a DML operation inside a query.

--advantage of no need of wait until the function completes.

CREATE OR REPLACE FUNCTION flags_etl 

RETURN album_table_type PIPELINED AS

v_a_id VARCHAR2(10);

v_a_flag VARCHAR2(10);

v_flg VARCHAR2(1);

BEGIN

LOOP

  FOR I IN 1..1000

  LOOP

     

  pipe row( album_scalar_type(I, 1) );

 dbms_output.put_line  (I);

  END LOOP;

END LOOP;

RETURN;

END;

/

select * from table(flags_etl())

where rownum <=100;

select * from table(flags_etl())A 

where rownum <=100

order by a.a_id desc 

-- so the loop will get terminated only 100 times.

-- so this wont wait until function completes

--------------------------------------------------------------------------

Demo --  NO_DATA_NEEDED

--------------------------------------------------------------------------

A pipelined table function may create more data than is needed by the process querying it. When this happens, 

the pipelined table function execution stops, raising the NO_DATA_NEEDED exception. 

This doesn't need to be explicitly handled provided you do not include an OTHERS exception handler.

CREATE OR REPLACE FUNCTION flags_etl 

RETURN album_table_type PIPELINED AS

v_a_id VARCHAR2(10);

v_a_flag VARCHAR2(10);

v_flg VARCHAR2(1);

BEGIN

LOOP

  FOR I IN 1..1000

  LOOP

     

  pipe row( album_scalar_type(I, 1) );

 dbms_output.put_line  (I);

  END LOOP;

END LOOP;

RETURN;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line('OTHERS Handler');

    RAISE;

END;

/

select * from table(flags_etl())

where rownum <=100;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

OTHERS Handler