DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);
END;
In a Distributed Database System Can we execute two queries simultaneously ? Justify ? As Distributed database system based on 2 phase commit, one query is independent of 2 nd query so of course we can run.
If the two queries are firing on the table it is not possible. If the queries are firing on different different tables then it is possible.
1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?
STARTING ORACLE ERROR NO IS ORA-00000: normal, successful completion Forward Declarations:
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.
You cannot make forward declarations of a variable or cursor. This technique works only with modules (procedures and functions).
Example of forward Declaration:
CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE calc_rating(. . .); -- forward declaration PROCEDURE award_bonus(. . .)
IS -- subprograms defined
BEGIN -- in alphabetical order calc_rating(. . .);
. . . END;
PROCEDURE calc_rating(. . .) IS
BEGIN . . . END;
END forward_pack;
Explian rowid,rownum?what are the psoducolumns we have?
Rowid is a unique identifier, hexadecimal string representing the unique address of a row in its table. When a row is inserted into the table it generates a row id and will be removed when the row is deleted.
Rownum: For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.
You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
- CURRVAL and NEXTVAL - LEVEL
- ROWID
- ROWNUM
What is difference b/w stored procedures and application procedures,stored function and application
1) Stored subprograms: These are coded and compiled from within the Oracle database via SQL Plus etc.
2) Application subprograms: These are coded and compiled from within the Oracle development tools such as Oracle Developer.
State the difference between implict and explict cursor's
IMPLICIT CURSORS are used primarily when returning a single row of data.
With Implicit cursors, the declaration, open, fetch and close functions are done automatically.
SELECT column_name
INTO :block.field_name FROM table_name;
EXPLICIT CURSORS are used, when you are getting more than a single row of data that needs to be processed further. Explicit cursor requires the cursor to be declared, opened, fetched and then closed.
State the advantage and disadvantage of Cursor's
The main disadvantage of cursors is that it process records sequentially. It increases number of context switches between sql and pl/sql. It hampers the performance. We may overcome this problem by using collections.
How to trace PL/SQL Package? How to trace PL/SQL procedures? How to trace SQL statement? What is DBMS_TRACE?
When you want to see the execute plan of a SQL statement, you need to use TRACE function of Oracle.
The dbms_trace package provides an API to allow the actions of PL/SQL programs to be traced, where the scope and volume of the tracing is user configurable. This package is loaded by default, but the schema tables to hold the collected data is created by running the
$ORACLE_HOME/rdbms/admin/tracetab.sql as the SYS user, shown in the trace_setup.sql script.
How to trace the errors in pl/sql block code..
USER_ERRORS or
SHOW ERRORS
Else use DBMS_OUTPUT.PUT_LINE
What can be the Maximum size of a plsql block?
There is no limit on the number of bytes. however, there is a limit on the size of the parse tree generated for your pl/sql code.
How to display the contents of a current record fetched in a ref cursor SQL> create or replace function get_emps(dno in number) return sys_refcursor 2 is
3 return_value sys_refcursor;
4 begin
5 open return_value for
6 select * from emp where deptno = dno;
7 return return_value;
8 end;
9 /
Function created.
SQL> var rc refcursor
SQL> exec :rc := get_emps(30)
PL/SQL procedure successfully completed.
SQL> print rc
1 What are the advantages and disadvantages of using PL/SQL or JAVA as the primary programming tool for database automation.#2 Will JAVA replace PL/SQL?
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)
How to handle exception in Bulk collector?
During bulk collect you can save the exception and then you can process the exception.
Look at the below given example:
DECLARE TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST
SAVE EXCEPTIONS
DELETE * FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION WHEN OTHERS THEN -- this is not in the doco, thanks to JL for pointing this out
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP -- Iteration is
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; -- Error code is
SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
END LOOP;
END;
How to view the contents of tables created by the following procedure after the Loop?
CREATE OR REPLACE PROCEDURE A0_BULK_COLLECT_TEST IS
TYPE EMPLOYEE_MRNO IS TABLE OF A_REGISTRATION_HEADER.ARH_MR_NUM%TYPE;
TYPE EMPLOYEE_NAME IS TABLE OF VARCHAR2(255);
MRNUMBERS EMPLOYEE_MRNO;
NAMES EMPLOYEE_NAME;
CURSOR crBulkCollect IS
SELECT ARH_MR_NUM, ARH_FIRST_NAME||' '||ARH_MIDDLE_NAME||' '||ARH_LAST_NAME FROM A_REGISTRATION_HEADER
WHERE ARH_CTGRY_CD='EMP';
BEGIN
OPEN crBulkCollect;
FETCH crBulkCollect BULK COLLECT INTO MRNUMBERS, NAMES;
CLOSE crBulkCollect;
END;
FOR ALL j IN 1..MRNUMBERS.COUNT dbms_output.put_line(MRNUMBERS(j));
dbms_output.put_line(NAMES(j));
What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.
All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "High Water Mark" or HWM, which means table, cannot contain data beyond the HWM.
When TRUNCATE is used on the table, all the rows of that table is deleted and the HWM is decreased to the minimum size. But when DELETE is issued on the table, the rows are deleted
and nothing is done on the HWM. Thats is, the HWM is never decreased even after the no of rows for the table is less or none. That is this keeps some space always with its own.
Other reated issue on this is.... When your query perform full table scan on the table on which the DELETE is done, the query will scan the disk space till the HWM (even if there is no row ) which will result in bad response time.... But if TRUNCATE is used, the HWM will be reset and the query will take less time...
What is pragma? can any one give me the example of autonomous Transaction ?can we change the order of procedure parameter while calling procedure?
Pragama is compile directive .
Example of Autonomous transactions
Suppose you are updating value from table and you don't have update trigger on that table but still you want to maintain a log entry for this update in seprate table.
You can write a procedure and call that procedure to do this . But you can not use COMMIT in this called procedure because it will save the entire transaction.
To avoid this you can declare this procedure as autonomous transaction procedure so that the execution of this procedure will be treated as totally diff. transaction and you can issue commit in called procedure without affecting the main transaction.
Can we use commit in trigger and cursors?
Yes we write commit on trriger but only in autonomous transaction only Yes We can use commit statement in triggers with 10G.
Here is example :
CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO i
FROM t1;
INSERT INTO t2 VALUES
(i);
COMMIT;
END;
/
Difference between truncate and delete DELETE
1. Delete creates a rollback segment and can be rollback.
2. It is DML statement.
3. Delete command deletes records one by one 4. Delete allows conditional remove of records 5. Trigger fires on delete statement
6. Does not reset the High Watermark of the table.
TRUNCATE
1. Truncate is a DDL statement.
2. Cannot be rollback.
3. Truncate removes all the records at a time.
4. Truncate does not allow conditional remove of records 5. Trigger do not fires on truncate statement.
6. It also resets the HIGH WATERMARK which will improve subsequent query performance.
What is Mutation of a trigger? why and when does it oocur?
When a trigger tries to access its own table, from which its fired, then the trigger is said to be mutating and table is mutating table.
Why do we need to create a force view?what is its purpose?give some examples?
Force View does forces the creation of a View even when the View will be invalid. NoForce Is the default.
When a view is created for base table which does not exist then in that situation we use FORCE.
CREATE FORCE VIEW view_force AS
SELECT * FROM xyz; Where xyz does not exist.
The force view scripts are reusable in data warehousing environments where migration of data from one db to other wills occurs, so they use the same script in diff databases even if the base table may be created afterwards.
What is the data type of Null?
Oracle treats NULL as character value of length of 0.
So, the default data type of NULL is a character data type and to prove it, we create a view on a null column with an alias a, and then describe it to see the datatype and length of the string.
Here is the code...
create view myview
as
select null a from dual;
describe myview;
The describe command shows that the column a has a data type of a varchar2(0).
So the Answer is : Character Type
What is the purpose of FORCE while creating a VIEW
The purpose of FORCE keyword is to create a view if the underlying base table doesnot exists.
ex : create or replace FORCE view <viewname> as <query>
That View will be created with the message View created with compilation errors
Once you create the table that invalid view will become as valid one.
How can I speed up the execution of query when number of rows in the tables increased . Indexed the columns (Primary key)
2. Use the indexed / Primary key columns in the where clause
3. check the explain paln for the query and avoid for the nested loops / full table scan (depending on the size of data retrieved and / or master table with few rows)
4. YOU CAN SPEED UP THE QUERY BY USING THE ROWID IN THE SELECT STATEMENT.
What is autonomous Transaction? Where are they used?
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done. Autonomous_transcation is used to create a new session. It is more useful when you want to have log
like:
You want when ever update statement is fired on emp table. You want a entry should be made in
logfile that emptable is attempted to update no matter updation was successful or not
CREATE TABLE EMPLOG(TERMINAL_NAME VARCHAR2(50), UPDATE_DATE DATE);
CREATE OR REPLACE TRIGGER UPDATE_EMP_TRIG BEFORE UPDATE ON EMP
DECLARE
RMSG VARCHAR2(50) ;
PRAGAMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT ' EMP TABLE IS UPDATED FROM TERMINAL : '||USERENV('TERMINAL') INTO RMSG FROM DUAL;
INSERT INTO EMPLOG VALUES(RMSG, SYSDATE);
COMMIT;
END;
/
now if user issues
UPDATE EMP SET SAL = SAL + 400 ; ROLLBACK;
This query will not effect emp table but "emplog" will have a entry
What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.
IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the sub-program then it will give a error.
By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled
exception, the original parameters are unchanged.
IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the sub-program then it will give a error.
OUT: the out parameter will always have a NULL value during the begining of the sub-program, we can modify its value in the sub-program and then this modified value can be sent to the calling program. Suppose from calling evironment u are sending a variable which has some value to the program, but if that corresponding variable is mrked as OUT in the sub-program, then in the sub-program that value will be replaced with NULL, and then the operation in the sub-program will be done.
NOTE: this behaviour can be changed using the NOCOPY option, but remmeber the restriction imposed on NOPCOY.
IN-OUT: well this is the variable, where u can read the variable and write the variable.
And one more thing to note is that, for OUT and IN-OUT the variables will be passed by value.
if NOCOPY option is not used,
but the restriction with the NOCOPY is that its a hint to the compiler, and not necessary that the compiler will be using it, so be careful while usin the NOCOPY option.
I want to insert the following information in userAction table:Which user execute which query on which date? the user Action table contains the following attributes: USER DATE QUERY please write to me how to resolve this problem?
Step 1: Store the query in a variable before executing it.
Step 2: Simply insert the above query variable, sysdate and user into the User Action table.
Note : User name (person logged in into the application) can be obtained from the session.
1) Why it is recommended to use INOUT instead of OUT parameter type in a procedure?2) What happen if
OUT parameter will be useful for returning the value from subprogram, value can be assigned only once and this variable cannot be assigned to another variable.IN OUT parameter will be used to pass the value to subprogram and as well as it can be used to return the value to caller of subprogram. It acts as explicitly declared variable. Therefore it can be assigned value and its value can be assigned to another variable. So IN OUT will be useful than OUT parameter.
If nothing is assigned to OUT parameter NULL is returned.
How can one view all the procedures, functions, triggers and packages created by the user select object_name, object_type from user_objects
where object_type in ('PACKAGE','PROCEDURE','FUNCTION','TRIGGER')
What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
1. System - level or transaction level
Rollback the current transaction entirely on errors. This was the unique behavior of old drivers becauase PG has no savepoint functionality until 8.0.
2. Statement
Rollback the current (ODBC) statement on errors (in case of 8.0 or later version servers). The driver calls a SAVEPOINT command just before starting each (ODBC) statement and
automatically ROLLBACK to the savepoint on errors or RELEASE it on success. If you expect Oracle-like automatic per statement rollback, please use this level.
3. User Level
You can(have to) call some SAVEPOINT commands and rollback to a savepoint on errors by yourself. Please note you have to rollback the current transcation or ROLLBACK to a savepoint on errors (by yourself) to continue the application.
What is the DATATYPE of PRIMARY KEY?is it Binary integer..i'm not sure..1.Varchar2 2.Char 3.Binary integer 4.Number
Binary Integer
What is a purity level? How it is should be taken into consideration when your writing any database objects i.e., trigger,function, procedure etc.,
Purity level defines what type of datastructure function reads and modify.
Threre are 4 types of purity level 1)WNDS(Write No Database State) 2) RNDS(Read No Database State) 3)WNPS(Write No Package State) 2) RNPS(Read No Package State)
For package function , PRAGMA RESTRIC_REFRENCES is required.
Ex. PRAGMA RESTRICT_REFRENCES(functionname,WNPS,RNPS) What is PL/Sql tables?Is cursor variable store in PL/SQL table?
pl/sql table is temparary table which is used to store records temrparaily in PL/SQL Block, whenever block completes execution, table is also finished.
Cursor variable doesn't store with pl/sql Table . IT is a pointer to cursor & it is not item, It is dynamic.
What is difference between varray and nested table.can u explain in brief and clear my these concepts.also
1. Nested tables are unbounded where as varray has maximun limit.
2. Nested tables are dense intially but can be sparse latter. So you can delete individual elements from nested tables. Varrays are always dense .It is not possible to delete individual elements in varrays.
3. Varrays stores data in-line. Nested tables are stored separately, main table maintains a pointer to nested table.
4. When stored in database varrays retain their order where as nested tables do not.
What steps should a programmer should follow for better tuning of the PL/SQL blocks?
Difference between procedure and function? What is the use of ref cursor return type?
Tuning can be taken care by using the Correct Index on the table.We should not use Not equal to, Distinct on the Indexed columns.
SQL Queries – Best Practices
1. Always use the where clause in your select statement to narrow the number of rows returned.
If we don’t use a where clause, the Oracle performs a full table scan on our table and
If we don’t use a where clause, the Oracle performs a full table scan on our table and