Stored Procedure

[NET Developer]

Can anyone please help me - I am new to Oracle and trying to learn how to create a simple stored procedure.

I wanted to convert the following MS SQL stored procedure to an Oracle stored procedure - see my attempt below:

MS SQL stored procedure:

CREATE PROCEDURE InsertHelpDesk
(
@EmployeeID int,
@Description nvarchar,
)
AS
INSERT INTO HelpDesk (EmployeeID, Description)
VALUES (@EmployeeID, @Description)

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

Oracle Stored procedure attempt:

CREATE PROCEDURE INSERTHELPDESK
(
: EMPLOYEEID NUMBER;
: DESCRIPTION NVARCHAR;
)
AS
BEGIN
INSERT INTO HELPDESK (EMPLOYEEID, DESCRIPTION)
VALUES (: EMPLOYEEID, : DESCRIPTION)
END;

I keep getting compile errors with the Oracle stored procedure. Can anyone tell me what's wrong or an example of how it should be. I am passing parameter values in from an asp.net web application. Any help much appreciated. Daniel

[PAVB]

Create Procedure Inserthelpdesk
IS
Employeeid Number;
Description varchar2(50);
Begin
Insert 
Into     Helpdesk (employeeid, Description)
           Values (Employeeid, Description)
;
commit
;
End Inserthelpdesk;
/

Next time please post the errors you get.

[NET Developer]

Hi Pav - thanks for the response it was most helpful.
The below stored procedure compiles successfully...

Create Procedure Inserthelpdesk
IS
Employeeid Number;
Description varchar2(50);
Begin
Insert
Into Helpdesk (employeeid, Description)
Values (Employeeid, Description)
;
End Inserthelpdesk;

However... I want to bind data to those variables that I am passing in from an ASP.Net Web application. I assume you bind data to variables using the colon (: ) symbol - see below...

Create Procedure Inserthelpdesk
IS
:Employeeid Number;
:Description varchar2(50);
Begin
Insert
Into Helpdesk (employeeid, Description)
Values (:Employeeid, :Description)
;
End Inserthelpdesk;

However when I do this and try to compile the stored procedure I get the following error message:

Line # = 2 Column # = 1 Error Text = PLS-00049: bad bind variable 'EMPLOYEEID'

Line # = 2 Column # = 1 Error Text = PLS-00103: Encountered the symbol "" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor external language The symbol "pragma was inserted before "" to continue.

Am I using the correct bind symbol and in correct way?

[PAVB]

No, you are not.

Your storedproc shall look something like...

Create Procedure Inserthelpdesk
IS
sql_stmt varchar2(200);
Employeeid Number;
Description varchar2(50);
Begin
Employeeid := 1
Description := 'TEST 1'
sql_stmt := 'INSERT INTO Helpdesk(employeeid, Description) VALUES(:a, :b)';
EXECUTE IMMEDIATE sql_stmt USING Employeeid , Description;
commit;
End Inserthelpdesk;
/

... you are almost there, nothing 20 or 30 hours of reading wouldn't fix.

[slimdave]

Create Procedure Inserthelpdesk
(p_Employeeid Number,
p_Description varchar2)
IS
Begin
Insert 
Into Helpdesk (employeeid, Description)
Values (p_Employeeid, p_Description);
End Inserthelpdesk;
/

syntax not tested

[NET Developer]

Hi Guys,

Thanks for all your help. Much appreciated and very helpful.
Just replacing the ":" with "p_" seemed to do the trick.

[slimdave]

the p_ is just a convention that indicates a parameter -- g_ for globals, l_ for locals as well. It lets you use essentially the same names for variables as you use for column names and makes bind variables a little more visible in SQL.