-
Rename a Column
How to rename a column in a SQL table?
-
Lock Row of a Table using SQL
How to lock a particular row of a table using SQL?
-
Changing Rows into Colums
How to change rows to columns and columns into rows?
-
-
Function return more than one value
Hi,My questions is " Can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.My understanding is if out can't use as a parameter...
-
-
Alias in Joins
Why do we use aliases during joining two tables?
-
Find all tables in a database
How to find all the tables existing in a database?
-
sub query and correlated sub query
What is the difference between sub query and correlated sub query
-
PL/SQL Block output
begin For i in 1..5 loop insert into A values(i); savepoint 1; end loop; rollback to savepoint 1; commitend;--initially there are no data in table A. So my question is after execution of this block what should be the data present in table A?
-
Delete contents in two tables at a time
How to delete contents in two tables at a time? When you delete contents in one table then other table contents should also be deleted.
-
SET Operator
What is a SET operator? What are the types what is the difference between SET operators and JOINS?
-
Table and Column Level Locks
How to handle the table level locks and column level locks?
-
CBO & RBO
What is CBO and RBO? What is diff between these two?
-
Left Outer & Right Outer Join
What is the use of Left Outer & Right Outer Join, Using Equi Join with Union we can do the outer join then why we need to go for Outer Join
-
Example for calling procedure
Give an example for calling procedure with user and system exception
-
Data stored in Database
How data is stored in database?
-
PL/SQL tables
How will implement the PL/SQL tables?
-
SQL and PLSQL
What is the difference between SQL & PLSQL?
-
Oracle triggers
can we issue rollback, commit in the trigger body. if we issue what is the result
-
Difference between 'TABLE OF' and 'REF CURSOR'
What exactly the difference between 'TABLE OF' and 'REF CURSOR' in PL/SQL?For what purposes these both might be used?
-
Establishing many to many relationship in SQL
How will you establish many to many relationship in SQL?Ramco Systems dt. 02 Feb' 2008
-
What is a deadlock?
There is a table with Pluks('plk') and Zinks ('zin'). Each Pluk onlyresides on a single Zink and each Zink may contain multiple Pluks. Howdo you find how many Pluks are on each and every Zink? Please provide SQL.What is a deadlock?
-
Copy structure of a table
How can I copy the stucture of a table to a text file.
-
Copying table with constraints and data
How to copy a table with constraints and data to another table.
-
Constraint and Index
What is difference between constraint and index? How do use them in SQL query?
-
View sysdate
How can we view sysdate for n number of times
-
Oracle long datatype
I'm using oracle9i to create a table which stores lyrics in long datatype. But if i store a long string of text and display it from the table, it displays only 80 chars. So someone help me how to store the whole lyrics txt file
-
-
-
-
-
Oracle : Table Level and Column Level constraints
We have two type of constraints - Table Level and Column Level. Which is better and why..?
-
-
Oracle Cursor types
What are the types of cursors apart from explicit and implicit cursors ? Explain when and where they are used ?
-
-
-
-
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.
-
-
-
-
-
-
-
-
-
-
How do you set table for read only access ?
if i am updating one record in a table at that time no other user can't able insert ,update the record in same table How is it possible
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
while this query dint work :
"select * from (select rownum,a.* from emp a) where rownum=70;"
2. as i've read that rownum can work only for < and <= but not for = or > or >=, then why the query worked for rnum=70">1. Why is it so that the given query worked :"select * from (select rownum rnum,a.* from emp a) where rnum=70; "while this query dint work :"select * from (select rownum,a.* from emp a) where rownum=70;"2. as i've read that rownum can work only for < and or >=, then why the query worked for rnum=70
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line What is a package ? What are the advantages of packages ?
-
Give the structure of the procedure ?
PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;
-
What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
-
What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is always returns a value using the return statement. A PROCEDURE may return one or more values through parameters or may not return at all.
-
Where the Pre_defined_exceptions are stored ?
In the standard package. Procedures, Functions & Packages ;
-
What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.For triggers related to INSERT only NEW.column_name values only available.For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.For triggers related to DELETE only OLD.column_name values only available.
-
-
What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex ...
-
What will happen after commit statement ?
Cursor C1 is Select empno, ename from emp; Begin open C1; ...
-
What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closeswhen all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; ...
-
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.These attributes are proceeded with SQL for Implicit Cursors...
-
Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.PL/SQL uses Implicit Cursors for queries.User defined cursors are called Explicit Cursors. They can be declared and used.
-
What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
-
What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of differenttable or views and variables. E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type); e_rec emp% ROWTYPE cursor...
-
What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not know about variable's data typeii. If the database definition of a column in a table changes, the...
-
What should be the return type for a cursor variable.Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE