Like Operation in expression transformation

To check if a sub string is present in a string like '%%' is used in SQL format. Equivalent regular expression informatica will be


REG_MATCH(Employee,'.*Be.*')


Employee Result

Beat                 TRUE

Bearo                 TRUE

Kartheek         FALSE

Deb                 FALSE

Meta data table to know DML operations on table

Use below table if you want to know recent updates, inserts or deletions into table.

ALL_TAB_MODIFICATIONS -describes tables accessible to the current user that have been
                                                     modified since the last time statistics were gathered on the tables

DBA_TAB_MODIFICATIONS - provides such information for all tables in the database.

USER_TAB_MODIFICATIONS- provides such information for tables owned by the current user

VI Command

To use vi: vi filename.      -->this is a command to open a file
To get insert mode of vi: i. -->enter i to change vi mode to insert for making changes to file
To enter vi command mode: [esc] Counts -->enter esc if you are done with changes in insert mode 
                                                                          and if u want to exit(you have to run below commands                                                                            for exiting after clicking esc)
To exit vi and save changes: ZZ or :wq. --> This is a command to close a file with saving
To exit vi without saving changes: :q!   -->This is a command to close a file with out saving

Query to delete duplicate records in oracle

delete from
   customer
where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1)

LISTAGG - Concatenate multiple rows into a single delimiter-separated string

It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

LISTAGG is a in-built function in Oracle that lets you concatenate multiple rows of data into a single delimiter-separated string. LISTAGG was introduced in Oracle 11G R2, before which one would use the circuitous MAX(SYS_CONNECT_BY_PATH) or STRAGG methods for the same result.

Here’s how LISTAGG works.

Using the standard departments and employees tables of HR schema: list the employees in a comma-separated list against each department they belong to.

The SQL:

SELECT deptno
     , LISTAGG(empno, ',')
         WITHIN GROUP (ORDER BY empno)
         AS emp
FROM   emp
GROUP BY deptno;
When executed:

SQL> SELECT deptno
  2       , LISTAGG(empno, ',')
  3           WITHIN GROUP (ORDER BY empno)
  4           AS emp
  5  FROM   emp
  6  GROUP BY deptno;

deptno emp
------------- ---------------------------------------
           10 200
           20 201,202
           30 114,115,116,117,118,119
           40 203
           50 120,121,122,123,124,125,126,127,128,129

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name “DFF Name”,
ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”

FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘Give Title Name’
AND ffc.descriptive_flex_context_code like ‘Give Context Code Value’

ORDER BY att.column_seq_num

Fusion BIP security

Ensure that the queries which you are building drive through one or more of the following views:

PER_POSITION_SECURED_LIST_V

PER_PUB_PERS_SECURED_LIST_V

CMP_SALARY_SECURED_LIST_V

PER_ASSIGNMENT_SECURED_LIST_V

PER_DEPARTMENT_SECURED_LIST_V

PER_JOB_SECURED_LIST_V

PER_LDG_SECURED_LIST_V

PER_LEGAL_EMPL_SECURED_LIST_V

PER_LOCATION_SECURED_LIST_V

PAY_PAYROLL_SECURED_LIST_V

PER_PERSON_SECURED_LIST_V

PER_GRADE_SECURED_LIST_V