Write a PL/SQL anonymous block with exception handling to print your name 10 times?

Sri

• Feb 7th, 2020

Begin
i varchar2(10) :=xxxxxxx
begin
for x in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/

Bibhudatta Panda

• Jun 18th, 2019

Code
 

Answer Question  Select Best Answer

 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.

Rounak037

• Jan 22nd, 2020

Cursor is used when the user needed to update record in a single or in row buy row manner in a database table, Cursor is required to process rows individually for queries returning multiple rows.
Basically cursor are two types:
1. Implicit Cursor.
2. Explicit Cursor.

rahulg5211

 Profile Answers by rahulg5211 Questions by rahulg5211

• Oct 4th, 2009

Cursor is an small memory allocation or workstation where operation or processing is done on the data.Types of attribute of cursor are-:1)ROWCOUNT2)ISOPEN3)FOUND4)NOTFOUND

Answer Question  Select Best Answer

RAVI KUMAR BURRI

• Jan 19th, 2020

SET AUTOTRACE ON EXPLAIN

machomanic

 Profile Answers by machomanic Questions by machomanic

• Jul 8th, 2010

You can use the command SQL> SET TIMING ON;This will show how much time your query took for the execution.

Answer Question  Select Best Answer

Divisha

• Dec 30th, 2019

Can You please elaborate in what way can we do it ?

psingla

 Profile Answers by psingla Questions by psingla

• May 6th, 2010

In 10g we can

Answer Question  Select Best Answer

Hi Can anyone explain what this mean? I have practise PL/SQL about REF obj_type, my codes are: CREATE or REPLACE TYPE home_type AS OBJECT (street VARCHAR2(50), city VARCHAR2(20), state VARCHAR2(20), zipcode VARCHAR2(6), owner VARCHAR2(10));/CREATE TABLE homes OF home_type;INSERT INTO homes VALUES ('300 Regina St', 'WATERLOO', 'ON', '010010', 'HFWANG');INSERT INTO homes VALUES ('301 Regina St', 'WATERLOO',...

priti

• Nov 11th, 2019

Kindly let me know what do you want in result using ref cursor? here you ref cursor as a table which is not exists in system. that is what it is giving error

krishnaveni_g

 Profile Answers by krishnaveni_g Questions by krishnaveni_g

• Jul 15th, 2008

I checked the code it's working fine.

Answer Question  Select Best Answer

Write a PLSQL function that accepts some text and validates that the text has only Alphanumeic characters and returns true else returns false

Priya

• Oct 25th, 2018

LENGTH(TRIM(TRANSLATE(string1, abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789, )))

This function will return a null value if string1 is alphanumeric. It will return a value "greater than 0" if string1 contains any non-alphanumeric characters.

Answer Question  Select Best Answer

 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.

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 3rd, 2008

---

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
?
2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5.?Function returns 1 value only. Procedure can return multiple values (max 1024).

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.
?
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

mahendra pal

• Sep 18th, 2018

Procedure may or may not return value

mangesh patil

• Aug 22nd, 2017

Procedure may or may not return value where as function should return one value.

Improve Answer

Shivam Softa

• Sep 19th, 2018

SELECT LISTAGG(VAL) WITHIN GROUP(ORDER BY LE) FROM
(select LEVEL LE , substr(SHIVAM,-LEVEL,1) VAL FROM DUAL CONNECT BY LEVEL

ushodaya

• May 20th, 2018

Declare
str1 varchar2(50):=&str;
str2 varchar2(50);
len number;
i number;

begin
len:=length(str1);

for i in reverse 1..len
loop
str2:=str2 || substr(str1,i,1);
end loop;

dbms_output.put_line(Reverse of String is:||str2);
end;
/

Answer Question  Select Best Answer

What are the advantages of Gathering Statistics?

Shivam Softa

• Sep 19th, 2018

Helps Oracle engine to create better execution plan to execute the query.

ushodaya

• May 20th, 2018

We should generate statistics for all tables and clusters and all types of indexes accessed by your SQL statements before using the cost-based approach. If the size and data distribution of your table...

Answer Question  Select Best Answer

Explain when do we use a Procedure and when do we prefer using functions.

Mabula Shaik

• Aug 18th, 2018

We can add few more points, Those points are: 1) procedure can allow select as well as DML statements in it, Where as function can allow only select statements. 2) a function can be called in proc...

Deva

