Script to assign a concurrent program to a request group from backend

DECLARE
  l_program_short_name  VARCHAR2 (200);
  l_program_application VARCHAR2 (200);
  l_request_group       VARCHAR2 (200);
  l_group_application   VARCHAR2 (200);
  l_check               VARCHAR2 (2);
BEGIN
  --
  L_PROGRAM_SHORT_NAME := '<CONCURRENT PROGRAM SHORTNAME>';
  L_REQUEST_GROUP := '<REQUEST GROUP NAME>';
  begin
  --Query to find application of request group
    SELECT APPLICATION_NAME
    INTO l_group_application
    FROM FND_APPLICATION_TL FA,
      FND_REQUEST_GROUPS FRG
    WHERE FA.APPLICATION_ID   =FRG.APPLICATION_ID
    and FRG.REQUEST_GROUP_NAME=L_REQUEST_GROUP;
    --Query to find application of concurrent program
    SELECT APPLICATION_NAME
    INTO l_program_application
    FROM FND_CONCURRENT_PROGRAMS FC,
      FND_APPLICATION_TL FA
    WHERE FA.APPLICATION_ID       =FC.APPLICATION_ID
    and FC.CONCURRENT_PROGRAM_NAME= L_PROGRAM_SHORT_NAME ;
 --   query to check if concurrent program already assigned to request group
    SELECT 'Y'
    INTO l_check
    FROM fnd_request_groups frg,
      FND_REQUEST_GROUP_UNITS FRGU,
      fnd_concurrent_programs fcp
    WHERE frg.request_group_id      = frgu.request_group_id
    AND frg.application_id          = frgu.application_id
    AND frgu.request_unit_id        = fcp.concurrent_program_id
    AND frgu.unit_application_id    = fcp.application_id
    AND fcp.concurrent_program_name = L_PROGRAM_SHORT_NAME
    AND FRG.REQUEST_GROUP_NAME      = L_REQUEST_GROUP ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    l_check:='N';
  WHEN OTHERS THEN
    dbms_output.put_line ('Adding Concurrent Program to Request Group Failed: '|| SQLERRM);
  END;
  IF(l_check <> 'Y') THEN
    apps.fnd_program.add_to_group (program_short_name => l_program_short_name, program_application => l_program_application, request_group => l_request_group, group_application => l_group_application );
    dbms_output.put_line ('Program assigned to request group: '|| l_request_group);
  ELSE
    dbms_output.put_line ('Program already assigned to request group: '|| l_request_group);
  END IF;
  --
  COMMIT;
END;

Converting rows into column using pivot function

we can convert row into columns using pivot function
let us consider the following data as sample data

DEPTNO	ENAME	JOB
10	ALLEN	ANALYST
10	JONES	CLERK
10	FORD	MANAGER
10	ABCD	PRESIDENT
10	CLARK	SALESMAN
20	MILLER	ANALYST
20	SMITH	CLERK
20	WARD	MANAGER
20	efgh	PRESIDENT
20	MARTIN	SALESMAN
30	SCOTT	ANALYST
30	TURNER	CLERK
30	ADAMS	MANAGER
30	BLAKE	PRESIDENT
30	KING	SALESMAN

now create a table as emp_jobs with this data


using pivot function
we are converting rows into columns

select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));

the output is in the form

DEPTNO	PRESIDENT'	MANAGER'	CLERK'	ANALYST'	SALESMAN'
20	efgh	WARD	SMITH	MILLER	MARTIN
10	ABCD	FORD	JONES	ALLEN	CLARK
30	BLAKE	ADAMS	TURNER	SCOTT	KING

but while using pivot function aggregate function should be used 

we can also achieve this conversion without using pivot function using this sql query

 select
   a1.deptno,
   a1.ename  AS PRESIDENT,
   a2.ename AS MANAGER,
   a3.ename AS CLERK
from
   emp_jobs  a1,
   emp_jobs a2,
   emp_jobs  a3
   where
   a1.deptno=a2.deptno
   and a2.deptno=a3.deptno
   and a3.deptno=a1.deptno
   and a1.job = 'PRESIDENT'
   and a2.job = 'MANAGER'
   and a3.job ='CLERK';

How to Import data from Excel to Oracle table with SQL Developer

connect to database

expand it and it will display all the object types

 Right click on the object type table


Click on Import data

browse the file and click on open

Click on next

Give the table name that to be created
and click on next

selected columns are the columns that ll appear in the table
click on next

Check all the data types for the columns defaultly it will be varchar2
after confirming all the columns  click on next


Now click on finish




Table with given data is created