Some good question and answers that i got from different oracle forums 

1.I am not able to get much details about the use of STANDBY_ARCHIVE_DEST . Can anyone clarify the same ? Is it the place where archived logs of standby redo logs are stored ? In that case if we have both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST set then whether both of them are used , if so in which cases each destination is used ?

Ans:- If both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n are specified, then STANDBY_ARCHIVE_DEST will be used, however it is sufficient enough to simply use LOG_ARCHIVE_DEST_n.  STANDBY_ARCHIVE_DEST is actually deprecated in 11g.

2. what is the need of standby redo log file??
Ans:- Standby redo logs are necessary for the higher protection levels such as 
Guaranteed, Instant, and Rapid. In these protection modes LGWR from the 
Primary host writes transactions directly to the standby redo logs. 
This enables no data loss solutions and reduces the amount of data loss 
in the event of failure. Standby redo logs are not necessary if you are using 
the delayed protection mode. 

If you configure standby redo on the standby then you should also configure 
standby redo logs on the primary database. Even though the standby redo logs
are not used when the database is running in the primary role, configuring 
the standby redo logs on the primary database is recommended in preparation 
for an eventual switchover operation. 
Standby redo logs must be archived before the data can be applied to the 
standby database. The standby archival operation occurs automatically, even if 
the standby database is not in ARCHIVELOG mode. However, the archiver process 
must be started on the standby database. Note that the use of the archiver 
process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have 
online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.


 3. what is the importance of DBID in rman??

In the case that you need to restore control file or spfile using RMAN , you need to set the DBID first.

4. RMAN Not able to find Controlfile autobackup. what may be the reason?
If Your source database has the controlfile autobackup set to a non-default location , for example
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%d/controlfile_backup_%d_%F';
Set the controlfile autobackup format on the target and try to restore it.

Refer this http://waseemslearning.blogspot.com/2011/03/rman-06563-control-file-or-spfile-must.html

Suppose on the target server, you have copied the controlfile autobackup to location "/u01/autobackup/", then on the target server do as:
RMAN>run
{
set controlfile autobackup format for device type disk to '/u01/autobackup/%F';
restore controlfile from autobackup;
}

Physical vs logical standby database in oracle 

The Data Guard Configuration consists of one Production database and upto nine Standby databases. The production database can be a single instance or RAC database similarly Standby database can be single instance or RAC database. It is also possible to have single instance standby database for a RAC production database.

The standby database is a transactionally consistent copy of the production database. It is created initially from the backup of production database. Once created, the data guard automatically synchronizes the standby.

There are two types of standby database, they are

1. Physical Standby
2. Logical Standby

What is physical standby database?

Physical standby database is physically identical to the primary database. It is block by block copy of the primary images. The archived redo log files are shipped to standby database and applied the archived redo log files on the standby database. So standby database should be always in recovery mode. This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

What is logical standby database?

Logical standby database is logically identical to the primary database. Oracle use the logminer technology to transforms standard archived redo logs(by default) or redo logs(if real-time apply enabled) into SQL statements and applies them to the logical stand by database. A logical standby database can remain open and the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. Oracle9i introduced logical standby database.

Physical standby is different from logical standby:

• Physical standby schema matches exactly the source database.

• Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode.  Upon arrival, the archived redo logs are applied directly to the standby database.

Logical standby is different from physical standby:

• Logical standby database does not have to match the schema structure of the source database.

 • Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete).  This DML is transported and applied to the standby database.

 • Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.

 • Logical standby database can have additional materialized views and indexes added for faster performance.

Configuring Physical standbys offers these benefits:

• An identical physical copy of the primary database

• Disaster recovery and high availability

• High Data protection

• Reduction in primary database workload

• Performance Faster

Configuring Logical standbys offer:

• Simultaneous use for reporting, summations and queries

• Efficient use of standby hardware resources

• Reduction in primary database workload

• Some limitations on the use of certain datatypes

A comparison

S.No	Physical Standby	Logical Standby
1	Identical to the Primary database including the physical organization in the disk	Same logical information but physical organization and the structure of data are different.
2	DG uses Redo Apply technology, which applies redo data using standard recovery techniques.	DG uses SQL Apply, which first transforms the redo data into SQL statements and then executes the statement.
3	Can be used for Backups	Can be opened for reporting.
4	All data types are supported	Not all data types are supported for eg. LONG, NCLOB, LONG RAW, BFILE, XML types are not supported. To see what other datatypes which may not be support in your logical standby database, you can query the view DBA_LOGSTDBY_UNSUPPORTED.
5	Can open in ‘Read only’ but cannot apply logs.	Can open in normal mode and simultaneously apply the logs.
6	No additional objects can be created.	Additional indexes, materialized views can be created