• Feb 10th, 2018

Procedure and function both are db objects..both will be stored as pre-compiled objects in our db.. the major difference is 1) Procedure may or may not return a value..but function must return a valu...

Answer Question  Select Best Answer

If I create N number of small procedures with one PL/SQL block (or) procedure why we need package?

Prasad Bagaregari

• May 11th, 2018

An embedded object where all the required units can be in one place.

Tarika Bhutani

• May 25th, 2017

Whenever we run any subprogram of a package the whole package get loaded in SGA. Which actually reduces the number of context switching and hence increases speed and better performance. Whereas in procedures we the context switching is more. Procedures are useful for writing small modules.

Answer Question  Select Best Answer

Amol Bakane

• Apr 2nd, 2018

The following hierarchy is followed when a trigger is fired. 1) BEFORE statement trigger fires first. 2) Next BEFORE row level trigger fires, once for each row affected. 3) Then AFTER row level tr...

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The orders of triggers execution is like this???? ? 1.before statement level?? 2.before row level?? 3. after row level?? 4 after statement level

Answer Question  Select Best Answer

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

Aditya

• Feb 28th, 2018

Lock table < table name > in exclusive mode;

Smruti R Das

• Sep 26th, 2012

GRANT SELECT ON table_name TO user_name

Answer Question  Select Best Answer

Explain what is Simple Cursor and What is Parameterized Cursor? And Difference between both??

sandeep

• Feb 1st, 2018

In real time we are using more than one cursor in PL/SQL block. so in this we are sending one cursor values into another cursor so the receiving cursor must be parameterised cursor

Akash Mohan singh

• Dec 26th, 2017

A parameterized cursor are static cursor that can accept passed in parameter values when they are opened. Parameterized can only reference to its own parameter parameterized cursor cannot reference to local variable.

Answer Question  Select Best Answer

shriram

• Jan 5th, 2018

To add one more point to the disadvantages of packages :
We cannot declare ref cursor in a package.
also if a dependent object is dropped the entire package body is invalid until it is recompiled.

Kavitha

• Nov 19th, 2007

Disadvantages of Triggers    It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.    Triggers execu...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 13th, 2008

---

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. 
It contains only one row and  one column of VARCHAR2 datatype.
Used to refer an object which does not have any pysical reference in database table.
Ex:- Select sysdate from dual.

Aditya Kumar

• Dec 17th, 2017

If it contains only one column with varchar2 data type then how can it return a date through sysdate.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 13th, 2008

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. It contains only one row and  one column of VARCHAR2 datatype.Used to refer an object which does not have any pysical reference in database table.Ex:- Select sysdate from dual.

Improve Answer

What is Global temporary table? and what are the benefits of it?

Prabhat Sahu

 Profile Answers by Prabhat Sahu Questions by Prabhat Sahu

• Oct 24th, 2017

Global Temporary Table(GTT): --------------------------------------- --> GTT operation perform in-memory(RAM) so reading data from GTT is a "Logical Read" -> hence its faster. --> GTT are of 2 type...

vanishavadlya

 Profile Answers by vanishavadlya Questions by vanishavadlya

• Oct 24th, 2009

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for t...

Answer Question  Select Best Answer

How can we hide a package from other users?

Anshul soni

• Sep 27th, 2017

Answer 1: using wrapper utility
Answer 2: using authid definer.

Arundhati

• Apr 20th, 2017

To Hide a object, dont give any grants

Answer Question  Select Best Answer

Create an anonymous PL/SQL block to enroll a student in a particular class. You will use the ENROLLMENTS table. Accept a STU_ID and CLASS_ID as input parameters. Use "today's date" for the ENROLLMENT_DATE and the string "Enrolled" for the STATUS.

Mohana Thangavelu

• Sep 9th, 2017

Begin
Insert into enrollments values(&stud_Id,&class_Id,sysdate);
Commit;
End;

Answer Question  Select Best Answer

Nand Kumar Sahu

• Jul 13th, 2017

Bulk binding is very useful in performance tunning scenarios. BULK COLLECT and FORALL are tow main key of bulk binding. in bulk binding any DML operation is performed in a single execute so context swiching is not going to happening.

Rajan

• Mar 17th, 2017

Within a PL SQL code if there is any DML statement, it involves context switching. Every time there is a context switch, the cost is more. To handle this we have a functionality called bulk binding w...

Answer Question  Select Best Answer