-
Stored Procedure
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
-
Code:
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.
Last edited by PAVB; 06-29-2007 at 10:45 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Binding data to Stored Procedure variables
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?
-
No, you are not.
Your storedproc shall look something like...
Code:
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Code:
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
-
Hi Guys,
Thanks for all your help. Much appreciated and very helpful.
Just replacing the ":" with "p_" seemed to do the trick.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|