-
What is the difference between using IS and AS while creating a procedure, function package and package body?
-
Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL
Art11
- Sep 27th, 2011
My example is not perfect but close."sql SELECT deptno, job, ename, sal ,CURSOR( SELECT ename, deptno, job, sal , DENSE_RANK () OVER (ORDER BY sal desc) rnk ...
dipanjan80
- Oct 6th, 2010
?Using LEAD analytical function its possible to get hierarchical set of data. Here is an example:?SELECT deptno, empno, sal,?LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) N...
-
How to write a query or procedure or function to retrieve all the tables from database where the table dont have any data (no rows).
Database has 100 tables and some of the tables dont have any data. I want to pullout those table names from database in Oracle.
-
What is the difference between IS/AS while creating a procedure ?i.e Create or Replace procedure is/as .....
kirangsv
- Jan 6th, 2008
Well there is no difference between is/as as they said both r one and the same when i have gone thru the book u can use either is / as
cutesree
- Dec 17th, 2007
There is no difference between is/as ... is is used for creating a new procedure with a new name where as 'as' is used to create a new procedure with existing procedure name... it will not display "procedure already exists".
-
What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
krishnaindia2007
- May 3rd, 2008
Mutate means chage. Mutating table is a table that is being modified by update, delete or insert statement.
-
If you write a function and procedure with performs same task and return a value, which will execute fast and why.
santosh_pokiri
- Nov 29th, 2007
Function: Whenever a function executes it returns value.ex:create or replace function sum(x number)asy number;begin y:=x+2; return y; -- this ...
amahanit
- Oct 16th, 2007
hi ravgopal i got u'r reply,thanks. but there is still one query ,in procedures OUT and INOUT parameters are copied from actual parameters to formal parameters(it takes extra time),so to...
-
What is Pragma ? How do we know that which ERROR code is associate to what error ?
krishnaindia2007
- Jun 23rd, 2008
Pragma is a compiler directive. It passes information to compiler. These are processed at compile time not at runtime. &nbs...
rajakumar_na
- Nov 2nd, 2007
Pragma is a compiler directive. Using SQLCODE & SQLERRM we know that which ERROR code is associate to what error.
-
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
-
PL/SQL tables
How will implement the PL/SQL tables?
krishnaindia2007
- May 29th, 2008
- Pl/sql is in memory representation of a table.- It is just a data type. ( Collection type)- It may store any number of rows from table.Here is a simple exampleCREATE OR REPLACE PROCEDURE TEST_...
tonyrobert
- Mar 27th, 2008
INDEX By Tables composed of two components1.? Primary key of data type BINARY_INTEGER2.? Column of scalar or record data type.Since this table is unconstrained, it can increase its size dynamically....
-
PL SQL Logs
List at least three common ways to write logs in PL SQL?
yogesh
- Aug 4th, 2011
We can't return more than 1 value in function but by using OUT parameter we can get multiple values in function
which is not good programming, by using ref cursor we just save memory location of function doesn't return more than 1 value.sububhaai
- May 14th, 2009
1. Create a log table and insert your logging messages into this table.2. Write to a log file, using the DBMS_FILE package, but this is not recomended because there is lot of I/O.
-
How to find error line of a package at run time
How can the error line can be identified for a package at run time ?
muraligrandhi
- Jun 9th, 2010
There are two ways for finding error line in a package
1. SHOW ERROR
2. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
First method is useful for all the versions but second one is 10G or above versions only.tripathi.atul
- Sep 9th, 2008
Starting from Oracle 10G we can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to get the line from where the error originated.
-
Oracle Mass Update
How do we do mass updates in Oracle PL SQL?
-
Return Multiple Rows
How to return more than one row through functions? Explain with example.
rajivgupta780184
- Oct 31st, 2008
create or replace function f1 return sys_refcursor as
r1 sys_refcursor;
begin open r1 for select * from emp;
return(r1);
end;
call the function as: select f1 from dual;
Thanks & Regards
RAJIV GUPTA (ORACLE CONSULTANT)
YOGIK TECHNOLOGIES PVT LTD.raghuprasad
- Sep 1st, 2008
Eg: SELECT empname,sal FROM employee_table WHERE sal<MAX(sal) AND sal>MIN(sal);Hope the above statement executes.
-
-
Composite Variables
What are Composite Variable?
Rams
- Aug 7th, 2011
A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, coll...
agitatedsoul10
- Aug 4th, 2010
A Variable holds a value in a memory location. The Association of a variable to other variables can be called as composite variables.
-
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...
-
DATATYPES
1-WHAT IS THE MAX LENGHT OF VARCHAR2 DATATYPE IN PL/SQL?2-WHAT IS THE USE OF UROWID?3-WHAT ARE THE SUBTYPES?GIVE ANY 2 EXAMPLE?4- WHAT IS THE GENERAL STANDARD FOR DECLARATION?
pravesh28
- Dec 27th, 2010
1... max length is 327672 rowid means row plus id actuall its psedocolmn that store the value of each rowwith the help of rowid>>> we can remove duplicate value>>> we can dispa...
muraligrandhi
- Nov 9th, 2010
1. Maximum length of a varible in PL/SQL is 32767 bytes2. The UROWID or Universal Rowid is useful for storing logical addresses of all the rows of Index-organized tables. It can store both the logical...
-
Populating Tables in Oracle 11g
How do you populate tables or data in Oracle 11g step by step?
-
What are Oracle Hints and How do you use them ?
Explain the real-time situation where would you use Oracle Hits
-
Oracle exception block
In the exception part,what will happen if u don’t give (when others) where will it show error compile time or run time
NAND
- Feb 27th, 2012
It will give the error at run time if it is not able to find the type of exception thrown in the current PL/SQL block or the outer block.
It will not throw the compile time error.Mark Haynes
- Feb 27th, 2012
Now, to clarify, I am taking the following statement; "in the exception part, what will happen if you don't give the (when others) statement" to mean an exception block with no other when statements a...
PL/SQL Interview Questions
Ans