Usually organizations use Logical Standby databases mainly for reporting purposes and not for failover/switchover operations. For failover and switchover they use physical standby database. The reason is maintaining logical standby is almost a full time job, need extensive tuning of log apply services, and over hundreds of patches, the logical is usually 3 to 5 hours behind the live database, thus making it impossible for failover/switchover.

Note:-

Oracle recommends not to apply by DML operations on logical standby tables maintained by SQL Apply. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.

As per Oracle10gR2, Some of the SQL statments will not be shipped from primary database to logical standby database. Some sample SQL statements are......

1. CREATE or ALTER or DROP MATERIALIZED VIEW

2. CREATE or ALTER or DROP MATERIALIZED VIEW LOG

3. ALTER SESSION

4. CREATE PFILE

5. CREATE or DROP DATABASE LINK

what is the use of bind variable in oracle 

Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. 
Note:- oracle perform hard parse only when it does't find the sql hash value on the memory for the statement  it want to execute.
Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.
Consider the following example,

A table is created.
SQL> create table bind_ex (
  2     col_1 number,
  3     col_2 varchar2(10)
  4  );

Table created.

and filled with some values..
SQL> insert into bind_ex values (1, 'one'  );
1 row created.
SQL> insert into bind_ex values (2, 'two'  );
1 row created.
SQL> insert into bind_ex values (3, 'three');
1 row created.

Although the three insert statements do conceptually the same thing, they are not the same statement. This can be verified by examing v$sql ,
SQL> set linesize 100
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )


The line returned is the statement to retrieve the other three. Since these three other statements are different, Oracle had to analyse them first in order to execute them. This is a costly operation and referred to as hard parse.
Since it is (sometimes) desirable to prevent such hard parses, bind variables can be used. In an SQL statements, bind variables are indicated by a colon (:) followed by a name or a number. So, these insert statements rewritten with bind variables will then look like: insert into bind_ex values(:bind_var_1, :bind_var_2).
In PL/SQL, execute immediate can be used for bind variables:


SQL> declare
  2    stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)';
  3  begin
  4    execute immediate stmt using 4, 'four';
  5    execute immediate stmt using 5, 'five';
  6    execute immediate stmt using 6, 'six' ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Again checking v$sql:
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )
insert into bind_ex values(:bind_var_1, :bind_var_2)
declare   stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)'; begi

6 rows selected.

SQL>

The statment returns two new Statements: the PL/SQL block and the one that was (three times) executed within the block. Note:- Thus the use of bind variable will reduce the hard parse and will improve the database performance to a great extend .

More good Links..

http://www.dbforums.com/oracle/1677686-procedure-using-bind-variable.html

http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php

Batch file for taking cold backup and expdp

In many case we need to take the cold backup , am happy to share this batch file that will  take the full database backup and finally it will automatically start the database after cold backup.

1. First create a file name backup.txt and save the following contents,
REM
echo off
echo coldbackup started
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set dayofweek=%%i
set month=%%j
set day=%%k
set year=%%l
set curtime=%time%
set fname=BOOST-%%i-%%j-%%k-%%l
)
md e:\dbbackup\%fname%
set oracle_sid=boost  --> here change your database name
sqlplus -s "sys/maku as sysdba" @e:\coldbackup.sql
echo cold backup completed succesffully.
pause

After saving , rename it as backup.bat .

2.Now create coldbackup.sql as follows ,
set lines 1000
set head off
set term off
set feedback off
spool e:\coldbackup.bat
select 'copy '||name||' e:\dbbackup\%fname%' from v$datafile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$controlfile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$tempfile;
select 'copy '||member||' e:\dbbackup\%fname%' from v$logfile;
spool off
shutdown IMMEDIATE
host e:\coldbackup.bat
startup
host del e:\coldbackup.bat
set term on
set head on
set feedback on
set verify on
EXIT

And keep this file in e:\ folder .

3. Our script is ready , now run the batchfile "backup.bat" . The script will first create a spool file named "coldbackup.bat" and it contain the necessary command to copy the files from  the command line , and  will execute this spooled batch file by host e:\coldbackup.bat after  successful execution we will delete this spooled batch file. We can see the cold backup got generated in e:\dbbackup folder.

A simple batch file for taking date-wise expdp of a schema 

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

