Oracle Notes

Using which language oracle is developed? -------------------------------------------------------------------------------"C" ofcourse!!! Database Running Slow -------------------------------------------------------------------------------Remove unnecessary joins,� too much normalization can effect query to perform slow, excess use of cursors, indexes, procedures or temporary tables may effect on query to perform slow Is there any syntactical diff bet Oracle 9i & 10g -------------------------------------------------------------------------------In 9i we does not have the facility to brign the table that is been dropped, but in 10g we have something like flashback In oracle 10g we have new datatype called binary double and binary float. 10 g also has collection api, which allows us to use procedures and funtion with collections. In Oracle 10g external procedures can also be called Oracle 9i Tools and Utilities -------------------------------------------------------------------------------Utility cannot uninstall it is an inbuilt Tool can uninstall View - Base Tables -------------------------------------------------------------------------------View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example).View just stores the query defination and not the data. If you delete the base table, the view becomes INVALID. In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure Catch The Error -------------------------------------------------------------------------------Catch The Error SQL>DECLARE 2 dayofweek VARCHAR2(200); 3 BEGIN 4 SELECT TO_CHAR(sysdate,'Day') INTO dayofweek FROM DUAL ; 5 6 IF dayofweek = 'Tuesday' 7 THEN 8 DBMS_OUTPUT.PUT_LINE('Aloha!!! Today Is Tuesday'); 9 ELSE 10 DBMS_OUTPUT.PUT_LINE('Today is '||to_char(sysdate,'Day')); 11 END IF; 12 END; 13 /

SELECT TO_CHAR(sysdate,'Day') INTO dayofweek FROM DUAL

returns

"Name of day padded with blanks to length of 9 characters" IF 'Tuesday

' = 'Tuesday' is not ture

You have to use the� TRIM before TO_CHAR as given below SELECT TRIM(TO_CHAR(sysdate,'Day')) INTO dayofweek FROM DUAL� returns. Oracle Dbms Pipe Commands -------------------------------------------------------------------------------dbms_pipe is a IPC methodology which allows processes to communicate with each other using pipes. The important functions/procedures in this package are (1) CREATE_PIPE to create a new pipe ==> dbms_pipe.create_pipe(pipename) where pipename can be Oracle supplied or user defined as below dbms_pipe.create_pipe(dbms_pipe.unique_session_name) -- Creates a PIPE based on your unique Session ID To send data prepare the data using dbms_pipe.pack_message(mesg_buf) where mesg_buf can be varchar2 At the other end of the pipe you can receive the data using dbms_pipe.receive_message function

How many ways you can delete the table records ? -------------------------------------------------------------------------------To delete the records in a table , there are 2 ways . 1.Using the delete statement 2.using the truncate statement . To delete the entire table drop statement is used . Deleting a record from a table : use the following statement. delete from table_name where column_name = some_value; . The above statement deletes a single record at a time. To delete all the records in a table the following statement is used. delete * from table_name; coming to the truncate statement , its like a delete statement without a where clause. the syntax is Truncate table table_name. It also removes all the records in the table. In case of delete * from table_name and truncate table table_name all the records in the table is deleted but the table structure and its columns , constraints ,

