-
How do you compile a package?
Larry
- Nov 6th, 2007
Execute package_name;
-
TEMP
column1
1
2
4
7
5
so in this table i want search missing number from 1 to 10.
so output should be
3
6
8
9.
how to get this o/p?">In my table "temp" one column contain 1 to 10 range of values in between some numbers are missing. like belowTEMPcolumn112475so in this table i want search missing number from 1 to 10.so output should be 3689.how to get this o/p?
chakraborty_sarkar
- Jul 10th, 2007
SELECT LEV FROM (SELECT LEVEL LEV FROM dual CONNECT BY LEVEL < 10) WHERE LEV NOT IN (SELECT COLUMN1 FROM TEMP);
Amit
- Jun 14th, 2007
If I have Emp Table and id column
1
2
4
5
7
SELECT 10-id FROM Emp WHERE id in (SELECT id FROM Emp WHERE id 5)
-
How can we print the error code in exception handling section?
Kiran Birajdar
- Mar 21st, 2016
EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,An error was encountered - ||SQLCODE|| -ERROR- ||SQLERRM); END; Or you could log the error to a table using the SQLERRM function ...
devi
- Sep 25th, 2007
You can print the 'sqlcode'?as followsException?When others thendbms_output.put_line('error code is'||sqlcode)end;?
-
What is the disadvantage of out paramter in functions
g_sidhu
- Feb 19th, 2008
We cant use functions with OUT parameter in sql statements because there is no way for you to declare a variable in a SQL query and in order to call an IN OUT -- you need a local variable (to supply a value to be read from and to supply a place to put the out value into).
ramaswamy
- Sep 26th, 2007
Functions with "out" parameters?cannot be used in?SQL statments however I disagree with you?rajeev because functions without parameters can also be called in select statments as long as they havea return()?(Eg: SYSDATE)
-
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...
PL/SQL Interview Questions
Ans