Syntax to write a stored procedure in oracle

Create or replace procedure pname

(variable Direction data type, variable direction data type,….)  as number varchar2


Statement;                         create procedure abc



End;àkey word



Sp Ex:-1

A SP for adding two numbers

àopen oracle software

àtype as follows at SQL Prompt

Create or replace procedure add 2(a in number, b in number, c out number)as


Bi direction. Operator



[:=B1-Directional operator]


SP EX:-2

A Procedure for turning the spelling of a given number

Create or replace procedure Get spell(n in number, X out vachar2)as


To_ data(n, ’J’)X

Select  to – char(to-date(n,’J’),’JSP’) in to x

Upper case

From dual;

End;          logical  table which is created and destroyed at runtime

A Query to display all procedure names

Select* from user-object where



Steps to execute a procedure at SQL prompt

Variable a varchar2(100)

Exec Get spell(98765,:a)

Print a;

Copy it to the sql prompt


Calling the stored procedure from c#.NET






Place two textboxes and a button




Using system. data.ole Ob;

Code for button1_click


OleDb connection cn=new oleDb connection

(“Prorider = msdaora.1;user id = scott; password = tiger”);

Cn. Open();

oleDb command cmd= new oleDb command();

cmd. connection=();

cmd. command text=”Get spell”;

Procedure name

cmd. command type= command type stored procedure;

oleDb parameter P1,P2;

P1= cmd. Parameter Add(“a”, oleDb type. Integer);

P1.Direction = Parameter Direction. Input;

P1. Value = Int. parle(text box1. text);

P2= cmd. Parameter Add(“b”, oleDb type. Varchar,100);

P2.Direction = Parameter Direction. out put;

//for out put Parameter value is not allowed.

cmd. Execute non Query();

text box2.text = p2. Value. To string();






Working with data relations




  • Data relation is a class, which is the part of dataset
  • Data relation class, is used to create the relation in between two tables with the belD 001 primary and foreign keys
  • Logical Link .between tow tables is called as relation
  • To create a Relation, fire things are required:-

(a) Parent table

(b) primary key

(c)Child table

(d)Foreign key

(e)Relation name

  • Data grid view control of .NET 3.5 is capable to display only one table at a time

Data grid control of  .NET 1.1 is capable to display more than one table also at a time

  • Data grid control is not available in the tool box by default, hence it need to be added

Screenshot_24 Open WFAP

Screenshot_24Open Tool boxScreenshot_24 right click inside of general tabScreenshot_24 choose ItemsScreenshot_24Select data gridà ok

Screenshot_24Place a button and data grid control on the form using system. Data. oleDb;

Screenshot_24Code for button 1 _click


oleDb connection cn = new oleDb connection

(“user id =scott; pass word = tiger; provider = msdaora.1”);

oleDb Data adapter dl=new oleDb Data Adapter

(“Select*from dept”,cn);

oleDb Data Adapter d2= new oleDb Data Adapter

(“Select*from emp”,cn);

Data set ds=new dataset();


d2.fill(ds,”e”)                     Allas names

Data Column prk = ds. Tables[“d”].Columns[“deptno”];

Data Column fk = ds. Tables[“e”].Columns[“dept no”];

Data relation dr=new data relation(“rel”, prk, fk);

Alias name for the relation



Ds. Relation. Add(dr);




Data gridl. data source=ds;


Data Grid view1.Data source= ds. Tables[“d”];