indexes remain the same . To remove the table from the database,use the drop table statement. drop table table_name; What is difference between TRUNCATE & DELETE -------------------------------------------------------------------------------The Main Difference Between DELETE & TRUNCATE Are :[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command [2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records [3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions [4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately : What are the data types allowed in a table -------------------------------------------------------------------------------binary Bigint bit Char datetime decimal Float image Int Money nchar Ntext nvarchar Numeric Real smalldatetime smallint smallmoney sql_variant sysname text timestamp tinyint varbinary varchar uniqueidentifier these are the data types used in sql table What are the types of SQL statement -------------------------------------------------------------------------------select - data retrieval insert, update, delete, merge - DML Create, alter, drop, rename, truncate - DDL Commit, rollback, savepoint - transaction control grant, revoke - DCL What is ON DELETE CASCADE -------------------------------------------------------------------------------ON DELETE CASCADE option permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity. For example: CREATE TABLE project (

Project_Id varchar2(3) constraint project_id_pk PRIMARY KEY, Project_name varchar2(100)) CREATE TABLE employee ( Project_Id varchar2(3), Project(Project_Id), employee_code varchar2(3), employee_name varchar2(25), salary Number(7,2), job_title varchar2(100), constraint employee_projid_fk FOREIGN KEY (Project_Id) REFERENCES Project(Project_Id) ON DELETE CASCADE) How do I display row number with records -------------------------------------------------------------------------------Select ROWNUM, col1, col2 from tablename; OR select rownum, table_name.* from table_name; Display the records between two range I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text �Not Applicable� want to display, instead of blank space. How do I write the que -------------------------------------------------------------------------------SELECT NVL (To_Char(comm), 'Not Applicable') FROM emp OR select ename,decode(nvl(comm,0),0,'Not Applicable',comm) from scott.emp;

What is a transaction -------------------------------------------------------------------------------A collection of DML statements that form a logical unit of work is called a transaction whatever you do before executing a commit or rollback is called as a transaction. What is a join -------------------------------------------------------------------------------Join is a condition to retrive the data from a single or multiple table. Basiclly we can access the date from multiple table in two ways 1. with join Inner Join Left outer Join Right outer Join Full Join 2. without join

Cross Join What is the sub-query- In and Exists -------------------------------------------------------------------------------"In" is used to find in a range of values. Eg: select empno, ename from employee where department in ("SALES","ACCOUNTS","FINANCE","HR"); Records from every department are fetched. Checks department whether in SALES or in ACCOUNTS or in FINANCE or in HR. Exists just checks for the condition and returns true or false. Eg: select empno, ename from employee where exists(sal>0); What is correlated sub-query -------------------------------------------------------------------------------A correlated sub-query is a subquery that references the value/s from the main query. example : SELECT colA , colB FROM tableA WHERE colB < ( SELECT max(colX) FROM tableB WHERE tableB.colY = tableA.colA) Oracle executes correlated subquery for each record from of main query .. hence these types of queries have greater impact on performance and must be avoided when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes. EXAMPLE : Select deptno, ename, sal from emp a where sal> (select avg(sal) from emp b where a.deptno =b.deptno) order by deptno; What is a corelated subquery -------------------------------------------------------------------------------1.IF THE OUTPUT OF AN OUTER QUERY ACTS AS A INPUT TO THE INNER QUERY THEN THAT QUERY IS KNOW AS CORELATED SUB QUERY. 2.WHEN A QUERY IS ASSOCIATED WITH ALLIAS NAME THEN THAT QUERY IS KNOWN AS CORELATED SUBQUERY What is a Non- corelated subquery --------------------------------------------------------------------------------

Non corelated subquery, where the subquery has been executed once for the entire parent statement. Subquery vs Join -------------------------------------------------------------------------------The main difference betwen subquery and join is subquery is faster when we have to retrieve data from large number of tables.Because it becomes tedious to join more tables. join is faster to retrieve data from database when we have less number of tables

Explain CONNECT BY PRIOR -------------------------------------------------------------------------------The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id; Difference between SUBSTR and INSTR -------------------------------------------------------------------------------INSTR function finds the numeric starting position of a string within a string. As eg. Select INSTR('Mississippi','i',3,3) test1, INSTR('Mississippi','i',1,3) test2, INSTR('Mississippi','i',-2,3) test3 from dual; Its output would be like this Test1 Test2 Test3 ___________________________________________ 11 8 2 SUBSTR function returns the section of thte specified string, specified by numeric character positions. As eg. Select SUBSTR('The Three Musketeers',1,3) from dual; will return 'The'. Explain UNION, MINUS, UNION ALL and INTERSECT -------------------------------------------------------------------------------UNION - the values of the first query are returned with the values of the second

query eliminating duplicates. MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. UNION ALL - the values of both queries are returned including all duplicates INTERSECT - only the duplicate values are returned from both queries. Example abcde cdefg union - abcdefg Minus - ab Intersect - cde union all - abcdecdefg What is ROWID -------------------------------------------------------------------------------Rowid is the physical address for each record in the database and it is a fixedlength binary data. ROWID is the psedo columns indicate the stored location of the data physically in the database. The format of the rowid is: BBBBBBB.RRRR.FFFFF where: BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file. ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID. Latest Answer : rowid is a unique value assigned by system automatically when ever a insert statement gets successful.rowid contains the address of data file,data block, object id, rownumber What are the more common pseudo-columns -------------------------------------------------------------------------------common pseudo-columnsRownm,Rowid,Level,Currval,Nextval,sysdate,user,uid What is difference between CHAR and VARCHAR2 ... -------------------------------------------------------------------------------FOR ORACLE 9I AND ABOVE. CHAR CAN HAVE UPTO 2000 BUT FIX ( IT WILL TAKE 2000 Byies even used only 4 char)

varchar2 can have upto 4000 and variable size CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. How many LONG columns are allowed in a table -------------------------------------------------------------------------------Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, that means we will give primary key to any column in the table except the column having the long data type. If we give, we get the error ORA-02269: key column cannot be of LONG datatype. What is the fastest way of accessing a row in a ta... -------------------------------------------------------------------------------by using the index and rowid & constraints What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint -------------------------------------------------------------------------------to modify the datatype of a column that should be empty, but if increasing the width then it can be done even it holds the value. to add then not null column the table must be empty if not then add the column as nullable then modify that that column as not null. Where the integrity constraints are stored in data dictionary -------------------------------------------------------------------------------Constraints are found under the USER_CONSTRAINTS and user_cons_columns table. How to drop the column in a table -------------------------------------------------------------------------------You could drop column of a table by follwing command. And it works with all the version of oracle. SQL> Alter table drop column ; How ever instead of dropping a column you could make it unused. And at the end of month or week you could drop all the unused columns. This is due to avoid some risks. To make a column unused you could have following command: SQL> Alter table set unused column ; To drop unused columns you could use the following command: SQL> Alter table drop unused columns; You could also drop all the unused columns of a schema as well. Generally rollback cant be followed are implemented along with these DDL

statements. The commit statement automatically involves. How will you activate/deactivate integrity constraints... -------------------------------------------------------------------------------Alter table modify constraint < constraintname> disable/enable example alter table CONTACT_US modify constraint CONTACT_US_UK disable; What is an integrity constraint -------------------------------------------------------------------------------An integrity constraint is a rule that restricts the values in a database. There are 5 types: A NOT NULL constraint prohibits a database value from being null. A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null. A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. A foreign key constraint requires values in one table to match values in another table. A check constraint requires a value in the database to comply with a specified condition. What is a join ? Explain the different types of jo... -------------------------------------------------------------------------------As posted in earlier answer joins are used to fetch columns from more than one table by using a column which is present in both the tables. Joins are of two types: 1. Inner Join� - Matching column is given based on which joining the two tables occurs. all the rows which have a matching value of the column selected will be extracted. This is just like Intersection in sets. First find out all the matching values for the column selected for inner join, then return all the rows for this matching values. 2. Outer Join - Outer join is again divided into 3 types: i Left Outer Join ii Right Outer Join iii Full Outer Join In Left Outer join, unmatched column values for the first table are also extracted. In right outer join, unmatched column values for the second table and matched column values for both the tables are extracted.

In full join, all the columns irrespective of matches are extracted. What is referential integrity constraint -------------------------------------------------------------------------------It refers to the integrity between master and detail table. If the information from the master table is deleted then the corresponding record from the detail table should also get deleted as this information has no meaning without master table A foreign key means the values in one table must also appear in another table. The foreign key in the child table will generally reference a primary key in the parent table.The referencing table is called the child table & referenced table is called the parent table. What is the Subquery ? -------------------------------------------------------------------------------A subquery is a SELECT statement that is embedded in a clause of another SQL statement, Called the parent statement. The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query(main query). What is the usage of SAVEPOINTS -------------------------------------------------------------------------------By utilizing savepoints, you are able to rollback to a given point in your processing. An example of this would be: ... COMMIT; INSERT UPDATE SAVEPOINT A DELETE UPDATE INSERT SAVEPOINT B INSERT INSERT DELETE SAVEPOINT C ROLLBACK TO SAVEPOINT B I only lost the two inserts and delete. Had I not placed savepoints in my processing and used a rollback, I would have lost my entire transaction back to the last good commit. SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

Oracle Forms : -------------------------------------------------------------------------------Note: While creating a stored procedure, you may receive an error similar to the following: ORA-01031: insufficient privileges This indicates the DBA has not granted you the authorization to create stored procedures. Please contact your DBA to obtain this authorization. To see if the procedure compiled correctly, look in view USER_ERRORS as follows: SQL> SELECT * FROM user_errors; no rows selected If the message no rows selected appears, then no errors were found in the trigger. Another alternative is to use the SQL*Plus command called show errors. After submitting a trigger or a stored procedure, any errors found during compilation can be displayed using the show errors command. In some versions of SQL*Plus, the show errors command will not work due to a lack of buffer memory on the client computer. An error message may appear as in the following example: SQL> CREATE PROCEDURE test AS 2 BEGIN 3 SELECT SYSDATE INTO :temp FROM dual; 4 END; 5 / Warning: Procedure created with compilation errors. SQL> show errors buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA. No errors.

If the buffer overflow message appears, set the ARRAYSIZE variable to a lower number such as 2 with the following command: SQL>

SET ARRAYSIZE 2

Then use the show errors command as described: SQL> show errors Errors for PROCEDURE TEST: LINE/COL ERROR -------- -------------------------------------------3/23 PLS-00049: bad bind variable 'TEMP'

To see what stored procedures you have created, query the USER_SOURCE view in the data dictionary. The only major difference between the stored procedure created in the database schema through SQL*Plus and the procedure created in Oracle Forms is the way the header is formatted. Creating a stored procedure uses the following syntax: CREATE PROCEDURE OTHER_DEPARTMENT_MEMBERS (IN_EMPLOYEE_SSN IN NUMBER, OUT_COUNT_OTHER_MEMBERS OUT NUMBER) AS Contrast this with the procedure done in Oracle Forms: PROCEDURE OTHER_DEPARTMENT_MEMBERS (IN_EMPLOYEE_SSN IN NUMBER, OUT_COUNT_OTHER_MEMBERS OUT NUMBER) IS SEQUENCE: -----------------------------------------------------CREATE SEQUENCE department_seq INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE; The next step is to assign the next value of the sequence to the DNUMBER item in the DEPARTMENT data block. A trigger to do this would be created at the block level and should execute before any new record is inserted into the database. A PRE-INSERT trigger on the DEPARTMENT data block would be used for this example: BEGIN -- Get the next value for DNUMBER from the -- department_seq sequence. SELECT department_seq.nextval INTO :department.dnumber FROM dual; END; Checking constraints -----------------------------------------------------For example, in an EMPLOYEE data entry form, we may want to enforce a constraint that no employee can act as their own supervisor. Thus for any record, SSN may not equal SUPERSSN. To implement such a constraint, add the SUPERSSN item to the EMPLOYEE form (or simply create a new form from scratch using the wizard that includes both SSN and SUPERSSN columns) and then create a WHEN-VALIDATE-ITEM trigger on the SUPERSSN item in the EMPLOYEE data block: BEGIN IF (:SUPERSSN = :SSN) THEN MESSAGE('Employees may not supervise themselves!'); RAISE FORM_TRIGGER_FAILURE; END IF; END;

The FORM_TRIGGER_FAILURE will cause the execution of the item validation to be halted and the user will have to change the SUPERSSN before moving on to another item. To set up an Alert: -------------------------------------------------------------------------------Use the Object Navigator to display the Alerts area. Pull down the Navigator menu and choose Create. Click on the default name that is given for the new alert (something like ALERT4) and rename it: SUPERVISOR_ALERT Bring up the Property Palette for this Alert and fill in the following properties: Title: Supervisor Alert Message Message: Employees may not supervise themselves! Alert Style: STOP Button 1 Label: OK Leave the Button 2 Label and the Button 3 Label blank Default Alert button: Button 1 Leave all of the other properties with their defaults and close the Property palette. Change the above WHEN-VALIDATE-ITEM trigger on the SUPERSSN item in the EMPLOYEE data block to: DECLARE return_alert NUMBER; BEGIN IF (:SUPERSSN = :SSN) THEN return_alert := SHOW_ALERT ('SUPERVISOR_ALERT'); RAISE FORM_TRIGGER_FAILURE; END IF; END; The SHOW_ALERT procedure calls up the specified alert and obtains the return value (based on the button the user clicks on) to assign to a local variable called return_alert. In the form on when_button_pressed trigger, I've put the following code to call a report in a form:DECLARE v_url varchar2(200); v_url2 varchar2(2000); repid REPORT_OBJECT; v_rep VARCHAR2(100); rep_status VARCHAR2(50); BEGIN repid := find_report_object('DUMMY'); v_rep := RUN_REPORT_OBJECT(repid); rep_status := REPORT_OBJECT_STATUS(v_rep); WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP rep_status := report_object_status(v_rep); END LOOP; IF rep_status = 'FINISHED' THEN WEB.SHOW_DOCUMENT('http://reportserver)_IP:Port/reports/rwservlet/getjobid'||

substr(v_rep,instr(v_rep,'_',-1)+1),'_blank'); ELSE message('Error when running report'); END IF; END; Implicit Cursor attributes -------------------------------------------------------------------------------SQL%FOUND,SQL%NOT FOUND, SQL%IS OPEN, SQL%ROW COUNT SQL%ISOPEN exists. Oracle always evaluates this to FALSE as PL/SQL closes implicit cursors immediately after they are executed To view installed Oracle version information -------------------------------------------------------------------------------From the SQL> prompt, type the following: select * from v$version; What is difference between CHAR and VARCHAR2 -------------------------------------------------------------------------------char is a fixed length charater data type. varchar is a variable length length character data type. A varchar2 datatype, when stored in a database table, uses only the space allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we will use 52 bytes (leading length byte). A char datatype, when stored in a database table, always uses the maximum length and is blank padded. If you have char(1999) and put 50 bytes into it, it will consume 2001 bytes (leading length field is present on char's as well). In the database -- a CHAR is a VARCHAR that is blank padded to its maximum length. Display the number value in Words -------------------------------------------------------------------------------select

to_char(to_date(123, 'j' ),'jsp')

from dual;

eg: SQL>

select to_char(to_date(123,'j'),'jsp') from

dual;

TO_CHAR(TO_DATE(123,'J') -----------------------one hundred twenty-three This is using ' julian' functionHere 'j' used as a format in to_date function return the number in julian year.Whereas the 'jsp' used in to_char made the julian year spelled out.i.e. j-julian year and sp-spelling so it is ' jsp'. there is some limitations we cac use this only in numbers from 1 -5373484.I think we cannot retrive numbers in words using a single querry other than julian

option....ie,within the range 1 -5373484. but we can create a function for this conversion ...If any one knows another way of conversion using querry other than julian plz send me... What is the maximum SIZE allowed for each type -------------------------------------------------------------------------------VARCHAR2: Max Size=4000 bytes CHAR: Max Size=2000 bytes NUMBER(p,s): The precision p can range from 1 to 38. The scale s can range from -84 to 127 DATE: from January 1, 4712 BC to December 31, 9999 AD.

What is the maximum number of triggers, can apply to a single table -------------------------------------------------------------------------------Insert/Update/Delete :- 3 Before/After:- 2 Row Level/Statement Level:-2 Hence 3*2*2 =12 there can be n number of trigger can have in a table . type of trigger may be 12 Which date function returns number value -------------------------------------------------------------------------------months_between(date1,date2) returns the number value no other date functions return number value Any three PL/SQL Exceptions -------------------------------------------------------------------------------TOO_MANY_ROWS NO_DATA_FOUND INVALID_CURSORS CURSOR_ALREADY_OPEN WHEN_OTHERS What are PL/SQL Cursor Exceptions -------------------------------------------------------------------------------Cursor_already_open, Invalid_cursor Explicit Cursor attributes -------------------------------------------------------------------------------%IS OPEN,%FOUND,%NOT FOUND,%ROW COUNT Other way to replace query result null value with a text

-------------------------------------------------------------------------------NVL or Decode What is the output of SIGN function -------------------------------------------------------------------------------SIGN (a): Returns 1 if a is positive ,-1 if a is less than 0 and 0 if a is 0 What is meant by Scrollable cursor -------------------------------------------------------------------------------A scrollable cursor, however, can move forward and backward, and can seek any desired record in the cursor. Such operations are common in applications that present results sets in scrolling windows. With a scrollable cursor, application developers do not need to create and manage their own buffer for the records. With scrollable cursors, you can move directly to the rows you want without having to FETCH every other row returned by the cursor. the rows of a table can be fetched many times Sequence is repeated each time FETCH statement is issued What are the different tablespaces in database -------------------------------------------------------------------------------A tablespace is a collection of one or more datafiles.all database objects are stored in tablespaces. diff types of tablespaces:1)system tablespace. 2)temp tablespace. 3)tools tablespace 4)users tablespace. 5)rollback tablespace. 6)data and index tablespace. what is Ref cursor when we use ref cursor and advantage of ref cursor -------------------------------------------------------------------------------The scope of cursor is limited to a block/ programme. here block/ programme means upto a end of procedure/function and only one fixed query can be attached to that cursor. where as the scope of ref cursor is global. Generally ref cursor is used with package so diffrent internal procedure/function of that package can use this ref cursor with diffrent query. so one cursor can have multiple query.

