SQL Interview Questions
1. The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
TRUNCATE - cleans all data
RENAME- renames a table name
2. Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern
3. SELECT statements:
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.
4. The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value
5. The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
6. The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
7. The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name
8. Sort the Rows:
SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC
9. The IN operator may be used if you know the exact value you want to return for at least one of the columns.
SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)
10. BETWEEN ... AND
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.
11. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
12. Why does the following command give a compilation error?
DROP TABLE &TABLE NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
13. Which system tables contain information on privileges granted and privileges obtained? USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
14. Which system table contains information on constraints on all the tables created?obtained? USER_CONSTRAINTS.
15. State true or false. !=, <>, ^= all denote the same operation?
True.
16. State true or false. EXISTS, SOME, ANY are operators in SQL?
True.
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?
18. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;?
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
19. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
20. Which command executes the contents of a specified file?
START or @.
21. What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
sal = 11000, comm = 1000.
22. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN.
23. What command is used to get back the privileges offered by the GRANT command?
REVOKE.
24. What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );? NO.
Explanation : The query checks whether a given string is a numerical digit.
26. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN.
27. What operator performs pattern matching?
LIKE operator.
28. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
29. What operator tests column for the absence of data?
IS NULL operator.
30. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all.
31. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR.
32. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL).
33. What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
34. What command is used to create a table by copying the structure of another table?
CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
35. TRUNCATE TABLE EMP;DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP..
36. What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.
37. What are the wildcards used for pattern matching.?
_ for single character substitution and % for multi-character substitution.
38. What is the parameter substitution symbol used with INSERT INTO command?
&
39. What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.
40. What is difference between TRUNCATE & DELETE
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
41. What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
42. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.
43. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.
44. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
45. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
46. Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.
47. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
48. What is the fastest way of accessing a row in a table?
Using ROWID.
CONSTRAINTS
49. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.
50. What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
51. What is the usage of SAVEPOINTS?
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.
52. What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
53. What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
54. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.
55. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
56. 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 the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.
57. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.
58. How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
59. If 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.
60. What is a database link?
Database link is a named path through which a remote database can be accessed.
61. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.
62.What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
63. What are the advantages of VIEW?
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.
64. Can a view be updated/inserted/deleted? If Yes - under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
65. If a view on a single base table is manipulated will the changes be reflected on the base table?
If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.
66. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.
67. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
66. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.
67. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
68. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
69. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
70. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
71. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
72. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
73. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)
74. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
77. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger
78. Read the following code:
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;
RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
79. When invoking this procedure, you encounter the error:
ORA-000:Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated
with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error
code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
80. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END; You are about to add an argument to CALCULATE_BUDGET.
What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.
81. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
82. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
83. Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
84. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN RETURN TRUEl; ELSE RETURN FALSE; END IF; COMMIT; END; Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
85. Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
86. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
87. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
88. What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section,the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
89. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END; For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
90. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
91 How to implement ISNUMERIC function in SQL *Plus ? Method
1: Select length (translate(trim (column_name),'+-.0123456789',''))from dual; Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters) Method 2: select instr(translate('wwww','abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXX'),'X') FROM dual; It returns 0 if it is a number, 1 if it is not.
92 How to Select last N records from a Table? select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm) where a > ( select (max(rownum)-10) from clm) Here N = 10
The following query has a Problem of performance in the execution of the following
query where the table ter.ter_master have 22231 records. So the results are obtained
after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution
path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that
your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;
93. What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it
only checks for the existence (and status) of another foreign key Pointing to the
table. If one exists and is enabled, then you will get The following error. This
is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing
the TRUNCATE command, then re-enable them afterwards.
PL-SQL Interview Questions
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?
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
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 ?
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?
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?
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?
Expected answer: Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL?
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?
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.
SQL / SQLPlus Interview Questions
1. How can variables be passed to a SQL routine?
Expected answer: By use of the & symbol. For passing in variables the numbers
1-8 can be used (&1, &2,...,&8) to pass the values after the command into the
SQLPLUS session. To be prompted for a specific variable, place the ampersanded
variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double
ampersands tells SQLPLUS to resubstitute the value for each subsequent
use of the variable, a single ampersand will cause a reprompt for the
value unless an ACCEPT statement is used to get the value from the user.
2. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
3. How can you call a PL/SQL procedure from SQL?
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an example
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?username? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?? the values selected from the database.
6. What SQLPlus command is used to format output from a select?
Expected answer: This is best done with the COLUMN command.
7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
Level: High Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product?
Expected answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
Level: High Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Expected answer: Ascending
13. What is tkprof and how is it used?
Level: Intermediate to high Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
14. What is explain plan and how is it used?
Level: Intermediate to high Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width?
Level: Low Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen?
Level: Low
Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns
off screen output. This option can be shortened to TERM.
17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
Level: Low Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.
18. How do you generate file output from SQL?
Answer: By use of the SPOOL command
Posted by Sunil Dutt at 1:29 AM 0 comments Links to this post
Thursday, September 11, 2008
Customer Interface Diagram
Customer Interface Diagram
Posted by Sunil Dutt at 3:24 AM 1 comments Links to this post
Master Detail Forms
1) Create Two Tables (With Parent / Child relationships)
a) Create table orders
(order_no number(10),
custoner varchar2(20),
order_date date)
/
b) Create table lines
( line_no number(10),
order_no number(10),
item varchar2(20),
qty number(10),
price number(10),
amount number(10))
/
2) Alter the table structures and primary and foreign keys
Alter table orders add constraint orders_pk primary key (order_no);
Alter table lines add constraint lines_fk foreign key (order_no) references orders (order_no);
3) Create public synonyms for the tables.
4) Register two tables with constraints information.
5) Open form builder software.
6) Open template.fmb file.
7) Save as รจ TETMADET.fmb
8) Change form module name as form name.
9) Delete default blocks , canvasses and windows.
10) Create two windows , two canvasses
Window1 : Orders
Window2 : Lines
Canvas1 : Orders
Canvas2 : Lines
11) Assign Canvasses to Windows.
12) Assign Windows to Canvasses.
13) Assign property classes to windows and canvasses.
14) Create Two Blocks
Block1 : Orders Type : Form
Block2 : Lines Type : Tabular No of Records : 5
15) Define master detail block relationship between two blocks .
Relationship Name : Orders_Lines
16) Create a Control Block
17) Create a Check box on detail window/ detail canvas
Block => Control
Name => Orders_lines (Same as your master detail relationship name)
Checked value => Immediate
Unchecked Value => Deferred
Default => Immediate
18) Create a Push button in Master Window / Master Canvas
Name : LINES
Label : Lines ..
Block => Control
19) Assign Text Item property class to all text items on Orders and Lines block.
20) Set Module / Form Level properties
First navigation data block => Orders
Console Window => Orders
21) Modify App_custom Package
Close window procedure
If ( wnd = ‘Orders’) then
App_window.close_first_window;
Elsif (wnd = ‘Lines’) then
App_window.set_coordination(‘WHEN-WINDOW-CLOSED’, :CONTROL.ORDER_LINES (CHECKBOX) , ‘ORDERS_LINES’ (RELATIONSHIP NAME) );
End if;
Open Window Procedure
If (wnd = ‘Orders’) then
Go_block (‘ Orders’) ;
Return;
If (wnd = ‘Lines’) then
App_window.set_coordination (‘ OPEN-WINDOW’, :CONTROL,ORDERS_LINES , ‘ORDERS_LINES’);
Go_block (‘ Lines’);
End if;
22) Create a program unit
Name : Control
Type : Package Spec
Procedure Lines (Event In Varchar2) ;
Procedure Orders_Lines ( Event In Varchar2);
23) Create a program unit
Name : Control
Type : Package Body
Procedure Lines (Event In Varchar2) is
Begin
If (event = ‘ WHEN – BUTTON – PRESSED’ ) then
App_custom.open_window (‘Lines’);
Return;
End if;
End Lines;
Procedure Orders_Lines ( Event In Varchar2) then
Begin
If (Event = ‘ WHEN – CHECKBOX – CHANGED’) then
End if ;
End order_lines;
24) In control block write a WBP trigger on Lines Button.
Control . Lines ( ‘WHEN – BUTTON – PRESSED’);
25) In control block write a WCC trigger on Orders_Lines checkbox.
Control.orders_lines(‘WHEN- CHECKBOX – CHANGED’);
26) Modify PRE – FORM trigger
APP_WINDOW.SET_WINDOW_POSITION(‘ORDERS’,NULL,’ORDERS);
27) Save and Compile .
28) Register the form.
Posted by Sunil Dutt at 3:13 AM 0 comments Links to this post
CUSTOM FORM DEVELOPMENT
On the CLIENT machine create a FOLDER as say: c:\custom_sunil
In custom_sunil folder creates 2 folders forms, resource
Copy TEMPLATE.fmb, APPSTAND.fmb, .pll files to CLIENT
• Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to c:\custom_sunil\ forms directory copy all .pll files from /Applvis/visappl/au/11.5.0/resource to c:\custom_sunil\resource using ftp
• On windows go to command prompt
• Cd c:\custom_sunil\forms
• ftp cloneserver
• username: applmgr password: applmgr
• now you are at ftp prompt
• bin
• prompt
• cd visappl/au/11.5.0/forms/US ( here apparently cd $AU_TOP does not work)
• get TEMPLATE.fmb
• (file copied)
• get APPSTAND.fmb
• (file copied)
• lcd ./resource (check this. Basically you need to be in c:\custom_sunil\resource. You can go to that directory and then run ftp)
• cd visappl/au/11.5.0/resource
• mget *.pll
• ( now all .pll files are copied to c:custom_sunil/resource)
SET env variable FORMS60_PATH through regedit
• Regedit/HKEY_LOCAL_MACHINE/software/oracle
• Double click FORMS60_PATH
• At the end of existing value data add: ;c:\custom_sunil\froms;c:\custom_sunil\resource
Now open TEMPLATE.fmb and make the following changes
• DELETE BLOCKNAME &DETAIL BLOCK FROM DATABLOCK,DELETE BLOCKNAME FROM CANVAS AND WINDOW
• Create a window NEW_WIN, canvas NEW_CAN
• Create a new block based on the table you created in your custom schema
• In pre-form trigger: app_windows.set_window_position(‘NEW_WIN’)…….
• In program units open custom_package.AP_cutom_pacakge body
• Change : if (wind=’NEW_WIN’);
• Template name = SUNIL_FORM
• Save as SUNIL_FROM to c:\custom_sunil\forms
• (????????SET THE WINDOW NAME AS U HAVE CREATED NEW WINDOW IN PRE-FORM TRIGGER BY BLOCKNAME?????)
DEPLOY the FORM (upload it to AU_TOP/forms/US)
• Go to command prompt (on client)
• cd c:\cutom_sunil\forms
• ftp cloneserver
• cd visappl/au/11.5.0/froms/US
• bin
• prompt
• put SUNIL_FORM.fmb
Changing ORACLE_HOME (/Visdb/visdb/9.2.0 to /Applvis/visora/8.0.6)
• thru putty login as applmgr
• pwd: /Applvis
• echo $ORACLE_HOME: shows /Visdb/visdb/9.2.0
• cd visora
• cd 8.0.6
• . VIS_cloneserver.env (this changes ORACLE_HOME apparently based on pwd?)
• echo $ORACLE_HOME: shows /Applvis/visora/8.0.6
• now ORACLE_HOME is 8.0.6 (forms/reports home)
• pwd : gives /Applvis/visora/8.0.6
COMPILE and generate FMX
• (now you are in /Applvis/visora/8.0.6 directory and ORACLE_HOME is set to /Applvis/visora/8.0.6)
• pwd: gives /Applvis/visora/8.0.6
• f60gen module=$AU_TOP/forms/US/SUNIL_FORMS.fmb module_type=form user=apps output_file=$SUNIL_TOP/forms/US/SRIN_FORM.fmx compile_all=special batch=no
• this generates SUNIL_FORM.fmx and puts in SUNIL_TOP/forms/US
FORM REGISTRATION
• Login to applications with application developer responsibility
• Application/form
• Enter the following details
o Form: the fmx name (SUNIL_FORM)
o Application: Oracle Receivables (as per Amer) –give appropriate name based the intended use of this form
o user form name: SUNIL_FORM_U (this will appear in LOV)
o SAVE
Attach the FORM to FUNCTION
(Create a new function)
Application/function
Enter the following details
o Function: SUNIL_FUNCT
o User function name: SUNIL FUNCTION
o Form: SUNIL_FORM (previously registered)
o SAVE
Attach FUNCTION to MENU
Application/menu
• Enter the following details
o Menu: sunil_menu
o User menu name: sunil menu
o Seq: 1
o Prompt: sunil form
o Function: SUNIL_FUNCT( previously deined)
o SAVE
Attach MENU to RESPONSIBILITY
Attach RESPONSIBILITY to USER
Login as the new USER
• See the form
Posted by Sunil Dutt at 3:03 AM 3 comments Links to this post
Wednesday, September 10, 2008
TRIGGERS
TRIGGERS
IT IS A PL/SQL BLOCK OR PL/SQL PROCEDURE ASOCITED WITH A TABLE,VIEW,SCHEMA OR DATABASE EXECUTED AUTOMATICALLY RATHER IN TECHNICAL TERMS IMPLICITLY FIRED AUTOMATICALLY WHENEVER A SPECIFIC EVENT OCCURS UPON THE OBJECT ASSOCIAYED WITH.
TYPES OF TRIGGERS
THERE ARE TWO TYPES OF TRIGGERS.
APPLICATION TRIGGER:-FIRED WHENEVER AN EVENT //CAN BE CONSIDERED AS A DML OPERATION// OCCURS UPON A PARTICULAR APPLICATION.
DATABASE TRIGGER:-FIRED WHENVER AN EVENT (IN THE SENSE ANY DML OPERATION OR ANY SYSTEM EVENT I.E LOGON OR SHUTDOWN) OCCURS ON A SCHEMA OR DATABASE.THESE ARE FIRED IMPLICITLY
INSTEAD OF TRIGGERS
THESE ARE ONLY LIMITED TO THE VIEWS .THE NAME SIGNIFIES THE OPERATION OF THE TRIGGER.TO BE MORE ELABORATIVE WHENEVER A DML IS OPERATED UPON A VIEW THE ACTION IS TAKEN CARE OF BY THE INSTEAD OF TRIGGER.RATHER IF ANY OTHER TRIGGERS ARE ASSOCIATED WITH THAT TABLE THEN THOSE TRIGGER WILL BE FIRED.
WHY THE TRIGGERS ARE DESIGNED?
FOR RELATED TASKS TO BE PERFORMED AND TO CENTRALIZE THE GLOBAL OPERATIONS WHICH TAKES CARE OF THE ACTIONS RATHER CONSIDERING WHATEVER APPLIACTION OR WHOEVER THE USER MAY BE.
CREATING DML TRIGGERS
A TRIGGER CONTAINS
A) TRIGGER TIMING DESCRIBES ABOUT THE FIRING OF THE TRIGGER WRT TRIGGERING EVENT.
B) TRIGGERING EVENT DESCRIBES ABOUT THE DML WHICH IS TO BE TAKEN CARE FOR THE BY ACTIONS TO BE RAISED.
C) TRIGGER TYPE DESCRIBES ABOUT THE NUMBER OF TIMES OF EXECUTION OF TRIGGER.
D) TRIGGER BODY DESCRIBES ABOUT THE ACTIONS TAKEN CARE OF BY THE TRIBGGER.
DML TRGGER COMPONENTS
TRIGGER TIMING
A) BEFORE TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS BEFORE THE DML EVENT IS OPERATED UPON AN OBJECT.
B) AFTER TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS AFTER THE DML EVENT IS OPERATED UPON AN OBJECT.
C) INSTEAD OF SPECIFIES A SEPARATE EXECUTION PROCESS APART FROM THE TRIGGERING STATEMENT.THESE ACT UPON THE VIEWS AND ARE NOT MODIFIABLE.
DESCRIPTIONS
BEFORE TRIGGERS ARE USED TO DETERMINE THE STATUS OF TRIGGER STATEMENT WHETHER TO BE COMPLETED.ALSO BETTER EXPLANATION CAN BE ROLLBACK.ALSO TO FETCH THE COLUMN VALUES PRIOR TO EXECUTION AND TO VALIDATE RULES OF BUSINESS.
AFTER TRIGGERS ARE USED TO COMPLETE THE ACTON BEFORE TRIGGERING ACTION.IF THERE IS A PRESENCE OF A BEFORE TRIGGER THEN TO INITIATE A DIFFERENT ACTION.
INSTEAD OF TRIGGERS ARE USED TO MODIFY THE VIEWS WHICH CANNOT BE MODIFIED BY A SQL DML STATEMENT DUE TO LACK OF MODIFICATION INHERITANCE.THESE WORK IN THE BACKGROUND ACCORDING TO THE DML ACTIONS SPECIFIED IN THE TRIGGERING BODY.
TRIGGERING EVENTS CAN BE SPECIFICALLY LINKED WITH THE DMLS SUCH AS INSERT, UPDATE OR A DELETE.IN CASE OF UPDATE DML THE COLUMN LIST ARE TO SPECIFIED FOR WHICH THE TRIGGERING ACTIONS ARE TO BE TAKEN CARE OF.
TRIGGER TYPE SPECIFIES WHETHER THE TRIGGER IS TO BE FIRED FOR EACH ROW OR FOR MULTIPLE ROWS (STATEMENT TRIGGERS).THERE ARE TWO TYPES OF TRIGGERS.
1) ROW TRIGGERS ARE EXECUTED ONCE FOR EACH ROW RETRIEVED BY THE DML SPECIFIED IN THE STATEMENT.IT IS NOT EXECUTED IF THE STATEMENT DOES NOT RETURN ANY VALUE.IT IS NOT EXECUTED WHEN NO ROWS ARE SELECTED.
2) STATEMENT TRIGGER IS FIRED ONCE ON BEHALF OF THE TRIGGERING EVENT EVEN IF NO ROWS ARE AFFECTED AT ALL.
TRIGGER BODY EXPLAINS ABOUT THE ACTIONS TO BE TAKEN CARE OF BY THE TRIGGER.IT CAN BE PL/SQL BLOCK OR A CALL PROCEDURE.
NOTE
1) WHEN THE TRIGGERING DATA MANIPULATION STATEMENT EFFECTS THE A SINGLE ROW, BOTH THE ROW TRIGGER AND STATEMENT TRIGGER FIRE EXACTLY ONCE PROVIDED THE TYPE OF TRIGGER THAT HAS BEEN MENTIONED IN THE TRIGGER BODY.
2) WHEN A TRIGGERING DATA MANIPULATION STATEMENT AFFECTS MULTIPLE ROWS THEN THE STATEMENT TRIGGER FIRES EXACTLY ONCE AND THE ROW TRIGGER FIRES ONCE FOR EVERY ROWEFFECTED BY THE STATEMENT.
SYNTAX
CREATE TRIGGER
TIMING
EVENT1 OR OR
ON
TRIGGER NAME SHOULD BE UNIQUE COMPARED TO OTHER TRIGGERS.
SPECIFIES THE TIME WHEN THE TRIGGER WILL FIRE
EITHER OR
IDENTIFIES THE DML THAT CAUSES THE TRIGGER TO FIRE.
EITHER ,, OR ALL OF THE THREE.
NAME OF THE TABLE ASSOCIATED WITH THE TRIGGER.
EXPLAINS ABOUT THE ACTIONS PERFORMED.
IT BEGINS WITH A DECALERE AND END OR A CALL OF PROCEDURE.
USING COLUMNS NAMES WITH UPDATE TRIGGERS INCREASE THE PERFORMANCE BECAUSE THE TRIGGER IS FIRED ONLY WHEN THE UPDATION OF CONCERNED COLUMN OCCURS.IT IS NO WHERE CONCERNNED WITH THE UPDATION OF ANY OTHER COLUMNS OF THE DESCRIBED TABLE IN THE TRIGGER.
EXAMPLE:
IN THE DESCRIBED TRIGGER WHICH IMPLEMENTS THE BUSSINESS RULES THAT RESTRICTS THE ACCESS OF DATABASE TABLE AFTER THE OFFICE HOURS AND HOLIDAYS PROVIDED THE WORK DAY CALENDER IS 5 DAYS A WEEK.
CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES
BEFORE INSERT OR DELETE OR UPDATE ON EMPLOYEES
BEGIN
IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’,’SUN’)) OR
(TO_CHAR (SYSDATE,’HH24’) NOT BETWEEN ‘08’ AND ‘18’)
THEN
IF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSIF DELETING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSE
RAISE_APPLICATION_ERROR (-20500,’UR SATAMENT2’);
END IF;
END IF;
END;
DML ROW TRIGGERS
WE HAVE TO SPECIFY A SPECIAL PHRASE FOR INITIATING A ROW TRIGGER.REFERENING TO THE ABOVE MENTIONED SYNTAX OF TRIGGER AFTER THE TABLE NAME ‘FOR EACH ROW’ PHRASE IF SPECIFIED INDICATES THE TRIGGER TO BE A ROW TRIGGER.HERE THE NEW VALUES AND THE OLD VALUES ARE ALSO REFERED FOR CORELATION BETWEEN THE OLD VALUES AND NEW VALUES.
WE CAN ALSO RESTRICT THE FIRING OF A ROW TRIGGER BY SPECIFING A WHEN CLAUSE AFTER THE PHRASE MENTIONED ABOVE.
EXAMPLE
CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
IF NOT (:NEW.JOB_ID IN (‘AD_PRES’,’AD_VP’))
AND (:NEW.SALARY) > 15000
THEN
RAISE_APPLICATION_ERROR (-20500, )
END IF;
END;
EXPALAINATION
FOR AN UPDATE OR INSERT UPON EMPLOYEES TABLE IF THE JOB_ID SPECIED IS OTHER THAN AD_PRES AND AD_VP AND SALARY SPECIFIED IS GREATER THAN 15000 IN THE CASE TRIGGER IS FIRED.IN STRAIGHT EAPLAINTION THE EMPLOYEES WHICH HAVE A JOB_ID OF AD_PRES AND AD_VP CAN ONLY EARN A SALARY GREATER THAN 15000.
RESTRICTING A ROW TRIGGER
CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.JOB_ID = ‘SA_REP’)
BEGIN
IF INSERTING THEN
: NEW .COMMISSION_PCT:= 0;
ELSIF (:OLD.COMMISSION_PCT) IS NULL THEN
: NEW.COMMISSION_PCT:= 0;
ELSE
: NEW.COMMISSION_PCT:=:OLD.COMMISSION_PCT + 0.05;
END IF;
END;
EXPLAINATION
IF AN INSERT OPERATION IS OPERATED UPON THE EMPLOYEES TABLE WITH A JOB_ID SPECIFIED AS SA_REP AND COMMISSION_PCT WITH SOME VALUE THEN THE TRIGGER RESTRICT_SALARY WILL FIRE CAUSING A INSERTION OF ZERO IN COMMISION_PCT IN THE TABLE. (PLEASE NOTE LINE NO 7).OTHER WISE IF THE JOB IS OTHER THAN SA_REP THEN ROW IS FULLY INSERTED AS DESIRED.
IF AN UPDATE OPERATION IS OPERATED UPON THE TABLE THERE ARE TWO CASES TO BE NOTICED.
1) IF THE OLD VALUE OF COMMISSION_PCT IS A NULL THEN WHILE UPDATION OF SALARY RESTRICT_SALARY WILL FIRE AND THE COMMISSION_PCT WILL BE ASSIGNED A VALUE OF ZERO.(PLEASE SEE LINE NO 9)
2) IF THE OLD VALUE OF COMMISSION_PCT IS NOT NULL THEN RAISE_SALARY WILL FIRE AND NEW VALUE WILL BE WHAT EVER VALUE THAT HAS BEEN SPECIFIED IN THE ACTION.(PLEASE SEE LINE NO 11)
INSTEAD OF TRIGGERS
IF THE MODIFICATION IS REQUIERD TO BE DONE ON THE DATA OWNED BY AN UNUPDATEABLE VIEW I.E (A VIEW CONTAINING THE SET OPERATORS, DISTINCT CLAUSE, GROUP FUNCTIONS, OR JOINS BECOMES AN UNUPDATEABLE VIEW) THEN THE INSTEAD OF TRIGGERS ARE USED WHICH ARE FIRED BY THE ORACLE SERVER WITHOUT EXECUTING THE TRIGGERING STATEMENT OPERATING THE DML UPON THE UNDERLYING TABLES SPECIFIED.
CREATE OR REPLACE TRIGGER
INSTEAD OF
OR OR
ON VIEW_NAME
FOR EACH ROW
IF THE VIEW IS UPDATEABLE AND CONTAINS THE INSTEAD OF TRIGGERS THEN THESE TRIGGERS TAKE PRECEDENCE.
ALSO THE CHECK OPTIONS ARE NOT TAKEN CARE OF AT THE TIME OF FIRING OF INSTEAD OF TRIGGERS.RATHER IF WANTED THEY MUST BE SPECIFIED IN THE BODY OF INSTEAD OF TRIGGER.
SEE FOR MORE DETAILS ORACLE HAND BOOK
MANAGING TRIGGERS
WE CAN ALTER THE STATUS OF A TRIGGER BY DISABLING OR ENABLING IT.
ALTER TRIGGER DISABLE/ENABLE
FOR IN CASE OF TABLE
ALTER TABLE DISABLE/ENABLE ALL TRIGGERS
FOR RECOMPILING THE TRIGGER BODY
ALTER TRIGGER COMPILE
DROPPING A TRIGGER
DROP TRIGGER TRIGGER_NAME
CREATING DATABASE TRIGGERS
FOR CREATING A DATABASE TRIGGER FIRST THE TRIGGER CMPONENTS ARE TO BE DECIDED.
A TRIGGER DEFINED FOR A SYSTEM EVENT CAN BE AT A LEVEL OF DATABASE OR SCHEMA.FOR EXAMPLE THE LOG OFF TRIGGERS OR TRIGGERS INVOLVING DDL STATEMENTS ARE AT A LEVEL OF EITHER SCHEMA OR DATABASE. THE DATABASE SHUTDOWN TRIGGERS ARE AT A LEVEL OF SCHEMA.
TRIGGERS DEFINED AT SCHEMA LEVEL FIRES WHENEVER THE TRIGGERING EVENT INVOLVES THE SCHEMA OR TABLE.WHEREAS THE DATABASE LEVEL TRIGGERS FIRE FOR ALL USERS.
FOR DLL TRIGGERS THE POSSIBLE EVENTS MAY BE
1) CREATE STATEMENT
2) ALTER STATEMENT]
3) OR A DROP STATEMENT
ANY WAY THE CREATE TRIGGER SYNTAX WILL BE REMAINING THE SAME ALL THE TIME.
FOR A TRIGGER INVOLVED IN THE SYSYTEM EVENTS LIKE
1) AFTER SERVERERROR
2) AFTER LOGON
3) BEFORE LOGOFF
4) AFTER STARTUP
5) BEFORE SHUTDOWN
MUTATING TABLE
IT CAN BE DEFINED AS A TABLE BEING MODIFIED BY AN UPDATE, DELETE INSERT STATEMENT OR THE TABLE IS REQUIRED TO BE UPDATE BY THE EFFECTS OF ON DELETE CASCADE REFERENTIAL INTEGRITY ACTION.
A TRIGGERED TABLE IS ALSO A MUTATING ONE AS WELL AS ANY TABLE REFERENCING IT BY FOREIGN KEY CONSTRAINT.
PROTECTING DATA INTEGRITY WITH TRIGGERS
CREATE OR REPLACE CHECK_SALARY
BEFORE UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.SALARY)<(OLD.SALARY)
BEGIN
RAISE_APPLICATION_ERROR (-20508,’DECREASE SALARY NOT ALLOWED’)
END;
SNAPSHOT
IT IS A LOCAL COPY OF A TABLE DATATHAT ORIGINATES FROM ONE OR MORE REMOTE MASTER TABLES.THE DATA OF THE SANAPSHOT CAN BE QUERIED BUT NO DML OPERATIONS CAN BE OPERATED UPON A SNAPSHOT.TO KEEP IN PARALLEL WITH THE BASE TABLES THE SNAPSHOT SHOULD BE REFRESHED REGULARLY.
BENEFITS OF DATABASE TRIGGERS
IT PROVIDES US THE IMPROVED DATA SECURITY
IT ALSO FACILIATES IMPROVED DATA INTEGRITY.
THANK YOU
THIS DATA DOES NOT MAKE YOU MASTER IN TRIGGERS.THERE ARE SO MANY CONCEPTS I JUST TRIED MY LEVEL BEST TO CONCENTRATE ON BASICS
Posted by Sunil Dutt at 9:35 AM 0 comments Links to this post
All modules Interface Tables
GL INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE
AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE
AR INTERFACE TABLES
TNAME TABTYPE
------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE
RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE
FA INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------
FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE
INVENTORY INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE
TNAME TABTYPE
------------------------------ ------- --------------------------------------
MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE
TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE
PO INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- -------------------------
PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE
BOM INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------------------
BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE
WIP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- --------------------------
WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE
ORDER MANAGEMENT INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- -----------------------------------
SO_CONFIGURATIONS_INTERFACE TABLE
SO_HEADERS_INTERFACE_ALL TABLE
SO_HEADER_ATTRIBUTES_INTERFACE TABLE
SO_LINES_INTERFACE_ALL TABLE
SO_LINE_ATTRIBUTES_INTERFACE TABLE
SO_LINE_DETAILS_INTERFACE TABLE
SO_PRICE_ADJUSTMENTS_INTERFACE TABLE
SO_SALES_CREDITS_INTERFACE TABLE
SO_SERVICE_DETAILS_INTERFACE TABLE
WSH_DELIVERIES_INTERFACE TABLE
WSH_FREIGHT_CHARGES_INTERFACE TABLE
WSH_PACKED_CONTAINER_INTERFACE TABLE
Posted by Sunil Dutt at 8:55 AM 1 comments Links to this post
Tuesday, September 9, 2008
GL interface Diagram
Posted by Sunil Dutt at 5:59 AM 0 comments Links to this post
Trigger Solutions
Problems (For DML Triggers):
Q.1 Keep the backup of department data for DELETE and UPDATE.
Ans: CREATE OR REPLACE TRIGGER dept_backup_trg
AFTER DELETE OR UPDATE ON dept
FOR EACH ROW
BEGIN
INSERT INTO dept_backup (deptno, dname, loc)
VALUES (:OLD.deptno, :OLD.dname, :OLD.loc);
END;
Q.2 Secure emp table from SCOTT user for DELETE or UPDATE of manager and salesman records.
Ans: CREATE OR REPLACE TRIGGER emp_dml_check
BEFORE DELETE OR UPDATE ON emp
FOR EACH ROW
WHEN (USER = 'SCOTT')
BEGIN
IF UPPER(RTRIM(LTRIM(:OLD.JOB))) IN ('MANAGER', 'SALESMAN')
THEN
RAISE_APPLICATION_ERROR
(-20001, 'You can not update or delete MANAGER or SALESMAN records');
END IF;
END;
Q.3 Delete all related employees as soon as dept is deleted from dept table.
Ans: CREATE OR REPLACE TRIGGER Delete_Emp_Trg
AFTER DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno = :OLD.deptno;
END;
Problems (For DDL Triggers):
Q.1 Create a DDL trigger to prevent removal of any table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott
BEFORE DROP ON scott.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20001, 'No table can not be droped from SCOTT schema');
END;
2. Create a DDL trigger to prevent removal of emp table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott_on_Emp
BEFORE DROP ON scott.SCHEMA
BEGIN
IF UPPER(RTRIM(LTRIM(sys.dictionary_obj_name))) = 'EMP'
THEN
RAISE_APPLICATION_ERROR (-20001, 'EMP table can not be droped from SCOTT schema'); END IF;
END;
Q.3 create a DDL trigger to prevent removal of any table under any schema. (User must have ADMINISTER DATABASE TRIGGER privilege).
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Any_Table
BEFORE DROP ON DATABASE
BEGIN
IF UPPER(RTRIM(LTRIM(sys.sysevent))) = 'DROP' AND UPPER(RTRIM(LTRIM(sys.dictionary_obj_Type))) = 'TABLE
THEN
RAISE_APPLICATION_ERROR (-20001, 'Drop Table is not allowed under this Database');
END IF;
END;
Problems (For Instead Of Triggers)
1. create a trigger to allow Data Manipulation on EMP and DEPT tables via the View.
1.1 Create a view on emp and dept tables combination.
1.2 Create Instead Of Trigger on the View.
Ans: CREATE OR REPLACE TRIGGER emp_dept_vw_trg
INSTEAD OF INSERT OR DELETE OR UPDATE ON emp_dept_vw
FOR EACH ROW
BEGIN
IF INSERTING = True THEN
INSERT INTO dept (deptno, dname, loc)
VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);
--
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);
--
ELSIF UPDATING = True THEN
UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc WHERE deptno = :NEW.deptno;
--
UPDATE emp SET ename = :NEW.ename, job = :NEW.job, mgr = :NEW.mgr, hiredate = :NEW.hiredate, sal = :NEW.sal, comm = :NEW.comm, deptno = :NEW.deptno WHERE empno = :NEW..empno; ELSE DELETE FROM emp WHERE empno = :OLD.empno; DELETE FROM dept
WHERE deptno = :OLD.deptno;
END IF;
END;
Problems (For Database Events Triggers)
Q.1 Create a trigger that denies login for any user except SYSTEM or INTERNAL users.
Ans. CREATE OR REPLACE TRIGGER check_user_login
AFTER LOGON ON DATABASE
BEGIN ]
IF :sys.login_user NOT IN ('SYS', 'SYSTEM')
THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to Login');
END IF;
END;
Q.2 Create a trigger to load a package into KEEP buffer as soon as Database is started.
Ans. CREATE OR REPLACE TRIGGER pin_package
AFTER STARTUP ON DATABASE
BEGIN
DBMS_SHARED_POOL.KEEP ('SCOTT.EMP_PG', 'P');
END;
Posted by Sunil Dutt at 1:45 AM 0 comments Links to this post
Entire - Oracle
Good zip file of Entire Oracle worth reading this have a look friends
http://www.esnips.com/doc/d327c757-49b3-47f7-bf7d-36d6a854c497/Entire-Oracle
Posted by Sunil Dutt at 1:44 AM 2 comments Links to this post
Oracle Apps Technical Interview
1. Using PL/SQL, which exception is raised when a division by zero is attempted?
Ans ) zero divide error occurs
2. Which constraint type ensures that each row in a table contains an entry that uniquely identifies that row from any other. Ans ) primary key
3. Views can be defined with up to (?) columns. Ans ) 254
4. During database creation, which of the following database users require setting their password? Ans ) sys
5. What is a Cartesian product?
Ans )The product of a select statement which has more than one table, and no where clause
6. A developer calls and reports they are receiving an ORA-00901: invalid CREATE command. What is most likely the problem?
Ans ) The CREATE command was not followed by a valid option
7. The DECODE () function is similar to (?).
Ans ) an if then else construct
8. In which section of a PL/SQL block will a user defined exception be handled?
Ans ) exception block
9. Which of the following are used to determine the order of an ORDER BY clause?
Ans ) ASC
10. Which of the following are NOT number functions?
Ans ) TODATE()
11. What column heading is displayed for the following query? SELECT COUNT(ENAME) Count FROM EMP Ans) COUNT
12. More frequent checkpoints reduce the time needed to (?). Ans ) recover
13. A (?) is an area of memory, given a name, that is used to store a value.
Ans ) variable
14. How is the command SELECT * FROM EMP WHERE EMPNO=&X.45 interpreted if 123 is entered as a value for X?
Ans ) SELECT * FROM EMP WHERE EMPNO=12345;
15. The third value in a ROWID represents the (?) of that row. Ans ) file ID
16. Which of the following are NOT valid comparison operators?
Ans ) == (two equal signs)
17. What structure is used to prevent more than one user from updating the same data in a table at the same time? Ans ) locks
18. The default length for char data type is Ans ) one
19. The default order in which records are retrieved when an order by clause is used is
Ans ) Ascending
20. The operator precedence between AND & OR is Ans) AND over OR
21. The output of ltrim (\"BILL CLINTON\",BALL) is
Ans) ILL CLINTON
22. The output of substr (\"CREATE\",3,3) is Ans) EAT
23. The number of minimum join condition that are required to join n tables is N-1
24. In an outer join (+) sign is placed on the Ans) deficient table side
25. A sub-query can appear within which of the following clauses & operators 1. Select 2. Where 3. Having 4. Set operators Ans ) select , having and set operators
26. If where clause is not specified in an update statement then
Ans ) all rows updated
27. The database object from which multiple users may generate unique integer is
Ans ) sequence
28. Cluster columns must correspond to columns in each of the cluster tables in
Ans ) size and column name
29. The clause in the computer command that helps in computing count of rows is
Ans ) number
30. The elements, which are mandatory for a PL/SQL blocks are
Ans ) begin and end;
31. The concatenation operator in PL/SQL is Ans)
32. Which of the following are available in PL/SQL and not in SQL
Ans ) data access compatibility
33. The assignment operator in PL/SQL is Ans) =
34. The procedure which lets you issue user-defined error messages from a stored sub-program or database trigger is
Ans ) exception_unit and raise_application_error
35. Data can be retrieved into variables using which of the following options
Ans ) select into and fetch
36. Which of the following does a cursor For Loop do 1. Open a cursor 2. Fetch 3. close cursor Ans) ALL
37. Which statement can appear in a trigger body Ans) SQL set
38. To create a database trigger, the user must have the privilege.
Ans ) create trigger privilege
39. Which operator finds common rows from 2 or more different queries?
Ans ) intersect
40. Foreign key constraint can be enforced by using constraint.
Ans ) references
41. The statement fetches rows from a table to a cursor. Ans) select
42. The NVL() function is used to Ans) substitute a new value
43. The maximum size of LOB is Ans) 4gb
44. How many triggers can create per table Ans) 12
45. Maximum how many constraints can enforce on a single column Ans) four
46. PL/SQL stands for Ans ) procedural language of sql
47. which are the character functions will take string parameter and it will return numeric Ans) length and instr
48. Select trunc(199.99) from dual Ans) “199”
49. Which constraint is only a table level Ans) composite primary key
50. Triggers can call in subprograms Ans) yes
51. The NULL columns do not participate in arithmetic computations
52. You cannot create more than one trigger per table.
53. Which Procedure produces output on the screen
Ans ) dbms_output.putline()
54. The cursor is created by any SQL statement. Ans) implicit
55 Which triggers are used for views? Ans) instead of triggers
56 select statement is Ans ) DRL
57 trigger is fired Ans ) implicitly
58 A Cartesian product is Ans ) only available in oracle 8i
59 Select empcode, sum(basic) from empmaster? Can you create a view based on the above select? Ans ) no
60 Which of the following correctly describes how to specify a column alias
Ans ) Place the alias after each column, separated by white space.
61 The command to open a CURSOR FOR loop is
Ans ) No command is required
62 If left out, which would cause an infinite loop Ans) exit
63 After referencing NEXTVAL, the value in CURRVAL of a sequence is
Ans ) incremented by 1
64 What control structure prevents more than one user from updating data in a table?
Ans ) locks
65 Which of the following two constraints automatically create an index?
Ans ) Unique Constraint
66 How many equality conditions are required to join four tables without a Cartesian product? Ans) two
67 How do we join two or more tables? Using =
68 What is the minimum number of shared columns to join two tables in a select statement? Ans ) one
69 Which of the following are NOT valid comparison operators? Ans) >>
70 When entering a SELECT statement from the SQL prompt the command is terminated with which character? Ans ) /
71 What function is similar in function to an IF-THEN-ELSE statement?
Ans ) decode()
72 Which of the following is a character function? Ans) to_number()
73 The binary extension for the menu is Ans) .mmb
74 An ER Diagram which relates 2 entities with dark line joining one entity and with crow foot on the other side is called
Ans) Many to Many
75 Once defined, how long will a variable remain in SQL*Plus?
Ans) Until the session Completes
76 After an Update statement, you write an exception with no_data_found, too_many_rows and when others. If the Update statements where condition fails to update any rows then which exception will be raised?
Ans) no_data_found
77 Not Null is which type of constraint? Ans ) check
78 How do you make sure that Oracle will use an Index when you write a select statement? Ans) by selecting columns which have index
79 Forms allows you to manipulate data only if it is Ans) normal mode
80 The different views of objects in object Navigator
Ans ) Ownership view and Visual View
81 For undoing changes in the form during design time
Ans ) Press CTRL+Z
82 Alert message should not exceed Ans) 200 Characters
83 Data Blocks in the form builder can be classified as
Ans ) Master & Detail Block
84 The type of alerts can be set Ans) Note, Caution, Stop
85 The 4 different states of records are as follow ( when internal events of forms run time) Ans ) New, Insert, query, Changed
86 The hierarchy tree of objects in the form application
Ans ) Form(Module), Block, record, Item
87 VALIDATE RECORD , Trigger (event) occurs only if the record is Ans) insert
88 LOV (LIST OF VALUES) is a combination of
Ans ) Modal dialog Boxes, List and Record Groups
89 The trigger can be classified as the following:-
Ans ) Smart triggers, Un restricted, Triggers, restricted Triggers
90 The built in procedure to open a form that is independent from calling form is
Ans ) open_form
91 The way to involve Report from form object is Ans) run_report_object
92 The is the syntax and place the variable declared in form module to all the triggers in the form
Ans ) Declare in PREFOR and the syntax is :Global.
93 A primary key constraint cannot be added on the column of which data type
Ans ) long
94 The comprises set of files that protect altered database data that has not been written to data files Ans) redo log
95 Maximum size of raw data type is Ans) 2kb
96 Which one of the following is not applicable on INSTEAD OF Trigger
Ans ) Can be applied on tables
97 In Oracle 8, a char database can hold up to Ans) 2000 bytes
98 After creating a sequence, to view the first value we use which keyword
Ans ) nextval
99 In PL/SQL assignment operator is replaced using the reserved word
Ans) default
100 is a valid pseudo column
Ans) rowid, rownum, sysdate, uid, nextval, curval
101 The Index created by the Primary key column is called ans) unique index
102 The operation cannot be performed using views when the view definition includes more than on table Ans) insert
103 The parameter cannot be changed while altering a sequence
Ans ) start with n (n cant be changed)
104 To check the source code for the object types use the data dictionary view Ans user_source
105 Which of the following is most restrictive of table level locks?
Ans) exclusive lock
106 In PL/SQL block what is the default value of the variable Ans) null
107 Find output of the following ( Select substr( \"christmas\" , 4,3) from dual)
Ans ) ist
108 The ability of object to take more than one form is known as
Ans ) polymorphism
109 When the user is trying to assign values to the attributes of an uninitialized object, which of the following exception is raised Ans) VALUEERROR
110 The attribute in the create sequence syntax specifies that the sequence continues to generate values from the beginning after reaching either its max or min value Ans) increment
111 A composite unique key can contain how many number of columns Ans) 16
112 What operations can be performed on sequence Ans) Alter, Select
113 Restriction on triggers can be achieved using what clause Ans) when
114 To check the partition type which data dictionary can be used.
Ans ) user_tab_partitions
115 Outer joins retrieves Ans) both matched and unmatched rows
116 A function used to convert a null value to value is Ans) decode
117 Role is Ans ) group of privileges
118 A returns all rows returned by the simple join as well as those rows from one table that do not match any row from the other table Ans) outer join
119 The data that stored in the cursor is called as Ans ) active data set
120 what is the format mask for Julian data Ans ) j
121 Label is a word associated with an statements Ans) conditional
122 level locks are not released by rolling back to save point Ans ) row level
123 you can use the CASE function in place of Ans) decode
124 The writes modified blocks from the database buffer cache to the data files
Ans ) database writer
125 The magnitude range of a binary integer value in pL/sql is
Ans ) -231-1 to 231-1
126 Trigger name should not exceed characters Ans) 30 characters
127 The REF operator returns of the row object Ans) OID
128 PL/SQL is a structured language? Ans ) block structured language
129 Raw types are used to store which type of data Ans ) binary
130 PCTFREE is a portion of the data block that is not filled by rows as they are inserted in to a table but is reserved for Ans ) 'Future insert
131 Sign function returns Ans) +, - and =
132 Syntax of MERGE function
Ans) merge into using /<> on when matched these update set when not matched then insert values
133 The oracle engine provides procedure named that allows programmers to show user defined messages Ans) Raise_Application_Error
134 Which are called as trigger actions Ans) Before and after
135 A function that act on only one value at a time are called as
Ans ) Scalar function
136 is used to get cross tabulation & cumulative values
ans ) CUBE and ROLLUP
137 Which is used for bulk bind? Ans) for all
138 which is used to modify any operating system files Ans) UTL_File
139 which is used to insert values in to multiple tables using select statements
ans ) INSERTALL
140 Which appends on operating system specific line terminator Ans) put_line
141 Which is an object , that is completely contained within another
Ans ) embedded object
142 Which of the following is false regarding data files?
Ans ) Data files can be associated with two or more table spaces
143 In which data dictionary table the object type information will be stored
Ans ) User_Types
144 When you use the THE class in DML statements Ans) Nested table
145 Which is used to store complete column information at a time in to a variable
Ans ) bulk collect
146 when you use for update class in a cursor which is compulsory
ans ) where current of
147 When cursor is in for loop which operations are not necessary
Ans ) Open, Fetch, Close
148 RowID consistes of
Ans ) Data file Id, Object Id , Block Id, Row_Sequence_ID
149 What is a object, which can be created only at run time Ans ) Timer
150 what is the extension of .PLL Ans) Programming Link Library
151 what is the extension of object library module Ans) OBL
152 How many styles are there in alert property Ans) three
153 Display item doesn’t have which property Ans) navigational
154 When Timer Expired is correspondent to which object Ans) timer
155 When we compare with radio button & push button, push button will not have
Ans ) Button value property
156 Which function is used to create runtime record group Ans) create_group
157 which trigger and code is used to run application without connecting to database
ans ) on_logon,NULL
158 Form 6i is a ans ) 2 and 3 Tier architecture
159 When we select value from LOV, by default where the value is going to store
Ans ) return item
160 LOV always return ans ) boolean value
161 Alert always return ans) number value
162 Which procedure is used to change alert button label
Ans ) set_alert_button_properly
163 When we create a LOV automatically created ans ) record group
164 which function is used to create dynamic query record group
ans ) create_group_from_query
165 How many types of canvas are there ans ) five
166 Which variable cannot assign a value ans) system
167 When you have cascade delete behavior which trigger gets created
Ans ) PRE-DELETE
168 Which Trigger fires when an operator double clicks on a image item
Ans ) WHEN_IMAGE_ACTIVATED
169 The data type of an image item is ans ) BLOB
170 The default window name is ans ) window1
171 What needed to create for making a master/detail report Ans) Data link
172 A group of radio buttons are placed under the item ans ) Radio Group
173 An operator cannot navigate to a ans) Display item
174 The Default text label of alert button1 is ans) OK
175 The LOV can be attached to a text Item at runtime by the built in
Ans) SET_LOV_PROPERTY
176 The on_clear_detail and On_ populate_deatails triggers are used with
Ans ) ISOLATED operations
177 Which Symbol is used for lexical parameter Ans) &
178 What are called trigger time ans) before and after
179 In oracle any object name it should start with and it should not exceed characters
Ans ) character and 30
180 Which one of the following is applicable on INSTEAD OF Triggers
Ans ) complex views
181 Restriction on triggers can be achieved using ans ) where clause
182 Within a FOR LOOP, which variable cannot be changed by the user
Ans ) Index Variable
183 Which Cursor is created by any SQL statement ans ) implicit
184 Which clause within Cursor exclusively locks the rows returned by the query
Ans ) For update
185 Which Exceptions are internally defined by runtime system ans ) Pre defined
186 Which option is used with the create command to modify an existing named pl/sql block ans ) replace
187 Minimum privilege required to connect to oracle database is ans ) connect
188 How to call a procedure from a PL/SQL block ans ) Using procedure name
189 Which attribute provides a record type that represents a row in a table
Ans )% row
190 For iteration to proceed downwards from upper bound to lower bound we can use the optional keyword in a for loop ans ) reverse
191 if PL/SQL runs out of memory, then, what predefined exception is raised
Ans ) Storage_error
192 which exception is used when all exceptions are to be trapped
ans ) when others
193 The conditions can be given at which clause for grouped result
Ans ) Group by
194 Joining a table to itself is called ans ) self join
195 If inner query in sub query returns more than one row then which operator is used
Ans ) IN
196 The dual table contains column and row. Ans) single & single
197 Which option is used to grant privileges to all users Ans) Public
198 Which pseudo column used to refer to a sequence
Ans ) Curval & Nextval
199 Which level locks are not released by rolling back to save point ans) rowlevel
200 which function is used for opening a file ans) fopen
201 The procedure reads a line from the file ans) get_line
202 The colon symbol is used for parameter ans) bind
203 In the radio button property which value is must ans) initial
204 Which function is used to find out whether the runtime query record group is return rows or not
Ans ) populate_group_with_query and populate_group
205 :system.currentitem is going return ans ) Name
206 The default item type is ans) text
207 The display status of a check box is ans) checked, Unchecked
208 Which trigger performs an action whenever a record is cleared or deleted
Ans ) when_remove_record
209 Which trigger is associated with a button item ans) when_button_pressed
210 setting which property to Yes, a LOV is displayed as soon as the user enters an item ans ) auto refresh
211 The block coordination has deferred and ans) Auto query
212 The value of what parameter being passed to a called product is always the name of a record group defined in the current form ans) Data_parameter
213 Which built-in will call another form within a form ans) call_form
214 At least one must be assigned to each window ans) canvas
215 built-in is used to display a window at runtime ans) show_window
216 An executable menu module file has the extension ans ) mmx
217 type of menu items are not require PL/SQL code ans ) Magic & Separator
218 built-in is used to call a report from a form ans ) SRW.Run_product
219 what is the default value of date ans ) $$date$$
220 which system variable we can assign value ans ) system.date_thershold
221 what parameter is required to create group function ans ) Record groupname
222 which style has to be selected for making master/detail report ans ) group above
223 built-in is used to call report within a report ans ) srw.run_report
224 In show_editor procedure which are optional parameters ans) X and Y
225 Which trigger is associated with a list item ans) when_list_changed
226 which procedure is used to changed the size of image ans ) Image_zoom
227 which function is used to create dynamic query record group
ans ) populate_group_with_query
228 Maximum number of buttons that can be defined in an alert is ans ) 3
229 How many types of menu items are there in menu module ans) 5
230 How many arguments are required in add_group_column ans) 4
231 How many arguments are required in set_alert_button_property ans) 4
Posted by Sunil Dutt at 1:20 AM 0 comments Links to this post
Monday, September 8, 2008
SQL*Loader Examples
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:
sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data: load data
infile 'c:\data\mydata.csv'
into table emp ( empno, empname, sal, deptno )
fields terminated by "," optionally enclosed by '"'
The mydata.csv file may look like this:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20) )
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 ',' col2 ',' col3
from tab1
where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.
Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1, data2 )
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state )
Can one load data into multiple tables at once?
Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL )
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat'
BADFILE 'mydata.bad'
DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
( region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR )
Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE
INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3 )
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
1. The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
TRUNCATE - cleans all data
RENAME- renames a table name
2. Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern
3. SELECT statements:
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.
4. The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value
5. The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
6. The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
7. The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name
8. Sort the Rows:
SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC
SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC
9. The IN operator may be used if you know the exact value you want to return for at least one of the columns.
SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)
10. BETWEEN ... AND
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.
11. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
12. Why does the following command give a compilation error?
DROP TABLE &TABLE NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
13. Which system tables contain information on privileges granted and privileges obtained? USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
14. Which system table contains information on constraints on all the tables created?obtained? USER_CONSTRAINTS.
15. State true or false. !=, <>, ^= all denote the same operation?
True.
16. State true or false. EXISTS, SOME, ANY are operators in SQL?
True.
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?
18. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;?
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
19. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
20. Which command executes the contents of a specified file?
START or @.
21. What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
sal = 11000, comm = 1000.
22. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN.
23. What command is used to get back the privileges offered by the GRANT command?
REVOKE.
24. What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );? NO.
Explanation : The query checks whether a given string is a numerical digit.
26. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN.
27. What operator performs pattern matching?
LIKE operator.
28. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
29. What operator tests column for the absence of data?
IS NULL operator.
30. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all.
31. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR.
32. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL).
33. What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
34. What command is used to create a table by copying the structure of another table?
CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
35. TRUNCATE TABLE EMP;DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP..
36. What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.
37. What are the wildcards used for pattern matching.?
_ for single character substitution and % for multi-character substitution.
38. What is the parameter substitution symbol used with INSERT INTO command?
&
39. What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.
40. What is difference between TRUNCATE & DELETE
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
41. What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
42. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.
43. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.
44. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
45. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
46. Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.
47. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
48. What is the fastest way of accessing a row in a table?
Using ROWID.
CONSTRAINTS
49. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.
50. What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
51. What is the usage of SAVEPOINTS?
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.
52. What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
53. What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
54. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.
55. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
56. 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 the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.
57. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.
58. How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
59. If 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.
60. What is a database link?
Database link is a named path through which a remote database can be accessed.
61. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.
62.What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
63. What are the advantages of VIEW?
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.
64. Can a view be updated/inserted/deleted? If Yes - under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
65. If a view on a single base table is manipulated will the changes be reflected on the base table?
If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.
66. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.
67. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
66. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.
67. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
68. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
69. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
70. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
71. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values
72. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
73. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)
74. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
77. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger
78. Read the following code:
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;
RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
79. When invoking this procedure, you encounter the error:
ORA-000:Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated
with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error
code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
80. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END; You are about to add an argument to CALCULATE_BUDGET.
What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.
81. Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
82. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
83. Examine this database trigger
52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt
84. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN RETURN TRUEl; ELSE RETURN FALSE; END IF; COMMIT; END; Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
85. Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
86. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
87. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
88. What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section,the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
89. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END; For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
90. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
91 How to implement ISNUMERIC function in SQL *Plus ? Method
1: Select length (translate(trim (column_name),'+-.0123456789',''))from dual; Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters) Method 2: select instr(translate('wwww','abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXX'),'X') FROM dual; It returns 0 if it is a number, 1 if it is not.
92 How to Select last N records from a Table? select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm) where a > ( select (max(rownum)-10) from clm) Here N = 10
The following query has a Problem of performance in the execution of the following
query where the table ter.ter_master have 22231 records. So the results are obtained
after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution
path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that
your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;
93. What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it
only checks for the existence (and status) of another foreign key Pointing to the
table. If one exists and is enabled, then you will get The following error. This
is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing
the TRUNCATE command, then re-enable them afterwards.
PL-SQL Interview Questions
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?
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
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 ?
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?
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?
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?
Expected answer: Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL?
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?
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.
SQL / SQLPlus Interview Questions
1. How can variables be passed to a SQL routine?
Expected answer: By use of the & symbol. For passing in variables the numbers
1-8 can be used (&1, &2,...,&8) to pass the values after the command into the
SQLPLUS session. To be prompted for a specific variable, place the ampersanded
variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double
ampersands tells SQLPLUS to resubstitute the value for each subsequent
use of the variable, a single ampersand will cause a reprompt for the
value unless an ACCEPT statement is used to get the value from the user.
2. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
3. How can you call a PL/SQL procedure from SQL?
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an example
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?username? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?? the values selected from the database.
6. What SQLPlus command is used to format output from a select?
Expected answer: This is best done with the COLUMN command.
7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
Level: High Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product?
Expected answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
Level: High Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Expected answer: Ascending
13. What is tkprof and how is it used?
Level: Intermediate to high Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
14. What is explain plan and how is it used?
Level: Intermediate to high Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width?
Level: Low Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen?
Level: Low
Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns
off screen output. This option can be shortened to TERM.
17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
Level: Low Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.
18. How do you generate file output from SQL?
Answer: By use of the SPOOL command
Posted by Sunil Dutt at 1:29 AM 0 comments Links to this post
Thursday, September 11, 2008
Customer Interface Diagram
Customer Interface Diagram
Posted by Sunil Dutt at 3:24 AM 1 comments Links to this post
Master Detail Forms
1) Create Two Tables (With Parent / Child relationships)
a) Create table orders
(order_no number(10),
custoner varchar2(20),
order_date date)
/
b) Create table lines
( line_no number(10),
order_no number(10),
item varchar2(20),
qty number(10),
price number(10),
amount number(10))
/
2) Alter the table structures and primary and foreign keys
Alter table orders add constraint orders_pk primary key (order_no);
Alter table lines add constraint lines_fk foreign key (order_no) references orders (order_no);
3) Create public synonyms for the tables.
4) Register two tables with constraints information.
5) Open form builder software.
6) Open template.fmb file.
7) Save as รจ TETMADET.fmb
8) Change form module name as form name.
9) Delete default blocks , canvasses and windows.
10) Create two windows , two canvasses
Window1 : Orders
Window2 : Lines
Canvas1 : Orders
Canvas2 : Lines
11) Assign Canvasses to Windows.
12) Assign Windows to Canvasses.
13) Assign property classes to windows and canvasses.
14) Create Two Blocks
Block1 : Orders Type : Form
Block2 : Lines Type : Tabular No of Records : 5
15) Define master detail block relationship between two blocks .
Relationship Name : Orders_Lines
16) Create a Control Block
17) Create a Check box on detail window/ detail canvas
Block => Control
Name => Orders_lines (Same as your master detail relationship name)
Checked value => Immediate
Unchecked Value => Deferred
Default => Immediate
18) Create a Push button in Master Window / Master Canvas
Name : LINES
Label : Lines ..
Block => Control
19) Assign Text Item property class to all text items on Orders and Lines block.
20) Set Module / Form Level properties
First navigation data block => Orders
Console Window => Orders
21) Modify App_custom Package
Close window procedure
If ( wnd = ‘Orders’) then
App_window.close_first_window;
Elsif (wnd = ‘Lines’) then
App_window.set_coordination(‘WHEN-WINDOW-CLOSED’, :CONTROL.ORDER_LINES (CHECKBOX) , ‘ORDERS_LINES’ (RELATIONSHIP NAME) );
End if;
Open Window Procedure
If (wnd = ‘Orders’) then
Go_block (‘ Orders’) ;
Return;
If (wnd = ‘Lines’) then
App_window.set_coordination (‘ OPEN-WINDOW’, :CONTROL,ORDERS_LINES , ‘ORDERS_LINES’);
Go_block (‘ Lines’);
End if;
22) Create a program unit
Name : Control
Type : Package Spec
Procedure Lines (Event In Varchar2) ;
Procedure Orders_Lines ( Event In Varchar2);
23) Create a program unit
Name : Control
Type : Package Body
Procedure Lines (Event In Varchar2) is
Begin
If (event = ‘ WHEN – BUTTON – PRESSED’ ) then
App_custom.open_window (‘Lines’);
Return;
End if;
End Lines;
Procedure Orders_Lines ( Event In Varchar2) then
Begin
If (Event = ‘ WHEN – CHECKBOX – CHANGED’) then
End if ;
End order_lines;
24) In control block write a WBP trigger on Lines Button.
Control . Lines ( ‘WHEN – BUTTON – PRESSED’);
25) In control block write a WCC trigger on Orders_Lines checkbox.
Control.orders_lines(‘WHEN- CHECKBOX – CHANGED’);
26) Modify PRE – FORM trigger
APP_WINDOW.SET_WINDOW_POSITION(‘ORDERS’,NULL,’ORDERS);
27) Save and Compile .
28) Register the form.
Posted by Sunil Dutt at 3:13 AM 0 comments Links to this post
CUSTOM FORM DEVELOPMENT
On the CLIENT machine create a FOLDER as say: c:\custom_sunil
In custom_sunil folder creates 2 folders forms, resource
Copy TEMPLATE.fmb, APPSTAND.fmb, .pll files to CLIENT
• Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to c:\custom_sunil\ forms directory copy all .pll files from /Applvis/visappl/au/11.5.0/resource to c:\custom_sunil\resource using ftp
• On windows go to command prompt
• Cd c:\custom_sunil\forms
• ftp cloneserver
• username: applmgr password: applmgr
• now you are at ftp prompt
• bin
• prompt
• cd visappl/au/11.5.0/forms/US ( here apparently cd $AU_TOP does not work)
• get TEMPLATE.fmb
• (file copied)
• get APPSTAND.fmb
• (file copied)
• lcd ./resource (check this. Basically you need to be in c:\custom_sunil\resource. You can go to that directory and then run ftp)
• cd visappl/au/11.5.0/resource
• mget *.pll
• ( now all .pll files are copied to c:custom_sunil/resource)
SET env variable FORMS60_PATH through regedit
• Regedit/HKEY_LOCAL_MACHINE/software/oracle
• Double click FORMS60_PATH
• At the end of existing value data add: ;c:\custom_sunil\froms;c:\custom_sunil\resource
Now open TEMPLATE.fmb and make the following changes
• DELETE BLOCKNAME &DETAIL BLOCK FROM DATABLOCK,DELETE BLOCKNAME FROM CANVAS AND WINDOW
• Create a window NEW_WIN, canvas NEW_CAN
• Create a new block based on the table you created in your custom schema
• In pre-form trigger: app_windows.set_window_position(‘NEW_WIN’)…….
• In program units open custom_package.AP_cutom_pacakge body
• Change : if (wind=’NEW_WIN’);
• Template name = SUNIL_FORM
• Save as SUNIL_FROM to c:\custom_sunil\forms
• (????????SET THE WINDOW NAME AS U HAVE CREATED NEW WINDOW IN PRE-FORM TRIGGER BY BLOCKNAME?????)
DEPLOY the FORM (upload it to AU_TOP/forms/US)
• Go to command prompt (on client)
• cd c:\cutom_sunil\forms
• ftp cloneserver
• cd visappl/au/11.5.0/froms/US
• bin
• prompt
• put SUNIL_FORM.fmb
Changing ORACLE_HOME (/Visdb/visdb/9.2.0 to /Applvis/visora/8.0.6)
• thru putty login as applmgr
• pwd: /Applvis
• echo $ORACLE_HOME: shows /Visdb/visdb/9.2.0
• cd visora
• cd 8.0.6
• . VIS_cloneserver.env (this changes ORACLE_HOME apparently based on pwd?)
• echo $ORACLE_HOME: shows /Applvis/visora/8.0.6
• now ORACLE_HOME is 8.0.6 (forms/reports home)
• pwd : gives /Applvis/visora/8.0.6
COMPILE and generate FMX
• (now you are in /Applvis/visora/8.0.6 directory and ORACLE_HOME is set to /Applvis/visora/8.0.6)
• pwd: gives /Applvis/visora/8.0.6
• f60gen module=$AU_TOP/forms/US/SUNIL_FORMS.fmb module_type=form user=apps output_file=$SUNIL_TOP/forms/US/SRIN_FORM.fmx compile_all=special batch=no
• this generates SUNIL_FORM.fmx and puts in SUNIL_TOP/forms/US
FORM REGISTRATION
• Login to applications with application developer responsibility
• Application/form
• Enter the following details
o Form: the fmx name (SUNIL_FORM)
o Application: Oracle Receivables (as per Amer) –give appropriate name based the intended use of this form
o user form name: SUNIL_FORM_U (this will appear in LOV)
o SAVE
Attach the FORM to FUNCTION
(Create a new function)
Application/function
Enter the following details
o Function: SUNIL_FUNCT
o User function name: SUNIL FUNCTION
o Form: SUNIL_FORM (previously registered)
o SAVE
Attach FUNCTION to MENU
Application/menu
• Enter the following details
o Menu: sunil_menu
o User menu name: sunil menu
o Seq: 1
o Prompt: sunil form
o Function: SUNIL_FUNCT( previously deined)
o SAVE
Attach MENU to RESPONSIBILITY
Attach RESPONSIBILITY to USER
Login as the new USER
• See the form
Posted by Sunil Dutt at 3:03 AM 3 comments Links to this post
Wednesday, September 10, 2008
TRIGGERS
TRIGGERS
IT IS A PL/SQL BLOCK OR PL/SQL PROCEDURE ASOCITED WITH A TABLE,VIEW,SCHEMA OR DATABASE EXECUTED AUTOMATICALLY RATHER IN TECHNICAL TERMS IMPLICITLY FIRED AUTOMATICALLY WHENEVER A SPECIFIC EVENT OCCURS UPON THE OBJECT ASSOCIAYED WITH.
TYPES OF TRIGGERS
THERE ARE TWO TYPES OF TRIGGERS.
APPLICATION TRIGGER:-FIRED WHENEVER AN EVENT //CAN BE CONSIDERED AS A DML OPERATION// OCCURS UPON A PARTICULAR APPLICATION.
DATABASE TRIGGER:-FIRED WHENVER AN EVENT (IN THE SENSE ANY DML OPERATION OR ANY SYSTEM EVENT I.E LOGON OR SHUTDOWN) OCCURS ON A SCHEMA OR DATABASE.THESE ARE FIRED IMPLICITLY
INSTEAD OF TRIGGERS
THESE ARE ONLY LIMITED TO THE VIEWS .THE NAME SIGNIFIES THE OPERATION OF THE TRIGGER.TO BE MORE ELABORATIVE WHENEVER A DML IS OPERATED UPON A VIEW THE ACTION IS TAKEN CARE OF BY THE INSTEAD OF TRIGGER.RATHER IF ANY OTHER TRIGGERS ARE ASSOCIATED WITH THAT TABLE THEN THOSE TRIGGER WILL BE FIRED.
WHY THE TRIGGERS ARE DESIGNED?
FOR RELATED TASKS TO BE PERFORMED AND TO CENTRALIZE THE GLOBAL OPERATIONS WHICH TAKES CARE OF THE ACTIONS RATHER CONSIDERING WHATEVER APPLIACTION OR WHOEVER THE USER MAY BE.
CREATING DML TRIGGERS
A TRIGGER CONTAINS
A) TRIGGER TIMING DESCRIBES ABOUT THE FIRING OF THE TRIGGER WRT TRIGGERING EVENT.
B) TRIGGERING EVENT DESCRIBES ABOUT THE DML WHICH IS TO BE TAKEN CARE FOR THE BY ACTIONS TO BE RAISED.
C) TRIGGER TYPE DESCRIBES ABOUT THE NUMBER OF TIMES OF EXECUTION OF TRIGGER.
D) TRIGGER BODY DESCRIBES ABOUT THE ACTIONS TAKEN CARE OF BY THE TRIBGGER.
DML TRGGER COMPONENTS
TRIGGER TIMING
A) BEFORE TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS BEFORE THE DML EVENT IS OPERATED UPON AN OBJECT.
B) AFTER TRIGGERS EXECUTION OF TRIGGER BOBY OCCURS AFTER THE DML EVENT IS OPERATED UPON AN OBJECT.
C) INSTEAD OF SPECIFIES A SEPARATE EXECUTION PROCESS APART FROM THE TRIGGERING STATEMENT.THESE ACT UPON THE VIEWS AND ARE NOT MODIFIABLE.
DESCRIPTIONS
BEFORE TRIGGERS ARE USED TO DETERMINE THE STATUS OF TRIGGER STATEMENT WHETHER TO BE COMPLETED.ALSO BETTER EXPLANATION CAN BE ROLLBACK.ALSO TO FETCH THE COLUMN VALUES PRIOR TO EXECUTION AND TO VALIDATE RULES OF BUSINESS.
AFTER TRIGGERS ARE USED TO COMPLETE THE ACTON BEFORE TRIGGERING ACTION.IF THERE IS A PRESENCE OF A BEFORE TRIGGER THEN TO INITIATE A DIFFERENT ACTION.
INSTEAD OF TRIGGERS ARE USED TO MODIFY THE VIEWS WHICH CANNOT BE MODIFIED BY A SQL DML STATEMENT DUE TO LACK OF MODIFICATION INHERITANCE.THESE WORK IN THE BACKGROUND ACCORDING TO THE DML ACTIONS SPECIFIED IN THE TRIGGERING BODY.
TRIGGERING EVENTS CAN BE SPECIFICALLY LINKED WITH THE DMLS SUCH AS INSERT, UPDATE OR A DELETE.IN CASE OF UPDATE DML THE COLUMN LIST ARE TO SPECIFIED FOR WHICH THE TRIGGERING ACTIONS ARE TO BE TAKEN CARE OF.
TRIGGER TYPE SPECIFIES WHETHER THE TRIGGER IS TO BE FIRED FOR EACH ROW OR FOR MULTIPLE ROWS (STATEMENT TRIGGERS).THERE ARE TWO TYPES OF TRIGGERS.
1) ROW TRIGGERS ARE EXECUTED ONCE FOR EACH ROW RETRIEVED BY THE DML SPECIFIED IN THE STATEMENT.IT IS NOT EXECUTED IF THE STATEMENT DOES NOT RETURN ANY VALUE.IT IS NOT EXECUTED WHEN NO ROWS ARE SELECTED.
2) STATEMENT TRIGGER IS FIRED ONCE ON BEHALF OF THE TRIGGERING EVENT EVEN IF NO ROWS ARE AFFECTED AT ALL.
TRIGGER BODY EXPLAINS ABOUT THE ACTIONS TO BE TAKEN CARE OF BY THE TRIGGER.IT CAN BE PL/SQL BLOCK OR A CALL PROCEDURE.
NOTE
1) WHEN THE TRIGGERING DATA MANIPULATION STATEMENT EFFECTS THE A SINGLE ROW, BOTH THE ROW TRIGGER AND STATEMENT TRIGGER FIRE EXACTLY ONCE PROVIDED THE TYPE OF TRIGGER THAT HAS BEEN MENTIONED IN THE TRIGGER BODY.
2) WHEN A TRIGGERING DATA MANIPULATION STATEMENT AFFECTS MULTIPLE ROWS THEN THE STATEMENT TRIGGER FIRES EXACTLY ONCE AND THE ROW TRIGGER FIRES ONCE FOR EVERY ROWEFFECTED BY THE STATEMENT.
SYNTAX
CREATE TRIGGER
TIMING
EVENT1 OR OR
ON
TRIGGER NAME SHOULD BE UNIQUE COMPARED TO OTHER TRIGGERS.
SPECIFIES THE TIME WHEN THE TRIGGER WILL FIRE
EITHER OR
IDENTIFIES THE DML THAT CAUSES THE TRIGGER TO FIRE.
EITHER ,, OR ALL OF THE THREE.
NAME OF THE TABLE ASSOCIATED WITH THE TRIGGER.
EXPLAINS ABOUT THE ACTIONS PERFORMED.
IT BEGINS WITH A DECALERE AND END OR A CALL OF PROCEDURE.
USING COLUMNS NAMES WITH UPDATE TRIGGERS INCREASE THE PERFORMANCE BECAUSE THE TRIGGER IS FIRED ONLY WHEN THE UPDATION OF CONCERNED COLUMN OCCURS.IT IS NO WHERE CONCERNNED WITH THE UPDATION OF ANY OTHER COLUMNS OF THE DESCRIBED TABLE IN THE TRIGGER.
EXAMPLE:
IN THE DESCRIBED TRIGGER WHICH IMPLEMENTS THE BUSSINESS RULES THAT RESTRICTS THE ACCESS OF DATABASE TABLE AFTER THE OFFICE HOURS AND HOLIDAYS PROVIDED THE WORK DAY CALENDER IS 5 DAYS A WEEK.
CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES
BEFORE INSERT OR DELETE OR UPDATE ON EMPLOYEES
BEGIN
IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’,’SUN’)) OR
(TO_CHAR (SYSDATE,’HH24’) NOT BETWEEN ‘08’ AND ‘18’)
THEN
IF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSIF DELETING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR (-20500,’UR STATEMENT’);
ELSE
RAISE_APPLICATION_ERROR (-20500,’UR SATAMENT2’);
END IF;
END IF;
END;
DML ROW TRIGGERS
WE HAVE TO SPECIFY A SPECIAL PHRASE FOR INITIATING A ROW TRIGGER.REFERENING TO THE ABOVE MENTIONED SYNTAX OF TRIGGER AFTER THE TABLE NAME ‘FOR EACH ROW’ PHRASE IF SPECIFIED INDICATES THE TRIGGER TO BE A ROW TRIGGER.HERE THE NEW VALUES AND THE OLD VALUES ARE ALSO REFERED FOR CORELATION BETWEEN THE OLD VALUES AND NEW VALUES.
WE CAN ALSO RESTRICT THE FIRING OF A ROW TRIGGER BY SPECIFING A WHEN CLAUSE AFTER THE PHRASE MENTIONED ABOVE.
EXAMPLE
CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
IF NOT (:NEW.JOB_ID IN (‘AD_PRES’,’AD_VP’))
AND (:NEW.SALARY) > 15000
THEN
RAISE_APPLICATION_ERROR (-20500, )
END IF;
END;
EXPALAINATION
FOR AN UPDATE OR INSERT UPON EMPLOYEES TABLE IF THE JOB_ID SPECIED IS OTHER THAN AD_PRES AND AD_VP AND SALARY SPECIFIED IS GREATER THAN 15000 IN THE CASE TRIGGER IS FIRED.IN STRAIGHT EAPLAINTION THE EMPLOYEES WHICH HAVE A JOB_ID OF AD_PRES AND AD_VP CAN ONLY EARN A SALARY GREATER THAN 15000.
RESTRICTING A ROW TRIGGER
CREATE OR REPLACE TRIGGER RESTRICT_SALARY
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.JOB_ID = ‘SA_REP’)
BEGIN
IF INSERTING THEN
: NEW .COMMISSION_PCT:= 0;
ELSIF (:OLD.COMMISSION_PCT) IS NULL THEN
: NEW.COMMISSION_PCT:= 0;
ELSE
: NEW.COMMISSION_PCT:=:OLD.COMMISSION_PCT + 0.05;
END IF;
END;
EXPLAINATION
IF AN INSERT OPERATION IS OPERATED UPON THE EMPLOYEES TABLE WITH A JOB_ID SPECIFIED AS SA_REP AND COMMISSION_PCT WITH SOME VALUE THEN THE TRIGGER RESTRICT_SALARY WILL FIRE CAUSING A INSERTION OF ZERO IN COMMISION_PCT IN THE TABLE. (PLEASE NOTE LINE NO 7).OTHER WISE IF THE JOB IS OTHER THAN SA_REP THEN ROW IS FULLY INSERTED AS DESIRED.
IF AN UPDATE OPERATION IS OPERATED UPON THE TABLE THERE ARE TWO CASES TO BE NOTICED.
1) IF THE OLD VALUE OF COMMISSION_PCT IS A NULL THEN WHILE UPDATION OF SALARY RESTRICT_SALARY WILL FIRE AND THE COMMISSION_PCT WILL BE ASSIGNED A VALUE OF ZERO.(PLEASE SEE LINE NO 9)
2) IF THE OLD VALUE OF COMMISSION_PCT IS NOT NULL THEN RAISE_SALARY WILL FIRE AND NEW VALUE WILL BE WHAT EVER VALUE THAT HAS BEEN SPECIFIED IN THE ACTION.(PLEASE SEE LINE NO 11)
INSTEAD OF TRIGGERS
IF THE MODIFICATION IS REQUIERD TO BE DONE ON THE DATA OWNED BY AN UNUPDATEABLE VIEW I.E (A VIEW CONTAINING THE SET OPERATORS, DISTINCT CLAUSE, GROUP FUNCTIONS, OR JOINS BECOMES AN UNUPDATEABLE VIEW) THEN THE INSTEAD OF TRIGGERS ARE USED WHICH ARE FIRED BY THE ORACLE SERVER WITHOUT EXECUTING THE TRIGGERING STATEMENT OPERATING THE DML UPON THE UNDERLYING TABLES SPECIFIED.
CREATE OR REPLACE TRIGGER
INSTEAD OF
OR OR
ON VIEW_NAME
FOR EACH ROW
IF THE VIEW IS UPDATEABLE AND CONTAINS THE INSTEAD OF TRIGGERS THEN THESE TRIGGERS TAKE PRECEDENCE.
ALSO THE CHECK OPTIONS ARE NOT TAKEN CARE OF AT THE TIME OF FIRING OF INSTEAD OF TRIGGERS.RATHER IF WANTED THEY MUST BE SPECIFIED IN THE BODY OF INSTEAD OF TRIGGER.
SEE FOR MORE DETAILS ORACLE HAND BOOK
MANAGING TRIGGERS
WE CAN ALTER THE STATUS OF A TRIGGER BY DISABLING OR ENABLING IT.
ALTER TRIGGER DISABLE/ENABLE
FOR IN CASE OF TABLE
ALTER TABLE DISABLE/ENABLE ALL TRIGGERS
FOR RECOMPILING THE TRIGGER BODY
ALTER TRIGGER COMPILE
DROPPING A TRIGGER
DROP TRIGGER TRIGGER_NAME
CREATING DATABASE TRIGGERS
FOR CREATING A DATABASE TRIGGER FIRST THE TRIGGER CMPONENTS ARE TO BE DECIDED.
A TRIGGER DEFINED FOR A SYSTEM EVENT CAN BE AT A LEVEL OF DATABASE OR SCHEMA.FOR EXAMPLE THE LOG OFF TRIGGERS OR TRIGGERS INVOLVING DDL STATEMENTS ARE AT A LEVEL OF EITHER SCHEMA OR DATABASE. THE DATABASE SHUTDOWN TRIGGERS ARE AT A LEVEL OF SCHEMA.
TRIGGERS DEFINED AT SCHEMA LEVEL FIRES WHENEVER THE TRIGGERING EVENT INVOLVES THE SCHEMA OR TABLE.WHEREAS THE DATABASE LEVEL TRIGGERS FIRE FOR ALL USERS.
FOR DLL TRIGGERS THE POSSIBLE EVENTS MAY BE
1) CREATE STATEMENT
2) ALTER STATEMENT]
3) OR A DROP STATEMENT
ANY WAY THE CREATE TRIGGER SYNTAX WILL BE REMAINING THE SAME ALL THE TIME.
FOR A TRIGGER INVOLVED IN THE SYSYTEM EVENTS LIKE
1) AFTER SERVERERROR
2) AFTER LOGON
3) BEFORE LOGOFF
4) AFTER STARTUP
5) BEFORE SHUTDOWN
MUTATING TABLE
IT CAN BE DEFINED AS A TABLE BEING MODIFIED BY AN UPDATE, DELETE INSERT STATEMENT OR THE TABLE IS REQUIRED TO BE UPDATE BY THE EFFECTS OF ON DELETE CASCADE REFERENTIAL INTEGRITY ACTION.
A TRIGGERED TABLE IS ALSO A MUTATING ONE AS WELL AS ANY TABLE REFERENCING IT BY FOREIGN KEY CONSTRAINT.
PROTECTING DATA INTEGRITY WITH TRIGGERS
CREATE OR REPLACE CHECK_SALARY
BEFORE UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.SALARY)<(OLD.SALARY)
BEGIN
RAISE_APPLICATION_ERROR (-20508,’DECREASE SALARY NOT ALLOWED’)
END;
SNAPSHOT
IT IS A LOCAL COPY OF A TABLE DATATHAT ORIGINATES FROM ONE OR MORE REMOTE MASTER TABLES.THE DATA OF THE SANAPSHOT CAN BE QUERIED BUT NO DML OPERATIONS CAN BE OPERATED UPON A SNAPSHOT.TO KEEP IN PARALLEL WITH THE BASE TABLES THE SNAPSHOT SHOULD BE REFRESHED REGULARLY.
BENEFITS OF DATABASE TRIGGERS
IT PROVIDES US THE IMPROVED DATA SECURITY
IT ALSO FACILIATES IMPROVED DATA INTEGRITY.
THANK YOU
THIS DATA DOES NOT MAKE YOU MASTER IN TRIGGERS.THERE ARE SO MANY CONCEPTS I JUST TRIED MY LEVEL BEST TO CONCENTRATE ON BASICS
Posted by Sunil Dutt at 9:35 AM 0 comments Links to this post
All modules Interface Tables
GL INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE
AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE
AR INTERFACE TABLES
TNAME TABTYPE
------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE
RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE
FA INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------
FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE
INVENTORY INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE
TNAME TABTYPE
------------------------------ ------- --------------------------------------
MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE
TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE
PO INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- -------------------------
PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE
BOM INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ----------------------------------
BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE
WIP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- --------------------------
WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE
ORDER MANAGEMENT INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- -----------------------------------
SO_CONFIGURATIONS_INTERFACE TABLE
SO_HEADERS_INTERFACE_ALL TABLE
SO_HEADER_ATTRIBUTES_INTERFACE TABLE
SO_LINES_INTERFACE_ALL TABLE
SO_LINE_ATTRIBUTES_INTERFACE TABLE
SO_LINE_DETAILS_INTERFACE TABLE
SO_PRICE_ADJUSTMENTS_INTERFACE TABLE
SO_SALES_CREDITS_INTERFACE TABLE
SO_SERVICE_DETAILS_INTERFACE TABLE
WSH_DELIVERIES_INTERFACE TABLE
WSH_FREIGHT_CHARGES_INTERFACE TABLE
WSH_PACKED_CONTAINER_INTERFACE TABLE
Posted by Sunil Dutt at 8:55 AM 1 comments Links to this post
Tuesday, September 9, 2008
GL interface Diagram
Posted by Sunil Dutt at 5:59 AM 0 comments Links to this post
Trigger Solutions
Problems (For DML Triggers):
Q.1 Keep the backup of department data for DELETE and UPDATE.
Ans: CREATE OR REPLACE TRIGGER dept_backup_trg
AFTER DELETE OR UPDATE ON dept
FOR EACH ROW
BEGIN
INSERT INTO dept_backup (deptno, dname, loc)
VALUES (:OLD.deptno, :OLD.dname, :OLD.loc);
END;
Q.2 Secure emp table from SCOTT user for DELETE or UPDATE of manager and salesman records.
Ans: CREATE OR REPLACE TRIGGER emp_dml_check
BEFORE DELETE OR UPDATE ON emp
FOR EACH ROW
WHEN (USER = 'SCOTT')
BEGIN
IF UPPER(RTRIM(LTRIM(:OLD.JOB))) IN ('MANAGER', 'SALESMAN')
THEN
RAISE_APPLICATION_ERROR
(-20001, 'You can not update or delete MANAGER or SALESMAN records');
END IF;
END;
Q.3 Delete all related employees as soon as dept is deleted from dept table.
Ans: CREATE OR REPLACE TRIGGER Delete_Emp_Trg
AFTER DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno = :OLD.deptno;
END;
Problems (For DDL Triggers):
Q.1 Create a DDL trigger to prevent removal of any table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott
BEFORE DROP ON scott.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20001, 'No table can not be droped from SCOTT schema');
END;
2. Create a DDL trigger to prevent removal of emp table under scott schema.
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Scott_on_Emp
BEFORE DROP ON scott.SCHEMA
BEGIN
IF UPPER(RTRIM(LTRIM(sys.dictionary_obj_name))) = 'EMP'
THEN
RAISE_APPLICATION_ERROR (-20001, 'EMP table can not be droped from SCOTT schema'); END IF;
END;
Q.3 create a DDL trigger to prevent removal of any table under any schema. (User must have ADMINISTER DATABASE TRIGGER privilege).
ANS. CREATE OR REPLACE TRIGGER Drop_Check_For_Any_Table
BEFORE DROP ON DATABASE
BEGIN
IF UPPER(RTRIM(LTRIM(sys.sysevent))) = 'DROP' AND UPPER(RTRIM(LTRIM(sys.dictionary_obj_Type))) = 'TABLE
THEN
RAISE_APPLICATION_ERROR (-20001, 'Drop Table is not allowed under this Database');
END IF;
END;
Problems (For Instead Of Triggers)
1. create a trigger to allow Data Manipulation on EMP and DEPT tables via the View.
1.1 Create a view on emp and dept tables combination.
1.2 Create Instead Of Trigger on the View.
Ans: CREATE OR REPLACE TRIGGER emp_dept_vw_trg
INSTEAD OF INSERT OR DELETE OR UPDATE ON emp_dept_vw
FOR EACH ROW
BEGIN
IF INSERTING = True THEN
INSERT INTO dept (deptno, dname, loc)
VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);
--
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);
--
ELSIF UPDATING = True THEN
UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc WHERE deptno = :NEW.deptno;
--
UPDATE emp SET ename = :NEW.ename, job = :NEW.job, mgr = :NEW.mgr, hiredate = :NEW.hiredate, sal = :NEW.sal, comm = :NEW.comm, deptno = :NEW.deptno WHERE empno = :NEW..empno; ELSE DELETE FROM emp WHERE empno = :OLD.empno; DELETE FROM dept
WHERE deptno = :OLD.deptno;
END IF;
END;
Problems (For Database Events Triggers)
Q.1 Create a trigger that denies login for any user except SYSTEM or INTERNAL users.
Ans. CREATE OR REPLACE TRIGGER check_user_login
AFTER LOGON ON DATABASE
BEGIN ]
IF :sys.login_user NOT IN ('SYS', 'SYSTEM')
THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to Login');
END IF;
END;
Q.2 Create a trigger to load a package into KEEP buffer as soon as Database is started.
Ans. CREATE OR REPLACE TRIGGER pin_package
AFTER STARTUP ON DATABASE
BEGIN
DBMS_SHARED_POOL.KEEP ('SCOTT.EMP_PG', 'P');
END;
Posted by Sunil Dutt at 1:45 AM 0 comments Links to this post
Entire - Oracle
Good zip file of Entire Oracle worth reading this have a look friends
http://www.esnips.com/doc/d327c757-49b3-47f7-bf7d-36d6a854c497/Entire-Oracle
Posted by Sunil Dutt at 1:44 AM 2 comments Links to this post
Oracle Apps Technical Interview
1. Using PL/SQL, which exception is raised when a division by zero is attempted?
Ans ) zero divide error occurs
2. Which constraint type ensures that each row in a table contains an entry that uniquely identifies that row from any other. Ans ) primary key
3. Views can be defined with up to (?) columns. Ans ) 254
4. During database creation, which of the following database users require setting their password? Ans ) sys
5. What is a Cartesian product?
Ans )The product of a select statement which has more than one table, and no where clause
6. A developer calls and reports they are receiving an ORA-00901: invalid CREATE command. What is most likely the problem?
Ans ) The CREATE command was not followed by a valid option
7. The DECODE () function is similar to (?).
Ans ) an if then else construct
8. In which section of a PL/SQL block will a user defined exception be handled?
Ans ) exception block
9. Which of the following are used to determine the order of an ORDER BY clause?
Ans ) ASC
10. Which of the following are NOT number functions?
Ans ) TODATE()
11. What column heading is displayed for the following query? SELECT COUNT(ENAME) Count FROM EMP Ans) COUNT
12. More frequent checkpoints reduce the time needed to (?). Ans ) recover
13. A (?) is an area of memory, given a name, that is used to store a value.
Ans ) variable
14. How is the command SELECT * FROM EMP WHERE EMPNO=&X.45 interpreted if 123 is entered as a value for X?
Ans ) SELECT * FROM EMP WHERE EMPNO=12345;
15. The third value in a ROWID represents the (?) of that row. Ans ) file ID
16. Which of the following are NOT valid comparison operators?
Ans ) == (two equal signs)
17. What structure is used to prevent more than one user from updating the same data in a table at the same time? Ans ) locks
18. The default length for char data type is Ans ) one
19. The default order in which records are retrieved when an order by clause is used is
Ans ) Ascending
20. The operator precedence between AND & OR is Ans) AND over OR
21. The output of ltrim (\"BILL CLINTON\",BALL) is
Ans) ILL CLINTON
22. The output of substr (\"CREATE\",3,3) is Ans) EAT
23. The number of minimum join condition that are required to join n tables is N-1
24. In an outer join (+) sign is placed on the Ans) deficient table side
25. A sub-query can appear within which of the following clauses & operators 1. Select 2. Where 3. Having 4. Set operators Ans ) select , having and set operators
26. If where clause is not specified in an update statement then
Ans ) all rows updated
27. The database object from which multiple users may generate unique integer is
Ans ) sequence
28. Cluster columns must correspond to columns in each of the cluster tables in
Ans ) size and column name
29. The clause in the computer command that helps in computing count of rows is
Ans ) number
30. The elements, which are mandatory for a PL/SQL blocks are
Ans ) begin and end;
31. The concatenation operator in PL/SQL is Ans)
32. Which of the following are available in PL/SQL and not in SQL
Ans ) data access compatibility
33. The assignment operator in PL/SQL is Ans) =
34. The procedure which lets you issue user-defined error messages from a stored sub-program or database trigger is
Ans ) exception_unit and raise_application_error
35. Data can be retrieved into variables using which of the following options
Ans ) select into and fetch
36. Which of the following does a cursor For Loop do 1. Open a cursor 2. Fetch 3. close cursor Ans) ALL
37. Which statement can appear in a trigger body Ans) SQL set
38. To create a database trigger, the user must have the privilege.
Ans ) create trigger privilege
39. Which operator finds common rows from 2 or more different queries?
Ans ) intersect
40. Foreign key constraint can be enforced by using constraint.
Ans ) references
41. The statement fetches rows from a table to a cursor. Ans) select
42. The NVL() function is used to Ans) substitute a new value
43. The maximum size of LOB is Ans) 4gb
44. How many triggers can create per table Ans) 12
45. Maximum how many constraints can enforce on a single column Ans) four
46. PL/SQL stands for Ans ) procedural language of sql
47. which are the character functions will take string parameter and it will return numeric Ans) length and instr
48. Select trunc(199.99) from dual Ans) “199”
49. Which constraint is only a table level Ans) composite primary key
50. Triggers can call in subprograms Ans) yes
51. The NULL columns do not participate in arithmetic computations
52. You cannot create more than one trigger per table.
53. Which Procedure produces output on the screen
Ans ) dbms_output.putline()
54. The cursor is created by any SQL statement. Ans) implicit
55 Which triggers are used for views? Ans) instead of triggers
56 select statement is Ans ) DRL
57 trigger is fired Ans ) implicitly
58 A Cartesian product is Ans ) only available in oracle 8i
59 Select empcode, sum(basic) from empmaster? Can you create a view based on the above select? Ans ) no
60 Which of the following correctly describes how to specify a column alias
Ans ) Place the alias after each column, separated by white space.
61 The command to open a CURSOR FOR loop is
Ans ) No command is required
62 If left out, which would cause an infinite loop Ans) exit
63 After referencing NEXTVAL, the value in CURRVAL of a sequence is
Ans ) incremented by 1
64 What control structure prevents more than one user from updating data in a table?
Ans ) locks
65 Which of the following two constraints automatically create an index?
Ans ) Unique Constraint
66 How many equality conditions are required to join four tables without a Cartesian product? Ans) two
67 How do we join two or more tables? Using =
68 What is the minimum number of shared columns to join two tables in a select statement? Ans ) one
69 Which of the following are NOT valid comparison operators? Ans) >>
70 When entering a SELECT statement from the SQL prompt the command is terminated with which character? Ans ) /
71 What function is similar in function to an IF-THEN-ELSE statement?
Ans ) decode()
72 Which of the following is a character function? Ans) to_number()
73 The binary extension for the menu is Ans) .mmb
74 An ER Diagram which relates 2 entities with dark line joining one entity and with crow foot on the other side is called
Ans) Many to Many
75 Once defined, how long will a variable remain in SQL*Plus?
Ans) Until the session Completes
76 After an Update statement, you write an exception with no_data_found, too_many_rows and when others. If the Update statements where condition fails to update any rows then which exception will be raised?
Ans) no_data_found
77 Not Null is which type of constraint? Ans ) check
78 How do you make sure that Oracle will use an Index when you write a select statement? Ans) by selecting columns which have index
79 Forms allows you to manipulate data only if it is Ans) normal mode
80 The different views of objects in object Navigator
Ans ) Ownership view and Visual View
81 For undoing changes in the form during design time
Ans ) Press CTRL+Z
82 Alert message should not exceed Ans) 200 Characters
83 Data Blocks in the form builder can be classified as
Ans ) Master & Detail Block
84 The type of alerts can be set Ans) Note, Caution, Stop
85 The 4 different states of records are as follow ( when internal events of forms run time) Ans ) New, Insert, query, Changed
86 The hierarchy tree of objects in the form application
Ans ) Form(Module), Block, record, Item
87 VALIDATE RECORD , Trigger (event) occurs only if the record is Ans) insert
88 LOV (LIST OF VALUES) is a combination of
Ans ) Modal dialog Boxes, List and Record Groups
89 The trigger can be classified as the following:-
Ans ) Smart triggers, Un restricted, Triggers, restricted Triggers
90 The built in procedure to open a form that is independent from calling form is
Ans ) open_form
91 The way to involve Report from form object is Ans) run_report_object
92 The is the syntax and place the variable declared in form module to all the triggers in the form
Ans ) Declare in PREFOR and the syntax is :Global.
93 A primary key constraint cannot be added on the column of which data type
Ans ) long
94 The comprises set of files that protect altered database data that has not been written to data files Ans) redo log
95 Maximum size of raw data type is Ans) 2kb
96 Which one of the following is not applicable on INSTEAD OF Trigger
Ans ) Can be applied on tables
97 In Oracle 8, a char database can hold up to Ans) 2000 bytes
98 After creating a sequence, to view the first value we use which keyword
Ans ) nextval
99 In PL/SQL assignment operator is replaced using the reserved word
Ans) default
100 is a valid pseudo column
Ans) rowid, rownum, sysdate, uid, nextval, curval
101 The Index created by the Primary key column is called ans) unique index
102 The operation cannot be performed using views when the view definition includes more than on table Ans) insert
103 The parameter cannot be changed while altering a sequence
Ans ) start with n (n cant be changed)
104 To check the source code for the object types use the data dictionary view Ans user_source
105 Which of the following is most restrictive of table level locks?
Ans) exclusive lock
106 In PL/SQL block what is the default value of the variable Ans) null
107 Find output of the following ( Select substr( \"christmas\" , 4,3) from dual)
Ans ) ist
108 The ability of object to take more than one form is known as
Ans ) polymorphism
109 When the user is trying to assign values to the attributes of an uninitialized object, which of the following exception is raised Ans) VALUEERROR
110 The attribute in the create sequence syntax specifies that the sequence continues to generate values from the beginning after reaching either its max or min value Ans) increment
111 A composite unique key can contain how many number of columns Ans) 16
112 What operations can be performed on sequence Ans) Alter, Select
113 Restriction on triggers can be achieved using what clause Ans) when
114 To check the partition type which data dictionary can be used.
Ans ) user_tab_partitions
115 Outer joins retrieves Ans) both matched and unmatched rows
116 A function used to convert a null value to value is Ans) decode
117 Role is Ans ) group of privileges
118 A returns all rows returned by the simple join as well as those rows from one table that do not match any row from the other table Ans) outer join
119 The data that stored in the cursor is called as Ans ) active data set
120 what is the format mask for Julian data Ans ) j
121 Label is a word associated with an statements Ans) conditional
122 level locks are not released by rolling back to save point Ans ) row level
123 you can use the CASE function in place of Ans) decode
124 The writes modified blocks from the database buffer cache to the data files
Ans ) database writer
125 The magnitude range of a binary integer value in pL/sql is
Ans ) -231-1 to 231-1
126 Trigger name should not exceed characters Ans) 30 characters
127 The REF operator returns of the row object Ans) OID
128 PL/SQL is a structured language? Ans ) block structured language
129 Raw types are used to store which type of data Ans ) binary
130 PCTFREE is a portion of the data block that is not filled by rows as they are inserted in to a table but is reserved for Ans ) 'Future insert
131 Sign function returns Ans) +, - and =
132 Syntax of MERGE function
Ans) merge into using /<> on when matched these update set when not matched then insert values
133 The oracle engine provides procedure named that allows programmers to show user defined messages Ans) Raise_Application_Error
134 Which are called as trigger actions Ans) Before and after
135 A function that act on only one value at a time are called as
Ans ) Scalar function
136 is used to get cross tabulation & cumulative values
ans ) CUBE and ROLLUP
137 Which is used for bulk bind? Ans) for all
138 which is used to modify any operating system files Ans) UTL_File
139 which is used to insert values in to multiple tables using select statements
ans ) INSERTALL
140 Which appends on operating system specific line terminator Ans) put_line
141 Which is an object , that is completely contained within another
Ans ) embedded object
142 Which of the following is false regarding data files?
Ans ) Data files can be associated with two or more table spaces
143 In which data dictionary table the object type information will be stored
Ans ) User_Types
144 When you use the THE class in DML statements Ans) Nested table
145 Which is used to store complete column information at a time in to a variable
Ans ) bulk collect
146 when you use for update class in a cursor which is compulsory
ans ) where current of
147 When cursor is in for loop which operations are not necessary
Ans ) Open, Fetch, Close
148 RowID consistes of
Ans ) Data file Id, Object Id , Block Id, Row_Sequence_ID
149 What is a object, which can be created only at run time Ans ) Timer
150 what is the extension of .PLL Ans) Programming Link Library
151 what is the extension of object library module Ans) OBL
152 How many styles are there in alert property Ans) three
153 Display item doesn’t have which property Ans) navigational
154 When Timer Expired is correspondent to which object Ans) timer
155 When we compare with radio button & push button, push button will not have
Ans ) Button value property
156 Which function is used to create runtime record group Ans) create_group
157 which trigger and code is used to run application without connecting to database
ans ) on_logon,NULL
158 Form 6i is a ans ) 2 and 3 Tier architecture
159 When we select value from LOV, by default where the value is going to store
Ans ) return item
160 LOV always return ans ) boolean value
161 Alert always return ans) number value
162 Which procedure is used to change alert button label
Ans ) set_alert_button_properly
163 When we create a LOV automatically created ans ) record group
164 which function is used to create dynamic query record group
ans ) create_group_from_query
165 How many types of canvas are there ans ) five
166 Which variable cannot assign a value ans) system
167 When you have cascade delete behavior which trigger gets created
Ans ) PRE-DELETE
168 Which Trigger fires when an operator double clicks on a image item
Ans ) WHEN_IMAGE_ACTIVATED
169 The data type of an image item is ans ) BLOB
170 The default window name is ans ) window1
171 What needed to create for making a master/detail report Ans) Data link
172 A group of radio buttons are placed under the item ans ) Radio Group
173 An operator cannot navigate to a ans) Display item
174 The Default text label of alert button1 is ans) OK
175 The LOV can be attached to a text Item at runtime by the built in
Ans) SET_LOV_PROPERTY
176 The on_clear_detail and On_ populate_deatails triggers are used with
Ans ) ISOLATED operations
177 Which Symbol is used for lexical parameter Ans) &
178 What are called trigger time ans) before and after
179 In oracle any object name it should start with and it should not exceed characters
Ans ) character and 30
180 Which one of the following is applicable on INSTEAD OF Triggers
Ans ) complex views
181 Restriction on triggers can be achieved using ans ) where clause
182 Within a FOR LOOP, which variable cannot be changed by the user
Ans ) Index Variable
183 Which Cursor is created by any SQL statement ans ) implicit
184 Which clause within Cursor exclusively locks the rows returned by the query
Ans ) For update
185 Which Exceptions are internally defined by runtime system ans ) Pre defined
186 Which option is used with the create command to modify an existing named pl/sql block ans ) replace
187 Minimum privilege required to connect to oracle database is ans ) connect
188 How to call a procedure from a PL/SQL block ans ) Using procedure name
189 Which attribute provides a record type that represents a row in a table
Ans )% row
190 For iteration to proceed downwards from upper bound to lower bound we can use the optional keyword in a for loop ans ) reverse
191 if PL/SQL runs out of memory, then, what predefined exception is raised
Ans ) Storage_error
192 which exception is used when all exceptions are to be trapped
ans ) when others
193 The conditions can be given at which clause for grouped result
Ans ) Group by
194 Joining a table to itself is called ans ) self join
195 If inner query in sub query returns more than one row then which operator is used
Ans ) IN
196 The dual table contains column and row. Ans) single & single
197 Which option is used to grant privileges to all users Ans) Public
198 Which pseudo column used to refer to a sequence
Ans ) Curval & Nextval
199 Which level locks are not released by rolling back to save point ans) rowlevel
200 which function is used for opening a file ans) fopen
201 The procedure reads a line from the file ans) get_line
202 The colon symbol is used for parameter ans) bind
203 In the radio button property which value is must ans) initial
204 Which function is used to find out whether the runtime query record group is return rows or not
Ans ) populate_group_with_query and populate_group
205 :system.currentitem is going return ans ) Name
206 The default item type is ans) text
207 The display status of a check box is ans) checked, Unchecked
208 Which trigger performs an action whenever a record is cleared or deleted
Ans ) when_remove_record
209 Which trigger is associated with a button item ans) when_button_pressed
210 setting which property to Yes, a LOV is displayed as soon as the user enters an item ans ) auto refresh
211 The block coordination has deferred and ans) Auto query
212 The value of what parameter being passed to a called product is always the name of a record group defined in the current form ans) Data_parameter
213 Which built-in will call another form within a form ans) call_form
214 At least one must be assigned to each window ans) canvas
215 built-in is used to display a window at runtime ans) show_window
216 An executable menu module file has the extension ans ) mmx
217 type of menu items are not require PL/SQL code ans ) Magic & Separator
218 built-in is used to call a report from a form ans ) SRW.Run_product
219 what is the default value of date ans ) $$date$$
220 which system variable we can assign value ans ) system.date_thershold
221 what parameter is required to create group function ans ) Record groupname
222 which style has to be selected for making master/detail report ans ) group above
223 built-in is used to call report within a report ans ) srw.run_report
224 In show_editor procedure which are optional parameters ans) X and Y
225 Which trigger is associated with a list item ans) when_list_changed
226 which procedure is used to changed the size of image ans ) Image_zoom
227 which function is used to create dynamic query record group
ans ) populate_group_with_query
228 Maximum number of buttons that can be defined in an alert is ans ) 3
229 How many types of menu items are there in menu module ans) 5
230 How many arguments are required in add_group_column ans) 4
231 How many arguments are required in set_alert_button_property ans) 4
Posted by Sunil Dutt at 1:20 AM 0 comments Links to this post
Monday, September 8, 2008
SQL*Loader Examples
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:
sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data: load data
infile 'c:\data\mydata.csv'
into table emp ( empno, empname, sal, deptno )
fields terminated by "," optionally enclosed by '"'
The mydata.csv file may look like this:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20) )
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 ',' col2 ',' col3
from tab1
where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.
Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1, data2 )
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state )
Can one load data into multiple tables at once?
Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL )
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat'
BADFILE 'mydata.bad'
DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
( region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR )
Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE
INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3 )
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
No comments:
Post a Comment