-
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...
-
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...
-
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.
-
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
-
What will be the impact of replacing an API call with a stored PL/SQL call?
godsrikanth
- Dec 3rd, 2009
Difference is in performance. API is a package call which will process faster since its already compiled. It can be called globally. Stored PL/SQL call is not compiled into memory and may take more time.
-
Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use
berly.george
- Dec 1st, 2009
Instead of triggers are used:1) To control the DML operations on Views(Insert/Update/Delete)2) Makes the non updatable views updatable
hindol_saha
- Apr 9th, 2007
Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger...
-
When we need to use USING clause in the sql?For example in this below:SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations l USING (location_id) WHERE salary > 10000; Can anyone explain what is mean of USING in this sql statement? or USING concept in brief? Thank you.
hmounir
- Nov 17th, 2009
In natural joins USING clause can be used to specify only those culomns that should be used for an equijion
ravikumarzee@gmail.com
- Mar 9th, 2007
USING is also used while executing Dynamic SQL.e.g.,EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
-
What can be the Maximum size of a plsql block?
jabir.mkk
- Oct 16th, 2009
The maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'
-
What is the difference between all_ and user_ tables ?
pawan_kr2007
- Oct 11th, 2009
ALL_OBJECTS: Objects accessible to the user.USER_OBJECTS: Describes all objects owned by the current user.DBA_OBJECTS: Describes all objects in the database.
krishnaindia2007
- May 7th, 2008
The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easily? understood by users. All these views are owned by sys.Oracle provid...
-
Oracle extract records from temporary table
Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through Merge statement. If I use cursor in that temporary table, How can I extract all of the records from temporary table at the end of the stored procedure. Can you please give coding for this.
johnjerry
- Oct 7th, 2009
Make sure you do not COMMIT* in the middle of the procedure. You can do any number of insertion into and deletion from temporary table and use "Select * from temp_table" to fetch a...
priyanka jolly
- Nov 24th, 2007
Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is activeThanks Priyanka
-
Referenced and Dependent objects
How oracle manages dependency between referenced and dependent objects ?
johnjerry
- Oct 7th, 2009
We can set REMOTE_DEPENDENCIES_MODE inside init.ora parameter file to TIMESTAMP or SIGNATURE.ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = [TIMESTAMP|SIGNATURE]
YugundharK
- Oct 13th, 2008
Oracle manages dependency between referenced and dependent objects by using 2 modes1. Timestamp mode2. Signature mode
-
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.
surya72446
- Sep 30th, 2009
PL/SQL stands for procedural languages extensions to SQL and it's theme is to hold a bunch of SQL statements as a single function and return a single output.PL/SQL is advantageous compared to other pr...
g_sidhu
- Feb 16th, 2008
PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code. PL/SQL offers moder...
-
Indexed Select Statement
How can we find the select statement is indexed or not?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
FROM emp
WHERE emp_name = 'ABC';
2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
FROM emp
WHERE emp_name = 'ABC';
The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.promisinganuj
- Sep 13th, 2009
1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT * FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT * FROM...
-
Retriving Multiple Records
Why PL/SQL does not support retriving multiple records?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.
SELECT *
FROM emp;
This itself gives us multiple records. There is no additional feature required to do the same in PL/SQL.
At the same time, PL/SQL goes one step further and enable us to process these fetched rows one by one (unlike the update statements which updates all the rows selected).
There are several options for fethcing data in different PL/SQL constructs:
1. Using Ordinary Cursor
2. Using Ref Cursor
3. Using PL/SQL bulk collect
4. Using PL/SQL arraysBut the important thing here is that irrespective of the way we fetch the data, we act upon them row-by-row.
promisinganuj
- Sep 13th, 2009
Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.SELECT * FROM emp;This itself gives us multiple records. There is no additional feature requir...
Mad Hatter
- Nov 4th, 2008
Multiple records at a time could be retreved in PL/SQL using BULK COLLECT.To do that you define PL/SQL table, and load it using SELECT ... BULK COLLECT INTO <pl/sql table>FROM ..Optionaly could ...
-
Handle Multiple Rows Without Loops
How can we handle multiple rows without using loops in PL SQL?
sen_sam86
- Aug 18th, 2009
This can be done by a function called TABLE FUNCTION by means of this you can return more number of rows and columns this is more effective than using ref cursorregards sen-prakash
rajivgupta780184
- Oct 31st, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 fo...
PL/SQL Interview Questions
Read Best Answer
Ans