Normally in cursors, query is defined at the time the cursor is created. but in some cases, the query we need to use for a cursor is unknown until runtime. Oracle uses ref-cursors and cursor variables to satisfy this requirement How to drop the index -------------------------------------------------------------------------------Drop Index Indexname Where the integrity constraints are stored in Data Dictionary? -------------------------------------------------------------------------------user_constraints, dba_constraints, all_constraints How will you a activate/deactivate integrity constraints... -------------------------------------------------------------------------------alter table modify constraint disable/enable If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ? -------------------------------------------------------------------------------It won't, Because SYSDATE format contains time attached with it. What is a database link -------------------------------------------------------------------------------scott@8i> create database link ora8idev 2 connect to scott 3 identified by tiger 4 using 'ora8idev' 5 / Database link created. scott@8i> select * from dual@ora8idev; D X scott@8i> How to access the current value and next value from a sequence -------------------------------------------------------------------------------I would like to give you a small example to use the sequence.currval and sequence.nextval create sequence seq_name start with 1 minvalue 1

maxvalue 999 increment by 1 nocycle insert into table_name (sno,name) values (seqname.nextval,'abc'); select seqname.currval from dual You can find the last number used in the session for your sequence from the below mentioned query select last_number from user_sequences where sequence_name = What is CYCLE/NO CYCLE in a Sequence -------------------------------------------------------------------------------When you create a sequence with CYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will start over at the MINVALUE. This is not wise if using the sequence for primary key creation. When you create a sequence with NOCYCLE option, you are saying that when the sequence reaches its MAXVALUE, it will NOT start over at the MINVALUE. This option is safest if using the sequence for primary key creation. When the sequence reaches its MAXVALUE an oracle error is thrown. Because CYCLE/NOCYCLE value is optional, When no option is defined, the default value will NOCYCLE. What are the advantages of VIEW -------------------------------------------------------------------------------The advantages of View are: security:

