Oracle Database

DEFAULT ON NULL Feature in Oracle 12c Prior to 12c, the DEFAULT for a column would not be applied if you explicitly or implicitly inserted a NULL into that column. The functionality has been limited with various restrictions. For example you are restricted from using a SEQUENCE object to supply a default value. If there is a need to insert or update the default value you have to either use DEFAULT keyword into the statement or leave the column out of the INSERT statement entirely. In fact adding a new column that permists NULL values with a dfault values was an offline operation. SQL> create table TEST (id# number, name varchar2(20) default 'SPACE'); Table created. SQL> insert into TEST values (1,NULL); 1 row created. SQL> select id#, decode(name, NULL,'Null',name) from TEST;  ID#       DECODE(NAME) ---------- ----------          1 Null 1 row selected. SQL> insert into TEST(id#) values (2); 1 row created. SQL> select id#, decode(name,NULL

Invisible Column feature in Oracle 12c Oracle 12c allows a coulnm to be invisible. Invisible coulnm are considered only when they are explicitly referred, otherwise they are ignored for queries and DML operations. Sometimes it requires to hide particualr column from developers thus they can not include these data in reports. You can make a column visible or invisible whenever required using ALTER TABLE command: CREATE TABLE INV_EMP (EMP# NUMBER(4), SALARY NUMBER(8,2) INVISIBLE) INSERT INTO INV_EMP (EMP#,SALARY) VALUES (101, 6000); INSERT INTO INV_EMP VALUES(102); SQL> select * from INV_EMP;       EMP# -----------       101       102 SQL> select EMP#,SALARY from INV_EMP;       EMP#       SALARY ----------       -------------       101        6000       102 SQL> ALTER TABLE INV_EMP MODIFY (SALARY  VISIBLE); SQL> select * from INV_EMP;       EMP#      SALARY ----------      -------------       101       6000       102 You can do same for view as you

Identity Columns Feature in Oracle 12c Before Oracle 12c there was no direct equivalent of the Auto Number or Identity functionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type. The Syntax: GENERATED [ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ (identity_options ) ] Using ' ALWAYS ' keyword will force the use of the identity. In that case if an insert statement references the identity column, even you specify a NULL value, an error is occured. CREATE TABLE payslip_master (   empid NUMBER GENERATED ALWAYS AS IDENTITY,  latin_name Varchar2(50)); Insert into payslip_master (latin_name) values('SADHAN_001'); Insert into payslip_master (latin_name) values('SADHAN_002'); SQL> select * from cust_master;      EMPID LATIN_NAME ---------- --------------------          1 SADHAN_001      

Top-N Queries & Truncate Table Enhancement in Oracle 12c In fact there is already various method exist to fetch TOP N query results in the previous release. In the oracle 12c retrieving TOP N query simplified and become easier with the new Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses, together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set so you can easily retrieve the result set. The row limiting clause is simply added at the end of any SQL statement to fetch a specific set of records: §          OFFSET provides a way to skip the N first rows in a result set before starting to return any rows §          The FETCH clause limits the number of rows returned in the result set §          For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive. §          If it is larger than the

How to Manage Serial# through database Sequence Create sequence invoice_serial MINVALUE 1 maxvalue 99999999 increment by 1; If you need to modify the sequence to start with particular number Create sequence invoice_serial start with 8 increment by 1 maxvalue 99999999; Now write a PRE_INSERT trigger to access the sequence through form. select invoice_serial.nextval  into :invoice_details.serial# from dual; Note: In the above case database sequence updated and serial is displayed when you save the record. How to Control the sequence through database Table: Create a form as well as database table and sequence from where from where you want to handle the sequence. CREATE TABLE INVOICE_SEQUENCE ( SEQ_COMP NUMBER(1), SEQ_YEAR NUMBER(4), SEQ_TYPE NUMBER(2), SEQ_SEQ NUMBER(10)); PRIMARY KEY: SEQ_COMP, SEQ_YEAR, SEQ_TYPE CREATE SEQUENCE INVOICE_SEQ    MINVALUE 1    MAXVALUE 99999999    START WITH 1    INCREMENT BY 1    CACHE 20; Then create the required form

How to display database define ‘LOGO’ or ‘COMP_NAME’ or ‘CUSTOM_ERROR_MESSAGE’ through Oracle Forms. Display Database Defined ‘LOGO’ Create an image Item: Logo on the form set with required image size Item_Type: Image Enabled: Yes Image Format: TIFF Image Depth: Original Display Quality: High Database Item: No Write Form Level Trigger ‘When_New_Form_Instance declare       logo_path varchar2(250); begin select logo into   logo_path from   logo;       Read_Image_File(logo_path, 'ANY', 'logo'); exception       when   no_data_found then              null;   when   others then          show_message(2,2); end; Display System Date and Time on the Form Create two Item ‘Date’ and ‘Time’ on the Top of the form Item_Name: Date Item_Type: Text_Item Data Type: Date Initial Value: $$DATE$$ Format Mask: Dy  DD-MM-YYYY Item_Name: Time Item_Type: Text_Item Data Type: char Initial Value: $$TIME$$ Format Mask: Dy  DD-M

Script: To Find Archivelog Generation daywise Report. select thread#, to_char(first_time,'YYYY-MM-DD') day, to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06", to_char(sum(decode(substr(to_char(first_time,'HH24'

How to send E-mail from Oracle database sqlplus / as sysdba @?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plb Grant execute on UTL_MAIL to public; ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both; For Example: BEGIN UTL_MAIL.send(sender => 'shahid.ahmed@al-sadhan.com', recipients => 'you@address.com', subject => 'Test Mail', message => 'Everythings is OK', mime_type => 'text; charset=us-ascii'); END; / Note: You must have an outgoing SMTP server IP to configure sending mail from the database.  Oracle allows you to send mail using procedure may be just message or message from tables. CREATE TABLE EMP ( ENO NUMBER(4), ENAME VARCHAR2(50), BASIC NUMBER(10,2)); CREATE OR REPLACE PROCEDURE SENDMAIL(TOO IN VARCHAR, FROMM IN VARCHAR, SUBJECT IN VARCHAR, BODY IN VARCHAR) AS SMTP_HOST VARCHAR2(50) :='212.12.164.2'; PORT varchar2(2) :='25'; CONN UTL_SMTP.CONNECTION; BEGIN

Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULT procedure of DBMS_SQL package. Rather than defining explicit reference cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. The following procedure returns list of rows from SALARY table as return value from a procedure. CREATE OR REPLACE PROCEDURE Get_salary AS Salary_cursor SYS_REFCURSOR; BEGIN    OPEN salary_cursor FOR    SELECT * from pay_payment_master;    DBMS_SQL.RETURN_RESULT(salary_cursor); END; You can call the procedure and find the list of rows from the procedure as follows: EXECUTE Get_salary Functions in the WITH Clause: The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below. WITH   FUNCTION  expert(EMPLOYMENT_DATE date) RETURN NUMBER IS   BEGIN     RETURN  floor ((SYSDATE

DBA interview Question and Answer Part 2 I have configured the RMAN with Recovery window of 3 days but on my backup destination only one days archive log is visible while 3 days database backup is available there why? I go through the issue by checking the backup details using the list command. I found there is already 3 days database as well as archivelog backup list is available. Also the backup is in Recoverable backup. Thus it is clear due to any reason the backup is not stored on Backup place. Connect rman target database with catalog List backup Summary; List Archivelog All; List Backup Recoverable; When I check the db_recovery_dest_size , it is 5 GB and our flash-recovery area is almost full because of that it will automatically delete archive logs from backup location. When I increase the db_recovery_dest_size then it is working fine. If one or all of control file is get corrupted and you are unable to start database then how can you perform recovery? If