#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0

    How to create stored procedure in Oracle


    Hello,

    I am new to Oracle. I want to retrieve a list of employee names from a given empID. How to create a stored procedure for this query statement
    Thanks

    example:
    select name from employee table
    where empID = 10
    order by name;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Why do you want / need a stored procedure?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    You need to create a (pipelined) table function in order to return a result:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2363
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0

    how to create a stored procedure


    [QUOTE=shammat]You need to create a (pipelined) table function in order to return a result:



    Thanks for your answer. Why can't i create a stored procedure for the select statement
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by newbie28
    Thanks for your answer. Why can't i create a stored procedure for the select statement
    Because procedures don't return a result, only functions do.

    Remember: Oracle is not SQL Server!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0

    how to create a stored procedure


    Originally Posted by shammat
    Because procedures don't return a result, only functions do.

    Remember: Oracle is not SQL Server!
    Hi shammat,

    is it correct? Please see the function created below. How do I execute the function? Thanks,

    create function get_empnames (n_empID in varchar)
    return varchar
    is n_empNames varchar2(20);

    begin
    select names, address
    into n_empNames
    from C.employee
    where empID=n_empID;
    return(n_empNames);
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by newbie28
    is it correct?
    How should I know? You ran the code. Did you get an error?


    How do I execute the function? Thanks
    Like any other function:
    Code:
    SELECT get_empNames(42) 
    FROM dual;
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Apart from any/all mistakes in your code, I believe it would be considered better PL/SQL
    practice to define your names variable as
    Code:
    n_empNames C.employee.names%TYPE;
    I presume, from the way you use it, C is some other schema.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0
    Originally Posted by clivew
    Apart from any/all mistakes in your code, I believe it would be considered better PL/SQL
    practice to define your names variable as
    Code:
    n_empNames C.employee.names%TYPE;
    I presume, from the way you use it, C is some other schema.
    Thanks all your help! my function worked!

    I have another question about how to create a trigger

    I want to create a trigger to insert every new row from table A to table B. I dont know what syntax I have to write. And how to make a trigger fire? Thanks much for your big help!
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I want to create a trigger to insert every new row from table A to table B. I dont know what syntax I have to write. And how to make a trigger fire?
    First, you will generally get better help with a new question by starting a new thread with a relevant title.

    To learn the syntax for creating a trigger and understanding when it fires I think you
    have an obligation to go and read the documentation first.
    Then, if there are things you do not understand, ask about them here.

    Assuming you only want to do what you said, you would need an after insert trigger that does a simple insert query

IMN logo majestic logo threadwatch logo seochat tools logo