only selected column from a table made available to view.

Complaxity: write a complex query and assign this query to a view so next time no need to write that complex query. Faster: view refers to a query which is already parsed , so again it does not go to that stage efficient: while writing trigger a condition may arise, trigger makes the table in mutating state, so use of views in this sanirio makes it easy. Can a view be updated/inserted/deleted -------------------------------------------------------------------------------Yes a view can be updated/inserted/deleted only if it is based on single base table means it should be a simple view. while if a view is based upon more than two tables then it is a complex view and DML operations can be performed using instead of triggers. How do you control the constraints in forms ? --------------------------------------------------------------------------------

We control the constraints in forms while selecting primary key 'yes' in property palett. If a view on a single base table is manipulated will the changes be reflected on the base table -------------------------------------------------------------------------------yes How do I eliminate the duplicate rows -------------------------------------------------------------------------------DELETE from TableName WHERE rowid NOT IN ( SELECT MIN(rowid) FROM TableName GROUP BY ColumnName ); OR Using distinct. OR use unique ( select unique * from table_name; ) How do I display row number with records -------------------------------------------------------------------------------SELECT ROWNUM,table_name.* FROM table_name; What is the use of TNSNAME.ORA and LISTENER.ORA files -------------------------------------------------------------------------------tnsnames.ora is used to connect to the remote db. To get the connection listener.ora should be configured at the server and it should be up and running listener is used to get the client server communication. U can access ur server only if the listerner is ON IS 'define' a valid SQL statement? -------------------------------------------------------------------------------Hi, DEFINE is a command of SQL * PLUS Tool. This is only valid in SQL * PLUS. With DEFINE command, user can define the session variables. Ex:

SQL> DEFINE

pi=22/7

SQL> select Output:-

&pi

from

dual;

3.14285714

what is Snapshot in oracle 9i and what is difference between them... --------------------------------------------------------------------------------

A snapshot is a table that contains the results of a query of one or more tables or views, often located on a remote database. A snapshot is a read-only copy of a master table located on a remote node. Snapshots can be queried, but not updated; only the master table can be updated. Snapshots are periodically refreshed to reflect changes made to the master table. what is ALTER TABLE?HOW WE USE IT TO ADD A New column... -------------------------------------------------------------------------------Alter table is DDL language which is allow us to edit table schema. The syntax is following: alter table ; A table has following layout ; CITY, NAME , SEX List all the employees who have atleast one person reporting to them -------------------------------------------------------------------------------select empid,empname from employees a where EXISTS (select manager_id from employees where empid=a.empid) List the employee details if and only if more than 10 employees are presently in department 10 -------------------------------------------------------------------------------SELECT * FROM Empl WHERE Dpt IN (SELECT CASE WHEN COUNT(Dpt) > 10 THEN Dpt ELSE NULL END FROM Empl GROUP BY Dpt); How do you write a query to get the count of Male, count of female in a given city XXX.Query result should be in a single row with count of male , count of female as columns -------------------------------------------------------------------------------select count(decode(sex,'M',1)) Males,count(decode(sex,'F',1)) Females from group by city what is the function of 'force' in view? -------------------------------------------------------------------------------force in view is used to create the view even if there is no existence of the base tables and not to replace a view. As two views can not be created with same name. Actually one more thing important is that u can create view without the defination of table With force option. it will be create but the status of the view will be invalid Force view is created only when you want to create a view for which base table doesnt exist. As it will only create view but could not be used because it will be in invalid mode. It will come in valid mode only when you will create respective base table and recompile the view. For example:

Suppose we try to create a view vw_emp on a non existing table emp. SQL> Create View vw_emp as Select empid, empname from emp; This command will give an error ORA-000942. However if we use the FORCE option, it will create the view.However trying to access the view gives an error,because the table 'emp' doesnt exist. SQL> Create FORCE VIEW vw_emp as select empid,empname from emp; It will give a warning: View created with compilation error. What is the condition to be follwed for the natural join ... -------------------------------------------------------------------------------While performing Natural Join please ensure that the name of the common column of both the tables are identical and their data types are also the same. Example select e.empno,d.deptno from emp e,dept d where e.dept_no = d.deptno; how to decrypt the password on oracle 8/8i in system/scott user? what is the query fired to see the password? -------------------------------------------------------------------------------DBMS_OBFUSCATION_TOOLKIT

package provides the encryption of password.

even DBA can not come to know what is the password, ofcouse he can reset password.

the new

difference between view and table -------------------------------------------------------------------------------Difference between a view and a base relation: Views: 1. This is one type of relation which is not a part of the physical database. 2. It has no direct or physical relation with the database. 3. Views can be used to provide security mechanism. 4. Modification through a view (e.g. insert, update, delete) generally not permitted Base Relation: 1. A base relation is a relation that is not a derived relation. 2. While it can manipulate the conceptual or physical relations stored in the data. 3. It does not provide security. 4. Modification may be done with a base relation. Difference between a View and Materialized View -------------------------------------------------------------------------------ANS:

view: View is a logical or virtual memory which is based on select query and the simple view is the view in which we can not make DML command if the view is created by multiple tables. Materialize veiw: It works faster than simple, Its works as snap shot and used for security purposes and we can make DML command in materialize view these views contain the data itself .Reason being it is easier/faster to access the data of the remote schema.The main purpose of Materialized view is to do calculations and display data from multiple tables using joins . HOw to get /select the nth row from the table ? -------------------------------------------------------------------------------nth salary select salary from table_name a where &n=(select count(distinct salary) from table_name b where a.salary<=b.salary); n salaries select salary from table_name a where &n>=(select count(distinct salary) from table_name b where a.salary<=b.salary); Find out nth highest salary from emp table -------------------------------------------------------------------------------SELECT ename,sal FROM emp s WHERE (n-1)= (SELECT count(*) FROM emp e WHERE e.sal>s.sal) This will return you the right result To Display Odd/ Even number of records -------------------------------------------------------------------------------Odd number of records: select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); 1 3 5 Even number of records: select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp) 2 4 6 How can we order the column of day (mon to sun)such that monday comes first then tues and so no.Suppose we have retrieved day out of hiredate column.......... -------------------------------------------------------------------------------hi divya! its simple. Note down the query.

select to_char(hiredate,'day') from emp order by to_char(hiredate-1,'d')then u'll get d result as u like how many types of data base triggers are there, what is cascading how many types of queries are there -------------------------------------------------------------------------------Triggers Basicaly only two type trigger are:

Database trigger and Applicaton trigger

database trigger: on table (before, after , update, insert, delete, row level, statement level) 2 * 3 * 2 = 12 triggers Application trigger/system triggers ( after logon, before logoff, after startup, before shutdown, on error, on ddl ) so 12 + 6 queries:

subquery, inline query, co-related querry

cascading is used where primary/foreign key relation: by using cascade we can delete child record or can be set to null or default values To display the all employee name with end of �S� without using like operator ? -------------------------------------------------------------------------------select employee_name from employees where substr(employee_name,-1,1)='S'; What is the basic difference between primary key and (unique key along with Not NULL). -------------------------------------------------------------------------------For storing null values, a primary key on a column gurantees that the column must contain a value for a given row, but a unique key only guarantees that a column should be unique A table can have only one primary key only... but the same table can have any number of primary keys... We can define Primary key on only one column in a table. But for Unique key there is no limit. we can define as many columns as we want. Simple Note : primary key = unique + not null + the only one PK column in the table + index on the PK column Created index enchances the queries on the PK column. How can we use of select query to get the current date and time? -------------------------------------------------------------------------------select to_char(current_date,'dd-mon-rr hh:mi:ss') from dual; what are the number datatypes allowed in a table? --------------------------------------------------------------------------------

