-
Oracle Joins
There are 2 tables, A and B having 3 rows each. What will be result on executing the following query? : SQL> select * from A,B
-
What are the advantages and disadvantages of DBMS-SQL
neerajalatha
- Jul 7th, 2010
Disadvantages:It is slow when compared to native dynamic SQL and coding is also difficult when compared to native dynamic SQL.
stshekhar
- Jun 22nd, 2008
Disadvantage : 1) Executing DDL statements through dbms_sql sometime can result in deadlock. example : we are trying to drop one object at the time it is being accessed by other user.
-
Without closing the cursor, If you want to open it what will happen. If error, get what is the error?
ratna82
- Oct 10th, 2008
CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.
-
Pragma Init Exception
What is Pragma Init Exception ? what is difference between user defined exception and init pragma exception. what is the use of it.
g_sidhu
- Feb 19th, 2008
You trap a nonpredefined Oracle server error by declaring it first, or by using the OTHERS handler. The declared exception is raised implicitly. In PL/SQL, the PRAGMA EXCEPTION_INIT tells the compiler...
Sachin Kale
- Nov 21st, 2007
Pragma is a compiler Directive that is process at compile time, Not at run time.In pl/sql the Pragma Exception_init tells the compiler to asociate an exception name with an oracle error number.That le...
-
-
We have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;
Radha
- Jul 14th, 2011
Triggers fire when any condition on such trigger is satisfied...but we don't know when that trigger is going to be fired and if it is fired where can we see output of DBMS_output.put_line.
If we want to see output of trigger when it is fired we should mention Raise_application_error in trigger.pawan_kr2007
- Oct 11th, 2009
If you 'set serveroutput on' and execute the statement which fires the trigger the message will display on prompt otherwise it will not.
-
Hi all,Could anyone let me know the equivalent code in pl/sql for this java code? I really appreciate that.for (int i = 0; i < 10; i++) {if (i == 5){continue;}} thanks,kamalnayan.M
ravikumarzee@gmail.com
- Mar 8th, 2007
begin for i in 1..10 loopif i 5 then -- do stuff.end if;end loop
madhu
- Feb 9th, 2007
I hope this will work..
declare
i number(2);
Begin
for i in 1.. 10
loop
if (i ==5) then
else
exit;
end loop;
end; -
I want to tune the below query for performance issue can u please help me the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'AND A.POSTED_FLAG = 'N'AND ERROR_FLAG = ' '
kvsubramanian
- Feb 2nd, 2010
Try to Replace "AND A.ITEM like 'OA%'" with " AND substr(A.ITEM,1,2) = 'OA' ".
nagarajbng
- Oct 3rd, 2009
If you are using the UNION keyword then there is no need of using the distinct keyword in your query or else you can use UNION ALL keywordorBelow I am sending the modified queryIn your query which col...
-
Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> desplaying the table structure butselect * from DB1.T1 ---->giving--> table or view does not exist.. Can any one explain possible reason behind this.
todeepakmalhotra
- Nov 10th, 2008
You cannot use the database name before the table name like db1.t1.we can only use a user name?and then table name. eg. let we have a user "deepak" then we can write select * from deepak.t1 .? We ca...
oozaifa
- Nov 6th, 2008
Type schema name before table name or your id dont have the access to view the data from that table
-
Help me to write a procedure for:verify a table for record if it is exists need to update or else insert that record( using cursor write a procedure )
ss
- Sep 25th, 2007
Insert the row you want you as if it doesn't exist,?in the exception? write the update statementBegin//Inser the rowExceptionwhen< row already exists>//update the rowEnd
-
What happens when commit is given in executable section and an error occurs ?please tell me what happens if exception block is committed at the last?
ae_sathis
- Feb 24th, 2008
SQL> create table d ( sl number(4), name varchar2(5)); now do the following operation:SQL> begin insert into d values ('1223123123','geekinterview'); --making insert to fail here commit; exception ...
geetha24
- Feb 9th, 2007
Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.
-
-
-
-
-
Oracle triggers
Can we issue rollback, commit in the trigger body. if we issue what is the result
Read Best Answer
Editorial / Best Answer
binurajnair- Member Since Feb-2008 | Feb 28th, 2008
An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.
The following exercise will help you to understand the difference between autonomous and normal transactions.Create a table for the exercise.
create table test_table (a varchar2(50));
Create a non-autonomous procedure which will insert one row to this table.
CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
INSERT INTO test_table
VALUES ('Non Autnomous Insert');COMMIT;
END;
Now execute the following codeDECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Non Autnomous Insert');non_autonomous_proc;
INSERT INTO test_table
VALUES ('After Non Autnomous Insert');ROLLBACK;
END;
Now query the tableselect * from test_table;
As expected 2 rows will be retrievedA
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert
Now we will see the case of autonomous transaction.Clear the test_table
delete from test_table;
commit;Create an autonomous procedure which will insert one row to this table.
CREATE OR REPLACE PROCEDURE autonomous_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_table
VALUES ('Autnomous Insert');COMMIT;
END;Now Execute the following code
DECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Autnomous Insert');autonomous_proc;
INSERT INTO test_table
VALUES ('After Autnomous Insert');ROLLBACK;
END;And query the table
select * from test_table;
This will show only one rowA
--------------------------------------------------
Autnomous InsertConclusion
1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.
2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction
krishnaindia2007
- Jun 1st, 2008
We can use commit and rollback in trigers using autonomous transactions.- An atonomous transaction is an independent transaction initiated by another transaction.- When an atonomous transaction is cal...
arbind1982
- Mar 12th, 2008
we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration sectionCREATE TRIGGER anniversary_triggerBEFORE INSERT ON employees FOR EACH RO...
-
Find Sequence MAX Value
How to find the max value in a Sequence?
mstreete
- Sep 30th, 2008
If you don't own the sequence, you may look at all_sequences, which shows sequences in other schemas that you have access to:select max_valuefrom sys.all_sequenceswhere sequence_owner = '&sequence_owner' and sequence_name = '&sequence_name'
nandk.sharma
- Jun 30th, 2008
We can get the MAX value of a sequence by using the data dictionary view 'USER_SEQUENCES' .i.e. SELECT MAX_VALUE FROM USER_SEQUENCES WHERE SEQUENCE_NAME='NAME_OF_SEQUENCE'
-
PL SQL Procedure in Package
Procedure in package perfomes fastly over normal procedure, Explain.
monoranjang
- Jan 24th, 2011
A Storeprocedure is a Set of transactional SQL statements which will do some operation. Storedprocedure is precomplied . When we use sp's in our application it will reduce network traffic because ...
muraligrandhi
- Jun 8th, 2010
It's true. Procedures in the packages are faster than the stand alone procedures, because all the procedures in the package are compiled once and stored in the SGA at shared pool area. If we call the ...
-
TCL Statements in Trigger
Why are we not supposed to include TCL statements in a trigger?
manveshv
- Dec 30th, 2009
The trigger is fired when an event occurs while manipulating the data in table.So the trigger applies a lock on it restricting others to modify it.Locks are released when a TCL command is executed. Th...
Vansat
- Jun 16th, 2009
We cannot use TCL statements in triggers, because using them violates the integrity of the transaction. But if we want TCL statements to be used in triggers then that can be implemented by using Auton...
-
Procedure Parameters
What is call by value and call by reference in parameters (IN, OUT, INOUT)?
Read Best Answer
Editorial / Best Answer
lisha.varghese- Member Since Nov-2008 | Nov 17th, 2008
Three types of parameter modes
1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.
2) OUT parameter mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.
3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.
In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.
In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here no copying is required.
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT & IN OUT paremeterssamareshp
- Apr 17th, 2009
In Parameter:: call by reference OUT Parameter: call by valueIN OUT Parameter: call by valueif you use the hint NOCOPY with OUT Parameter and IN OUT Parameter then ::: call by reference
lisha.varghese
- Nov 17th, 2008
Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...
PL/SQL Interview Questions
Read Best Answer
Ans