-
How to change owner of a table?
chaitunitk
- Jun 3rd, 2010
Owner of a table is the schema name which holds the table. To change the owner just recreate the table in the new schema Using create table XXX AS select * from oldschemaname.XXXand drop the previous table in oldschema
Das
- Dec 21st, 2006
Owner of a table is the schema name which holds the table. To change the owner just recreate the table in the new schema and drop the previous table
-
Find null value in a table
What are all the different ways available to find the null value in a table
-
What are the characteristics of autonomous transactions?
ajeeteng
- May 5th, 2010
Autonomous transactions refer to the ability of PL/SQL temporarily suspend the current transaction and begin another transaction. The second transaction is known as an autonomous transaction, an...
uday
- Jun 29th, 2007
Autonomous transaction defined and executed?independent of calling program. u need to define PRAGMA AUTONOMOUS_TRANSACTION at declare section. stored procedure, function can be defined as autonomous transaction.
-
What is the use of PL/SQL procedure DBMS_TTS.TRANSPORT_SET_CHECK?
ajeeteng
- May 5th, 2010
The DBMS_TTS package checks if the transportable set is self-contained. TRANSPORT_SET_CHECK Procedure This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.
-
What are Hash, Nested loop and merge-sorted Join.
psingla
- Apr 27th, 2010
There are three types of alogrithm used by SQL compiler for joining purpose:1) Nested join algo2) Hash join algo3) Merge join algoComplier choose any one of these algo based on the best suited for per...
norman
- May 8th, 2007
In hash join hash table gets build using the inputs. Nested loops as the name implies uses looping construct strategy for storing and retrieving. In terms of performance comparison while retrieving da...
-
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
-
When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?
manish.baehal
- Apr 5th, 2009
COUNT(Distinct) is better than using Self Join to find redundant data as it is faster and shorter. Beside this COUNT(Distinct) provide more flexibility.
-
How PL SQL is different from T-SQL
psingla
- Apr 28th, 2010
PL/SQL is procedural language.It is built upon sql with extra added functionalities.It is used by ORACLE.robust and consistentexception handling is great the concept of package is greatdisallow bad co...
Yaseen
- May 12th, 2007
SQL (Structures Query Language)PL/SQL (Procedure Language / SQL) is the Oracle's version with lots of additional proprietary functions.T-SQL : Transact-SQL is Microsoft's proprietary version of SQL.
-
What is the maximum number of data string can be returned from DBMS_OUTPUT? andHow do you call a trigger from a table?
psingla
- Apr 27th, 2010
2) Triggers cannot be called rather they are fired automatically based upon their specification.
MuralidharanR
- Mar 17th, 2007
The maximum buffer size of DBMS_OUTPUT IS 1,000,000. A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Trigger can not be executed manually
-
Querying Customer Details from a Table
How to query the customer details from a Table who have purchased No. of items greater than 5?
psingla
- Apr 27th, 2010
I guess the table structure would be liketable_name (custname,item_purchased)so query will be:select custname from table_name group by custname having count(*)>5
rajivgupta780184
- Aug 12th, 2008
Select column 1,...column n from table namewhere purchaseno 5 order by purchaseno;try this one .....ThanksRAJIV GUPTA YOGIK TECHNOLOGIES PVT LTD.JAMSHEDPUR
-
Load White Space Delimited Data
How to load whitespace delimited data using SQL*Loader?
psingla
- Apr 21st, 2010
1. Create a ontrol file with extn (.ctrl). This file will map the flat file data with the Oracle table columns.2. Pass control file as one of the parameter of SQL Loader.
-
Call SQL Script
How to call SQL script file from PL/SQL?
dj_dj_dj
- Mar 18th, 2010
You can Call SQL Script by using @ and then giving path of the source fileFor ex: @c:/sqlscript.txt;RegardsDharmendra Jaiswal
prasadreddi
- Mar 17th, 2010
@<path>filename.sqlgive the path where the file is located extactly.Ex :- Let us say our file is located at c:tempabc.sqlSQL> @"c:tempabc.sql" It is better practice to use double qu...
-
Plz clarryfy this.Scinario is whether we can use out mode in functions?.I thought it's not possible. But in interview they are asking this.if i am telling no,they are not accepting me.Plz clarify this anyone.
kiran_marla
- Mar 10th, 2010
There is no difference between function and package, except function must and should return a value while procedure do not. But it is not a good practice to use out mode in functions.However a default...
vishadk
- Mar 7th, 2010
I believe you are asking about the use of 'out' in the function declaration.One can use 'Out' mode in the functions in the manner simlar to procedure i.e.Create or Replace function fun_out_example(par...
-
Autonomous Transcation
What are advantages of Autonomous Transcation?
raghunadhn
- Mar 3rd, 2010
Autonomous transactions are independent of the main transactions.You can commit the changes in the other tables using these transactions, even though the main transaction is not a success. Depending on one transaction, you can commit/rollback the other transactions.
namita04
- Dec 23rd, 2009
A autonomous transaction starts with the first SQL statement of the PL/SQL block and ends with a commit.
-
You have compiled some PL/SQL packages in your schema, and found aome errors in one procedure.how do you find which procedure produced the error?how do you find which section of the code produced the error and look at?
No answer
samareshp
- Feb 13th, 2010
While creating a package, we can use Show error Command to get the line number thats has error. And can be corrected. Even we can check in the data-dictonary view i.e. user_errors to figure out the er...
gem023
- Feb 11th, 2010
On SQL Plus you can give the command - show errors. This would show the line number and reason for the compilation error.
-
What is the difference between right join and right outer join..
samareshp
- Feb 13th, 2010
Both are same.outer join : We have 3 types of outer joins. Left Oute, Right Outer and Full Outer join.Left outer join:Select * from lefttable, righttable where lefttable.col1=righttable.col1(+)This wi...
krishnaindia2007
- May 13th, 2008
Both are same. Right outer join returns all the reocrds that satisfy the join condtion + rest of the records from right (or second) table.
-
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...
-
Execute Immediately
What is the meaning of execute immediately?
hmounir
- Jan 25th, 2010
EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created a...
Brajesh Singh Bhadoriya
- Jan 25th, 2010
Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which ...
-
Types of Lock
Explain share and exclusive type of lock.
hmounir
- Jan 25th, 2010
Oracle uses two modes of locking in a multi-user database:Exclusive lock mode (X) prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction t...
rahulmca
- Jan 24th, 2010
The two basic types of locks are DML (table) locks, and DDL (dictionary) locks. DML locks are designed to guarantee data integrity in a multi-user environment. DML locks prevent conflicting DML and DD...
-
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...
PL/SQL Interview Questions
Ans