package template

CREATE OR REPLACE PACKAGE CREATE_PKG AS
PROCEDURE PURCHASE_ORDER(P_PO_HEADERS_OBJ IN OUT WEB_PO_HEADERS_ALL_OBJ,
                         P_PO_LINES_TBL   IN OUT WEB_PO_LINES_ALL_TBL,
                         OUT_PUT          OUT VARCHAR2,
                         RETCODE          OUT VARCHAR2);
END CREATE_PKG;
/

CREATE OR REPLACE PACKAGE BODY CREATE_PKG AS
PROCEDURE PURCHASE_ORDER(P_PO_HEADERS_OBJ IN OUT WEB_PO_HEADERS_ALL_OBJ,
                         P_PO_LINES_TBL   IN OUT WEB_PO_LINES_ALL_TBL,
                         OUT_PUT          OUT VARCHAR2,
                         RETCODE          OUT VARCHAR2) IS

V_PO_NUMBER NUMBER;
BEGIN

BEGIN
SELECT NEXT_PO INTO V_PO_NUMBER FROM PO_VENDORS_ALL;
P_PO_HEADERS_OBJ.PO_NUMBER := V_PO_NUMBER;
EXCEPTION WHEN no_data_found THEN
OUT_PUT:='Vendor Not Defined';
RETCODE:='-1';
RETURN;
END;

BEGIN
SELECT PO_HEADERS_ALL_S1.NEXTVAL
INTO P_PO_HEADERS_OBJ.PO_HEADER_ID
FROM DUAL;
END;
INSERT INTO PO_HEADERS_ALL
(PO_HEADER_ID,
 PO_STATUS,
 PO_NUMBER,
 CREATED_BY,
 CREATION_DATE,
 EXPECTATION_DATE,
 VENDOR_ID,
 DELIVER_TO_LOCATION)
VALUES
(P_PO_HEADERS_OBJ.PO_HEADER_ID,
 P_PO_HEADERS_OBJ.PO_STATUS,
 P_PO_HEADERS_OBJ.PO_NUMBER,
 P_PO_HEADERS_OBJ.CREATED_BY,
 P_PO_HEADERS_OBJ.CREATION_DATE,
 P_PO_HEADERS_OBJ.EXPECTATION_DATE,
 P_PO_HEADERS_OBJ.VENDOR_ID,
 P_PO_HEADERS_OBJ.DELIVER_TO_LOCATION);
COMMIT;

FOR INDX IN NVL(P_PO_LINES_TBL.FIRST, 0) .. NVL(P_PO_LINES_TBL.LAST, -1)
LOOP
P_PO_LINES_TBL(INDX).PO_LINE_NUMBER := INDX;
P_PO_LINES_TBL(INDX).PO_HEADER_ID := P_PO_HEADERS_OBJ.PO_HEADER_ID;
INSERT INTO PO_LINES_ALL
(PO_HEADER_ID,
 PO_LINE_ID,
 PO_LINE_NUMBER,
 ITEM_ID,
 ITEM_NUM,
 QTY,
 UOM,
 UNIT_PRICE)
VALUES
(P_PO_LINES_TBL(INDX).PO_HEADER_ID,
 P_PO_LINES_TBL(INDX).PO_LINE_ID,
 P_PO_LINES_TBL(INDX).PO_LINE_NUMBER,
 P_PO_LINES_TBL(INDX).ITEM_ID,
 P_PO_LINES_TBL(INDX).ITEM_NUM,
 P_PO_LINES_TBL(INDX).QTY,
 P_PO_LINES_TBL(INDX).UOM,
 P_PO_LINES_TBL(INDX).UNIT_PRICE);
END LOOP;

BEGIN                                       
V_PO_NUMBER:=V_PO_NUMBER+1;
UPDATE PO_VENDORS_ALL SET NEXT_PO=V_PO_NUMBER WHERE PO_VENDOR_ID=P_PO_HEADERS_OBJ.VENDOR_ID;
END;  

COMMIT;
RETCODE := '0';
OUT_PUT := 'PO#' || P_PO_HEADERS_OBJ.PO_NUMBER || ' created';

END;
END CREATE_PKG;
/

Tunnel

