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 Run Stored Procedure (PL/SQL) From Within Discoverer

Discussion in 'Oracle Discoverer' started by abruck, Feb 12, 2009.

  1. abruck

    abruck Guest

    Does anyone know How to Run a Stored Procedure (PL/SQL) From Within Discoverer?

    Thanx!
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    well you can call a pl/sql procedure from Discoverer my importing the procedure into a workbook calculation and then use it in your workbook calculation.

    However as far as i know there is no before_report trigger. So one needs to either use a logon trigger or manually run another worksheet to call the pl/sql procedure and load your GTT.
     
  3. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Hi another way would be to create a function that in turn calls the procedure since one cannot call a procedure direct from within Discoverer. You can make the function return something just like "Success" when its finished.

    Of course the funtion and procedure will need to be imported into Discoverer using the Admin tool to make them available for use. Your function will need to be in an autonomous transaction before it can be called from Discoverer like for example:
    Code (Text):

    CREATE OR REPLACE FUNCTION DISCO_FN
    RETURN VARCHAR2 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    ...
     
  4. ranjithkeral

    ranjithkeral Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Gurgaon
    hi ,

    you can't generate output in discoverer report by using plsql procedure.
    but you can use stored procedures or functions in the discoverer report
    (that may be for calculate a filed value or for using inside a condition. )

    if you want to use a function in folder query then directly you can use it.
    If you need to call a function in Admin Edition or in a Desktop edition then
    you first create a function or procedure in database. then u need to register
    it in discoverer.
    Registering step is very simple.

    > go to tool and register plsql/stored procedure
    > give necessary values and arguments.
    > Apply and Save it.

    Hope I answered ur questions.

    All the best

    Regards

    Ranjith.k
    Polaris
     
  5. Hi Ranjit,

    In discoverer 4.1.37, there is no facility to register a procedure. Actually registering a procedure doent make any sense becuase it doent not return any value.

    But Yes, I can register a function and call a procedure within that function to do my work.

    If you are able to register a procedure please let me know the steps, and I'll be very thankful.

    Ronald.