set PATH=D:\oracle\product\10.2.0\db_1\BIN;%PATH% --> set your own path 
D:\oracle\product\10.2.0\db_1\BIN\expdp aiwa/aiwa@proddb dumpfile=prod_aiwa_%dtStamp%.dmp logfile=prod_aiwa_%dtStamp%.log directory=DATA_PUMP_DIR schemas=aiwa
pause

Package in oracle with example

A package is a group of procedures, functions, variables and sql statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Thus a package specification lists the functions and procedures in the package, with their call specifications: the arguments and their datatypes. It can also define variables and constants accessible to all the procedures and functions in the package.

Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.

Example.1

1. package specification

SQL> create or replace package circle_area_peri is
  2  function area(r number) return number; ---> function area is declared with datatype.
  3  function perimeter(r number) return number; ---> function perimeter is declared  with datatype,
  4  end;
  5  /
Package created.

SQL>

2. package body

SQL> create or replace package body circle_area_peri is
  2  function area(r number) return number is --> function area is implemented here.
  3  ar number(7,2);
  4  begin
  5  ar := 3.14159*r*r;
  6  return ar;
  7  end;
  8  function perimeter(r number) return number is --> function perimeter is implemented here.
  9  pr number(7,2);
 10  begin
 11  pr := 2*3.14159*r;
 12  return pr;
 13  end;
 14  end;
 15  /

Package body created.

SQL>

For using the package , create sql file as follows ,
ed packagedemo
declare
r number(5,2);
area number(7,2);
perimeter number(7,2);
ar number(7);
pr number(7);
begin
dbms_output.put_line('CIRCLE');
dbms_output.put_line('Enter the radius:');
r := &r;
area := circle_area_peri.area(r);
perimeter := circle_area_peri.perimeter(r);
dbms_output.put_line('Area of the circle is :'||area);
dbms_output.put_line('Perimeter of the circle is :'||perimeter);
end;

Execute the above sql to see how package works,
SQL> @packagedemo
Enter value for r: 10
old  12: r := &r;
new  12: r := 10;
CIRCLE
Enter the radius:
Area of the circle is :314.16
Perimeter of the circle is :62.83

PL/SQL procedure successfully completed.

SQL>

Programming Oracle with PL/SQL - Learn About Procedure and Function in Oracle with example
A PL_SQL Block are broadly divided into two categories that are anonymous block and named block .

Anonymous Block:-
Anonymous Block - These blocks have no name and are generally stored in a host file or entered directly into SQL*Plus and executed just once. Most of my example uses salary table and here also i used this table. The following example shows how anonymous block work with oracle.

SQL> select * from salary;
    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- --------------- ---------------            ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> declare increase  number :=10;
  2  begin
  3  update salary set netsal=netsal*(100+increase)/100;
  4  commit;
  5  end;
  6  /
PL/SQL procedure successfully completed. ---> it is an example for anonymous block

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- ---------------   --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan        HOD                     25000       1500       1200       2250      29700
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      24200
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      24200
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      24200

Named Pl-SQL Block:-
A pl-sql named block is also known as PL-SQL subprogram that can be called anywhere in the pl-sql application and is saved in the data dictionary. 

The same example that we used for anonymous pl-sql block can be used for explaining name pl-sql block.

SQL> create procedure inc_sal(increase number) as
  2  begin
  3  update salary set netsal=netsal*(100+increase)/100;
  4  commit;
  5  end;
  6  /
Procedure created.

SQL> execute inc_sal(12);  ---> calling the procedure

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
       ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      33264
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

PL-SQL subpogram have been devided into two,

1. Procedure
2. Function

Differences between Anonymous Blocks and Subprograms:-

Anonymous blocks	Subprograms
Unnamed PL/SQL blocks	Named PL/SQL blocks
Compiled every time	Compiled only once
Does not store in database	Stores in database
Cannot be invoked by other applications	These are named and therefore can be invoked by other applications
Do not return values	Subprogram called functions must return values
Cannot take parameters	Can take parameters

Procedure:-
A procedure is a block of code that carries out some action. It can, optionally, be defined
with a number of arguments. These arguments are replaced with the actual parameters
given when the procedure is invoked. The arguments can be IN arguments, meaning
that they are used to pass data into the procedure (if you not specify the variable type it will assume as IN type) , or OUT arguments, meaning that they are modified by the procedure and after execution the new values are passed out of the procedure. Arguments can also be IN-OUT, where the one variable serves both purposes. Within a procedure, you can define any number of variables that, unlike the arguments, are private to the procedure. To run a procedure, either call it from within a PL/SQL block or use the interactive EXECUTE command