-- Create table create table APPLSYS.FND_USER ( USER_ID NUMBER(15) not null, USER_NAME VARCHAR2(100) not null, LAST_UPDATE_DATE DATE not null, LAST_UPDATED_BY NUMBER(15) not null, CREATION_DATE DATE not null, CREATED_BY NUMBER(15) not null, LAST_UPDATE_LOGIN NUMBER(15), ENCRYPTED_FOUNDATION_PASSWORD VARCHAR2(100) not null, ENCRYPTED_USER_PASSWORD VARCHAR2(100) not null, SESSION_NUMBER NUMBER not null, START_DATE DATE not null, END_DATE DATE, DESCRIPTION VARCHAR2(240), LAST_LOGON_DATE DATE, PASSWORD_DATE DATE, PASSWORD_ACCESSES_LEFT NUMBER(15), PASSWORD_LIFESPAN_ACCESSES NUMBER(15), PASSWORD_LIFESPAN_DAYS NUMBER(15), EMPLOYEE_ID NUMBER(15), EMAIL_ADDRESS VARCHAR2(240), FAX VARCHAR2(80), CUSTOMER_ID NUMBER(15), SUPPLIER_ID NUMBER(15), WEB_PASSWORD VARCHAR2(240), USER_GUID RAW(16), GCN_CODE_COMBINATION_ID NUMBER(15), PERSON_PARTY_ID NUMBER ) tablespace APPS_TS_SEED pctfree 5 initrans 10 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); -- Create/Recreate indexes create index APPLSYS.FND_USER_F1 on APPLSYS.FND_USER (UPPER(EMAIL_ADDRESS)) tablespace APPS_TS_SEED pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create index APPLSYS.FND_USER_N1 on APPLSYS.FND_USER (EMPLOYEE_ID) tablespace APPS_TS_SEED pctfree 0 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create index APPLSYS.FND_USER_N2 on APPLSYS.FND_USER (CUSTOMER_ID) tablespace APPS_TS_SEED pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create index APPLSYS.FND_USER_N3 on APPLSYS.FND_USER (SUPPLIER_ID) tablespace APPS_TS_SEED pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create index APPLSYS.FND_USER_N4 on APPLSYS.FND_USER (PERSON_PARTY_ID) tablespace APPS_TS_SEED pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create index APPLSYS.FND_USER_N5 on APPLSYS.FND_USER (USER_GUID) tablespace APPS_TS_SEED pctfree 10 initrans 11 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create unique index APPLSYS.FND_USER_U1 on APPLSYS.FND_USER (USER_ID) tablespace APPS_TS_SEED pctfree 0 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); create unique index APPLSYS.FND_USER_U2 on APPLSYS.FND_USER (USER_NAME) tablespace APPS_TS_SEED pctfree 0 initrans 11 maxtrans 255 storage ( initial 16K next 1M minextents 1 maxextents unlimited ); -- Grant/Revoke object privileges grant select, insert, update, delete, references, alter, index on APPLSYS.FND_USER to APPS with grant option; grant update on APPLSYS.FND_USER to GEDBA; grant select, insert, update on APPLSYS.FND_USER to GESSS; grant select, update on APPLSYS.FND_USER to IDMADMIN; grant select on APPLSYS.FND_USER to IFLOW; grant select on APPLSYS.FND_USER to SABRIX with grant option; grant select, update on APPLSYS.FND_USER to SACOE;

Oracle Framework Interview questions

Set Of All The Interview Questions

Convert Row To Column

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

COLUMN employees FORMAT A50


--Here is the interview question.
--Write a sql query to transpose rows to columns.

--SQL to create the table
Create Table Countries
(
Country varchar2(50),
City varchar2(50)
);

Insert into Countries values ('USA','New York');
Insert into Countries values ('USA','Houston');
Insert into Countries values ('USA','Dallas');

Insert into Countries values ('India','Hyderabad');
Insert into Countries values ('India','Bangalore');
Insert into Countries values ('India','New Delhi');

Insert into Countries values ('UK','London');
Insert into Countries values ('UK','Birmingham');
Insert into Countries values ('UK','Manchester');

--Using PIVOT operator we can very easily transform rows to columns.
Select Country, City1, City2, City3
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3)
) Piv

Other Resources

Web

• Various Code Sample

Video
https://www.youtube.com/watch?v=C0mQqDnF7wQ

Submit Request from PLSQL

Net resource

Oracle Documentation API

Oracle Mind-Map

