Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and will be (partially?) implemented in Oracle8.
2. What are the difference between DDL, DML and DCL commands?
DDL is Data Definition Language statements. Some examples:
· CREATE - to create objects in the database
· ALTER - alters the structure of the database
· DROP - delete objects from the database
· TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
· COMMENT - add comments to the data dictionary
· GRANT - gives user's access privileges to database
· REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like what rollback segment to use
3. How does one escape special characters when building SQL queries?
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example: SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';
Use two quotes for every one displayed. Example: SELECT 'Franks''s Oracle site' FROM DUAL; SELECT 'A ''quoted'' word.' FROM DUAL; SELECT 'A ''''double quoted'''' word.' FROM DUAL;
4. How does one eliminate duplicates rows from a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving unique records in the table:
Method 1: SQL> DELETE FROM table_name A WHERE ROWID > ( 2 SELECT min(rowid) FROM table_name B 3 WHERE A.key_values = B.key_values);
Method 2: SQL> create table table_name2 as select distinct * from table_name1; SQL> drop table_name1; SQL> rename table_name2 to table_name1;
Method 3: (thanks to Kenneth R Vanluvanee) SQL> Delete from my_table where rowid not in( SQL> select max(rowid) from my_table SQL> group by my_column_name );
Method 4: (thanks to Dennis Gurnick) SQL> delete from my_table t1 SQL> where exists (select 'x' from my_table t2 SQL> where t2.key_value1 = t1.key_value1 SQL> and t2.key_value2 = t1.key_value2 SQL> and t2.rowid > t1.rowid);
Note: If you create an index on the joined fields in the inner loop, you, for all intents purposes, eliminate N^2 operations (no need to loop through the entire table on each pass by a record). This will speed-up th
Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL.
5. How can I generate primary key values for my table?
Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;Finally, create a unique index on this column.
6. How can I get the time difference between two date columns
Look at this example query: select floor(((date1-date2)*24*60*60)/3600) ' HOURS ' floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60) ' MINUTES ' round((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600 - (floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60))) ' SECS ' time_differencefrom ...
7. How does one add a day/hour/minute/second to a date value?
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples: SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual; SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400 -------------------- -------------------- -------------------- -------------------- 03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The following format is frequently used with Oracle Replication: select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual; NOW NOW_PLUS_30_SECS -------------------- -------------------- 03-JUL-2002 16:47:23 03-JUL-2002 16:47:53
8. How does one count different data values in a column? select dept, sum( decode(sex,'M',1,0)) MALE, sum( decode(sex,'F',1,0)) FEMALE, count(decode(sex,'M',1,'F',1)) TOTAL from my_emp_table group by dept;
9. How does one count/sum RANGES of data values in a column?
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example: select f2, sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100", sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59", sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29" from my_table group by f2;
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg. select ename "Name", sal "Salary", decode( trunc(f2/1000, 0), 0, 0.0, 1, 0.1, 2, 0.2, 3, 0.31) "Tax rate" from my_table;
10. Can one retrieve only the Nth row from a table?
this solution to select the Nth row from a table: SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10);
Alternatively... SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN (SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
11. Can one retrieve only rows X to Y from a table?
To display rows 5 to 7, construct a query like this: SELECT * FROM tableX WHERE rowid in ( SELECT rowid FROM tableX WHERE rownum <= 7 MINUS SELECT rowid FROM tableX WHERE rownum < 5);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
12. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery SELECT * FROM emp WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4) FROM emp);
Method 2: Use dynamic views (available from Oracle7.2): SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp ) temp WHERE MOD(temp.ROWNUM,4) = 0;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
13. How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example: SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10;
Use this workaround with prior releases: SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;
14. How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example: select LEVEL, EMPNO, ENAME, MGR from EMP connect by prior EMPNO = MGR start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example: select lpad(' ', LEVEL * 2) ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
15. How does one code a matrix report in SQL?
Look at this example query with sample output: SELECT * FROM (SELECT job, sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20, sum(decode(deptno,30,sal)) DEPT30, sum(decode(deptno,40,sal)) DEPT40 FROM scott.emp GROUP BY job) ORDER BY 1; JOB DEPT10 DEPT20 DEPT30 DEPT40 --------- ---------- ---------- ---------- ---------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5600
16. How does one implement IF-THEN-ELSE in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.
Some examples: select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees; select a, b, decode( abs(a-b), a-b, 'a > b', 0, 'a = b', 'a < b') from tableX; select decode( GREATEST(A,B), A, 'A is greater than B', 'B is greater than A')...
Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example: SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END FROM emp;
17. How can one dump/ examine the exact content of a database column? SELECT DUMP(col1) FROM tab1 WHERE cond1 = val1; DUMP(COL1) ------------------------------------- Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
18. Can one drop a column from a table?
From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Other workarounds: 1. SQL> update t1 set column_to_drop = NULL; SQL> rename t1 to t1_base; SQL> create view t1 as select
19. Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Some workarounds: 1. -- Use a view with correct column names... rename t1 to t1_base; create view t1
20. How can I change my Oracle password?
Issue the following SQL command: ALTER USER
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".
21. How does one find the next value of a sequence?
Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache. This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER SEQUENCE ... CACHE" to restore caching.
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.
22. Workaround for snapshots on tables with LONG columns
You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg: COPY TO SCOTT/TIGER@REMOTE -CREATE IMAGE_TABLE USING - SELECT IMAGE_NO, IMAGE - FROM IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.
23. What is SQL?
SQL, Structured Query Language, is a database query language that was adopted as an industry standard in 1986.
24. What is SQL3?
Among other enhancements, SQL3 supports objects. SQL3 probably won't be complete until 1998; however, Illustra already implements many SQL3 features. More information about SQL3 is available via anonymous ftp to speckle.ncsl.nist.gov:/isowg3.
25. What are the SQL reserved words?
I grep'd the following list out of the sql docs available via anonymous ftp to speckle.ncsl.nist.gov:/isowg3. SQL3 words are not set in stone, but you'd do well to avoid them. From sql1992.txt: AFTER, ALIAS, ASYNC, BEFORE, BOOLEAN, BREADTH, COMPLETION, CALL, CYCLE, DATA, DEPTH, DICTIONARY, EACH, ELSEIF, EQUALS, GENERAL, IF, IGNORE, LEAVE, LESS, LIMIT, LOOP, MODIFY, NEW, NONE, OBJECT, OFF, OID, OLD, OPERATION, OPERATORS, OTHERS, PARAMETERS, PENDANT, PREORDER, PRIVATE, PROTECTED, RECURSIVE, REF, REFERENCING, REPLACE, RESIGNAL, RETURN, RETURNS, ROLE, ROUTINE, ROW, SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SIGNAL, SIMILAR, SQLEXCEPTION, SQLWARNING, STRUCTURE, TEST, THERE, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL, VISIBLE, WAIT, WHILE, WITHOUT From sql1992.txt (Annex E): ABSOLUTE, ACTION, ADD, ALLOCATE, ALTER, ARE, ASSERTION, AT, BETWEEN, BIT, BIT_LENGTH, BOTH, CASCADE, CASCADED, CASE, CAST, CATALOG, CHAR_LENGTH, CHARACTER_LENGTH, COALESCE, COLLATE, COLLATION, COLUMN, CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS, CONVERT, CORRESPONDING, CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DATE, DAY, DEALLOCATE, DEFERRABLE, DEFERRED, DESCRIBE, DESCRIPTOR, DIAGNOSTICS, DISCONNECT, DOMAIN, DROP, ELSE, END-EXEC, EXCEPT, EXCEPTION, EXECUTE, EXTERNAL, EXTRACT, FALSE, FIRST, FULL, GET, GLOBAL, HOUR, IDENTITY, IMMEDIATE, INITIALLY, INNER, INPUT, INSENSITIVE, INTERSECT, INTERVAL, ISOLATION, JOIN, LAST, LEADING, LEFT, LEVEL, LOCAL, LOWER, MATCH, MINUTE, MONTH, NAMES, NATIONAL, NATURAL, NCHAR, NEXT, NO, NULLIF, OCTET_LENGTH, ONLY, OUTER, OUTPUT, OVERLAPS, PAD, PARTIAL, POSITION, PREPARE, PRESERVE, PRIOR, READ, RELATIVE, RESTRICT, REVOKE, RIGHT, ROWS, SCROLL, SECOND, SESSION, SESSION_USER, SIZE, SPACE, SQLSTATE, SUBSTRING, SYSTEM_USER, TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE, UNKNOWN, UPPER, USAGE, USING, VALUE, VARCHAR, VARYING, WHEN, WRITE, YEAR, ZONE From sql3part2.txt (Annex E) ACTION, ACTOR, AFTER, ALIAS, ASYNC, ATTRIBUTES, BEFORE, BOOLEAN,BREADTH, COMPLETION, CURRENT_PATH, CYCLE, DATA, DEPTH, DESTROY, DICTIONARY, EACH, ELEMENT, ELSEIF, EQUALS, FACTOR, GENERAL, HOLD, IGNORE, INSTEAD, LESS, LIMIT, LIST, MODIFY, NEW, NEW_TABLE, NO, NONE, OFF, OID, OLD, OLD_TABLE, OPERATION, OPERATOR, OPERATORS, PARAMETERS, PATH, PENDANT, POSTFIX, PREFIX, PREORDER, PRIVATE, PROTECTED, RECURSIVE, REFERENCING, REPLACE, ROLE, ROUTINE, ROW, SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SESSION, SIMILAR, SPACE, SQLEXCEPTION, SQLWARNING, START, STATE, STRUCTURE, SYMBOL, TERM, TEST, THERE, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL, VISIBLE, WAIT, WITHOUT sql3part4.txt (ANNEX E): CALL, DO, ELSEIF, EXCEPTION, IF, LEAVE, LOOP, OTHERS, RESIGNAL, RETURN, RETURNS, SIGNAL, TUPLE, WHILE
SQL FAQ: How To's
26. How do you tell what other database objects exist?
Information about databases, users, and objects in a database are stored in the Illustra system catalogs. Illustra provides DBA functions that make querying the system catalogs easier; for example, ml_dbase() lists all databases on an Illustra server: * select * from ml_dbase(); ------------------------------------------------------------ database_dba database_namedatabase_pathdatabase_release ------------------------------------------------------------ jolly jolly jolly 2.0.9 miro template1 template1 2.0.9 jolly testsaif testsaif 2.0.9 jiangwu jiangwu jiangwu 2.0.9 jta jta jta 2.0.9 pbrown pbrown pbrown 2.0.9 ------------------------------------------------------------ 6 rows selected
The following error means the dba functions have not been installed: * select * from ml_dbase(); XP0038:Cannot find a function named 'ml_dbase' taking no arguments
As the owner of a database, you can install the functions like this: % cd $MI_HOME/examples/dba_funcs % make MI_DATABASE=your_dbname all
The Illustra system adminstrator can install these functions in the template1 database so a database will automatically get them at the time it is created.
The DBA functions are fully documented in Chapter 6 of the Illustra System Administrator's Guide. Here is the summary list from that reference: ml_aggs([owner]) information about aggregates ml_cstat(object_name) column statistics for object ml_dbase([dba]) current databases ml_dbase_dr(dbname) dump times and logging status ml_finfo([owner [,language]]) information about functions ml_fsrc(func_name) source of sql functions ml_priv([grantee]) privileges ml_process([user_name]) server processes ml_rules(owner [, table_name]) information about rules ml_stat_procs(pid) lock status for process ml_tables([owner [,kind]]) tables, views and indices ml_tcols(object_name) column information for object ml_tindex(table_name) indices for table ml_tstat(object_name) statistics for objects ml_typecols(type_name) column information for type ml_types() user-defined types ml_user() user information
27. How do you extract just the month from an abstime?
substring extracts just a portion of a string: * return('now'::abstime) as Now; ------------------------------------- Now ------------------------------------- Mon Apr 18 09:06:10.490753 1994 PDT ------------------------------------- one row selected * return substring('now'::abstime::text from 5 for 3) as Month; --------------- Month --------------- Apr --------------- one row selected
For more examples, see section 3.5.7.2 in the Illustra User's Guide.
28. How do you remove duplicate entries from a table?
Table foo has duplicate entries for Donald Duck: * select * from foo; ----------------------------- first_name last_name ----------------------------- Donald Duck Mighty Mouse Donald Duck ----------------------------- 3 rows selected
This note describes two approaches for removing the duplicates:
Using a temporary table to remove duplicates.
Using oid to remove duplicates.
1. Using a temporary table to remove duplicates.
The steps for this method are:
select distinct into a temporary table. 2. * create table foo2 as select distinct first_name, last_name from foo;
Doublecheck the results. 4. * select * from foo2;5. -----------------------------6. first_name last_name 7. -----------------------------8. Donald Duck 9. Mighty Mouse 10. -----------------------------11. 2 rows selected
Drop the first table. 13. * drop table foo;
Rename the temporary table to the original table. 15. * alter table foo2 rename to foo;
Don't forget to recreate any indexes and permissions that were on the original table.
2. Using oid to remove duplicates.
This method describes how to remove duplicate entries from a table by using the oid. Syntax is for Illustra Version 2.
Here is the original state of table foo that has duplicate entries for Donald Duck: * select oid, * from foo; ------------------------------------------- oid first_name last_name ------------------------------------------- 202f.2001 Donald Duck 202f.2002 Mighty Mouse 202f.2003 Donald Duck ------------------------------------------- 3 rows selected
The goal is to leave the row with the lowest oid in place (202f.2001 in this example) and to delete all rows with an oid higher than this one (202f.2003 in this example). The steps are to:
Find all rows that have an oid greater than the min oid. 2. * select f1.oid, f1.first_name, f1.last_name3. from foo f14. where f1.oid::text >5. (select min(f2.oid::text)6. from foo f27. where f1.first_name=f2.first_name8. and f1.last_name=f2.last_name);9. -------------------------------------------10. oid first_name last_name 11. -------------------------------------------12. 202f.2003 Donald Duck 13. -------------------------------------------14. one row selected
Delete all rows that have an oid greater than the min oid. Change the select query to actually delete the duplicate row. Do this inside a transaction; and don't commit the change unless the results look correct. 16. * begin transaction;17. * delete from foo f118. where f1.oid::text >19. (select min(f2.oid::text)20. from foo f221. where f1.first_name=f2.first_name22. and f1.last_name=f2.last_name);23. one row deleted24. 25. + select * from foo;26. -----------------------------27. first_name last_name 28. -----------------------------29. Donald Duck 30. Mighty Mouse 31. -----------------------------32. 2 rows selected33. 34. + end transaction;
29. How does running vacuum make queries run faster?
The query optimizer uses statistics gathered on a table to determine the fastest way to execute a query. You can look at some of this information with the ml_cstat() and ml_tstat() functions. If statistics get out of date, for example if the distribution of values in a column changes dramatically, the optimizer might choose a slower way to execute the query.
The vacuum command invoked with the statistics modifier updates statistics.
Creating an index automatically updates the statistics for the column(s) being indexed.
30. How can I tell what indices a table has?
The ml_tindex() function lists all indices on a table; for example, the following query list all indices on the tables system catalog: * select * from ml_tindex('tables'); --------------------------------------------------------- table_name index_name archived keys --------------------------------------------------------- tables tablenameind f Column 19 tables tableidind f Column -2 tables tabletypeind f Column 17 --------------------------------------------------------- 3 rows selected
31. How can I tell which index Illustra is using?
Call the trace function with the 'Planner.1' argument. For more information, see Appendix E of the Illustra User's Guide.
32. Can I tell Illustra which index to use?
You can provide hints to the Illustra optimizer, including which index to use, with the using clause. For more information, see Appendix E of the Illustra User's Guide.
SQL FAQ: JOINS
I get too many rows when I join two tables.
I don't get enough rows when I join two tables.
Doing joins with Illustra ref().
Cartesian Products
When you join tables, make sure that the number of join predicates in the search condition is one less than the number of tables in the from list. Otherwise, you will get many more rows returned than you probably intended. For example, table english and spanish look like this: * select * from english; * select * from spanish; ----------------------------- ----------------------------- tag name tag name ----------------------------- ----------------------------- 1 one 2 dos 2 two 3 tres 3 three 4 cuatro ----------------------------- ----------------------------- 3 rows selected 3 rows selected
If you select from both tables without joining them in the where clause, you get a cartesian product, every possible combination of both: * select * from english, spanish; --------------------------------------------------------- tag name tag name --------------------------------------------------------- 2 dos 1 one 3 tres 1 one 4 cuatro 1 one 2 dos 2 two 3 tres 2 two 4 cuatro 2 two 2 dos 3 three 3 tres 3 three 4 cuatro 3 three --------------------------------------------------------- 9 rows selected
Most likely, this is not what you had in mind. Since there are two tables in the from_list, one join predicated is needed: * select * from english, spanish where english.tag = spanish.tag; --------------------------------------------------------- tag name tag name --------------------------------------------------------- 2 dos 2 two 3 tres 3 three --------------------------------------------------------- 2 rows selected
Inner and Outer Joins
A join between two tables does not include any rows from either table that have no matching rows in the other. This is called an inner join and frequently causes confusion since fewer rows are returned than the user expects. For example, tables english and spanish look like this: * select * from english; * select * from spanish; ----------------------------- ----------------------------- tag name tag name ----------------------------- ----------------------------- 1 one 2 dos 2 two 3 tres 3 three 4 cuatro ----------------------------- ----------------------------- 3 rows selected 3 rows selected
When you join these two tables, you get only the two rows that have the same tag: * select e.name, e.tag, s.name from english e, spanish s where e.tag = s.tag; ------------------------------------------- name tag name ------------------------------------------- two 2 dos three 3 tres ------------------------------------------- 2 rows selected
Row one in table english and row cuatro in table spanish fall into the outer joins: Joins +--------------+ left outer ---> one 1 +--------------+ +--> two 2 : dos inner join : +--> three 3 : tres +--------- - -+ 4 cuatro <--- right outer +--------------+
You can select outer join rows by using not exists. This query fetches the row in english that is not in spanish (the left outer join): * select e.name as English, e.tag, '--no row --' as Spanish from english e where not exists (select * from spanish s where e.tag=s.tag); ------------------------------------------- English tag Spanish ------------------------------------------- one 1 --no row -- ------------------------------------------- one row selected
This query fetches the row in spanish that is not in english (the right outer join): * select '--no entry--' as English, s.tag, s.name as Spanish from spanish s where not exists (select * from english e where e.tag=s.tag); ------------------------------------------- English tag Spanish ------------------------------------------- --no entry-- 4 cuatro ------------------------------------------- one row selected
You can string all statements together with union: * select e.name::text as English, e.tag, s.name::text as Spanish from english e, spanish s where e.tag = s.tag union select e.name::text, e.tag, '--no entry--'::text from english e where not exists (select * from spanish s where e.tag=s.tag) union select '--no entry--'::text, s.tag, s.name::text from spanish s where not exists (select * from english e where e.tag=s.tag) order by 2; ------------------------------------------- English tag Spanish ------------------------------------------- one 1 --no entry-- two 2 dos three 3 tres --no entry-- 4 cuatro ------------------------------------------- 4 rows selected
If you think this is a lot of trouble to retrieve outer join data, there's another way to handle known joins in Illustra that will factor in outer join data. Keep reading.
Solving Outer Joins in Illustra with ref()
Confusion with outer joins was described above. This section looks at another way to resolve outer join confusions in Illustra by using ref().
We start by creating the two tables like this and inserting data: create table spanish of new type spanish_t (name varchar(20), tag integer); create table english of new type english_t (name varchar(20), tag integer, sname ref(spanish_t)); insert into english (name, tag) values ('one', 1); insert into english (name, tag) values ('two', 2); insert into english (name, tag) values ('three', 3); insert into spanish (name, tag) values ('dos', 2); insert into spanish (name, tag) values ('tres', 3); insert into spanish (name, tag) values ('cuatro', 4);
Next we update the reference in english: * update english set sname = (select unique ref(s1) from spanish s1 where english.tag = s1.tag); 3 rows updated * select * from english; ------------------------------------------- name tag sname ------------------------------------------- one 1 NULL two 2 202d.2001 three 3 202d.2002 ------------------------------------------- 3 rows selected
Notice that the select from english returned the oid reference to spanish. You can dereference that oid as follows: * select name as english, tag, deref(sname).name as spanish from english; ------------------------------------------- english tag spanish ------------------------------------------- one 1 NULL two 2 dos three 3 tres ------------------------------------------- 3 rows selected
We can also take it the opposite way by updating the spanish_t type and spanish table as follows: * alter type spanish_t add column ename ref(english_t); * update spanish set ename = (select unique ref(e1) from english e1 where spanish.tag = e1.tag); 3 rows updated * select name as spanish, tag, deref(ename).name as english from spanish; ------------------------------------------- spanish tag english ------------------------------------------- dos 2 two tres 3 three cuatro 4 NULL ------------------------------------------- 3 rows selected
Finally, we can use union to select from both: * select name as english, tag, deref(sname).name as spanish from english union select deref(ename).name as english, tag, name as spanish from spanish order by 2; ------------------------------------------- english tag spanish ------------------------------------------- one 1 NULL two 2 dos three 3 tres NULL 4 cuatro ------------------------------------------- 4 rows selected
Realize that if new rows are inserted into either table, the reference must be set in the tables that references it.
Oracle PL/SQL FAQ
33. What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
34. Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
35. How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example: SELECT OBJECT_NAME, TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME, TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME, STATUS FROM USER_OBJECTS WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
36. How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code. SELECT TYPE, NAME, LINE FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
37. How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example: CREATE TABLE SOURCE_HIST -- Create history table AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.* FROM USER_SOURCE WHERE 1=2; CREATE OR REPLACE TRIGGER change_hist -- Store code in hist tableAFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name DECLARE BEGIN if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE') then -- Store old code in SOURCE_HIST table INSERT INTO SOURCE_HIST SELECT sysdate, user_source.* FROM USER_SOURCE WHERE TYPE = DICTIONARY_OBJ_TYPE AND NAME = DICTIONARY_OBJ_NAME; end if; EXCEPTION WHEN OTHERS THEN raise_application_error(-20000, SQLERRM); END; / show errors
38. How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is: wrap iname=myscript.sql oname=xxxx.plb
39. Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example: set serveroutput on begin dbms_output.put_line('Look Ma, I can print from PL/SQL!!!'); end; /
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
40. Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started: DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w'); UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n'); UTL_FILE.FCLOSE(fileHandler); EXCEPTION WHEN utl_file.invalid_path THEN raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.'); END; /
41. Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL). begin EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)'; end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
42. Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons: EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)'; -- Using bind variables... sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; -- Returning a cursor... sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples: CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END; /
More complex DBMS_SQL example using bind variables: CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS v_cursor integer; v_dname char(20); v_rows integer;BEGIN v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no); DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20); v_rows := DBMS_SQL.EXECUTE(v_cursor); loop if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then exit; end if; DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname); DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname); end loop; DBMS_SQL.CLOSE_CURSOR(v_cursor);EXCEPTION when others then DBMS_SQL.CLOSE_CURSOR(v_cursor); raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);END;/
43. What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example: DECLARE v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example: DECLARE v_EmpNo emp.empno%TYPE;
44. What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself. declare a number := NULL; b number := NULL; begin if a=b then dbms_output.put_line('True, NULL = NULL'); elsif a<>b then dbms_output.put_line('False, NULL <> NULL'); else dbms_output.put_line('Undefined NULL is neither = nor <> to NULL'); end if; end;
45. How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this: i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values: select sq_sequence.NEXTVAL into :i from dual;
46. Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
47. How does one loop through tables in PL/SQL?
Look at the following nested loop code example. DECLARE CURSOR dept_cur IS SELECT deptno FROM dept ORDER BY deptno; -- Employee cursor all employees for a dept number CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS SELECT ename FROM emp WHERE deptno = v_dept_no; BEGIN FOR dept_rec IN dept_cur LOOP dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno)); FOR emp_rec in emp_cur(dept_rec.deptno) LOOP dbms_output.put_line('...Employee is 'emp_rec.ename); END LOOP; END LOOP; END; /
48. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this: FOR records IN my_cursor LOOP ...do some stuff... COMMIT; END LOOP;
... to ... FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) THEN -- Commit every 10000 records COMMIT; END IF; END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
49. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
Grant direct access on the tables to your user. Do not use roles! · GRANT select ON scott.emp TO my_user;
Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.
50. What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
51. Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example: -- Database A: receives a PL/SQL table from database BCREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS BEGIN -- do something with TabX from database B null; END; / -- Database B: sends a PL/SQL table to database ACREATE OR REPLACE PROCEDURE pcalling IS TabX DBMS_SQL.VARCHAR2S@DBLINK2; BEGIN pcalled@DBLINK2(TabX); END; /
52. Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
53. Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
Oracle SQL*Loader FAQ
54. 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.
55. 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
56. 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
57. 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 )
58. 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 )
59. 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 )
60. 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 )
61. 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.
62. 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=.
63. What is SQL*Plus and where does it come from?
SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.
SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first releases of Oracle, its interface was extremely primitive and anything but user friendly.
64. What are the basic SQL*Plus commands?
The following SQL*Plus commands are available:
ACCEPT
Get input from the user
DEFINE
Declare a variable (short: DEF)
DESCRIBE
Lists the attributes of tables and other objects (short: DESC)
EDIT
Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT
Disconnect from the database and terminate SQL*Plus
GET
Retrieves a SQL file and places it into the SQL buffer
HOST
Issue an operating system command (short: !)
LIST
Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT
Display a text string on the screen. Eg prompt Hello World!!!
RUN
List and Run the command stored in the SQL buffer (short: /)
SAVE
Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
SET
Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW
Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL
Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START
Run a SQL script file (short: @)
65. What is AFIEDT.BUF?
AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
In the prehistoric days when SQL*Plus was called UFI, the file name was "ufiedt.buf", short for UFI editing buffer.
When new features were added to UFI, it was the initially named Advanced UFI and the filename was changed to "aufiedt.buf" and then to "afiedt.buf". They presumably needed to keep the name short for compatibility with some of the odd operating systems that Oracle supported in those days.
The name "Advanced UFI" was never used officially, as the name was changed to SQL*Plus before this version was released.
You can overwrite the default edit save file name like this: SET EDITFILE "afiedt.buf"
65. What is the difference between @ and @@?
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
A single @ symbol runs the script in your current directory, or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH.
@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files.
66. What is the difference between & and &&?
"&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value every time the variable is referenced.
Eg: SQL> SELECT * FROM TAB WHERE TNAME LIKE '%&TABLE_NAME.%';
67. How can one disable SQL*Plus formatting?
Issue the following SET commands to disable all SQL*Plus formatting: SET ECHO OFF SET NEWPAGE 0 SET SPACE 0 SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SET TRIMSPOOL ON
These settings can also be entered on one line, eg.: SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON
68. Can one send operating system parameters to SQL*Plus?
One can pass operating system variables to sqlplus using this syntax: sqlplus username/password @cmdfile.sql var1 var2 var3
Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example: sqlplus scott/tiger @x.sql '"test parameter"' dual Where x.sql consists of: select '&1' from &2; exit 5;
69. Can one copy tables with LONG columns from one database to another?
About the fastest way of copying data between databases and schemas are by using the SQL*Plus COPY statement. Look at this example: COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB - CREATE IMAGE_TABLE USING - SELECT IMAGE_NO, IMAGE - FROM IMAGES;
70. What is a mutating table error and how can you get around it?
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.
71. What packages (if any) has Oracle provided for use by developers?
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.
72. 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.
73. 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.
74. 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?
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.
75. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
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.
76. How can you find within a PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status variable.
77. How can you generate debugging output from PL/SQL?
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.
78. What are the types of triggers?
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.
79. How can variables be passed to a SQL routine?
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.
80. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
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.
81. How can you call a PL/SQL procedure from SQL?
By use of the EXECUTE (short form EXEC) command.
82. How do you execute a host operating system command from within SQL?
By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command.
83. You want to use SQL to build SQL, what is this called and give an example
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.
84. What SQLPlus command is used to format output from a select?
This is best done with the COLUMN command.
85. 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.
86. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
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.
87. 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?
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.
88. What is a Cartesian product?
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.
89. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
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.
90. What is the default ordering of an ORDER BY clause in a SELECT statement?
Expected answer: Ascending
91. What is explain plan and how is it used?
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.
92. How do you set the number of lines on a page of output? The width?
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.
93. How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
94. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
95. How do you generate file output from SQL?
Expected answer: By use of the SPOOL command
96. What are Background processes in Oracle and what are they.
There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer (DBWR):
Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b) LogWriter (LGWR):
LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transactions commit and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor (SMON):
The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
d) Process Monitor (PMON):
The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) Checkpoint (CKPT):
At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f) Archieves (ARCH) ::
The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror (RECO) ::
The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) ::
The Dispatcher is useful in Multi Threaded Architecture
i) Lckn ::
We can have upto 10 lock processes for inter instance locking in parallel sql.
97. How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.
They are
a) Data Defination Language(DDL) :
The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :
The DML statments manipulate database data.
c) Transaction Control Statements:
Manage change by DML
c) Session Control
Used to control the properties of current session enabling and disabling roles and changing .e.g :: Alter Statements, Set Role
d) System Control Statements
Change Properties of Oracle Instance .e.g:: Alter System
e) Embedded Sql
Incorporate DDL,DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close
98. What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.
99. Key Words Used in Oracle
The Key words that are used in Oracle are:
a. Committing
A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b. Rollback
A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c. SavePoint
For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transaction into smaller points.
d. Rolling Forward ::
Process of applying redo log during recovery is called rolling forward.
e. Cursor
A cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f. System Global Area(SGA) ::
The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g. Program Global Area (PGA) ::
The PGA is a memory buffer that contains data and control information for server process.
h. Database Buffer Cache ::
Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
i. Redo log Buffer
Redo log Buffer of SGA stores all the redo log entries.
j. Redo Log Files
Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
k. Process
A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.
100. What are Procedure, functions and Packages
Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks. Procedures do not Return values while Functions return one One Value
Packages
Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
101. What are Database Triggers and Stored Procedures
Database Triggers ::
Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules. We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g. operations insert, update ,delete
3 before ,after 3*2.A total of 6 combinations
At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures
Stored Procedures are Procedures that are stored in Compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
102. How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule ::
The Entity Integrity Rule enforces that the Primary key cannot be Null.
b) Foreign Key Integrity Rule ::
The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules ::
The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
103. What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.
104. What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deferred with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query
105. What are the Different Optimisation Techniques
The Various Optimization techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
106. How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement.e.g select DECODE (EMP_CAT,'1','First','2','Second'Null);
Here the Null is the else statement where null is done .
107.How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions e.g. My_exception exception
When My_exception then
108. How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
109. How many minimum groups are required for a matrix report
The minimum number of groups in matrix report are 4
110. What is the difference between static and dynamic lov
The static lov contains the predetermined values while the dynamic lov contains values that come at run time
111. What are snap shots and views
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
112. What are the OOPS concepts in Oracle.
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the peroperty classes as classes and the items as objects
113. What is the difference between candidate key, unique key and primary key
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
114. What is concurrency
Cuncurrency is allowing simultaneous access of same data by different users. Locks useful for accesing the database are
a) Exclusive
The exclusive lock is useful for locking the row when an insert,update or delete is being done.This lock should not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks can be put on the same resource.
115. Previleges and Grants
Privileges are the right to execute a particulare type of SQL statements.
e.g :: Right to Connect, Right to create, Right to resource
Grants are given to the objects so that the object might be accessed accordingly.The grant has to be given by the owner of the object.
116. Table Space,Data Files,Parameter File, Control Files
Table Space ::
The table space is useful for storing the data in the database. When a database is created two table spaces are created.
a) System Table space ::
This data file stores all the tables related to the system and dba tables
b) User Table space
This data file stores all the user related tables We should have seperate table spaces for storing the tables and indexes so that the access is fast.
Data Files
Every Oracle Data Base has one or more physical data files. They store the data for the database. Every data file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.
Parameter Files
Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files
Control files record the physical structure of the data files and redo log files
They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.
117. Physical Storage of the Data
The finest level of granularity of the data base are the data blocks.
Data Block
One Data Block correspond to specific number of physical database space
Extent
Extent is the number of specific number of contigious data blocks.
Segments
Set of Extents allocated for Extents. There are three types of Segments
Data Segment
Non Clustered Table has data segment data of every table is stored in cluster data segment
Index Segment
Each Index has index segment that stores data
Roll Back Segment
Temporarily store 'undo' information
118. What are the Pct Free and Pct Used
Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40
119. What is Row Chaining
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks .
120. What is a 2 Phase Commit
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit Phase :: Commit all participants to coordinator to Prepared, Read only or abort Reply
121. What is the difference between deleting and truncating of tables
Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
122. What are mutating tables
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
123. What are Codd Rules
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.
124. What is Normalisation
Normalisation is the process of organising the tables to remove the redundancy.There are mainly 5 Normalisation rules.
a) 1 Normal Form
A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form
A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
c) 3rd Normal Form
A table is said to be third Normal form when it is not dependant transitively
125. What is the Difference between a post query and a pre query
A post query will fire for every row that is fetched but the pre query will fire only once.
126. Deleting the Duplicate rows in the table
We can delete the duplicate rows in the table by using the Rowid
127. Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE
[ DISABLE all_trigger ]
128. What is pseudo columns ? Name them?
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
129. How many columns can table have?
The number of columns in a table can range from 1 to 254.
130. Is space acquired in blocks or extents ?
In extents .
131. what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values . Can not applied for HASH.
132. what are the data types supported By oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
133. What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
134. Can you use select in FROM clause of SQL select ?
Yes.
135. Where do u decalare Global Variable in Package? Ans: Package Specification 136. Can u create procedure or function without declaring it in Package specs? Ans: YES, It is called private procedure. 137. what is private function and public functions in package? Ans: If the function is declared in Package Specification then it is called Public Function. Public function can be called outside of Package. If the function is not declared in Package Specification then it is called Private Function. Private function can not be called outside of Package. 138. how do u call private functions in package?Ans: pack spcs p1... func f1(); -- Public function func f2(); -- Public function end; pack body p1... func f1(){}; -- public func f2(){}; -- public func f3(){}; -- Private func f4(){}; -- Private end; to call private call it in public function and public fun can be called from outside. 139. create a syquence, open a new session and execute first statement as select sequence. currval from dual; what will happene? Ans:It will give an error. First time we have to fire next val & then only we can use currval. 140. I have t1 table in scott .. and same i have scott1 schema with same name... i grant select on scott1.t1 to scott, now i create a synonym for scott1.t1, what happenes when it is created. will it give runtime error or error while creating synonym?Ans: This will give an error, Same name can not be used 141. How many types of Triggers... (24) 142. tell me diff between .. 7.x, 8, 8i, 9i ( undo tablespace) Ans: 9i New Features-------------- 1. Rollback Segment is being replaced by Undo Tablespace. (We can use either) 2. Flashback Query - It is used to retrack wrongly committed transaction 3. Online Table Creation (e.g. Create table as select * from ....... will generate updated table) 4. List Partition - Table can be partitioned on a list 5. Buffer catche size is now dynamic (upto 4 different sizes can be specified for buffers) 143. what is view? Ans: View is a virtual table (or logical container of data), which does not physically store data. 144. Why instade of trigger is created? Ans: To Insert/Update/Delete record from base table for any view operation 145. Are views updatable?Ans: Yes (Only if the view is based on one table, but it is NOT if more than one tables) 146. types of tuning?Ans: Application Tuning, Database Tuning, Memory Tuning, O/S Tuning 147. locking mode property of block(Immediate/Automatic/Delayed) Automatic (default):- Identical to Immediate if the datasource is anOracle database. For other datasources, Form Builder determines theavailable locking facilities and behaves as much like Immediate aspossible. Immediate:- Form Builder locks the corresponding row as soon as theend user presses a key to enter or edit the value in a text item. Delayed:- Form Builder locks the row only while it posts thetransaction to the database, not while the end user is editing therecord. Form Builder prevents the commit action from processing ifvalues of the fields in the block have changed when the user causes acommit action. 148. What is Function Over loading? Function Overloading means we can use the same procedure name more than one time in a package but the no of parameters should be different or their data type should be different. 149. How can we return a value in procedures? By using in, out parameters we can return the values in procedures 150. What are the features of OOPS used in PL/SQL ?
Ans: Inheritance – Reusability
Abstract Datatype Method Overloading151. Can we use label for anonymous PL/SQL block?
Ans: Yes, use it in “<
No comments:
Post a Comment