5 Replies Latest reply: May 2, 2013 10:45 AM by Billy~Verreynne RSS

    Call VB function from a Packages's Stored procedure

    896733
      I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable. How do I do this? I looked at the several means and nothing seems to work. Posting sample code would help a lot. Thanks

      VB Function I need to call
      GetAdvPInfo().User.Usergroup
        • 1. Re: Call VB function from a Packages's Stored procedure
          thomaso
          You can try to deploy .Net code as a Oracle stored procedure.
          Please read:
          http://www.oracle.com/technetwork/articles/dotnet/williams-sps-089817.html

          HTH
          T.
          • 2. Re: Call VB function from a Packages's Stored procedure
            L-MachineGun
            893730 wrote:
            I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable. How do I do this? I looked at the several means and nothing seems to work. Posting sample code would help a lot. Thanks

            VB Function I need to call
            GetAdvPInfo().User.Usergroup
            What is your purpose for calling this function?
            Perhaps Oracle can supply the information you need!
            Have you checked the SYS_CONTEXT() function?
            :p
            • 3. Re: Call VB function from a Packages's Stored procedure
              896733
              The sys_context() function will not work for this situation because a 3rd party web application is connected to oracle and it uses one login for all users. The procedure I want to call is a "hidden one" that the 3rd party has that will retrieve the actual user's data. Unfortunately since the procedure is hidden, the 3rd party provides me no assistance in calling it. Also the document linked was not helpful.

              Any Other Suggestions would be appreciated.
              • 4. Re: Call VB function from a Packages's Stored procedure
                thomaso
                You need to give us more information.
                1. Oracle database version and platform.
                2. 3rd party application location in respect of the database location and some brief architecture.

                Your options are quite limited:
                1. If your database is running on Windows platform you may try to reach 3rd party application using COM or DCOM (if that application exposes this automation).
                http://docs.oracle.com/cd/E18283_01/appdev.112/e10591/ch2insta.htm
                2. if Oracle database engine can reach 3rd party application, and application exposes command line you can use DBMS_SCHEDULER to call external procedure.
                http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sched.htm
                Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files

                Thomas
                • 5. Re: Call VB function from a Packages's Stored procedure
                  Billy~Verreynne
                  893730 wrote:
                  The sys_context() function will not work for this situation because a 3rd party web application is connected to oracle and it uses one login for all users. The procedure I want to call is a "hidden one" that the 3rd party has that will retrieve the actual user's data. Unfortunately since the procedure is hidden, the 3rd party provides me no assistance in calling it.
                  Do not use undocumented interfaces. As simple as that.

                  Besides, let's say you call that VB function from PL/SQL - how on earth will it provide you an answer when called from an Oracle server process, and running as a child process of that Oracle server process? How will it know who/what the web user is when it is not called from inside the app server process servicing that web user?

                  You executing it, on the side, as a brand new process, will run that VB function inside a process environment that is clean and has no web app context - and no link to the actual app server process that called Oracle.