7Vtdc6JKE/41ucwW38KlGnLWquzqavac3atTREfDuygW4m6yv35noBvmAxOMgPGt443a4AzzdD/N0z14ZQ7XT38lwfbxU7wg0ZWhLZ6uzJsrw7g2ewZ9Y5ZnsNhWL7esknCR27TSMAt/k9yoo3UfLshOODGN4ygNt2DUc+M83mzIPBVOXMaROMU2WOHwpWE2DyK0frBL+z/hIn0Eu65xI3wk4eoRZnJtOPAQzH+skni/gRmvDPM2e+WHd+kzTrIgy2AfpdeZCeBZBzgVDPek5V8t280Nz2AwDROvZCMs5XccrwVDQnYlmgBICJcN3x/iZEESYeIo3PzgkTV96t4kjukP2af105BEzMWi824PHC1gTshG8A384OuOJOOH/zHH0amDBxo92UnjJJhTvAytv91G4TxIw3iTD4UjZ8CJk3IYZ44g7FT9yhws400KkaW77HsYRcM4ivOVm7fecOANqX2XJvEPwh3x+x59FUcwIkxmeQwW8S+YQF0lLPwnSVICZCjhpGwh8ZqkyTPzdH7UsSAUgCiuA0P8KsPQxGB75CKQxmZuDMBtq2LsEmf6AWDCrxzy1a4DJvwMoj2AOrmbfblTwlmCWoRWApA74tqGppWg89Bm7oIJ2Jgc1Kfj3MP8Azg7PVgoh7NuVeCM2B8Bs4opzMXBRzaLfpJky9vEG2qUAKFLUcHrO/qwzEgKGrt4n8xhfIiqNEhWBFeSm8hCSIYqYgmJKPF+iinklOXrGlwNF1Sz75/v+9+OiyrPHFq9mypgqHt9PaPnS4RtgK26yFbbrIgiTKl8FGFkHQHjoRzZn4zElKjnF1U3KTaLaSNJ0HVFcrYJa50cqOuWErAqgymPMF0BhdsmNaDCk7rg1qu0LrSZhdICTncqwMYLEu44aKxCG+abxCG9/sOTGTgZjpEvD37GKwp5JOPQZeNIOSrKSJn7Cxhq5SvDhYUK7nciisPgIaGfVuyTEhA02Jn9MV0zpjJqZFIseMhOoDpuEEThiqm3OXUy018DRhEqc6I+HFiHiwU7e5DxfVBoSy5cltmrmncYtiCKYeZS5NVgZO3kf8BN8AuU/dIA8XK5o5F7mnt043LYifeL/9jZHDsdT3H/7Xj66dIVqq5ZENdYCrg1JaoDpeEpGs3o4QWXqH6ka2Nld5ZR1tswq8yWcbK+NNlGoYWYAWh7HsDIQ2u2pP6NHvinIfnPMhm9hG/spkIbF/D1O4BPnsKUO0S/sSM1iwakFp/DishornCoTiKeJ5XC+huzkTyQg3KyhWSEZWTzvs2ciZ4tvZn5lvds2bt6g2+78ayuOaJHPLl5Ude1VO6+MlKTvnXUG83nm3+n/pev/uz+lSSoprTtPtmyFNpAOnNFEKwqkdBQvVQBC9zqO5RffMo7TAtMf/kR1GOv8KKQaGLOA9fX1222WMJi7mpBtnkSmew350ldVOsWQtYGl1TRNpz6/XufllPUz08B9d3Uv5+O/L9509fJjXjOjX/nM8O5uCd3gEyvJvVQcZxEPVcVaV1Sr1m1UbQuROZBoNRmno2gdFAx2agRUVnIjKlLPUu6jdmoI9roZqjd10voZhTB/v/dzTBcqA74HZebMfR4zyQvxELUNuHe1om8wMnfubw4RV2gy4/oConpos0cp0hsB29xR4t13ZA23bQWs5y6cdnf7WgiWjP/0B1l3DY5D6Wk3o6jgcDshlJqIfMeKYX3wLdxqu72ZskpMTgNE1zUCqdQ6xTCASY7vgCWaj9lpCY5BQhxcTP6PLof9e9GszNqcF0zJRFe1Sltj03nrX8/6E7vsAx3KYl4IZ5/npAkpAvNnvkpRhONJzAPoqQ282Txe91msSzdOd9cLOuaLuYLC5/qaIN4arU8mY5vR3dnZJ0nec3E2qcT0mH5/A4rX4Vxp3Dp2OrX8sSovMZ+aBtckhhgy56t3XjqSVTCsroFKnlqy2TqT8bT+9mlbxhajtiMcHGpXTzSRtNBvf3ChGzjJPfiBe0YythWbhi2h+3FbBhiyAlJDEOj7W2loi8hb5gfm4/ktp4rJ7Ym8xHI9eZ8e6A98VIF9nbfduNZR9qZ8JCOx3rWkSoFT5Z/TXpW7UDMPvp39NFpbTacjiav7Ri++xuOLRWxtA6DRNXJEyqe2sWYkXRP/xBy4bjKzyt0jStk0cYzknxHOfxwQ83OTxECZ3mGwRZbeB42N4+vJCUeeWx7s62kBM2KFqSE4sKXPF/TvxXPUHbkX0+6U9CWrUSu+tvqsAp0L25kteFetVEw7qv/PbvwP+uUvugiIZpYGXeZEBVhXo8v53ymC//jeTJh1ISIqfZkxtCv5Z8Y89PLP6ya/h8=

SyntaxHighlighter

int x = foo();  /* This is a comment  This is not code
  Continuation of comment */
int y = bar();