scalar data type, boolean data type, referential data type, lob large objects these are the datatypes allowed in database. Disable Restricted Session -------------------------------------------------------------------------------Alter system disable restricted session; hi how to place different column in on column -------------------------------------------------------------------------------CREATE add1 add2 add3 );

OR REPLACE TYPE address AS OBJECT ( varchar2(25), varchar2(25), varchar3(25)

CREATE TABLE customer (cust_id address);

NUMBER(10),Cust_name

varchar2(25),addre

set describe depth all; desc customer; INSERT INTO customer(cust_id, cust_name addre)VALUES(1, �Ramesh�, address('Plot No.25', 'Sidharth Colony, �Hyderabad')); INSERT INTO customer(cust_id, cust_name addre)VALUES(2, �Suresh�, address('Plot No.42', 'Sri Ram Nagar, �Bangalore')); SELECT * FROM customer; -- selective select SELECT *FROM customer p WHERE p.addre.add3 = 'Hyderabad'; Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. -------------------------------------------------------------------------------The query can be in the form SELECT EMP_NAME, DEPT FROM EMP WHERE DEPT = (SELECT DEPT FROM EMP WHERE EMP_NAME='ABC'); What are HINTS in ORACLE? -------------------------------------------------------------------------------HINTS are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer

What is the different between Stand Alone Procedure and Package? -------------------------------------------------------------------------------Procedure is a named PL/SQL block tht is stored in the database. Package is a collection of functions and procedures stored within the database. Pkg consists of 2 parts 1. Specification - declares types,functions,procedures,exceptions,cursors 2. Body - implements the specification Whenevr a func/proc is referenced from the pkg thn the entire pkg is loaded in the memory so tht whn a diff func from the same pkg is referenced thn its already there in the memory What is Bitmapped indexes?2. What is b-tree index? -------------------------------------------------------------------------------B-Tree indexes are usually associated with the index that stores a list of ROWIDs for each key.While Bitmap is also organized as B tree , but the leaf node stores a bitmap for each value instead of a list of ROWIDs. B tree can be used for OLTP while bitmap is used for data warehousing system. What is the difference between cold backup and hot backup... -------------------------------------------------------------------------------Cold Backup- We can take the Backup while DB(eg. Oracle) is down. Hot Backup-We can take the Backup while DB(eg. Oracle) is running. Cold backup is a physical backup. During a cold backup the database is closed and not available to users. All files of the database are copied (image copy). The datafiles do not change during the copy so the database is in sync upon restore. Used when:Service level allows for some down time for backup Hot backup is a physical backup. In a hot backup the database remains open and available to users. All files of the database are copied (image copy). There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too. Upon a restore, the changes in the log files are reapplied to bring the database in sync. Used when:A full backup of a database is needed Service level allows no down time for the backup explaint the nvl2 function -------------------------------------------------------------------------------Sybtax:- - nvl2(expr1,expr2,expr3)In nvl2 if expr1 evaluates to NULL then expr3 is returned and if it evaluates to some value it returns expr2.for e.g select nvl2(commission_pct,'SAL+COMM','SAL') from employees;if commission_pct is having null value then SAL will be displayed else SAL+COMM. What is the Life of an SQL Statement? -------------------------------------------------------------------------------Once parsed, the SQL statement is available in the memory till the session is valid. What is the difference between Instance and a Database... --------------------------------------------------------------------------------

Instance is the memory structures and background processes used to interact with Database. Instance = SGA + Background Process Database is the physical files used to store information. the 3 types of physical files are: Datafiles, Control File and Redo-Log fileS(minimum 2). If the application is running very slow? At what points you need to go about the database in order to improve the performance? -------------------------------------------------------------------------------First of all check the process running on that server...if the idle process is 0, look if there are any run away processes taking most of the CPU and find exactly what it is doing... you can find the server process by typing the below command in solaries/AIX servers. hi!!! I want to know that..is there any special way to login into oracle SQL*PLUS Editor directly..Rather than every time u have enter user,password,host string...what should i do with login.sql script.. i Put it in /Bin directory..but not working.. -------------------------------------------------------------------------------create a SQL PLUS shortcut, select the short cut, right click and select properties options. u will see the TARGET option over there, type in the user/password@schema at the end of the text target :- C:oracleora92binsqlplusw.exe

user/password@schema

type of the text which bold in this mail. let me know if this work for u. or any doubts how many columns can be in the group by clause -------------------------------------------------------------------------------When you mention a column name in GROUP BY Cluase you must mention the same in SELECT statement Difference between nested query,sub query and nested query? -------------------------------------------------------------------------------query inside the query is nested query. it is also called as sub query. Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query. Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row. Select from table without using column name --------------------------------------------------------------------------------

we must follow 2 steps. 1. to find out column name using table name. 2. Then we can take corresponding column name from above results. Ex: Select column_name from all_tab_columns where table_name='countries'; The Output will be: COLUMN_NAME ------------------------COUNTRY_ID COUNTRY_NAME REGION_ID Then we wil use column names what u need from above list; What is PL/SQL ? -------------------------------------------------------------------------------hii

pl/sql is procedural language extenction to structure query language. which includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools. SQL - to execute a single querry @ a time. PL\SQL - to execute multiple lines of querry with advanced features of object oriented technique. What is the basic structure of PL/SQL ?... -------------------------------------------------------------------------------A PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part. The components of a PL/SQL are Declare,Begin , Exception , End. What are the datatypes a available in PL/SQL ? --------------------------------------------------------------------------------

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes? -------------------------------------------------------------------------------% TYPE provides variable.

the data type of a variable or a database column to that

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not know about variable's data type ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly. What is difference between % ROWTYPE and TYPE RECORD ? -------------------------------------------------------------------------------TYPE RECORD is a composite datatype. It consits of multiple pieces of information, called fields. TYPE RECORD fields can be defined by the user. Eg: DECLARE TYPE extra_book_info_t IS RECORD ( title books.title%TYPE, is_bestseller BOOLEAN ); first_book extra_book_info_t; here, 'title' is the data type defined in books table. We can declare a RECORD based on this type. basically the %rowtype is used in case of fetching the values of the cursor irrespective of how many columns in it and also the data types associated with the tables column.for eg. declare cursor c_example is select * from emp; v_emp emp%rowtype; begin for v_emp in c_example loop statements ...... end loop; end;

What is PL/SQL table ? -------------------------------------------------------------------------------Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key. Example for PL/SQL table: ================== In the below block "typlsql" and "ty_plsql2" are PL/SQL tables declare type ty_plsql is record ( empno number, ename varchar2(50)); TYPE ty_plsql2 is table of ty_plsql; ty_plsql1 ty_plsql2; j number; cursor c is select empno,ename from emp; begin ty_plsql1:=ty_plsql2(NULL,NULL); for i in c loop j:=1; ty_plsql1(j).empno := i.empno; ty_plsql1(j).ename := i.ename; dbms_output.put_line('Print the value : '||' No '|| ty_plsql1(j).empno ||' Name '|| ty_plsql1(j).ename); j:=j+1; end loop; end; can we insert a record in dual? -------------------------------------------------------------------------------Dual is a default table it contains one row, one column. like dual and insert records in that table

But we can create table

create table dual(name varchar2(20)); insert into dual values('Rushi'); commit; Select * from dual; It will show Rushi select sysdate from dual; This will show result from default dual table What is a cursor ? Why Cursor is requir... -------------------------------------------------------------------------------Cursor is a named private SQL area also called as context area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. there are three types of cursors 1. Static Cursor * Implicit Cursor * Explicit Cursor 2. Dynamic Cursor

3. Reference Cursor Cursor is a handler or pointer to Oracle Context Area.Context area is a memory space used for processing of sql statements. Explain the two type of Cursors ? -------------------------------------------------------------------------------Implicit cursor: implicit cursor is a type of cursor which is automatically maintained by the Oracle server itself.implicit cursor returns only one row. Explicit cursors:In explicit cursor you can explicitly assign a name to process information stored in private sql areas. This process involves four steps I. Declaring a cursor :- Involves assign a name to cursor and associating a query with it.. II. Open the cursor :- Executes the query and identify the result set. III. Fetch the cursor :- gets the result set and Loops through to process them IV. Close the cursor :- Releases the cursor What are the PL/SQL Statements used in cursor processing ? -------------------------------------------------------------------------------DECLARE CURSOR cursor name, OPEN Record types, CLOSE cursor name.

cursor

name,

FETCH cursor name INTO

or

Replace and Translate -------------------------------------------------------------------------------Translate: It is used to replace charecter by charecter Ex: Select translate('ashok','xp','sk') from dual; result:

ashok

Replace: It is used to replace word by word Ex: select replace('tech world','Tech','technology') from dual; result: technology world Table Level Constraint & Column Level Constraint --------------------------------------------------------------------------------

Table Level Constraint CREATE TABLE supplier_item ( supp_no INTEGER NOT NULL, item_no INTEGER NOT NULL, qty INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (supp_no, item_no) ) ; In the above example you can see that the table is already created and in the end of the statement we provide the Primary key constraint, So its like creating table and end of the statement we defind the constraint. The Supp_no and item no are primary key defined at last.. That is what Table level constraint is. And the normally when we are creating the table beside eack column we can mention the constraint indivisually called the column level constraint Example of Column level constraint CREATE TABLE supplier_item ( supp_no INTEGER NOT NULL PRIMARY KEY, item_no INTEGER NOT NULL, qty INTEGER NOT NULL DEFAULT 0 ; What are the cursor attributes used in PL/SQL ? -------------------------------------------------------------------------------%Found - Returns TRUE if the Last fetch returns a Row otherwise FALSE. %NotFound - Returns TRUE if the last fetch doesn't return any row otherwise FALSE. %Rowcount - Returns number of rows returned so far from the active set. %Isopen - Returns TRUE if the cursor is open otherwise FALSE. These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors. Note: %Isopen - Returns TRUE if the cursor is open otherwise FALSE. Its always returns FALSE incase of implicit cursor. Because after executing the DML statement Oracle Server automatically close the cursor. How to add new column in the existing table at our desired location? -------------------------------------------------------------------------------Syntax : - Alter Table ADD Column ( [size1], [size2], ...); Example : - Alter Table Employee ADD Column (Address2 Varchar2(28),Phone2 Number(6)); What is the maximum size of a form ? -------------------------------------------------------------------------------255 character width and 255 characters Length. SET Operator -------------------------------------------------------------------------------SET operators are used to combine similar type of data from two or more tables. The no of columns and their data type must be same in all the queries. The column names from the first query will appear in the result.

UNION - It returns rows of first query + rows of second query minus duplicate rows UNION ALL - It returns rows from both the queries including duplicate rows. MINUS - Rows that are unique for the first query will be retrieved INTERSECT - common rows from both the queries will be retrieved. Join is used to select columns from two or more tables. What is a cursor for loop ? -------------------------------------------------------------------------------The for loop in cursors are used to make less statements to write cursor. if u are opening a cursor normally using the open cursor ,then open cursor_name; fetch cursor_name into variable loop exit when cursor_name%notfound ; end loop; close cursor in the above statement u have to explicitly open and close the cursor, and also write exit statement to end the cursor loop if we write the same using for loop then compare the statements. for varible in cursor_name loop statements end loop; the above for loop does not need a open and close explicitly since those are involved in the for loop itself. and also never require the loop to explicitly end using any condition, since it ends the loop if there are no more rows in the memory. What is a deadlock? -------------------------------------------------------------------------------Dead lock is a unique situation in multiuser system that causes two or more users to wait indefinately for a locked resource. First user needs a resource locked by the second user and second user needs the resource locked by the first user.To avoid deadlocks avoid using exclusive locks. if using , commit the data frequently to release locks. What is SQL Tuning? -------------------------------------------------------------------------------SQL tuning is the process of ensuring that the SQL statements that an application will issue will run in the fastest possible time How to get the row values as comma separated by a query -------------------------------------------------------------------------------SELECT ENAME ||','||JOB||','||SAL FROM EMP; Question is been asked in the interview., I have table Department with details

like -------------------------------------------------------------------------------DeptId Dname 10 Finance 20 Sales 30 Marketing

Dlocation Del Mum Blore

The output should be in this format 10 20 30 Finance Sales Marketing Del Mum Blore The query I need in SQL not using any transformation. Can anyone help me out in this ? using decode function u can do that easily.... let me give u an example of 2 rows n 2 column x 1 2

y a

b

select decode(y,a,1,b,a) x,decode(y,a,2,b,b) y from table; result will be like this x 1 a

y 2 b

The WHERE CURRENT OF Clause -------------------------------------------------------------------------------PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data. If we give WHERE condition, the whole table have to be searched for the condition to be fulfilled for the updation to be done. But if we write 'WHERE CURRENT OF' the updation is done in the current record of the table without scanning the whole table. The general format for the WHERE CURRENT OF clause is as follows: UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name; DELETE FROM table_name WHERE CURRENT OF cursor_name; Example:

DECLARE CURSOR fall_jobs_cur IS SELECT ... same as before ... ; job_rec fall_jobs_cur%ROWTYPE; BEGIN OPEN fall_jobs_cur; LOOP FETCH fall_jobs_cur INTO job_rec; IF fall_jobs_cur%NOTFOUND THEN EXIT; ELSIF job_rec.do_it_yourself_flag = 'YOUCANDOIT' THEN UPDATE winterize SET responsible = 'STEVEN' WHERE CURRENT OF fall_jobs_cur; COMMIT; EXIT; END IF; END LOOP; CLOSE fall_jobs_cur; END; Oracle Insert statement execution ... OR What is the Hirerachy of SQL ?? -------------------------------------------------------------------------------The execution flow of sql statement is internally done by means of internal cursor. these are steps followed in executing the statement Parse bind execute fetch close cursor.... What is a database trigger ? Name some usages of database trigger ? -------------------------------------------------------------------------------A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens. Data auditing , Implementing complex business rules, security are main uses of database triggers. why order by clause maintains column number values instead of column names ? --------------------------------------------------------------------------------

The column numbers are used so that you can do the order by without keying in all the column names in the order by clause. select empno, ename from emp order by empno; -- qry 1 could be replaced by select empno, ename from emp order by 1; -- qry 2

If you wanted to change the order as may be order by ename asc, that would have required doing... select empno, ename from emp order by ename; -- qry 3 instead of just changing the qry to ... select empno, ename from emp order by 2; -- qry 4 Is it possible to use Transaction control statements inside triggers???... -------------------------------------------------------------------------------Autonomous Transaction is a feature of oracle 8i which maintains the state of its transactions and save it , to affect with the commit or rollback of the surrounding transactions.

Here is the simple example to understand this :-

ora816 SamSQL :> declare 2Procedure InsertInTest_Table_B 3 is 4 BEGIN 5 INSERT into Test_Table_B(x) values (1); 6 Commit; 7 END ; 8 BEGIN 9 INSERT INTO Test_Table_A(x) values (123); 10 InsertInTest_Table_B; 11 Rollback; 12 END; 13 / PL/SQL procedure successfully completed. ora816 SamSQL :> Select * from Test_Table_A; X---------- 123 ora816 SamSQL :> Select * from Test_Table_B; X---------- 1 Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior. Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION. ora816 SamSQL :> declare 2 3 4 5 6 7 8

Procedure InsertInTest_Table_B is PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT into Test_Table_B(x) values (1); Commit; END ;

9 BEGIN 10 INSERT INTO Test_Table_A(x) values (123); 11 InsertInTest_Table_B; 12 Rollback; 13 END; 14 /PL/SQL procedure successfully completed. ora816 SamSQL :> Select * from Test_Table_A;no rows selected ora816 SamSQL :> Select * from Test_Table_B; X---------- 1 Can you use a reference cursor as an input parameter in a procedure with out declaring it explicitly? -------------------------------------------------------------------------------Oh

sorry.

You can not declare . Here is the example PROCEDURE test_ref (emp_cur IN my_refcursor) IS emp_rec emp%ROWTYPE; BEGIN LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; dbms_output.put_line(emp_rec.ename ||' is a ' || emp_rec.job); END LOOP; END; It will throw error PLS-00201: identifier 'MY_REFCURSOR' must be declared But you can pass it as sys_refcursor without getting any error. CREATE OR REPLACE PROCEDURE test_ref (emp_cur IN sys_refcursor) IS emp_rec emp%ROWTYPE; BEGIN LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; dbms_output.put_line(emp_rec.ename ||' is a ' || emp_rec.job); END LOOP; END; What are two virtual tables available during database trigger execution ? -------------------------------------------------------------------------------OLD and NEW are two virtual tables available during database trigger execution. UPDATE statement has access to

both old and new values.

INSERT statement has access only to new values. Old values are NULL for insert statement. DELETE satement has access only to old values. statement. Write

the

order

New values are NULL for delete

of precedence for validation of a column in a table ?

-------------------------------------------------------------------------------create table a ( b number, c number check (c >100) ); create or replace trigger t1 before insert on a begin dbms_output.put_line('this is before insert trigger'); end; create or replace trigger t2 after insert on a begin dbms_output.put_line('this is after insert trigger'); end; insert into a values (1,99); / this is before insert trigger insert into a values (1,99) * ERROR at line 1: ORA-02290: check constraint (XXINV.SYS_C00106711) violated insert into a values (1,199); / this is before insert trigger this is after insert trigger 1 row created. So, if it is before insert trigger then the trigger will run first before the constriants in the table. if it is after insert trigger , contraints are checked first and then trigger will run. What is an Exception ? What are types ?... -------------------------------------------------------------------------------Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception handling part in the PL/SQL There are three types of exceptions 1. predefined Examples NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR ZERO_DIVIDE INVALID_CURSOR 2. non-predefined Examples Declare within the declarative section and allow allow Oracle server to raise implicitly o SQLCODE � Returns the numeric value for the seeor code

o

SQLERRM � Returns the message associated with error number

DECLARE -- PRAGMA EXCEPTION_INIT (exception, error_number) RAISE � WHEN EXCEPTION_NAME THEN � 3. user defined IF confidition the RAISE EXCEPTION or RAISE_APPLICATION_ERROR Predefined exceptions are defined by the system we have some predefined exception names to handle some exceptions non-predefined exceptions are also raised by the system only but we dont have any predefined names we have to trap the exceptions with error code defined by the system User defined exceptions are defined by the user. He has to declare the exception, he has to raise the exception. What is Pragma EXECPTION_INIT ? Explain the usage ? -------------------------------------------------------------------------------The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. It should be declare at the DECLARE section. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number) example declare salary number; FOUND_NOTHING exception; Pragma exception_init(FOUND_NOTHING ,100); begin select sal in to salaryfrom emp where ename ='ANURAG'; dbms_output.put_line(salary); exception WHEN FOUND_NOTHING THEN dbms_output.put_line(SQLERRM); end; Display the emploee records who joins the department before their manager? -------------------------------------------------------------------------------select t.empno,t.hiredate,t.mgr,t1.empno,t1.hiredate from emp t,emp t1 where t.mgr=t1.empno and t.hiredate < t1.hiredate What is Raise_application_error ? -------------------------------------------------------------------------------Raise_application_error is used to create your own error messages which can be more descriptive than named exceptions.

Syntax is:Raise_application_error (error_number,error_messages); where error_number is between -20000 to -20999.. How many bites does varchar2(30) needs ? -------------------------------------------------------------------------------The varchar2(30) will take 30 bites. Because the size is specified as 30. if you entered a data of only 20 chars the remaining 10 will be transformed to the other field or row. By this the wastage of the disk space can be minimised. What are the return values of functions SQLCODE and SQLERRM ? -------------------------------------------------------------------------------SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE. Dispaly employee records who gets more salary than the average salary in their department? -------------------------------------------------------------------------------select * from employe where salary > ( select avg(salary) from dept) where dept.deptno = employe.deptno); what is data structure -------------------------------------------------------------------------------The way the data maintained in the collection of tables can be explained by the data structure. The structure of database will show on which conditions the database will be maintained with out inconsistancy. Where the Pre_defined_exceptions are stored ? -------------------------------------------------------------------------------PL/SQL declares predefined exceptions in the STANDARD package. Write a query to display employee records having same salary? -------------------------------------------------------------------------------select a.empno,a.ename,a.sal from scott.emp a where a.sal in (select sal from scott.emp group by sal having count(*)>1) OR select distinct e.name,e.salary from employe e, employe a a.salary and e.name != a.name; NAME SALARY ---------- ---------MANI 10000 SELVAM 10000 SURAJ 10000 KAMAL 20000

