1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to call a database procedure in a select statements ?

Discussion in 'Oracle Forms and Reports' started by aadityainpccs, Jan 7, 2013.

  1. aadityainpccs

    aadityainpccs Active Member

    Messages:
    52
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    New delhi (INDIA)
    Hello,
    Please anybody tell me that how to execute an stored procedure having out parameter in a select statement?
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Do you want to return anything from that procedure?

    Note: - select statement doesn't accept any procedures. If you have any return values you can turn up this procedure to function. By that you can use it in select statement.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Instead Develop one function and call the procedure and take care of function return values.
     
  4. aadityainpccs

    aadityainpccs Active Member

    Messages:
    52
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    New delhi (INDIA)
    Actually,this question is asked to me,anyways I dont know it is possible or not,But there should be at least a provision to call(execute) a stored procedure/function in select statement.
    In stored procedure we can use in out parameter,let some processing and then use it in a select statement.but how?
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    This is an apples/oranges argument. You have yet to explain why you would want to call a procedure from within a SQL statement. Oracle allows calling a function already (either embedded like to_char/to_date or custom)...what exactly would calling a procedure accomplish for you that you CANNOT do from a function?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can call a function from within a select list and you can even use some functions as tables, such as the dbms_xplan functions:

    Code (SQL):
    SELECT *
    FROM TABLE(dbms_xplan.display);
     
    The TABLE operator performs a 'pickler fetch' from the called function which 'cleans up' the output and puts it into a readable form:

    http://dfitzjarrell.wordpress.com/2009/12/10/in-a-pickle/

    You cannot call a procedure from a select statement, however you can execute the procedure into a variable and use the variable in a subsequent SELECT, presuming the output of the procedure is suitable:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE fnorp (pnug IN NUMBER, pnog OUT NUMBER)
      2  IS
      3  BEGIN
      4          pnog := pnug;
      5  END;
      6  /
     
    PROCEDURE created.
     
    SQL>
    SQL> variable norpo NUMBER
    SQL>
    SQL> DECLARE
      2          fneem NUMBER:=1;
      3  BEGIN
      4          fnorp(fneem, :norpo);
      5
      6  END;
      7  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM dba_data_files WHERE file_id = :norpo;
     
    FILE_NAME
    --------------------------------------------------------------------------------
       FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    ONLINE_
    -------
    C:\ORADB\ORADATA\SMEDLEY\SYSTEM01.DBF
             1 SYSTEM                         1887436800     230400 AVAILABLE
               1 YES 3.4360E+10    4194302         1280 1886388224      230272
    SYSTEM

    SQL>
     
    Bharat likes this.