0
Answered

Confirm database operation can execute Oracle stored procedure

Adam van Vliet (Product Manager) 5 years ago • updated by anonymous 3 years ago 4

Confirm database operation can execute an Oracle stored procedure. The client stored procedure has two string input parameters and no output.

Download: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-1968077.html
Example data: http://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/Creatingaprocedure.htm

Create documentation page to link back to.

Response to include the following details:

  • Schema name was included (see example below).
  • The stored procedure was granted to the service account, e.g.: grant EXECUTE on "PMUSER"."TESTPROC" to "EBUSER";

Example used:

BEGIN
  PMUSER.TESTPROC();
END;

Notes for getting Oracle VM to work:

  1. Downloaded Oracle Technology Network Developer Day - Hands-on Database Application Development lab
  2. su, then password oracle.
  3. Fix up networking:
    • Assign IP: ifconfig eth0 192.168.x.y
    • Assign gateway: route add default gw 192.168.x.z
    • sudo gedit /etc/sysconfig/network
    • Change HOSTNAME value to a new name, e.g. testoracle.
    • Save, exit.
    • sudo gedit /etc/hosts
    • Add entry for new hostname to resolve to chosen IP, e.g. 192.168.x.y testoracle
    • Add new hostname to IPv4 and IPv6 loopback entries.
    • Save, exit.
  4. Fix up xhost for admin: export DISPLAY=":0"
  5. exit
  6. Fix up xhost: xhost +local:root
  7. su, then password oracle.
  8. netmgr
    • Expand tree and click on LISTENER
    • Find address for localhost, change to machine IP.
    • File > Save network configuration
  9. Stop the Oracle listener: lsnrctl stop
  10. Start the Oracle listener: lsnrctl start