where e.salary =

RAMESH SARA

20000 20000

6 rows selected. What is a stored procedure ? -------------------------------------------------------------------------------A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exceptionhandling section. A procedure can be compiled and stored in the database as a schema object. What is one time procedure -------------------------------------------------------------------------------One time only procedure is executed only once when the package is first invoked within the user session. The key word END is not used at the end of the one time only procedure. How do you view the last record added to a table? -------------------------------------------------------------------------------select * from test where rowid = ( select max(rowid) from test); Difference between functions and procedures -------------------------------------------------------------------------------Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. 2. Function can be called from SQL statements where as procedure can not be called from the sql statements 3. Functions are normally used for computations where as procedures are normally used for executing business logic. 4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. 5.� Function returns 1 value only. Procedure can return multiple values (max 1024). Why is insert faster than delete? -------------------------------------------------------------------------------Deletion is slow becoz searching has to be done b4 deleting .. but insertion can b done just like tat create a matrix query to display the job, the salary for that job based on department number and the total salary for that job, for departments 20,50,80, and 90,giving each column and appropriate heading. --------------------------------------------------------------------------------

select department,job,sum(salary) from emp where department in(20,50,80,9) group by rollup(department,job) Note : ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query. write a query to display the no.of people with the same job -------------------------------------------------------------------------------SELECT job,COUNT(*) FROM emp GROUP BY job; What are advantages fo Stored Procedures / -------------------------------------------------------------------------------Extensibility,Modularity, compilation.

