PL/SQL Scenario Questions – Part 1

PL/SQL Scenario Questions – Part 1

In this blog we will explore some PL/SQL – Scenario questions.


Scenario 1. Write PL/SQL program to show overloading concept of oops in PL/SQL.

Solution 1: See below example for PL/SQL package PKG_TEST, it has two procedure. Both procedure has same name & same input parameter name. But parameter type is different. This is example of overloading of objects in PL/SQL.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2);
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE ('Calling VARCHAR2 procedure');
  END PRC_SAMPLE;
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling number procedure');
  END PRC_SAMPLE;
END PKG_TEST;
/

pls0

Scenario 2. Write PL/SQL block which raise the user-defined exception if the day is weekend.

SET SERVEROUTPUT ON;
DECLARE
  exp_weeknds EXCEPTION;
BEGIN
  IF TO_CHAR(SYSDATE, 'DY')IN ('SUN','SAT') THEN
    raise exp_weeknds;
  ELSE
    DBMS_OUTPUT.put_line( 'Record processed!');
  END IF;
EXCEPTION
WHEN exp_weeknds THEN
  dbms_output.put_line('Cannot process the record as its weekend!');
END ;
/

pls1

Scenario 3. Write PL/SQL program to show hiding concept of oops in PL/SQL.

Solution 3: See below example for PL/SQL package PKG_TEST, it has two procedure in its body but only one is declared in package spec. The one which is not declared but present in the body is basically hidden from outside world & can be used internally only.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_INTERNAL(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   -- This procedure is not declared in package specification.
   DBMS_OUTPUT.PUT_LINE ('Calling PRC_INTERNAL procedure');
  END PRC_INTERNAL;
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling PRC_TEST procedure');
    PRC_INTERNAL('100');
  END PRC_TEST;
END PKG_TEST;
/

pls3

Now try calling the procedure which was not defined in declaration.

EXEC PKG_TEST.PRC_INTERNAL(‘100’);

pls4

Calling procedure PRC_TEST which is calling the internal procedure PRC_INTERNAL.

pls5

Scenario 4. Can we write commit in PL/SQL exception block.

Solution 4: Yes, but it is not recommended to write the commit. As if you want to logs the exception you can use pragma autonomous transaction.  Below example showing that we can use commit in exception block.

CREATE TABLE err_logs
  (
    error_number  VARCHAR2(25),
    error_message VARCHAR2(1000)
  );

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
  SELECT to_number('X') INTO ln_num FROM dual;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
  INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END;
/

pls6

Scenario 5. What is pragma autonomous transaction?

Solution 5: Fire an transaction which is independent of calling transaction and return to the calling transaction without affecting it’s state.

CREATE OR REPLACE PROCEDURE prc_log_errors (lv_err_code  IN  VARCHAR2,lv_err_msg IN VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END prc_log_errors;
/

If you see below example, DML transaction performed before exception occurred. As we have called the procedure with pargma autonomous which has commit in it, still my main translation data is roll backed.

CREATE TABLE test_table (TEST_ID NUMBER);

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
 INSERT INTO test_table(TEST_ID)
    VALUES(1);
     INSERT INTO test_table(TEST_ID)
    VALUES(2);
  SELECT to_number('X') INTO ln_num FROM dual;
  commit;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
 prc_log_errors(lv_err_code,lv_err_msg);
 rollback;
END;
/

pls6

pls5

Scenario 6. Can you fire DDLs in function?

Solution 6: Yes using dynamic sql, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
  BEGIN
    EXECUTE immediate 'drop table TEST_TABLE';
     dbms_output.put_line('Table dropped');
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
EXECUTE immediate 'CREATE TABLE TEST_TABLE (TEST_ID NUMBER)';
dbms_output.put_line('Table created');
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/

pls4

Now calling that function in SQL statement:

pls5

Same thing with the DML also you can use in function but cannot call using SQL statement. I have tried calling function from sql which has DML & commit;

pls6

Scenario 7. Can you call procedure from sql statement?

Solution 7: No

CREATE OR REPLACE PROCEDURE PRC_SQUARE(
    INPUT_NUM IN NUMBER,OUTPUT_NUM OUT NUMBER )
IS
BEGIN
OUTPUT_NUM:= INPUT_NUM*INPUT_NUM;
END;
/

SELECT PRC_SQUARE(2) FROM dual;

pls6

Call procedure using below method.

declare 
ln_out NUMBER; 
begin 
PRC_SQUARE(6,ln_out); 
dbms_output.put_line('Square is: '|| ln_out); 
end; /

pls5

Function you can using SQL statements. As it has always return type.

create or replace FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/

pls6


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Oracle SQL Scenario Questions – Part 3

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover RPAD, PLAD, Null Function, Instr, Substr, regex_substr, translate.


 

Scenario 1. What will be the output of following SQL statement.

select rpad('TEST      ',12, '*') from dual;

length (‘TEST      ‘) = 10

Output:

rpad

Scenario 2. What will be the output of following SQL statement.

select lpad('1234',12, '*') from dual;

lpad

Scenario 3: What will be the output of following SQL statement.

select nullif(12,12) from dual;
select nullif(12,13) from dual;

nullif

NULLIF (expression 1, expression 2)

  • NULLIF function returns NULL if expression 1 and expression 2 are equal.
  • NULLIF function returns expression 1 if not equal.
  • expression 1 cannot be literal null.
  • Datatypes should be same for both expression.
select nullif('NULL',12) from dual;

nullif

Scenario 4: What will be the output of following SQL statement.

select NVL2('', 'Name avalailbe', 'Name not avalailbe') from dual;
select NVL2('Ram', 'Name avalailbe', 'Name not avalailbe') from dual;

vnl2

NVL2( input_string, value_if_not_null, value_if_null )

You can substitutes a value when a null value is encountered as well as when a non-null value is encountered.

Scenario 5: Rotating columns to rows, see below screenshot. See below source data.

unpv

select * from sales_data
unpivot
( SALES for MONTH_ in (JAN, FEB, MAR, APR) );

unpv2

Scenario 6: Draw this pattern.

* 
* * 
* * * 
* * * * 
* * * * *
select rpad('*', rownum, '*') from dual connect by rownum <=5;

star

Scenario 7: Draw this pattern.

* * * * * 
* * * * 
* * * 
* * 
*
select rpad('*', rownum, '*') from dual connect by rownum <=5
order by 1 desc;

star

Scenario 8: What will be the output of following SQL statement.

select translate ( 'HeXXo WorXd' , 'X', 'l') from dual;

translate

Scenario 8: Extract the state code from these addresses.

insttr

Option 1: Using instr & substr

select ADDRESS1,  instr(ADDRESS1,','),
trim(substr(address1,instr(ADDRESS1,',')+1)) state_code from address;

instr2

Option 2: Easy way as you know that codes are last two char of string.

select address1, substr(address1,-2) state_code from address;

subsst

Scenario 8: Extract the state code from these addresses. Here after the 2nd occurrence of delimiter ~.

kerala

select ADDRESS1, regexp_substr(ADDRESS1, '[^~]+',1,2) state_name,
regexp_substr(ADDRESS1, '[^~]+',1,3) state_code from address;

kk


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL Scenario Questions – Part 2

Oracle SQL Scenario Questions  – Part 2

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover analytical functions, list aggregate, date functions,  union all, pivot & prime numbers.


Scenario 1. In below example remove the duplicate. Example: Records 1 & 2 are identical.

1Src

CREATE TABLE "DISTANCE"
  (
    "SOURCE_NAME" VARCHAR2(20 BYTE),
    "DEST_NAME"   VARCHAR2(20 BYTE),
    "DISTANCE"    NUMBER
  );

Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','JAIPUR',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('JAIPUR','DELHI',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','PUNE',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('PUNE','DELHI',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','SPITI',731);

COMMIT;

Solution:  You can use the LEAST/GREATEST function to find out the dupes.

SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME),
GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance )AS RNUM
FROM DISTANCE d;

2

From this data set you can filter the rows using the RNUM column to fetch distinct rows.

WITH data_set AS
(SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance ) AS RNUM
FROM DISTANCE d
)
SELECT * FROM data_set WHERE RNUM =1;

