ORACLE Objects? tables, views, sequences, stored procedures, synonyms, indexes, materialized view etc. Dual? is an Oracle built in Sudo table. It contains only one column and no records. SELECT SYSDATE from dual; –This query will fetch current system date. Select 1 from dual; –This query will fetch 1. Select ‘Mahesh’ from dual; –This query will… Continue reading ORACLE INTERVIEW QUESTIONS AND ANSWERS
INITCAP – The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to lowercase. INITCAP(‘tech on the net’); Result: ‘Tech On The Net’ INSTR – The Oracle/PLSQL INSTR function returns the location of a substring in a string. INSTR(‘Tech on the net’, ‘e’, 1, 2) Result: 11 (the… Continue reading ORACLE FUNCTIONS
Oracle PLSQL 11g New Features sequences have never been directly supported in PL/SQL. In versions prior to 11g, assigning a variable from a sequence (i.e. retrieving NEXTVAL or CURRVAL) is a SQL-only operation (we typically use a SELECT..INTO..FROM DUAL construct). In 11g, however, Oracle has added sequence fetching (using NEXTVAL and CURRVAL) to the list… Continue reading Oracle PLSQL 11g New Features
Oracle 11g New Features a. Alter table to add columns with default value In Oracle 11g, we can alter a table to add a column with a default value. Earlier, a developer used to update the value for the new column using UPDATE statement. Refer the example code [1]. Example Code [1] ALTER TABLE EMPLOYEES… Continue reading Oracle 11g New Features
Oracle 12c New Features When you truncate a parent table with child tables, you get:ORA-02266: unique/primary keys in table referenced by enabled foreign keys. In Oracle 12c, you can use: truncate table <Parent> cascade; Must have defined the FK as ON DELETE CASCADE. Otherwise ORA-14705: unique or primary keys referenced by enabled foreign keys in… Continue reading Oracle 12c New Features
ORACLE VIEWS V$SYSSTAT Statistics such as the amount of data changed and the number of transactions executed. V$SQL The actual SQL commands recently executed, how many time it was executed and how much CPU time it took. V$FILESTAT The number of reads and writes per file, and time spent on disk I/O. V$DATAFILE Lists… Continue reading ORACLE VIEWS
Data Dictionary The data dictionary is full of ‘Metadata’, information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. If you are like me, you are a bit forgetful. The data dictionary is a repository of information about the Oracle database, known… Continue reading Data Dictionary
UTL_SMTP The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS. CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2, p_from IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2, p_smtp_host IN VARCHAR2, p_smtp_port IN NUMBER DEFAULT 25) AS l_mail_conn UTL_SMTP.connection; BEGIN l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); UTL_SMTP.helo(l_mail_conn, p_smtp_host); UTL_SMTP.mail(l_mail_conn, p_from); UTL_SMTP.rcpt(l_mail_conn,… Continue reading UTL_SMTP
UTL_FILE DECLARE V1 VARCHAR2(32767); F1 UTL_FILE.FILE_TYPE; BEGIN — In this example MAX_LINESIZE is less than GET_LINE’s length request — so the number of bytes returned will be 256 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN(‘MYDIR’,’MYFILE’,’R’,256); UTL_FILE.GET_LINE(F1,V1,32767); UTL_FILE.FCLOSE(F1); — In this example, FOPEN’s MAX_LINESIZE is NULL and defaults to 1024, — so… Continue reading UTL_FILE
EXPLAIN PLAN Query optimization diagram Executing explain plan Explain plan for select * from employee; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Reading explain plan https://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm ———————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ———————————————————————————– | 0 | SELECT STATEMENT | | 3 | 189 | … Continue reading EXPLAIN PLAN
ORACLE SQL PLSQL Advanced Tutorial 