Oracle Shooter

SQL Overview --============================================================== =================================================================== SQL Overview :- These commands can be classified into the following groups based on their nature. DDL - Data Definition Language -------------------------------- 1 CREATE :- Creates a new table, a view of a table, or other object in the database. 2 ALTER :- Modifies an existing database object, such as a table. 3 DROP :- Deletes an entire table, a view of a table or other objects in the database. Ex:- CREATE TABLE XXSD_TEST_TL( EMP_NO VARCHAR2(10), EMP_NAME VARCHAR2(100), SALARY NUMBER ) select * from XXSD_TEST_TL ALTER TABLE XXSD_TEST_TL  ADD ( DOB DATE) DROP TABLE XXSD_TEST_TL --===================================================================== DML - Data Manipulation Language -------------------------------- 1 SELECT :- Retrieves certain records from one or more tables. 2 INSERT :- Creates a re

Action Set  :- Summary in Oracle Alert --================================================================== Query:- select EMPNO, ENAME, SAL , sysdate INTO &EMP_NO, &EMP_NAME, &EMP_SAL, &ALERT_DATE from scott.emp where deptno = 10 order by empno; --========================== Mail Content :- hi all, please find the detail =**= Enter summary template below this line =**= ** &EMP_NO  |  &EMP_NAME | &EMP_SAL =**= Enter summary template above this line =**= Regards Oracle team

Action Set :- Detail  in Oracle Alerts --==================================================================== Hi all, please find the below details.. &EMP_NO  |  &EMP_NAME | &EMP_SAL regard Oracle team --====================================================================

