301. Display the details of the employees who are managed
by the same manager as the employees with employee_id 174 or 141 and work in
the same department as the employees with employee_id 141 or 174.
select
employee_id,manager_id,department_id from employees
where
manager_id in ( select manager_id from employees
where
employee_id in(141,174))
and department_id in ( select department_id from employees
where
employee_id in(141,174))
and
employee_id not in (178,174);
302. Display the employees details who earn more than the
average salary in their department.
Select
a.last_name, a.salary,a.department_id,b.salavg
From
employees a , (select department_id, avg(salary) salavg from employees group by
department_id)b
Where
a.department_id=b.department_id
And
a.salary>b.salavg;
OR
Select
last_name,salary,department_id from employees outer
Where
salary >(select avg(salary) from employees where department_id=
outer.department_id);
303. Display
details of those employees who have switched jobs at least twice .
Select
e.employee_id, last_name,e.job_id
From
employees e
Where
2<= (select count(*) from job_history
Where
employee_id=e.employee_id);
304. Display
employees who have at least one person reporting to them
select
employee_id ,last_name,job_id,department_id
from
employees outer
where
exists (select ‘x’ from employees where manager_id=outer.employee_id);
OR
select
employee_id ,last_name,job_id,department_id
from
employees outer
where
employee_id in (select manager_id from
employees where manager_id is not null);
305. Display
departments that do not have any
employees
select
department_id, department_name
from
departments d where not exists
(
select ‘x’ from employees where department_id= d.department_id);
306. maintain
only the last four job history records in the job_history table then when an
employee transfers to a fifth job , delete the oldest record.
delete from job_history jh
where
employee_id=
(select
employee_id from employees e where jh.employee_id=e.employee_id and start_date=
(select
min(start_date) from job_history jh
where
jh.employee_id=e.employee_id)
and 5> (select count(*) from job_history jh
where jh.employee_id=e.employee_id group by employee_id having count(*)
>=4));
307. Display
the reporting hierarchy in organization start with employee id 101 .
select employee_id, last_name, job_id, manager_id
from
employees
start
with employee_id=101
connect
by prior employee_id=manager_id;
308. Display
the reporting hierarchy in organization start from top
select
last_name||’ reports to ‘|| prior
last_name “walk top down”
from
employees start with manager_id is null
connect
by prior employee_id=manager_id;
OR
column
org_chart format A12
select
lpad(last_name,length(last_name)+(level*2)-2,’_’) as org_chart
from
employees start with manager_id is null
connect
by prior employee_id=manager_id;
309. Display
the reporting hierarchy in organization start from top and eliminate employee
Higgins and all Childs row
select
department_id,employee_id,last_name,job_id,salary
from
employees
start
with manager_id is null
connect
by prior employee_id=manager_id
and
last_name != ‘Higgins’;
310. Display
the reporting hierarchy in organization start from top and eliminate employee
Higgins row but process the child rows
select
department_id,employee_id,last_name,job_id,salary
from
employees where last_name != ‘Higgins’
start
with manager_id is null
connect
by prior employee_id=manager_id;
311.What is Functional Dependency
Given a relation R, attribute Y of R
is functionally dependent on attribute X of R if and only if each X-value has
associated with it precisely one -Y value in R
312. Definition of relational
DataBase by Dr. Codd (IBM)?
A Relational Database is a database
where all data visible to the user is organized strictly as tables of data
values and where all database operations work on these tables.
313.Is Sysdate a system variable or
a system function?
System Function
314.Are views automatically updated
when base tables are updated ?
Yes
315. If all the values from a cursor
have been fetched and another fetch is issued, the output will be : error, last
record or first record ?
Last Record
316. What’s the command to see the
current user name?
Sql> show user;
317. What’s the command to change the SQL prompt name?
SQL> set sqlprompt “database-1 >
”
database-1 >
database-1 >
318. How do you switch to DOS prompt
from SQL prompt?
SQL> host
319. How do you view version
information in Oracle?
SQL> select banner from $version;
320. What Oracle package allows you
to schedule one-off or recurring jobs in your database?
DBMS_JOB
321. What is B-Tree, Reverse Key, Bit-Mapped Key,
Function Based index and , Index organized Tables(IOTs) ?
Upto 30
column can be placed for multicolumn Index.
a)
B-Tree Index - All
Indexs are B-Tree Indexes be default unless otherwise stipulated whenever the
index is created. Rowid is used to
locate the actual row in the table(Simple Index or Unique index).
b)
Reverse Key Index - It is actually B-Tree Index with reverse key. It reverse the
byte of column indexed. The rowed is not reversed. For example 'Wentz' will be
'ztneW'. It is also a bit map index.
Advantage of
reverse key indexes is that insertion become distributed across all leaf keys
in the index. This work well in the equality comparisons such as where lastname
= 'Wentz'.
DisAdvantage - Rang
scanning does not work very well.
c) Bit Map Index -
1. A
Bit Map Index is an alternative to the B-Tree Index. In this Bit Map is created
for each key value instead of a list of
Rowids as in a B-Tree index.
2.Each
bit in bit map is corresponding top a rowId. When bit is set then it means that
the row with the corresponding RowId contains the key value.
3.A
mapping function convert the bit position to an actual Rowid so the row can be
found whenever performing a query.
Ex -
Product_no
= 'P00001' Product_no = 'PO3453' Product_no= 'P06734'
----------------------------- ------------------------------ ---------
1 0 0
0 1 0
0 0 1
0 0 1
0 1 0
Each
entry or 'bit' in the bitmap index corresponds to a single row of the table.
The value of each bit depends upon the values of the corresponding row in the
table. For instance, the bitmap Product_no = 'P00001' contains 1 as its first bit. This is because
the product no P00001 is in the first row of the table.
Advantages
1. Work
well with the tables containing million of rows.
2. Bit
Map index improve response time dramatically whenever multiple join conditions
reference the column having the Bit Map index created on it. Bit Map index also
works good for join conditions having many (AND, OR clause).
3. Bit
Map works well when the column referenced in the Bit Map index is not updated
or deleted frequently.
4. Bit
Map indexes work well when the columns contained in the Bit Map index has a low
cardinality is usually (a small number of distinct values). Low cardinality is usually whenever a column
has less then 12 distinct values. An example would be a column containing YES
or NO.
DisAdvantages
1. Does
not work well with the tables that have the column in the Bit Map Index updated
frequently.
2. The
bit Map index requires rebuilding frequently.
d) Function based Index -
1. A
function based index precomputes the value of the expression or function and
stores it in the index.
2. The
expression or function can't be aggregate function such as SUM, AVG, COUNT,
MAX, MIN, GROUPING, STDDEV, LAST, FIRST, RANK etc.
3.
Function Based indexes still used the B- tree indexing method.
4. Use
function based index whenever a function is used in the where clause or join
condition of a query. An example would be whenever searching for a persons last
name using UPPER function.
5. If a
person's last name is stored in mixed case and the search condition is provide
in the upper case, a full table scan is performed if a function based index is
not created using the UPPER function.
6. If
function is a PL/SQL function or package and the function or package is disabled Oracle will not use
the Function Based index.
e) Index Organized Table (IOTs):
1.
Index Organized Table (IOTs) stores the indexed columns of the table in the
table itself. An index Organized table (IOTs) will in most instance contain an
index on the primary key of the table that does not change frequently.
2.
Index organized tables store the data in the order of primary key of the table.
3.
Index organized tables can provide very fast access to table data when the
query execute a exact match or range search on the indexed primary key of the
table.
4. The
decreased time is primarly because the Oracle only has to make a singe trip to
the table since the index is part of the table index. No going to index and
then to the table as in other indexes.
5.
Storage requirement for the index are
reduced since the index column is not duplicated in the separate index.
6. The
keyword ORGANIZATION INDEX indicates to Oracle that the table will be on IOT.
7.
Indexed organized tables can be become large since they contain all rows of the
table. The mandatory OVERFLOW clause provides the necessary space to divide a
row into parts.
8. If
you use IOT, make sure your application will not update primary key
frequently.
322. Object Types: Abstract Datatype, Nested
Tables , Varying Array, Large Objects, References, Object Views.
1.
Abstract Datatype:
Abstract
datatype is a datatype that consists of
one or more subtypes. abstract
data types can be nested and can contain references to other abstract data
types.
2.
Nested Tables:
A
nested table is a table within a table. A nested table is a collection of rows,
represented as a column within the main table. For each record within main
table, the nested table may contain multiple rows. In a sence, It's a way of storing a one-to
many relationship within one table.
Consider
a table that contained information about departments, in which each department
have many projects in progress at any one time, In a strictly relational model,
two separate tables would be created.
Nested
tables allow storing the information about projects within DEPARTMENT table.
The project table records can be accessed directly via the DEPARTMENT table,
without the need to perform join.
The
ability to select data without traversing joins makes data access easier. Even
if methods for accessing nested data are not defined. Department and Project
data have clearly been associated.
3.
Varying Array:
A
varying array is a set of objects, each with the same data types. The size of
the array is limited when it is created.(When the table is created with a
varying array, the array is a nested table with a limited set of rows) .
Varying
arrays also known as VARRAYS, allows storing repeated attributes in tables.
For
example, suppose there is a PROJECT table, and projects have workers assigned
to them.
A
project can have many workers, and a worker can work on multiple projects. In a
strictly relational implementation, a PROJECT table, a WORKER table, and an
intersection table PROJECT_WORKER can be created that stores the relationships
between them.
Varying
array can be used to store the worker names in the PROJECT table. If the
projects are limited to ten workers or fewer, varying array with a limit of the
entries can be created. The datatype for the varying array can be created. The
datatype for the varying array will be whatever datatype is appropriate for the
worker name values.
The
varying array can then be populated, so that for each project the names of all
of the project's workers can be selected without querying the WORKER table.
4.
Large Objects:
A large
object, or LOB, is capable of storing large volumes of data. The LOB datatypes
available are BLOB, CLOB, NLOB and BFILE.
a.
CLOB - Single byte character data upto
4 gigabyte.
b.
NCLOB - Single byte or fixed length
multibyte National Character set up 4 gigabyte.
c.
BLOB- Unstructured
binary data up to 4 gigabyte.
d.
BFILE - Binary data stored in an
external file. It exist at operating system level. The data base only maintains
a pointer to the file. The size of the external file is limited only by the OS.
Since the data is stored outside the database, ORACLE does not maintain
concurrency or integrity of the data.
Multiple
LOBs per table can be used. For example, there can be table with a CLOB column
and two BLOB columns.
5.
References : Nested tables and varying
arrays are embedded objects. They are physically embedded within another
object. Another type of object, called referenced objects are physically
separate from the object that refer to them.
References (also know as REFs) are essentially pointers to row objects.
A row object is different from a column object. An example would be a varying
array, it is an object that is treated as a column in a table. A row object, on
the other hand, always represent a row.
6.
Object Views : Object view allow adding OO concepts on top
of existing relational tables. For example, an abstract datatype can be created
based on existing table definition. Thus, object views give the benefits of
relational table storage and OO structures. Object views allow the development
of OO features within a relational database, a kind og bridge between the
relational and OO worlds.
323.
What are the set operator?
Set
operators combine the results of two queries into one result.
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.
1.If
both queries select values of datatype CHAR, the returned values have datatype
CHAR.
2.If
either or both of the queries select values of datatype VARCHAR2, the returned values
have datatype VARCHAR2.
324.
What are Row Operators?
Row
operators return or reference particular rows. ALL retains duplicate rows in
the result of a query or in an aggregate expression. DISTINCT eliminates
duplicate rows from the result of a query or from an aggregate expression.
PRIOR refers to the parent row of the current row returned by a tree-structured
query. You must use this operator in the CONNECT BY clause of such a query to
define the parent-child relationship.
325.
What are comparison operators?
Typically,
you use comparison operators in the WHERE clause of a data manipulation
statement to form predicates, which compare one expression to another and
always yields TRUE, FALSE, or NULL. You can use all the comparison operators
listed below to form predicates. Moreover, you can combine predicates using the
logical operators AND, OR, and NOT.
ALL -Compares a value to each value in a list or
returned by a subquery and yields TRUE if all of the individual comparisons
yield TRUE.
ANY,
SOME -Compares a value to each value in a list or returned by a subquery and
yields TRUE if any of the individual comparisons yields TRUE.
BETWEEN-
Tests whether a value lies in a specified range.
EXISTS -Returns
TRUE if a subquery returns at least one row.
IN -Tests
for set membership.
IS NULL
-Tests for nulls.
LIKE -Tests
whether a character string matches a specified pattern, which can include
326.
What is the use of dynamic SQL statement prepare and execute
Dynamic
SQL lets programs accept or generate SQL statements at run time. Unlike
precompiled SQL or SQL module language, in which SQL statements are known at
compile time, dynamic SQL lets the user formulate the statements at run time.
Preparing
the SQL statement for execution: When a SQL statement enters the Oracle library
cache, the following steps must occur before the statement is ready to execute:
1.
Syntax check - The SQL statement is checked for proper spelling and word order.
2.
Semantic parse - Oracle verifies all of the tables and column names against the
data dictionary.
3.
Stored Outline check - Oracle checks the data dictionary to see if a stored
outline exists for the SQL statement.
4.
Generate execution plan - Oracle uses cost-based optimizer algorithms and
statistics in the data dictionary to determine the optimal execution plan.
5.
Create binary code-Oracle generates a binary executable based on the execution
plan.
Once a
SQL statement is prepared for execution, subsequent executions will happen very
fast, because Oracle recognizes identical SQL statements and re-uses
executables for those statements.
However,
for systems that generate ad hoc SQL or SQL with embedded literal values, SQL
execution plan generation time is significant, and previous execution plans
often can't be used. For those queries that join many tables, Oracle can spend
a significant amount of time determining the proper order to join the tables
together.
Evaluating table join order
The
most expensive step in the SQL preparation process is the generation of the execution
plan, particularly when dealing with a query with multiple joins. When Oracle
evaluates table join orders, it must consider every possible combination of
tables. For example, a six-way table join has 720 (permutations of 6, or 6 * 5
* 4 * 3 * 2 * 1 = 720) possible ways that the tables can be joined together.
This permutation issue becomes even more pronounced when you have more than 10
joined tables in a query: For a 15-way table join, there are over one trillion
(1,307,674,368,000 to be exact) possible query permutations that must be
evaluated.
327.
What are the Different Optimisation Techniques
The
Various Optimisation 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
328.
Where does oracle store information
about query?
Oracle
actually 'stores' two relevant things in buffer.
Firstly,
it stores the actual code you're executing along with the execution plan. It
stores this in the Library Cache which is a component of the Shared Pool which
is a component of the System Global Area (SGA). When Oracle parses the
statement and determines the execution plan (which takes time and in the case
of complex queries can take a substantial amount of time), this is stored away
so that when the same code is subsequently executed, Oracle doesn't have to
redo all the work of parsing and redetermining the execution plan. It's already
there (note all users have access to this area of memory).
Therefore,
the first time you execute the query it probably needs to be (hard) parsed and
so initially needs to go through this overhead. Subsequent times you execute
the query, Oracle probably finds the SQL in the Library cache and is more
efficient as a result as the hard parse is not performed.
To 'get
rid' of this, you can issue the ALTER SYSTEM FLUSH SHARED_POOL;
Secondly,
Oracle stores the 'blocks' that contain the rows of interest in a Buffer Cache
that is also a component of the SGA. The reason it does this is twofold.
Firstly, Oracle can only manipulate and extract information out of the blocks
once they're loaded into memory. However they are stored in a shared memory
structure because having gone to all the trouble of retrieving the data from
disk, there might be a possibility that the blocks might need to be accessed
again. Note this possibility is greater for the session that has read in the
block(s) but depending on the data (eg. block on a freelist) and the data
structure (eg. popular index) it is quite likely that another session may want
access to the same block. The Buffer Cache is organized in a number of linked
lists (eg. Least Recently Used LRU list) to encourage popular blocks to remain
cached in memory.
Therefore,
the first time you run your query, the blocks need to be read from disk and
loaded into the Buffer Cache. However when you subsequently re-execute the
query, the blocks are probably still cached in memory, so you avoid the
(expensive) disk reads and the query runs faster as a result.
There
is no flush Buffer Cache command (as it never makes sense to do tis).
329.
What is TTITLE, BTITLE, REPHEADER, PERFOOTER ?
you can
set a title to display at the top of each page of a report. You can also set a
title to display at the bottom of each page. The TTITLE command defines the top
title; the BTITLE command defines the bottom title.
You can
also set a header and footer for each report. The REPHEADER command defines the
report header; the REPFOOTER command defines the report footer.
330.
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 .
If you
want to compare, for instance, tbl.Payment with 200,use this code:
Select
decode(sign(tbl.Payment -200),0,'two hundred',1,'Greater than 200','less than
200');
This is
a nice shortcut rather than writing the procedure.
The
value of sign(tbl.Payment -200) is 0 if
tbl.Payment = 200.
The
value of sign(tbl.Payment -200) is 1 if
tbl.Payment > 200.
otherwise,
or
The
value of sign(tbl.Payment -200) is -1 if
tbl.Payment < 200
331.
What is SERVEROUTPUT setting? Can I
print inside a PL/SQL program?
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.
332.
What built-in functions/operators are
available for manipulating strings?
The
most useful ones are LENGTH, SUBSTR, INSTR, and ||:
1.
LENGTH(str) returns the length of str in characters.
2.
SUBSTR(str, m, n) returns a portion of str, beginning at character m, n
characters long. If n is omitted, all characters to the end of str will be
returned.
3.
INSTR(str1,str2,n,m) searches str1 beginning with its n-th character for the
m-th occurrence of str2 and returns the position of the character in str1 that
is the first character of this occurrence.
4. str1
|| str2 returns the concatenation of str1 and str2
333.
Is it possible to write a PL/SQL procedure that takes a table name as input and
does something with that table?
For
pure PL/SQL, the answer is no, because Oracle has to know the schema of the
table in order to compile the PL/SQL procedure. However, Oracle provides a
package called DBMS_SQL, which allows PL/SQL to execute SQL DML as well as DDL
dynamically at run time.
For
example, when called, the following stored procedure drops a specified database
table:
CREATE
PROCEDURE drop_table (table_name IN VARCHAR2) AS
cid INTEGER;
BEGIN
-- open new cursor and return cursor ID
cid := DBMS_SQL.OPEN_CURSOR;
-- parse and immediately execute dynamic
SQL statement
-- built by concatenating
table name to DROP TABLE command
DBMS_SQL.PARSE(cid,
'DROP TABLE ' || table_name, dbms_sql.v7);
-- close cursor
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
-- if an exception is
raised, close cursor before exiting
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
-- reraise the exception
RAISE;
END drop_table;
.
RUN;
334.
What is the correct syntax for ordering query results by row-type objects?
As a
concrete example, suppose we have defined an object type Person Type with an
ORDER MEMBER FUNCTION, and we have created a table Person of PersonType
objects. Suppose you want to list all PersonType objects in Person in order.
You'd probably expect the following to work:
SELECT * FROM Person p ORDER BY p;
But it
doesn't. Somehow, Oracle cannot figure out that you are ordering PersonType
objects. Here is a hack that works:
SELECT * FROM Person p ORDER BY
DEREF(REF(p));
335.
How you specify column separators?
The
solution is to concatenate the rows together with whatever separator you choose
to display.
For
example: SELECT col_a || '|' ||
col_b FROM table;
This
will show the result like:
first|100
second|200
thrid|300
336.
Can U disable database trigger? How?
Yes.
With respect to table
ALTER TABLE TABLE [DISABLE all_trigger]
337.
Rownum with ORDER BY clause?
One of
the most often uses of the pseudo column rownum is to provide serial numbers to
the records in a query. This feature is widely used in reports to represent
systematic display of information.
For
instance Listing A
Select
rownum, ename, empno from emp10;
Table A
ROWNUM
ENAME EMPNO
---------
---------- ---------
1 KING 7839
2 BLAKE
7698
3 CLARK
7782
4 JONES 7566
5 MARTIN
7654
However,
when we order this statement the rownum gets disturbed as shown below ( Listing
B and Table B).
Listing
B
select
rownum, ename, empno from emp10 order by ename;
Table B
ROWNUM
ENAME EMPNO
---------
---------- ---------
2 BLAKE 7698
3 CLARK 7782
4 JONES 7566
1 KING 7839
5 MARTIN 7654
As we
can see from above the employee names did get ordered but the rownum also got
the wrong order. The desired result was
BLAKE having rownum 1 , CLARK having a rownum of 2 and so on. To achieve this we have to outer
join this table with dual that process forces a implicit order on the rownum as
shown below ( Listing C, Table C).
Listing
C
select
rownum, ename, empno from emp10 a , dual d
where a.ename = d.dummy (+) order
by ename;
Table C
ROWNUM
ENAME EMPNO
------- ---------- ---------
1 BLAKE 7698
2 CLARK 7782
3 JONES 7566
4 KING 7839
5 MARTIN 7654
The
trick is to do an outer join with the column that you want to order and this
process does not disturb the rownum order.
In addition to that if the column is of number datatype then one should make sure to use TO_CHAR
datatype conversion function.
338.
How many columns can table have?
The
number of columns in a table can range from 1 to 254 (32 bit).
339.
What is the max. length for variable name and value to be stored in it
A
variable name must begin with character and can be followed by a maximum
of 29 character.
340.
What are the uid and user function?
UID
returns an integer that uniquely identifies the session user (the user who
logged on).
SELECT
UID FROM DUAL;
UID
----------
19
USER
returns the name of the session user (the user who logged on) with the datatype
VARCHAR2. Oracle compares values of this function with blank-padded comparison
semantics.
In a
distributed SQL statement, the UID and USER functions identify the user on your
local database. You cannot use these functions in the condition of a CHECK
constraint.
SELECT
USER, UID FROM DUAL;
USER UID
------------------------------
----------
SCOTT 19
341.
What does the set escape command does? or How does one disable interactive
prompting in SQL*Plus?
ESC[APE]
{\|c|OFF|ON}
Defines
the character you enter as the escape character. OFF undefines the escape character.
ON enables the escape character. ON changes the value of c back to the default
"\".
You can
use the escape character before the substitution character (set through SET
DEFINE) to indicate that SQL*Plus should treat the substitution character as an
ordinary character rather than as a request for variable substitution
If your
data contains special punctuation characters, you may encounter difficulties in
SQLPLUS of ORACLE. There are the solutions:
To
obtain a single quote character ('), use two single quotes, i.e., '', to get
one single quote character. For example, you would enter 'Smith''s Home' in SQL
for the string "Smith's Home".
The
'&' character is used by SQLPLUS as substitution character for data input.
To use an '&' character as part of your string value, you need to turn on
the escape character first use the following command:
SQL>set
escape \
If you
want enter a string containing '&, you would use '\' to escape the '&'
character. For example, to enter the string, "Johnson & Son Co.",
as 'Johnson \& Son Co.'
342. How to insert an ampersand into an
table?
create
table x (a varchar2(10));
Table
created.
SQL>
insert into x values ('a'||chr(38)||'b');
1 row
created.
SQL>
set escape on
SQL>
insert into x values ('x\&y');
1 row
created.
SQL>
set escape off
SQL>
set define ^
SQL>
insert into x values('m&n');
1 row
created.
SQL>
select * from x;
a&b
x&y
m&n
No comments:
Post a Comment