-
Select Multiple Information
How to select multiple data from different tables which do not have related constraints?
bhagavatula
- Dec 10th, 2009
It is not necessary to have 'related constraints' between tables to retrieve data from multiple tables. However, there should be a logic to associate data between the tables (related data) based on which you can select information from multiple tables
-
Debug PL/SQL Code
How will you debug PL/SQL Code?
geek.hari
- Dec 14th, 2009
One conventional and popular method of debugging a pl/sql code is through printing the program execution path using a printable statement. For this you have to enable the output mode using the command...
OraclePassport
- Apr 17th, 2009
The Best way to debug is by using an AUTONOMOUS TRANSACTION. Create a dummy table and insert into the table using the AT. This wont affect the main code, but one could know which part of the code the execution has reached while the program is running itself.
-
I want to insert the following information in userAction table:Which user execute which query on which date?the userAction table contains the foolowing attributes:USER DATE QUERYplease write to me how to resolve this problem?
harinicm
- Dec 11th, 2009
Use AUDIT is used to track the occurrence of the sql statements in the subsequent user sessions (not the current session). The audited record contains:Name of the userType of the operationObject...
-
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...
-
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...
-
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 ...
-
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...
-
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.
-
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.
-
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.
-
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...
-
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...
-
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.
-
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
-
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
-
Call SOAP Web Service from PL/SQL
How a SOAP web service can be called from PL/SQL block?How can we handle the result set returned by the SOAP service?
-
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.
-
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.
-
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 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...
PL/SQL Interview Questions
Ans