Submit Concurrent Request From Oracle Alerts --============================================================= CREATE OR REPLACE PROCEDURE APPS.XXSD_EMP_Alert_RPT(     errbuf           OUT VARCHAR2,    retcode          OUT NUMBER,    P_DATE VARCHAR2) AS        CURSOR C1 IS      select *    from XXSD_EMP_ALERT_HISTORY_TL    where ALERT_FIRE_DATE = TO_DATE(NVL(P_DATE, TO_DATE(SYSDATE)))    ;     BEGIN       fnd_file.put_line (          fnd_file.output,             'Employee_No'          || '|'          || 'Employee_Name'          || '|'          || 'Salary');       FOR I IN C1       LOOP          fnd_file.put_line (             fnd_file.output,                I.EMP_NO             || '|'             || I.EMP_NAME             || '|'             || I.SALARY);       END LOOP; EXCEPTION    WHEN OTHERS    THEN       fnd_file.put_line (fnd_file.LOG,                          'Entered INTO excep

ERROR:- ORA-00984 column not allowed here ERROR in Alerts ERROR:- APP-ALR-04020: Oracle Alert was unable to execute "&VALUE". ERROR LOG:- ALECDC: Check Periodic Alert +---------------------------------------------------------------------------+ Current system time is 19-MAY-2019 14:01:25 +---------------------------------------------------------------------------+ PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. values (7782, CLARK, 2450, 19-MAY-19 ) * ERROR at line 2: ORA-00984: column not allowed here APP-ALR-04020: Oracle Alert was unable to execute "&VALUE". Check that this file exists and that its read protection is set correctly. (ROUTINE=alssoa) (FILE=5244221/alr/lib/alspsa.c) (LINE=400) +---------------------------------------------------------------------------+ Start of log messages from FND_FILE +----------------------------------------------------------------------

how to use Sql Statement Script in Oracle Alert --===================================================================== CREATE TABLE XXSD.XXSD_EMP_ALERT_HISTORY_TL (   EMP_NO     VARCHAR2(240 BYTE),   EMP_NAME             VARCHAR2(240 BYTE),   SALARY               NUMBER(15),   Alert_FIRE_DATE  DATE   ) CREATE SYNONYM APPS.XXSD_EMP_ALERT_HISTORY_TL FOR XXSD.XXSD_EMP_ALERT_HISTORY_TL --======================================================================

Vi command in Unix --===================================================================== vi command:- visual Editor Modes Of Vi:- there are three modes 1. vi Modes :- vi mode: in this mode, most keys on the keyboard are defined to be a specific command. As the key or key sequence is issued, that command is executed. Ex:- h j k l :- cursor CTL-f :- forward screen CTL-b :- backward screen G :- end of file x :- delete character dw :- delete word dd :- delete line yy :- copy line in buffer D :- delete to EOL p :- paste/put buffer u :- undo last command CTL-r :- redo last undo (linux/vim) . :- repeat last editing command n :- find next occurrence of string cw :- change word # :- command - repeate command # times 2. input Modes :-  we are in Input mode if we press any key like "i - Inser", "a - For Append", "o - for open new line" <ESC> to end input i - insert a - append A - append at EOL

SQL Query Order of execution --================================================================= As per my understanding, the order of processing is 1. The FROM/WHERE clause goes first. 2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause. 3. GROUP BY is applied. 4. HAVING is applied. 5. SELECT is applied. 6. Distinct is Applied 7. ORDER BY is applied. SQL Statement Parsing:-  Syntax Check Semantic Chek Execution:-  FROM Clause Where Clause GROUP By Clause HAVING clause SELECT DISTINCT  ORDER BY 1. syntax check -  Are all keywords present OR Not Like "select" , "from" ,  2. semantic check against the dictionary - Are all table names spelt correctly, etc.

how to schedule concurrent request in oracle apps --====================================================================== Scheduling concurrent program in Oracle Apps Select “Single  Request ” and click on OK button. Enter the  concurrent program  name to be  scheduled  and the respective parameters and click on  Schedule  button. This will open up the  Schedule  window. Schedule  options: The below are the available  scheduling  options, we will see what they mean --============================================================== 5. Schedule options: The below are the available scheduling options, we will see what they mean. (i)  As Soon as Possible : Selecting this option will kick off the concurrent program immediately. (ii)  Once : Selecting this option lets you specify at what time and date you want to kick off the concurrent program. The concurrent program will be submitted only once at the specified time and date. (iii)  Periodically : Lets you schedul

How to generate the Public and Private Key of our System --================================================================== How to make password less login in Linux      ┌───────────────────────────────────────────────────────┐      │                • MobaXterm Personal Edition v11.1 •                │      │              (X server, SSH client and network tools)              │      │                                                                    │      │ ➤ Your computer drives are accessible through the /drives path     │      │ ➤ Your DISPLAY is set to 10.20.100.52:0.0                          │      │ ➤ When using SSH, your remote DISPLAY is automatically forwarded   │      │ ➤ Each command status is specified by a special symbol (✔ or ✘)    │      │                                                                    │      │ • Important:                                                       │      │ This is MobaXterm Personal Edition. The Professional edition     

The listed oracle ID is missing the event table Error while creating Oracle Event based Alert on custom table -===================================================================== --============================================================ create table XXSD.XX_ERROR_ON_ALERT_TL ( HDR_ID NUMBER,  NAME  VARCHAR2(100),  EMP_NO VARCHAR2(100),  GRADE VARCHAR(2), DOB DATE, last_update_date          DATE  default sysdate, last_updated_by           NUMBER  default -1, creation_date                 DATE        default sysdate, created_by                      NUMBER    default -1, last_update_login        NUMBER  ) --=================================================================== ------------------------------------------------------------------------------ --REGISTER TABLE--  ------------------------------------------------------------------------------ DECLARE  CURSOR C1 IS  SELECT (select APPLICATION_SHORT_NAME from fnd_ap

Event Base Alert in Oracle Apps --================================================================= select * from XXSD.XX_EVENT_BASE_ALERT_TL drop table XXSD.XX_EVENT_BASE_ALERT_TL --=================================== Create Table --=================================== create table XX_EVENT_BASE_ALERT_TL ( HDR_ID NUMBER,  NAME  VARCHAR2(100),  EMP_NO VARCHAR2(100),  GRADE VARCHAR(2), DOB DATE, last_update_date          DATE  default sysdate, last_updated_by           NUMBER  default -1, creation_date                 DATE        default sysdate, created_by                      NUMBER    default -1, last_update_login        NUMBER  ) --=================================== select check --===================================  select * from XX_EVENT_BASE_ALERT_TL --=================================== Check Table isregister in database or not --===================================  select * from all_objects  where object_name = 'XX_EVENT_BASE_ALERT_TL'

Periodic Alert in Oracle --===================================================================== Ondemand:- --===================== when you are developing a periodic alert so that you can test your alert at any point of time as you want. so you can selet this ondemand frequency. When you will sure that the alert is working fine, then you can change the frequency as per business need. On Day of the Month-- --===================== If you choose this frequency, Oracle Alert checks your alert on a monthly basis on the day number you enter in the Day field. If you want your alert checked on the last day of the month, enter the number 31. On Day of the Week-- --===================== If you choose this frequency, Oracle Alert checks your alert on the day of the week you enter in the Day field. Every N Calendar Days-- --===================== If you choose this frequency, must be enter a value in the Days field. Oracle Alert considers every day a calendar day, and does n

SQL Inline Query, Inline View and SubQuery --====================================================================== --==================================================== -- Sql Inline Query                                                                                                                                                              /* Inline Query is just the Select Statement */      --==================================================== Ex_1:- select * from scott.emp Ex_2:- select * from scott.emp where deptno = 20 --=================================================== Sql Inline View                                                                                                                                                      /* When we use SQL Subquery (A query within a query) in From clause of the select statement it is called inline view. */ --==================================================== ex_3:- select * from ( select * from scott.

Register View in Oracle Apps --===================================================================== --============================== Create View --============================== create or replace view XXSD_ALERT_V as select EMP_NO,GRADE from XXSD.XXSD_EVENT_BASE_ALERT_TL --============================== Select  View --============================== select * from XXSD_ALERT_V --============================== Check  View is created in Database --============================== select * from all_objects where object_name = 'XXSD_ALERT_V' --============================== check view is register in Oracle Apps or Not --============================== select * from FND_VIEWS where view_name in ('XXSD_ALERT_V') --============================== check view Columns are register in Oracle Apps or Not --============================== select * from FND_VIEW_COLUMNS where view_id in ( select view_id from FND_VIEWS where view_name in ('XXSD_ALERT

Register Table in Oracle Apps --=================================================== select * from XXSD.XXSD_EVENT_BASE_ALERT_TL drop table XXSD.XXSD_EVENT_BASE_ALERT_TL --=================================== Create Table --=================================== create table XXSD.XXSD_EVENT_BASE_ALERT_TL ( HDR_ID NUMBER,  NAME  VARCHAR2(100),  EMP_NO VARCHAR2(100),  GRADE VARCHAR(2),  DOB DATE  ) --=================================== Check Table --===================================  select * from XXSD.XXSD_EVENT_BASE_ALERT_TL --=================================== Check  Table is created in Database --===================================  select * from all_objects  where object_name = 'XXSD_EVENT_BASE_ALERT_TL' --=================================== Check Table is register or Not --===================================  select * from fnd_tables  where table_name = 'XXSD_EVENT_BASE_ALERT_TL' --=================================== Check T