Create Oracle Stored Procedure example

Following is a basic oracle store procedure example:

CREATE OR REPLACE PROCEDURE procPrintHelloWorld
IS

 usr_id VARCHAR2(20):=' sujan ';
BEGIN
 
  DBMS_OUTPUT.PUT_LINE('Hello World!'|| usr_id);
 
END;
/

To see output on console you need to run following command.

 set serveroutput on size 30000;

Run store procedure:

 EXEC procPrintHelloWorld;

If you get any compilation error, run following command to display it.

SHOW ERRORS

Bind Oracle Listener with SID

Most of the time when we try to access oracle database from outside of localhost using oracle client or JDBC driver , we will get "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor" error. But database can be accessible from local machine . To avoid this problem we need follow this steps.

1. Go to Oracle Net Manager from All Program> Oracle> Configuration and Migration Tool> Oracle Net Manager,
2. In Oracle Net Manager ,change oracle listener host name "Localhost" to database server IP or server full domain name , e.g : 60.84.278.155 or db.acm.com.
3. Then you need to save change by clicking File>Save Network Configuration.
4. Do not forget to add listener port into firewall if your firewall is enable.
5. Next you have to bind your listener with oracle SID. For this you will change listener.ora. You will get this file into Oracle_HOME\NETWOReK\ADMIN . e.g C:\app\Administrator\product\11.2.0\dbhome_2\NETWORK\ADMIN\listener.ora. Add this in your listener.ora

   SID_LIST_LISTENER =
   (SID_LIST =
   (SID_DESC =
   (SID_NAME = orcl)
         (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_2)
   
   )
   )
   
   

6. Restart Listener to effect the change. In command prompt type following command to restart listener.

      
     lsnrctl stop
     lsnrctl start
   

Finally you will be to connect to database from outside of localhost successfully .