-
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; ...
-
In function and procedure the parameter pass is "call by value" or "call by reference"?
krishnaindia2007
- May 3rd, 2008
IN :- actual parameter is passed by reference (a pointer to the value is passed in) OUT:- actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified I...
ashwinee2
- Apr 11th, 2008
Take this:IN parameters are passed by reference, so the value cannot be changed in the procedure and functions.Out and IN-OUT parameters are passed by value, so the values can be changed inthe procedures and fucnctions.
-
What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno into v_empno from emp where empno = 10;exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found ');end;
when others then *ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminated
kiran_marla
- Jul 7th, 2010
Error occurs, why because "when others" must and should be placed at last in exception block
neerajalatha
- Jul 5th, 2010
when others then*ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminatedThis is right answer
-
In PL/SQL I need to get like this scenario. if I am doing manipulation on a table, I need to display a record as Non-Updateable. It need to show a message to the user. How it is possible.Thanks in Advance.
rajivgupta780184
- Aug 12th, 2008
CREATE OR REPLACE TRIGGER SHOW_BEFORE_DMLBEFORE INSERT OR DELETE OR UPDATE ON SERVICE_ORDER_STATUS? --(TABLE NAME)FOR EACH ROW BEGIN?DBMS_OUTPUT.PUT_LINE('NON-UPDATABLE TABLE');?ROLLBACK;END;ThanksRajiv GuptaYogik Technologies Pvt. Ltd.
SonuYadav
- Aug 11th, 2008
You can user sql%rowcount to check whether the dml does something or notegbegindelete from tbl_xxxwhere col_yyy = 'ASDF';dbms_output.put_line (sql%rowcount);end;this way it will return the no of rows deleted
-
2)what is meant by forward declaration in functions?">
1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?
muraligrandhi
- Jun 23rd, 2011
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. For example, if program A calls program B and program B calls program A,&nb...
KD09714
- Jun 26th, 2008
Example of forward declaration in package : CREATE OR REPLACE PACKAGE pkg_CBRef_Seq AS PROCEDURE sp_CBRCreateDropSeq_scd ( p_cod_CountryCode IN country.country_code%TYPE, ...
-
-
-
now i want the result as
(248)9078905,(234)5678909 into another table called phone2.
">Hi Guys,i have an interview question. can anyone help me reg this.i have a table called "phone" and filed as "phone number" and table have values like 2489078905,2345678909now i want the result as (248)9078905,(234)5678909 into another table called phone2.
SURESHMALLIPEDDI
- Aug 9th, 2011
Select '('||substr(phone,1,3)||')'||substr(phone,4) "formated phone number",phone from phone
paresh Borad
- Aug 1st, 2011
Use this query
create table newphone as (select '(' || substr(phnumber, 1,3) || ')' || substr(phnumber,4,10) "phonenumber" from oldphone) -
What is a NOCOPY parameter? Where it is used?
raghav_sy
- Jan 15th, 2007
hi one more thing i wan to add abt NOCOPY, it is that its a hint to compiler, so its compiler wish whether to follow this thing or not, so before using this just go through the conditions when this th...
-
-
Suppose one table contain many identical records. Then how to delete only identical Records.
Nikhil_4_Oracle
- Mar 12th, 2007
Hey,Above?Soln is not working with tables where data is like,COL1?----------1511 *708111 *5511 *78--Here 11 Repeats 3 times fine...--Check d rowidselect rowid,col1 from nodup;ROWID?????????...
suni123
- Mar 5th, 2007
Hi,To delete duplicate rows from table EMPDELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROW...
-
How to add XL Sheet in PL/SQL?
rajivgupta780184
- Aug 12th, 2008
By using SQL LOAER, TOAD we can insert?EXCEL file data into respective Table & we can also create or upload ORACLE table data to?.xls file from that.ThanksRajiv GuptaYogik Technologies Pvt. Ltd.
-
Explicit cursor and Select into statement
What is the difference between an explicit cursor and select into statement?
neerajalatha
- Jul 5th, 2010
Select statement can return multiples rows at a time and there exists only one network round trip.Explicit cursors are memory areas which acts as a handle or pointer to context area and allows to fetc...
rashlikesu
- May 12th, 2010
When select into query returns just one row, Oracle uses implicit cursor to execute and fetch the results for the query.But if the select statement returns more than one row, an Explicit cursor is created to fetch the data.
-
Status of Procedure
If a procedure is referring a table and if the structure of the table is changed, lets say data type of the any column is changed, then is it possible that the status of the procedure remains valid? Explain
Read Best Answer
Editorial / Best Answer
tar.goyal- Member Since Aug-2010 | Aug 4th, 2010
It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.
This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.tar.goyal
- Aug 4th, 2010
It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.
anironic
- Jul 21st, 2010
If PL/SQL stored SP %type and does not explicity declare a datatype, then it is quite possible that Stored procedure remains valid, even after changing the datatype in the table. Ex, you have a declar...
-
Load excel data into oracle database
1. How to load excel data into oracle database by using sql loader?
2. What are the ways to load data from excel file in oracle database ? -
How would you hide a table in sql. ie the table can be only visible to its maker.
Not known yet
ankurlibra
- Sep 26th, 2010
We are misunderstanding the question and I assume that Abhishek want to say that "How we can hide a table info from user?".To hide table info or as per question make it invisible to other user. A tabl...
pravelu
- Apr 23rd, 2010
It can be done using role grants. Grant view or debug privileges to only the set of users who can see these tables. The other users who are not granted the privilege cannot view this table. Every single user is a Schema in Oracle.It is by default, that the table cannot be seen by others.
-
-
How can i get set identity for last coloumn of the table.
Nitesh Srivastava TechM
- Nov 10th, 2011
See the code below
Code- span style="color: #ff0000;">'EMP''EMP'
In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
vnraos
- Sep 5th, 2008
First, let us understand why these errors cropup.1) NO_DATA_FOUND - is raised only for "select into" statements when? the where clause of the query does not match any rows.2)TOO_MANY_rows - is raised...
binurajnair
- Jul 30th, 2008
If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown belowSELECT count(rowid)into v_count from...
Read Best Answer
Ans