How to pass multiple parameters in oracle stored procedure

 create or replace procedure multiple_parameters(par_test in varchar2)

   is
    begin
     for c in (select *
                from bidar
                where city in (select regexp_substr(par_test, '[^,]+', 1, level)
                                from dual
                                connect by level <= regexp_count(par_test, ',') + 1
                               )
              )
    loop
       dbms_output.put_line(c.city||' '||c.city||' '||c.city||' '||c.city);
     end loop;
   end;
   /

Python 3.9 Tutorial for Beginners | Introduction to Python

 

Python 3.9 Tutorial for Beginners | Introduction to Python

ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

Oracle Run Procedure with one in parameter and multiple out parameter

ORA-06550: line 2, column 2: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

I get this error:

ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin case declare end exception exit for goto if loop mod null pragma raise return select update while with an identifier a double-quoted 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: PL/SQL procedure successfully completed.

I'm really not sure how to change my call to the procedure to make it work with SQL Developer. Any help would be greatly appreciated.


Declare
x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
GET_EMPLOYEE(1, x, y, z);
DBMS_OUTPUT.PUT_LINE(x);
End;




Or an another version of stub execution.

var x varchar2(30);
var y varchar2(30);
var z varchar2(40);
exec GET_EMPLOYEE(1, :x, :y, :z);

Oracle Database 20c Preview Released

https://blogs.oracle.com/database/ga-of-oracle-database-20c-preview-release/comment-submitted?cid=502343e9-f65a-4329-8ed1-fccdab399e1a

Complex Queries in SQL ( Oracle )

Complex Queries in SQL ( Oracle )

These questions are the most frequently asked in interviews.

To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.
select * from emp where rownum <= &n;
Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp); 
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
 Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';

Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

Select  any salary <3000 from emp table.

select * from emp  where sal> any(select sal from emp where sal<3000);

Select  all salary <3000 from emp table.

select * from emp  where sal> all(select sal from emp where sal<3000);

Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;

How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And  sal<2000

Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

 How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

how would like to calculate table size and particular clob column size inside that table.

The size of the table and the size of the LOB are two totally different things.




Barring cases where the LOB is less than 4k and stored inline, the LOB data is stored outside the table in a separate segment.



select dbms_lob.getlength(JSON_DATA)/ 1024 / 1024  MB  from ABC

MB

---------

730.1875



If you want to get the combined size of the table and of its LOB segments, you could do something like



SELECT SUM(bytes)/1024/1024 MB

  FROM dba_segments

 WHERE (owner = 'HONNIKERY' and

       segment_name = 'HONNIKERY_CLON')

    OR (owner, segment_name) IN (

        SELECT owner, segment_name

          FROM dba_lobs

         WHERE owner = 'HONNIKERY'

           AND table_name = 'HONNIKERY_CLON' )


MB

---------

730.1875

ETL Is Dead, Long Live Streams: real-time streams w/ Apache Kafka

ETL Is Dead, Long Live Streams.

https://www.slideshare.net/ConfluentInc/etl-is-dead-long-live-streams

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Partitioned table

SQL> desc honnikery
Name                          Null?    Type
----------------------------- -------- ------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
and it has a couple of honnikerytitions

SQL> select honnikerytition_name
2  from   dba_tab_honnikerytitions
3  where  table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.

SQL> create table Prabhakar as
2  select * from honnikery
3  where 1=0;
Table created.
SQL> desc Prabhakar
Name                          Null?    Type
----------------------------- -------- --------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
So now I’m ready to go…But then this happens…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns

SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.

I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS

SQL> select column_name
2  from   dba_tab_cols
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.

So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.

No. All I need do is get the columns in my template table into a similar state.

SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.



More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

ORA-01502: index ‘string.string’ or partition of such index is in unusable state


The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.



SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));

Index Status Types in DBA_INDEXES

SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID



ORA-01502: index ‘string.string’ or partition of such index is in unusable state


SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';

Toad Shortcuts Keys

