|
| Author |
Message |
    
Megamama
Vooriki Bewarse
Username: Megamama
Post Number: 3530
Registered: 07-2004
Posted From: 84.230.141.94
Rating: N/A
Votes: 0 (Vote!) | | Posted on Thursday, January 06, 2005 - 11:46 am: |    |
nee enkamma...intha maata antaava.. |
Hemanth
Desanike Pedda Bewarse
Username: Hemanth
Post Number: 3661
Registered: 03-2004
Posted From: 4.29.247.8
Rating: N/A
Votes: 0 (Vote!) | | Posted on Thursday, January 06, 2005 - 11:38 am: | |
naa gola ni dengaleka naa mohana ivi padethee saduvukunta anegaa ivi padesav, sare potha leee. 
kikiki, Just kidding. thanks mama. Bye. |
Hemanth
Desanike Pedda Bewarse
Username: Hemanth
Post Number: 3658
Registered: 03-2004
Posted From: 4.29.247.8
Rating: N/A
Votes: 0 (Vote!) | | Posted on Thursday, January 06, 2005 - 11:27 am: | |
Copy paste sesesukunna mama.  |
Megamama
Vooriki Bewarse
Username: Megamama
Post Number: 3526
Registered: 07-2004
Posted From: 84.230.141.94
Rating: N/A
Votes: 0 (Vote!) | | Posted on Thursday, January 06, 2005 - 11:25 am: | |
Can I Update From Another Table ?
Yes. For example, if we had a table DEPT_SUMMARY, we could update the number of employees field as follows:
update DEPT_SUMMARY s
set NUM_EMPS = (
select count(1)
from EMP E
where E.DEPTNO = S.DEPTNO
);
Can I remove duplicate rows ?
Yes, using the ROWID field. The ROWID is guaranteed unique. There are many variations on this theme, but the logic is to delete all but one record for each key value.
delete from EMP E
where not E.ROWID = (
select min(F.ROWID)
from EMP F
where F.EMP_ID = E.EMP_ID
);
Can I implement Tree Structured Queries ?
Yes! This is commonly asked by those migrating from non-RDBMS apps. This is definitely non-relational (enough to kill Codd and then make him roll in his grave) and is a feature I have not seen in the competition.
The definitive example is in the example SCOTT /TIGER database, when looking at the EMP table (EMPNO and MGR columns). The MGR column contains the employee number of the "current" employee's boss.
You have available an extra pseudo-column, LEVEL, that says how deep in the tree you are. Oracle can handle queries with a depth up to 255.
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
You can get an "indented" report by using the level number to substring or lpad a series of spaces and concatenate that to the string.
select lpad(' ', LEVEL * 2) || ENAME ........
You use the start with clause to specify the start of the tree(s). More than one record can match the starting condition.
One disadvantage of a "connect by prior" is that you cannot perform a join to other tables. Still, I have not managed to see anything else like the "connect by prior" in the other vendor offerings and I like trees. Even trying to doing this programmatically in embedded SQL is difficult as you have to do the top level query, for each of them open a cursor to look for lower level rows, for each of these.......
soon you blow the cursor limit for your installation.
The way 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.
Note that you can't trick Oracle by using CONNECT BY PRIOR on a view that does the join.
How can I get information on the row based on group information ?
Imagine we have the EMP table and want details on the employee who has the highest salary. You need to use a subquery.
select e.ENAME, e.EMPNO, e.SAL
from EMP e
where e.SAL in (
select max (e2.SAL)
from EMP e2
);
You could get similar info on employees with the highest salary in their departments as follows
select e.ENAME, e.DEPTNO, e.SAL
from EMP e
where e.SAL = (
select max (e2.SAL)
from EMP e2
where e2.DEPTNO = e.DEPTNO
);
How can I get a name for a temporary table that will not clash ?
Use a sequence, and use the number to help you build the temporary table name. Note that SQL-92 is developing specific constructs for using temporary tables.
How can I discover what tables, columns, etc are there ?
Oracle maintains a live set of views that you can query to tell you what you have available. In V6, the first two to look at are DICT and DICT_COLUMNS which act as a directory of the other dictionary views. It is a good idea to be familiar with these.
Not all of these views are accessible by all users. If you are a DBA you should also create private DBA synonyms by running $ORACLE_HOME/rdbms/admin/dba_syn.sql in your account.
How can I rename a column ?
There is no way a column can be renamed using normal SQL. It can be done carefully by the DBA playing around with internal SYS dictionary tables and bouncing the database, but this is not supported. (I have successfully done it in V4 thru V7). Do backup the database first unless you feel brave. I've written a quick and dirty script rncol.sql to do this. If you can't figure out how to use it from the source you definitely should not run it.
You can use a similar dirty trick for changing ownership of tables if storage space is limited.
Is there a formatter for SQL or PL/SQL ?
There are a number of "beautifiers" for various program languages. The cb and indent programs for the C language spring to mind (although they have slightly different conventions). As far as I know there is no PD formatter for SQL available.
Given that there are PD general SQL parsers and that the SQL standards are drafted in something close to BNF, maybe someone could base a reformatter based on the grammar.
Note that you CANNOT use cb and indent with Pro*C as both these programs will screw up the embedded SQL code.
I have recently heard that Kumaran Systems have a Forms PL/SQL and SQL formatter, but I do not now if they have unbundled it.
How come records for the date I want are missing ?
You are trying to retrieve data based on something like:
SELECT fld1, fld2 FROM tbl WHERE date_field = '18-jun-60'
You *know* there are records for that day - but none of them are coming back to you.
What has happened is that your records are not set to midnight (which is the default value if time of day not specified).
You can either use to_char and to_date functions, which can be a bad move regarding SQL performance, or you can say
WHERE date_field >= '18-jun-60' AND date_field < '19-jun-60'
An alternative could be something like
WHERE date_field between '18-jun-1960'
AND to_date('23:59:59 18-jun-60', 'HH24:......YY')
;
How can I interpret a two digit year after 2000 ?
When converting to dates from characters when you only have two characters for the year, the picture format "RR" will be interpreted as the year based on a guess that that date is between 1950 and 2049.
What are these V$ tables?
There are a number of tables/views beginning with V$ that hold gory details for performance monitoring. These are not guaranteed to be stable from minor release to minor release and are for DBAs only.
There are usually no real underlying tables (unlike SYS.OBJ$) and are dummied up by the RDBMS kernel software in much the same way that UNIX System V.4 dummies up the files in the /proc or /dev/proc directories.
If you have any code depending on these (and the widely used tools supplied by Oracle but unsupported are in this category) then you need to verify that everything works each time you upgrade your database. And when a major revision changes, all bets are off.
How do I get a top ten ?
This question often gets the response WHERE ROWNUM <= 10 but this will not work (except accidentally) because the ROWNUM pseudocolumn is generated before the ORDER or WHERE clauses come into effect.
One elegant SQL-only approach (although it will be a bitch on a large table) was suggested by Stowe@aol.com
select a.ordered_column, a.other_stuff
from table_name a
where 10 > (
select count(1)
from table_name b
where b.ordered_column
< a.ordered_column )
order by a.ordered_columnl;
I do not believe that straight SQL is the way to go for such problems when you have PL/SQL available.
My approach is to use PL/SQL instead (in SQL*Plus):
variable tenthsal number
declare
n number;
cursor c1 is
select SAL from EMP order BY SAL desc;
begin
open c1;
for n in 1..10 loop
fetch c1 into :tenthsal;
end loop;
close c1;
end:
/
select * from EMP
where SAL <= :tenthsal
order by SAL desc;
Late news: index descending hint to SQL works if you use a dummy restriction to force use of the index. Needs V7, etc.
How do control which rollback segment I use ?
In SQL, you may need to control the rollback segment used as the default rollback segment may be too small for the required transaction, or you may want to ensure that your transaction runs in a special rollback segment, unaffected by others. The statement is as follows:
SET TRANSACTION USE ROLLBACK SEGMENT segment_name;
On a related note, if all you are doing are SELECTS, it is worth telling the database of this using the following:
SET TRANSACTION READ ONLY;
Both these statements must be the first statement of the transaction.
How do I order a union ?
(Governments around the world have been trying to figure this one out).
Use the column number.
Say we are getting a list of names and codes and want it ordered by the name, using both EMP and DEPT tables:
select DEPTNO, DNAME from DEPT
union
select EMPNO, ENAME from EMP
order by 2;
Who are SCOTT, SYSTEM and SYS ?
These three users are common in many databases. See the glossary entries under SCOTT, SCOTT and SYS. Another common user/password is PLSQL/SUPERSECRET used for PL/SQL demo stuff.
How can I avoid blowing rollback segments ?
The simple answer is make sure you have them big enough and keep your transactions small, but that is being a smartarse.
More recent versions of Oracle have an option for the session that you can set that commits every so many DML statements. This is OK except for where you are doing your work in a single statement rather than using PL/SQL and a loop construct.
Imagine you have a HUGE table and need to update it, possibly updating the key. You cannot update it in one go because your rollback segments are too small. You cannot open a cursor and commit every n records, because usually the cursor will close. You cannot have a number of updates of a few records each because the keys may change - causing you to visit records more than once.
The solution I have used was to have one process select ROWID from the appropriate rows and pump these (via standard I/O) to another process that looped around reading ROWIDs from standard input, updating the appropriate record and committing every 10 records or so. This was very easy to program and also was quite fast in execution. The number of locks and size of rollback segments required was minimal.
If you are writing in Pro*C and use MODE=ORACLE, there are ways around it too, but not if you are using MODE=ANSI.
How can I restore passwords ?
OK, so this is really a DBA question, but it is worth putting in here because it involves SQL regardless of interface.
First, look at the PASSWORD column in DBA_USERS. It looks like gobbledygook because it is an encrypted password. However you can use this if you have saved it somewhere else. Say you want to impersonate a user in a batch run overnight. First stash the gobbledygook password away somewhere, grant connect to the user identified by some password you know and then run your batches using the new known password.
To restore the password to what it was use the following syntax (which I think is undocumented).
grant connect to SCOTT identified by passwords GOBBLEDYGOOK;
Note especially the S on the end of PASSWORDS.
Not Formatted Yet
Z.23. Who do various access methods compare ?
How you organize your SQL and indices controls what access
methods will be used. The following ranking is valid for
V6. I do not know about V7.
QUERY PATH RANKING (lowest rank is the best)
Rank Path
1 ROWID = constant
2 Unique index column(s) = constant(s)
3 Entire unique contatenated index = constant
4 Entire cluster key = corresponding key in another
table in same cluster
5 Entire cluster key = constant
6 Entire non-unique contatenated index = constant
7 Non-unique single column index merge
8 Most leading concatenated index = constant
9 Index column BETWEEN low AND hi or LIKE 'C%'
10 Sort/merge (joins only)
11 MAX/MIN of single indexed column
12 ORDER BY entire index
13 Full table scans
14 Unindexed column = constant or column IS NULL
or column LIKE '%C%'
A fuller discussion of this is in the document by Tina London,
discussed elsewhere.
|
Megamama
Vooriki Bewarse
Username: Megamama
Post Number: 3524
Registered: 07-2004
Posted From: 84.230.141.94
Rating: N/A
Votes: 0 (Vote!) | | Posted on Thursday, January 06, 2005 - 11:24 am: | |
Mods a theddu archive sesi dobbaru
ikkade estunna
******************************************
FAQ ON ORACLE PL/SQL
Basics of PL/SQL
1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
2. What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.
4. What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part.
Datatypes PL/SQL
5. What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
Cursors
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
12. What are the cursors attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows featched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are featched.
% NOT FOUND - to check whether cursor has featched any row. True if no rows are featched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.
Database Triggers
16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the retruned boolean value.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.
21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integarity Constraints.
I & ii.
Exception :
22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.
25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.
26. Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
27. What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
28. What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is alway returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
29. What are advantages fo Stored Procedures /
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
30. What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
31. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
32. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
33. Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
34. Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
35. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
36. What is a package ? What are the advantages of packages ?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, Information. Hiding,. reusability and Better Performance.
37.What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
38. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
39. How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
40. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.
|
|
|
|