3

Scenario 2. In below example customer data is present for product with dates. Fetch the customer who came in all months of quarter at least once. Like Jan, Feb, Mar or Apr, May, June etc. Notice in below data-set C1 & C3 came in all months of quarter Q1 & Q2 respectively.

11

Solution: You can use the date functions to find out the months & quarter.

SELECT CUST_ID,
listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
ORDER BY BILL_DATE) months_name,
TO_CHAR(BILL_DATE,'q')
FROM SALES
GROUP BY CUST_ID,
TO_CHAR(BILL_DATE,'YYYY'),
TO_CHAR(BILL_DATE,'q');

111

WITH dataset AS
  (SELECT CUST_ID,
    listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
  ORDER BY BILL_DATE) months_name,
    TO_CHAR(BILL_DATE,'YYYY') year_,
    TO_CHAR(BILL_DATE,'q') quarter_
  FROM SALES
  GROUP BY CUST_ID,
    TO_CHAR(BILL_DATE,'YYYY'),
    TO_CHAR(BILL_DATE,'q')
  )
SELECT *
FROM dataset
WHERE months_name IN ('01||02||03','04||05||06','07||02||09','10||11||12') ;
 

12

Scenario 3.  Output of this SQL: Select count(1), SUM(1) from dual where 1=2;

