7/29/2019 orlc qns

1/5

Oracle Basic Functions Tutorial# Function Description Demonstration1 ABS To get absolute value ofany number.SQL > select abs(-10) from dual;ABS(-10)----------102 ADD_MONTHS Add number of monthsis the specified dateand get the next date.SQL > select sysdate, add_months(sysdate,2) from dual;SYSDATE ADD_MONTH--------- ---------10-APR-06 10-JUN-063 ASCII This will return theASCII value of thecharacter specified.SQL > select ascii('A') from dual;ASCII('A')----------654 AVG To get the average SQL> select avg(sal) from emp;

AVG(SAL)----------2073.214295 CEIL To get the nextgreater integer.SQL> select ceil(23.8) from dual;CEIL(23.8)----------246 CHR It is reverse of ASCIIfunction explainedabove, used to convertASCII to its character.

SQL> select chr(65) from dual;C-A7 COALESCE8 COMPOSE9 CONCAT To concatenate twostrings. You can usetwo different tablefields to concatenate.SQL> select concat('Pak','istan') from dual;CONCAT('--------

Pakistan10CONVERT To convert from onecharacter set toanother.SQL> SELECT CONVERT(' A B C D E', 'WE8ISO8859P1', 'US7ASCII')2 FROM DUAL;CONVERT('ABCDE','WE8-------------------------

7/29/2019 orlc qns

2/5

A B C D ESQL>11COUNT Count number ofrecords in a table.SQL> select count(*) from emp;COUNT(*)----------14SQL> select deptno, count(*) from empgroup by deptno;DEPTNO COUNT(*)---------- ----------10 320 530 6Forms Questions1) Which trigger are created when master -detail rela?A) master delete property1) NON-ISOLATED (default)a) on check delete masterb) on clear detailsc) on populate details2) ISOLATED

a) on clear detailsb) on populate details3) CASCADEa) per-deleteb) on clear detailsc) on populate details2) which system variables can be set by users?A) SYSTEM.MESSAGE_LEVELSYSTEM.DATE_THRESHOLDSYSTEM.EFFECTIVE_DATESYSTEM.SUPPRESS_WORKING3) What are object group?A) An object group is a container for a group of objects. You define an object g

roupwhen you want to package related objects so you can copy or reference them in anothermodule.4) What are referenced objects?A) Referencing allows you to create objects that inherit their functionality andappearance from other objects. Referencing an object is similar to copying an object,except that the resulting reference object maintains a link to its source object. Areference object automatically inherits any changes that have been made to the sourceobject when you open or regenerate the module that contains the reference object

.5) Can you store objects in library?A) Referencing allows you to create objects that inherit their functionality andappearance from other objects. Referencing an object is similar to copying an object,except that the resulting reference object maintains a link to its source object. Areference object automatically inherits any changes that have been made to the sourceobject when you open or regenerate the module that contains the reference object

7/29/2019 orlc qns

3/5

.6) Is forms 4.5 object oriented tool ? why?A) Yes , partially.1) PROPERTY CLASS - inheritance property2) OVERLOADING : procedures and functions.7) Can you issue DDL in forms?A) yes, but you have to use FORMS_DDL.Referencing allows you to create objects that inherit their functionality and appearancefrom other objects. Referencing an object is similar to copying an object, except that theresulting reference object maintains a link to its source object. A reference objectautomatically inherits any changes that have been made to the source object whenyouopen or regenerate the module that contains the reference object. Any string expressionup to 32K: a literal an expression or a variable representing the text of a block of dynamically createdPL/SQL code a DML statement or a DDL statement

Restrictions:The statement you pass to FORMS_DDL may not contain bind variable references inthestring, but the values of bind variables can be concatenated into the string beforepassing the result to FORMS_DDL.8) What is SECURE property?A) Hides characters that the operator types into the text item. This setting istypicallyused forpassword protection.Oracle1) What are the Back ground processes in Oracle and what are they.?

A) This is one of the most frequently asked questions. There are basically 9 Processesbut in a General system we need to mention the first five background processes.Theydo the house keeping Activities forthe Oracle and are common in any system.The various background processes in oracle area) Data Base Writer (DBWR): Data Base Writer Writes Modified blocks from Databasebuffer cache to Data Files. This is required since the data is not written whenever atransaction is committed.b) Log Writer (LGWR): Log Writer writes the redo log entries to disk. Redo Log d

ata isgenerated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWRwrites log entries into a online redo log file.c) System Monitor (SMON): The System Monitor performs instance recovery at instancestartup. This is useful for recovery from system failured) Process Monitor (PMON): The Process Monitor performs process recovery when userProcess fails. Pmon Clears and Frees resources that process was using.

7/29/2019 orlc qns

4/5

e) Checkpoint (CKPT): At Specified times, all modified database buffers in SGA arewritten to data files by DBWR at Checkpoints and Updating all data files and control filesof database to indicate themost recent checkpointf) Archieves (ARCH): The Archiver copies online redo log files to archival storal whenthey are busy.g) Recoveror(RECO) : The Recoveror is used to resolve the distributed transaction innetworkh) Dispatcher (Dnnn) : The Dispatcher is useful in Multi Threaded Architecturei) Lckn : We can have upto 10 lock processes for inter instance locking in parallel sql.2) How many types of Sql Statements are there in Oracle?A) There are basically 6 types of sql statments.They area) Data Defination Language(DDL) : The DDL statments define and maintain objectsand drop objects.b) Data Manipulation Language(DML) : The DML statments manipulate database data.c) Transaction Control Statements: Manage change by DMLd) Session Control : Used to control the properties of current session enablinganddisabling roles and changing .e.g :: Alter Statements,Set Role

e) System Control Statements: Change Properties of Oracle Instance .e.g:: AlterSystemf) Embedded Sql : Incorporate DDL,DML and T.C.S in Programming Language.e.g::Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close3) What is a Transaction in Oracle?A) A transaction is a Logical unit of work that compromises one or more SQLStatements executed by a single User. According to ANSI, a transaction begins with firstexecutable statment and ends when it is explicitly commited or rolled back.4) Key Words Used in OracleA) The Key words that are used in Oracle are ::a) Commiting : A transaction is said to be commited when the transaction makespermanent changes resulting from the SQL statements.

b) Rollback: A transaction that retracts any of the changes resulting from SQLstatements in Transaction.c) SavePoint : For long transactions that contain many SQL statements, intermediatemarkers or savepoints are declared. Savepoints can be used to divide a transactino intosmaller pointd) Rolling Forward: Process of applying redo log during recovery is called rollingforward.e) Cursor : A cursor is a handle ( name or a pointer) for the memory associatedwith aspecific statement. A cursor is basically an area allocated by Oracle for execut

ing theSql Statement. Oracle uses an implicit cursor statement for Single row query andUsesExplcit cursor for a multi row query.f) System Global Area(SGA) : The SGA is a shared memory region allocated by theOracle that contains Data and control information for one Oracle Instance.It consists ofDatabase Buffer Cache and Redo log Buffer.g) Program Global Area (PGA) : The PGA is a memory buffer that contains data andcontrol information for server process.

7/29/2019 orlc qns

5/5

h) Database Buffer Cache : Databese Buffer of SGA stores the most recently usedblocksof datatbase data.The set of database buffers in an instance is called DatabaseBufferCache.i) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.