Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Stored Procedure

Hybrid View

  1. #1
    Join Date
    Jun 2007
    Posts
    3

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jun 2007
    Posts
    3

    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?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2007
    Posts
    3
    Hi Guys,

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

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width