Reusability,

Maintainability

and

one

time

How to give select privilege on all the objects owned by an user(say user1) to another user say(user2) using a single sql statement? -------------------------------------------------------------------------------You can try this command GRANT SELECT ON ALL TO USER2 Create a query that display the last name,hire date and the day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.LAST_NAME HIRE_DATE DAYGrant 24-MAY-99 -------------------------------------------------------------------------------select last_name,hire_date, to_char(hire_date,'DAY') as DAY from emp order by to_char(hire_date-1,'d'); What are the modes of parameters that can be passed to a procedure ? -------------------------------------------------------------------------------IN,OUT,IN-OUT parameters i want to try create the database from my script w... -------------------------------------------------------------------------------CREATE DATABASE testdb DATAFILE 'testdb_system' SIZE 10M LOGFILE GROUP 1 ('testdb_log1a', 'testdb_log1b') SIZE 400K, GROUP 2 ('testdb_log2a', 'testdb_log2b') SIZE 400K; To run script SQl> @ C:script.txt How do you control the constraints in forms ? --------------------------------------------------------------------------------

Select the use constraint property is ON Block definition screen. OR we control the constraints in forms while selecting primary key 'YES' in property palette. What are the two parts of a procedure ? -------------------------------------------------------------------------------Procedure Specification and Procedure Body. explain the differences between cost-based optimizer and Rule-based optimizer ? Why DB2, Sybase is cost based and SQL, Oracle are Rule Based Optimisers ? -------------------------------------------------------------------------------A long time ago, the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically, the RBO used a set of rules to determine how to execute a query. If an index was available on a table, the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example, assume someone put an index on the GENDER column, which holds one of two values, MALE and FEMALE. Then someone issues the following query: SELECT * FROM emp WHERE gender='FEMALE'; If the above query returned approximately 50% of the rows, then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10% of the total table volume, using an index would slow things down. The RBO would always use an index if present because its rules said to. It became obvious that the RBO, armed with its set of discrete rules, did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table, its indexes and the data distribution to make better informed decisions. Using our previous example, assume that the company has employees that are 95% female and 5% male. If you query for females, then you do not want to use the index. If you query for males, then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO. Give the structure of the procedure ? -------------------------------------------------------------------------------Create or replace procedure proc_name (optional parameters) as begin executable statements; exception handling end; end proc_name; Can we create two blocks with the same name in form 3.0 ? --------------------------------------------------------------------------------