Select count(1), SUM(1) from dual where 1=2;

333

Scenario 4.  Print 1st day of Month, Last Day, Mid, Week, Quarter for given date.

WITH dataset AS
  (SELECT to_date('10-JAN-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('20-FEB-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('05-JUN-2019','dd-mon-yyyy') date_ FROM dual
  )
SELECT to_char(date_,'DD-MM-YYYY'), to_char(date_,'Q') Quarter ,to_char(date_,'W') week,
trunc((date_),'MM') FIRST_DATE_OF_MONTH, last_day(date_) LAST_DATE_OF_MONTH
FROM dataset;

4444

Print Day of the date:

day

Scenario 5.  In below example you have to print date twice. See sample date.

Input:  555  Output Required: 6666

Solution:

WITH data_set AS
  ( SELECT id_ FROM dataset
  UNION ALL
  SELECT id_ FROM dataset
  )
SELECT * FROM data_set ORDER BY 1;

555

Scenario 6.  In below example you have ~ tilde separated date in column VAL. You have to break the string using SQL like below. Refer required output screenshot.

Input: 7777 Required Output: 7777

CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));

INSERT INTO  DATASET VALUES (1, 'A~B~C~D');
INSERT INTO  DATASET VALUES (2, 'X~Y~Z');
INSERT INTO  DATASET VALUES (5, 'K');
COMMIT;

Solution: 

WITH data_set AS
  ( SELECT id_,val, regexp_count(val,'~') sep_cnt FROM dataset
  ),
  numseries AS
  (SELECT rownum rnum FROM DUAL CONNECT BY rownum =numseries.rnum
ORDER BY 1;

7777

Scenario 7.  Similar to Scenario 6, In below example you have data in VAL column & you have bring it in one row separated by double pipe ||.

Input : 7777 Required Output: 7777

DROP TABLE DATASET;
CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'A');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'B');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'C');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'D');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Y');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Z');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'X');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('5', 'K');
COMMIT;

Solution: 

SELECT ID_,
  listagg (VAL, '||') within GROUP (
ORDER BY VAL) NEW_VAL
FROM dataset
GROUP BY ID_;

7777

Scenario 8. Rotating rows to column, see below screenshot.

Input: 7777 Output: 77

Solution : Using Pivot Clause

SELECT *
FROM
  (SELECT prod_id, month_, sales FROM sales_data
  ) pivot( MAX(sales) FOR month_ IN ('Jan' AS JAN, 'Feb' AS FEB, 'Mar' AS MAR, 'Apr' AS APR) );

 

7777

Scenario 9 : Print prime numbers using SQL

WITH a AS
  ( SELECT rownum rn FROM dual CONNECT BY rownum <=20
  ),
  b AS
  (SELECT rownum rn FROM dual CONNECT BY rownum =b.rn
  ),
  prime_num AS
  (SELECT rn FROM div WHERE mod_=0 GROUP BY rn HAVING COUNT(rn1)=2 ORDER BY 1
  )
SELECT * FROM prime_num;

prime


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Interval Partitioning

In the previous blog Oracle Range Partition we have explored how we can create range partition in oracle using date as partition key. Also we have seen one example where the range partition key is defined maximum upto ’01-APR-2019′ (Non-inclusive). But we are trying to insert ’20-OCT-2019′ date data, which is not getting mapped to any partition, that’s why ORA-14400 error occurred. See below error report provided by oracle.

SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”

Here comes the Interval Partition, which is extension of  Oracle Range Partition in which, interval partitions are automatically created by the database when data is inserted into the partition.