Shortcut	Description
F1	Windows Help File
F2	Toggle Output Window
Shift+F2	Toggle Data Grid Window
F3	Find Next Occurrence
Shift+F3	Find Previous Occurrence
F4	Describe Table, View, Procedure, Function
F5	Execute SQL as a script
F6	Toggle between SQL Editor and Results Panel
F7	Clear All Text
F8	Recall Previous SQL Statement
F9	Execute Statement
Ctrl+F9	Set Code Execution Parameters
Shift+F9	Execute Current SQL statement at Cursor
F10 or right-click	Pop-up Menu
Ctrl+F12	External Editor, Pass Contents
Ctrl+A	Select All Text
Ctrl+C	Copy
Ctrl+E	Execute Explain Plan on the Current Statement
Ctrl+F	Find Text
Ctrl+G	Goto Line
Ctrl+L	Convert Text to Lowercase
Ctrl+M	Make Code Statement
Ctrl+N	Recall Named SQL Statement
Ctrl+O	Open a Text File
Ctrl+P	Strip Code Statement
Ctrl+R	Find and Replace
Ctrl+S	Save File
Shift+Ctrl+S	Save File As
Ctrl+T	Columns Drop-down
Shift+Ctrl+R	Alias Replacement
Shift+Ctrl+T	Columns Drop-Down no alias
Ctrl+Spacebar	Code Templates
Ctrl+U	Converts Text to Uppercase
Ctrl+V	Paste
Ctrl+X	Cut
Ctrl+Z	Undo Last Change
Ctrl+.	Display Pop-up List of Matching Table Names
Shift+Ctrl+Z	Redo Last Undo
Alt+Up Arrow	Display Previous Statement
Alt+Down Arrow	Display Next Statement (After Alt+Up Arrow)
Ctrl+Home	In the data grid: goes to the top of the record set
Ctrl+End	In the data grid: goes to the end of the record set
Ctrl+Tab	Cycles through the Collection of MDI Child Windows

BULK COLLECT & FORALL vs. CURSOR & FOR LOOP in oracle