No. Explain how procedures and functions are called ???... Function is called as part of an expression. sal := calculate_sal ('a822'); procedure is called as a PL/SQL statement calculate_bonus ('A822'); What is Overloading of procedures ? -------------------------------------------------------------------------------Overloading procs are 2 or more procs with the same name but different arguments. Arguments needs to be different by class it self. ie char and Varchar2 are from same class. What are the main advantages of packages ??? -------------------------------------------------------------------------------Package is a schema object that groups logically related PL/SQL types, items and subprograms. Basically contains two parts namely specification and body. 1- Since packages has specification and body separate so, whenever any ddl is run and if any proc/func(inside pack) is dependent on that, only body gets invalidated and not the spec. So any other proc/func dependent on package does not gets invalidated. 2- Whenever any func/proc from package is called, whole package is loaded into memory and hence all objects of pack is availaible in memory which means faster execution if any is called. And since we put all related proc/func in one package this feature is useful as we may need to run most of the objects. 3- we can declare global variables in the packag what is the difference between rownum,rowid -------------------------------------------------------------------------------Rownum is just the serial No of your output while Rowid is automatically generated unique id of a row an it is generated at the time of insertion of row. Rownum is numeric and rowid is 16 bit hexadecimal no. What are two parts of package ? -------------------------------------------------------------------------------The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ? --------------------------------------------------------------------------------

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures. How packaged procedures and functions are called from the following? -------------------------------------------------------------------------------a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); b. BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCTION NAME(arguments); END; END EXEC; c. EXECUTE PACKAGE NAME.PROCEDURE VARIABLE g_salary NUMBER EXECUTE :g_salary := get_sal(117) What is a Trigger ? -------------------------------------------------------------------------------A piece of logic that is executed at or triggered by a SQL *forms event. Can we create sequence to a View?? -------------------------------------------------------------------------------Sequence is used to generate numbers which can be stored in tables. View does not have data of its own and is generally used to query data. So sequence can not be created to a view. Name the tables where characteristics of Package, procedure and functions are stored -------------------------------------------------------------------------------The Data dictionary tables/ Views where the characteristics of subprograms and Packages are stored are mentioned below a) USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS b) USER_SOURCE, ALL_SOURCE, DBA_SOURCE c) USER_DEPENCENCIES d) USER_ERRORS, ALL_ERRORS, DBA_ERRORS There are how many maximum no of colimns in atable in oracle?There are how many maximum no of canvases in a form in forms6i an forms9i? -------------------------------------------------------------------------------A table contains maximum

1000 columns

What is Data Concarency and Consistency? --------------------------------------------------------------------------------

Data Concarency => Means that many users can access data at the same time. Data Consistency => M How consistent is the view of the data between and within multiple sessions, transactions or statements What is the data type of the column of Dual table?... -------------------------------------------------------------------------------DataType of the Column of Dual table is Varchar2(1) What is difference between varchar and varchar2 -------------------------------------------------------------------------------both are same varchar is ANSI standardvarchar2 is Oracle Pro *C -------------------------------------------------------------------------------The Pro* c/C++ precompiler takes the SQL statement that embeded in C/C++ code convert into standard C/C++ code . when succefully precompile this code ,the result is a C or C++ programe that we compile and used to build the application that access the Oracle Application What is the difference between "NULL in C" and "NU... -------------------------------------------------------------------------------The NULL in C treated as Zero or void. but in SQL NULL but in orcale it means if no data available at this column and it can't manuplated What are the types of TRIGGERS ? -------------------------------------------------------------------------------trigger in Forms are totaly differ from trigger in oracle. Forms trigger: base on 1. 4. others other 3 levels of triggers 1)formlevel 2)block level

mouse navigation

2. key navigation

3. Transactional

3) item level

What is the output of the following pl/sql block ? -------------------------------------------------------------------------------declare v_empno emp.empno%type; begin select empno into v_empno from emp where empno = 10; exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found '); end; when others then * ERROR at line 6: ORA-06550: line 6, column 2:

PLS-00370: OTHERS handler must be last among the exception handlers of a block ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated When Others is always handeled at the very end of all handeled exceptions. In this others should be in the last means after No_data_Found. what is normalization? what is the advantage of normalization (briefly) -------------------------------------------------------------------------------Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability What are the privileges that view does not have as compared to normal table? -------------------------------------------------------------------------------We can't� select� next value and current value� from views. What are the different types of key triggers ? -------------------------------------------------------------------------------Key-function Key-others Key-startup key-next key-help all triggers has "key" word comes under key triggers How to see the existing constraints in a table? -------------------------------------------------------------------------------by using the command user_constraints how do you generate prime numbers in sql not in p... -------------------------------------------------------------------------------to generate prime numbers select decode(n,2,n,decode(mod(n,2),1,n,0)) primes from prime_test; What is REF Cursor? -------------------------------------------------------------------------------ref cusor is a dynamic type of cursor which is used to provide Reusability of cursor. CURSOR IN WHICH QUERY CAN BE CHANGED AT RUNTIME It is dynamic Cursor. They are two types 1) Strong- Return type 2) weak- No Return type 1.strong ref cursor: This has a return type defined. 2. weak ref cursor.

this doesnt have a return type 1. Describe the difference between a procedure, function and anonymous pl/sql block. Level: Low Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn?t have to. 2. What is a mutating table error and how can you get around it? Level: Intermediate Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other. 3. Describe the use of %ROWTYPE and %TYPE in PL/SQL Level: Low Expected answer: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type. 4. What packages (if any) has Oracle provided for use by developers? Level: Intermediate to high Expected answer: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked. 5. Describe the use of PL/SQL tables Level: Intermediate Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD. 6. When is a declare statement needed ?

Level: Low The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used. 7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why? Level: Intermediate Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL. 8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? Level: Intermediate Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception. 9. How can you find within a PL/SQL block, if a cursor is open? Level: Low Expected answer: Use the %ISOPEN cursor status variable. 10. How can you generate debugging output from PL/SQL? Level:Intermediate to high Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used. 11. What are the types of triggers? Level:Intermediate to high Expected Answer: There are 12 types of triggers in PL/SQL that consist of

combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.

---

Related Documents

Oracle Notes

May 2020 8

Oracle Notes

April 2020 14

Oracle Designer Notes

July 2019 25

Oracle Important Notes

November 2019 14

Oracle 9i Notes

May 2020 48

Oracle Asm Notes

November 2019 18

---

More Documents from "Shahid Mahmud"

Format For Vendor Bank Transfer Details.docx

December 2019 6

Jde_instances_liverpool.xlsx

December 2019 4

Oracle Notes

April 2020 14

Issue Document Saudi.docx

December 2019 3

Normalisation

April 2020 7

Designcatalogue_unit 5.pdf

November 2019 3

Copyright © 2024 PDFCOKE.