Monthly Partition:

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  INTERVAL
  (
    NUMTOYMINTERVAL(1, 'MONTH')
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

After that we have inserted the data of future months, i.e. SEP & OCT for which Partitions were not available. See below insert statements.

INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567890', 'ABC', TO_DATE('20-SEP-19', 'DD-MON-RR'));
INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567891', 'ABC', TO_DATE('20-OCT-19', 'DD-MON-RR'));
COMMIT;

After committing the data let see the automatic partitions created by Oracle :

2

Notice that a system generated partition named SYS_P601 & SYS_P402 has been created after inserting rows with a partition key greater than the provided partition.

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT partition (SYS_P601);

It would be really painful to look up the system generated partition name every time, if you have to query directly partition. Therefore, oracle provided a syntax to specify a partition is by using the partition for (DATE) clause in the query: See below example:

partition for (to_date(’15-OCT-2019′,’DD-MON-YYYY’));

SELECT * FROM T_ACCOUNT partition  for (to_date('01-SEP-2019','DD-MON-YYYY'));

The following restrictions apply on Interval Partition:

  • Only one partitioning key column allowed (NUMBER or DATE)
  • Minimum one partition must be defined when the table is created.
  • MAXVALUE partition cannot be defined.
  • NULL values are not allowed in the partition column.
  • Can’t be used at the subpartition level.
  • Not supported for index-organized tables.
  • You cannot create a domain index on an interval partitioned table.

 

Daily Partitions Example:

CREATE TABLE T_TRANSACTIONS
  (
    TRANSACTION_KEY NUMBER,
    AMOUNT          NUMBER,
    CUST_ID         NUMBER,
    TRANSACTION_DATE DATE
  )
  PARTITION BY RANGE
  (
    TRANSACTION_DATE
  )
  INTERVAL
  (
    NUMTODSINTERVAL(1, 'day')
  )
  (
    PARTITION P_01012019 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

 


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Range Partition

Range partitioning is useful when data has some sort of logical range. Example dates or numbers. See below example where monthly range partitions created based on date column in account table.

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
    PARTITION P_JAN2019 VALUES LESS THAN (TO_DATE('01-FEB-2019','DD-MON-YYYY')),
    PARTITION P_FEB2019 VALUES LESS THAN (TO_DATE('01-MAR-2019','DD-MON-YYYY')),
    PARTITION P_MAR2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY'))
  );

 

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT PARTITION(P_MAR2019);

 

Check partitions metadata in user_tab_partitions & subpartitions.

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_ACCOUNT';

1

To check the Partition KEY of the table, use below query:

SELECT partition_name,
  column_name,
  high_value,
  partition_position
FROM USER_TAB_PARTITIONS PART ,
  USER_PART_KEY_COLUMNS PART_KEY
WHERE table_name ='T_ACCOUNT'
AND PART.table_name = PART_KEY.name;

In the above example any record having acc_date less than ’01-JAN-2019′ will go to P_DEC2018 partition. See below example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2010', 'DD-MON-YYYY')
  );
COMMIT;
SELECT * FROM T_ACCOUNT PARTITION (P_DEC2018) ;

In the this example any record having acc_date greater than ’31-MAR-2019′ will error out, as no partition exists for that date.

Example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2019', 'DD-MON-YYYY')
  );

Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification

1

Here in this example we can see the partition key is defined maximum upto 2019-04-01 (Non-inclusive) . But we are trying to insert 20-OCT-2019, which is not getting mapped to any partition, that’s why this error occured as date value which we are trying to insert is T_ACCOUNT not satisfying the partition key range criteria.

Solution: To fix it, you may have to add new partitions or add a Maxvalue partition.

ALTER TABLE T_ACCOUNT ADD partition P_OCT2019 VALUES less than (TO_DATE('01-NOV-2019', 'DD-MON-YYYY'));
ALTER TABLE T_ACCOUNT ADD partition P_MAXVALUE VALUES less than (MAXVALUE);

You can also go with : Oracle Interval Partitioning to resolve this issue, but this not preferable.

Drop Partition:

Once historical data is no longer required for business analysis, after retention period the whole partition can be dropped.

DROP PARTITION P_DEC2018;

Updating Key:

When you are trying to update the partition key column for any record.

UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';

Error report:
SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 – “updating partition key column would cause a partition change”
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old
partition key.

You can’t move a row with an update: Row movement disabled, which is the default option.

Solution:  We can enable Row movement to partition table T_ACCOUNT. It allows rows to be moved across partitions.

ALTER TABLE T_ACCOUNT ENABLE ROW MOVEMENT;
UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';
COMMIT;

 


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Data Integrator | Master & Work Repositories Relationships

Oracle Data Integrator | Master & Work Repositories Relationships

Folks,

In this blog we will explore the relationship between ODI Master & Work Repository.

You can refer this blog  for better understanding of ODI Master & Work Repositories, how they differ from each other & how to setup new repository in ODI Studio using Oracle technology.


Master & Work Repositories Relationships

Master Repository is usually associated with multiple Work Repositories.

Each Work repository can associate with only one Master Repository.

Case 1)  When single Work Repository is attached to the Master Repository.

  • When we have to isolate any environment from the rest of the environments.
  • Usually case of Production/Pre-Production environment.

In below figure we can see that production/pre-prod master repository is isolated from other repositories. Its like one to one relationship. No information sharing.

 

Case 2)  When multiple Work Repository is attached to same Master Repository.

  • See below Figure 2, both work repositories are sharing same master repository.
  • Basically both are sharing the same Topology and Security data.
sharing
Figure 2

 

In real word production environment is isolated from rest of the environments.

practical.PNG


Thanks!

Happy Learning! Your feedback would be appreciated!