The order that the variables are passed is important as PL/SQL will assign them to the procedure’s variables in the order that they were passed to the procedure.

POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example:-
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER) 
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax 
GROSS (ECOM => ECO, ESAL => ESA)

Example.1
A simple procedure and calling it from command mode by execute command,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
      ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL>  create or replace procedure  inserttosal (prollno number, pname varchar ,pbpay number)
  2     as
  3     begin
  4     insert into salary(rollno,empname,bpay) values(prollno, pname,pbpay);
  5     commit;
  6     end;
  7     /
Procedure created.

SQL> exec inserttosal(10005,'Rakesh.K',22000); --> proc called by exec command

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000
     10005 Rakesh.K                                     22000 ---> new row inserted by proc
SQL>

Example.2
Here procedure is called from a pl-sql block,

SQL> create  or replace procedure pro_salinsert (prollno IN number,pempname IN varchar,
  2  pdesign In varchar , pbpay IN number ) is
  3  pda number(6,2);
  4  pta number(6,2);
  5  ppf number(6,2);
  6  pnetsal number(8,2);
  7  begin
  8  pda := pbpay*8/100;
  9  pta := pbpay*10/100;
 10  ppf := pbpay*7/100;
 11  pnetsal := pbpay + pda + pta - ppf;
 12  insert into salary values
 13  (prollno,pempname,pdesign,pbpay,pda,pta,ppf,pnetsal);
 14  end;
 15  /
Procedure created.

SQL> set lines 450
SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan       HOD                      25000       1500       1200       2250      33264
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

SQL> ed insert_by_proc
declare
prollno salary.rollno%type;

pempname salary.empname%type;

pdesign salary.design%type;

pbpay salary.bpay%type;

begin

prollno := &prollno;

pempname := '&pempname';

pdesign := '&pdesign';

pbpay := &pbpay;

pro_salinsert (prollno , pempname , pdesign ,pbpay); ---> procedure called here

end;

/

SQL> @insert_by_proc

Enter value for prollno: 10005

old   8: prollno := &prollno;

new   8: prollno := 10005;

Enter value for pempname: Aneesh.K

old   9: pempname := '&pempname';

new   9: pempname := 'Aneesh.K';

Enter value for pdesign: Typist

old  10: pdesign := '&pdesign';

new  10: pdesign := 'Typist';

Enter value for pbpay: 15000

old  11: pbpay := &pbpay;

new  11: pbpay := 15000;

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                    25000       1500       1200       2250      33264

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

     10005 Aneesh.K         Typist                    15000       1200       1500       1050      16650

SQL>

Example.3
In this example, i used OUT variable in a procedure,

SQL> create or replace procedure pro_circlearea ( r in number , area out number ) is
  2  begin
  3  area := 3.14159*r*r;
  4  end;
  5  /
Procedure created.

SQL> create table circle (
  2  radius number(5,2), area number(7,2));
Table created.

SQL> ed circleinsert
declare 
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
pro_circlearea(r => pradius, area => parea); --> here i used  NOTATIONAL parameters
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old   8: pradius :=&pradius;
new   8: pradius :=10;

PL/SQL procedure successfully completed.
SQL> select * from circle;

    RADIUS       AREA
   ---------    ----------
           10      314.16

Function:-

A function is similar in concept to a procedure, but it does not have OUT arguments and cannot be invoked with the EXECUTE command. It returns a single value, with the RETURN statement.Anything that a function can do, a procedure could also do. Functions are generally used to support specific operations: small code blocks, that will be used many times.Procedures are more commonly used to divide code into modules, and may contain long and complex processes.

Example.1
Here i created a function to calculate the area of the circle,


SQL> create or replace function func_circlearea
  2  (r number) return number is
  3  area number(7,2);
  4  begin
  5  area := 3.14159*r*r;
  6  return area;
  7  end;
  8  /
Function created.

SQL> ed circleinsert
declare 
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
parea := func_circlearea(pradius); ---> function is called here
insert into circle values (pradius,parea);
end;
/

SQL> @circleinsert
Enter value for pradius: 10
old   8: pradius :=&pradius;
new   8: pradius :=10;
PL/SQL procedure successfully completed.

SQL> select * from circle;

    RADIUS       AREA
   ----------   ----------
           10       314.16
SQL>
you can also retreive the information by,

SQL> select func_circlearea(11) from dual;

FUNC_CIRCLEAREA(11)
-------------------
             380.13
Note:- It will not insert the records into circle table , but display the return value of the function.