SQL> create table sarlakg (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

 Example CURSOR_FOR_OPEN_QUERY

SQL> set timing on;
SQL> CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY
  2   IS
  3   l_sOwner VARCHAR2(30);
  4   l_sName VARCHAR2(30);
  5   l_sType VARCHAR2(19);
  6   CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;
  7   BEGIN
  8   dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
  9   OPEN cur;
 10   dbms_output.put_line('Before LOOP: ' || systimestamp);
 11   LOOP
 12   FETCH cur INTO l_sOwner, l_sName, l_sType;
 13   IF cur%NOTFOUND THEN
 14   EXIT;
 15   END IF;
 16   INSERT INTO sarlakg values (l_sOwner, l_sName, l_sType);
 17   END LOOP;
 18   CLOSE cur;
 19   dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.47
SQL> exec CURSOR_FOR_OPEN_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.80
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55731

Elapsed: 00:00:00.00
SQL>


SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example FOR_QUERY

SQL> CREATE OR REPLACE PROCEDURE FOR_QUERY
  2   IS
  3   BEGIN
  4   dbms_output.put_line('Before CURSOR: ' || systimestamp);
  5   FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP
  6   INSERT INTO sarlakg values (cur.owner, cur.name, cur.type);
  7   END LOOP;
  8   dbms_output.put_line('After CURSOR: ' || systimestamp);
  9   COMMIT;
 10   END;
 11  /

Procedure created.

Elapsed: 00:00:00.13
SQL> exec FOR_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.58
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01
 /


 SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example BULK_COLLECT_QUERY


 SQL> CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
  2   IS
  3   TYPE sOwner IS TABLE OF VARCHAR2(30);
  4   TYPE sName IS TABLE OF VARCHAR2(30);
  5   TYPE sType IS TABLE OF VARCHAR2(19);
  6   l_sOwner sOwner;
  7   l_sName sName;
  8   l_sType sType;
  9   BEGIN
 10   dbms_output.put_line('Before Bulk Collect: ' || systimestamp);
 11   SELECT owner, object_name, object_type
 12   BULK COLLECT INTO l_sOwner, l_sName, l_sType
 13   FROM all_objects;
 14   dbms_output.put_line('After Bulk Collect: ' || systimestamp);
 15   --
 16   FORALL indx IN l_sName.FIRST..l_sName.LAST
 17   INSERT INTO sarlakg values (l_sOwner(indx), l_sName(indx), l_sType(indx));
 18   --
 19   dbms_output.put_line('After FORALL: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.04
SQL> exec BULK_COLLECT_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68
SQL>


SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01

ORA-30009: Not enough memory for CONNECT BY operation

ORA-30009: Not enough memory for CONNECT BY operation
Cause: The memory size was not sufficient to process all the levels of the hierarchy specified by the CONNECT BY clause.
Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to a reasonably larger value. Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a reasonably larger value.

SQL> select count(*) from dual connect by level <= 100000000;
select count(*) from dual connect by level <= 100000000
                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation



SQL>select count(*) from
  2  (select level from dual connect by level <= 10000),
  3  (select level from dual connect by level <= 10000);

  COUNT(*)
----------
 100000000

Order By and Null values

SQL*Loader Concepts

SQL*Loader Features

Bad,log,discard files will be generated automatically by Oracle
Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
Specify the character set of the data.
Load data from multiple datafiles during the same load session.

Load data into multiple tables during the same load session.

Manipulate the data before loading it, using SQL functions.

Generate unique sequential key values in specified columns.

Use the operating system's file system to access the datafiles.



SQL*Loader Command-Line Reference
userid -- Oracle username/password
      control -- Control file name
          log -- Log file name
          bad -- Bad file name
         data -- Data file name
      discard -- Discard file name
   discardmax -- Number of discards to allow
                (Default all)
         skip -- Number of logical records to skip
                (Default 0)
         load -- Number of logical records to load
                (Default all)
       errors -- Number of errors to allow
                (Default 50)
         rows -- Number of rows in conventional path bind array
                or between direct path data saves
                (Default: Conventional Path 64, Direct path all)
     bindsize -- Size of conventional path bind array in bytes
                (System-dependent default)
       silent -- Suppress messages during run
                (header, feedback, errors, discards, partitions, all)
       direct -- Use direct path
                (Default FALSE)
      parfile -- Parameter file: name of file that contains
                parameter specifications
     parallel -- Perform parallel load
                (Default FALSE)
     readsize -- Size (in bytes) of the read buffer
         file -- File to allocate extents from


SQL*Loader Parameters

Parameters can be grouped together in a parameter file. You could then specify the name of the parameter file on the command line using the PARFILE parameter.

Certain parameters can also be specified within the SQL*Loader control file by using the OPTIONS clause

SQL*Loader Control File
Contains mapping information between file & the table
CONTROL specifies the name of the control file that describes how to load data.
If a file extension or file type is not specified, it defaults to CTL. If omitted, SQL*Loader prompts you for the file name.

Input Data and Datafiles

Data Conversion and Datatype Specification

Discarded and Rejected Records

Log File and Logging Information

Conventional Path Loads, Direct Path Loads, and External Table Loads

Partitioned Object Support

Application Development: Direct Path Load API

What's an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Query processing can be divided into 7 phases:

[1] Syntactic Checks the syntax of the query
[2] Semantic Checks that all objects exist and are accessible
[3] View Merging Rewrites query as join on base tables as opposed to using views
[4] Statement
     Transformation Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP Generation QEP = Query Evaluation Plan
[7] QEP Execution QEP = Query Evaluation Plan


Join Types

Sort Merge Join (SMJ)
Nested Loops (NL)
Hash Join



Sort Merge Join

SQL> explain plan for
select /*+ ordered */ e.deptno,d.deptno
from emp e,dept d
where e.deptno = d.deptno
order by e.deptno,d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
  MERGE JOIN
    SORT JOIN
      TABLE ACCESS FULL EMP [ANALYZED]
    SORT JOIN
      TABLE ACCESS FULL DEPT [ANALYZED]



Nested Loops

SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
  NESTED LOOPS
    TABLE ACCESS FULL DEPT [ANALYZED]
    TABLE ACCESS FULL EMP [ANALYZED]

Hash Join

SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp,dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT  [CHOOSE] Cost=3
  HASH JOIN
    TABLE ACCESS FULL DEPT
    TABLE ACCESS FULL EMP





SQL> set autotrace traceonly explain;
SQL> select * from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20M|    95M|  8480   (3)| 00:01:42 |
|   1 |  TABLE ACCESS FULL| TEST |    20M|    95M|  8480   (3)| 00:01:42 |
--------------------------------------------------------------------------

SQL> select /*+ PARALLEL(4) */ *
  2  from test
  3  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3388271637

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    20M|    95M|  2351   (3)| 00:00:29 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    20M|    95M|  2351   (3)| 00:00:29 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    20M|    95M|  2351   (3)| 00:00:29 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| TEST     |    20M|    95M|  2351   (3)| 00:00:29 |  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

SQL> select /*+ PARALLEL(2) */ *
  2  from test ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3388271637

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    20M|    95M|  4703   (3)| 00:00:57 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    20M|    95M|  4703   (3)| 00:00:57 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    20M|    95M|  4703   (3)| 00:00:57 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| TEST     |    20M|    95M|  4703   (3)| 00:00:57 |  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 2 because of hint

SQL>

how to delete duplicate records from a table in Oracle

4 ways to delete duplicate records Oracle

SQL> select empno,count(empno) from emp21 group by empno
  2  having count(empno)>1;

     EMPNO COUNT(EMPNO)
---------- ------------
      7782            2
      7844            2
      7698            2
      7902            2
      7566            2
      7788            2
      7654            2
      7934            2
      7876            2
      7900            2

10 rows selected.


1. Using rowid

  SQL>        delete from emp21 e1
         where rowid not in
          (select max(rowid) from emp21 e2
           where e1.empno = e2.empno );



2. Using self-join

  SQL> delete from emp
           where rowid not in
          (select max(rowid) from emp group by empno);


3. Using row_number()

SQL> delete from emp21 where rowid in
                   (
                     select rid from
                      (
                        select rowid rid,
                          row_number() over(partition by empno order by empno) rn
                          from emp21
                      )
                    where rn > 1
                  );



4. Using dense_rank()

SQL>               (
                 select rid from
                  (
                    select rowid rid,
                      dense_rank() over(partition by empno order by empno) rn
                      from emp21
                  )
                where rn > 1
               );

sql loader in oracle example

SQL-Loader: The Step by Step Basics - Example

SQL> create table customer_ldr (cid number primary key,cname varchar2(20),
loc varchar2(20),type char(1),load_date date);
 /

Table created.

*******************data.txt*************************

1,prabhakar,bidar,y,sysdate,
2,manju,bang,y,sysdate,
3,daivik,mysore,y,sysdate,


*********************controlfile*******************

 options  ( skip=1 )
load data
infile 'D:\sql_loader\lod\data.txt'
TRUNCATE
into table  customer_ldr
fields terminated by "," optionally enclosed by "#" TRAILING NULLCOLS
(cid ,cname,loc,type, load_date  sysdate)



C:\Users\Admin>sqlldr scott/tiger control='D:\sql_loader\lod\sysdate_ldr.txt' log='D:\sql_loader\lod\log.txt'

SQL*Loader: Release 11.2.0.1.0 - Production on Sun Oct 7 22:21:50 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4




SQL> select * from customer_ldr;

       CID CNAME                LOC                  T  LOAD_DATE
---------- -------------------- -------------------- - ---------
         1 prabhakar            bidar                y       07-OCT-18
         2 manju                bang                 y        07-OCT-18
         3 daivik               mysore               y       07-OCT-18

Mutating triggers / Mutating errors in triggers

create or replace trigger trig_mut
    before insert on emp10
    for each row
    begin
    insert into emp10(empno,sal) values (:new.empno,:new.sal);
    end;
    /
Trigger created.



SQL> insert into emp10 select * from emp;
insert into emp10 select * from emp
            *
ERROR at line 1:
ORA-04091: table SCOTT.EMP10 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_MUT", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG_MUT'

Why I don't need to COMMIT in database trigger?

We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:

SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));

Table created.

SQL>  CREATE TABLE  honnikery (col1 NUMBER);

Table created.

SQL> create or replace trigger honni_trig
  2      after insert on honnikery
  3      begin
  4      insert into log values(sysdate,'insert on tab1');
  5      commit;
  6      end;
  7  /

Trigger created.

SQL> INSERT INTO honnikery VALUES (1);
INSERT INTO honnikery VALUES (1)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.HONNI_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.HONNI_TRIG'


As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.


create or replace trigger honnikery_trig
    after insert on honnikery
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    insert into log values(sysdate,'insert on honnikery');
    commit;
    end;

Trigger created.

SQL> INSERT INTO honnikery VALUES (1);
1 row created.