Using Ref Cursors To Return Recordsets.

CREATE TABLE STHOLTAB
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE TABLE TEST_CURSOR
(
  BRANCD  VARCHAR2(5 BYTE)                      NOT NULL,
  HOLDTE  DATE                                  NOT NULL,
  HOLDES  VARCHAR2(500 BYTE)                    NOT NULL
)

CREATE OR REPLACE PROCEDURE DPR_CURSOR_RETURN(p_BRANCD    IN  VARCHAR2,
                                              p_recordset OUT SYS_REFCURSOR) AS
BEGIN
  OPEN p_recordset FOR
    SELECT BRANCD, HOLDTE, HOLDES
    FROM   STHOLTAB
    WHERE BRANCD=P_BRANCD
    ORDER BY BRANCD;
END DPR_CURSOR_RETURN;

DECLARE
C_RECORDSET SYS_REFCURSOR;
V_BRANCD VARCHAR2(100);
V_HOLDTE DATE;
V_HOLDES VARCHAR2(1000);
BEGIN
DPR_CURSOR_RETURN('003',C_RECORDSET);

LOOP
FETCH C_RECORDSET  INTO  V_BRANCD, V_HOLDTE, V_HOLDES;

    INSERT INTO TEST_CURSOR VALUES(V_BRANCD, V_HOLDTE, V_HOLDES);
   
    COMMIT;
    EXIT WHEN C_RECORDSET%NOTFOUND;
END LOOP;

END;

Steps to configure Oracle 11g EM DBConsole manually.

Step 1. Drop the existing configuration if you already present sysman user.

emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Sep 22, 2013 4:07:56 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:08:16 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/                                                                                        emca_2013_09_22_16_07_56.log.
Sep 22, 2013 4:08:16 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Sep 22, 2013 4:08:19 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Sep 22, 2013 4:11:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:11:19 PM


Step 2. Create the OEM GRID repository

emca -repos create

STARTED EMCA at Sep 22, 2013 4:12:06 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:12:19 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/emca_2013_09_22_16_12_06.log.
Sep 22, 2013 4:12:19 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 22, 2013 4:18:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:18:57 PM
-bash-4.1$ emca -config dbcontrol db

STARTED EMCA at Sep 22, 2013 4:19:42 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: micrdb
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

Local hostname ................ micrsvr.localdomain
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ micrdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 22, 2013 4:20:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/micrdb/emca_2013_09_22_16_19_42.log.
Sep 22, 2013 4:20:24 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 22, 2013 4:21:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 22, 2013 4:21:17 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Sep 22, 2013 4:21:17 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Sep 22, 2013 4:21:42 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Sep 22, 2013 4:21:42 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 22, 2013 4:22:14 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Sep 22, 2013 4:22:14 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 22, 2013 4:23:31 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 22, 2013 4:23:31 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://micrsvr.localdomain:1158/em <<<<<<<<<<<
Sep 22, 2013 4:23:35 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/micrsvr.localdomain_micrdb/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 22, 2013 4:23:35 PM

Step 4. Configure EM Grid control

emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://micrsvr.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/micrsvr.localdomain_micrdb/sysman/log
 

Step 5. Use EM With URL(https://10.11.201.204:1158/em/)

  Check Your EM Status

emctl status dbconsole

Start Your EM  

emctl start dbconsole

Stop Your EM

emctl stop dbconsole 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action: 
- Check your database service are started.
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener.
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name.

ORA-12518 has been known to occur in Oracle Net Services version.

The reason ORA-12518 is being throw may be because of DEDICATED connections because Oracle 10g has a value of PROCESSES is defaulted at 150 which can be lower than necessary in a production system.

ORA-12518 may be thrown in SHARED SERVER because the dispatcher may have reached the maximum connection value, so it denies all other.   

 ORA-12518, you would need to try increasing the PROCESSES parameter so that it can handle the needed number of processes.  You can ensure that you have the needed value by monitoring the listener log for ORA-12518.  Also, note that because the PROCESSES parameter is static, the database will need to be bounced.  

In shared server mode
 
SQL> alter system set dispatchers='(PROTOCOL=TCP)' ;

System altered.

In Dedicated server mode

SQL> conn /as sysdba
Connected.
SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  452984832 bytes
Fixed Size                  1291120 bytes
Variable Size             167775376 bytes
Database Buffers          276824064 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>

Calculation Method Of Database Session, Processes, Transaction.

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

E.g.
processes=500
sessions=555
transactions=610

sql> alter system set processes=500 scope=both sid='*';
sql> alter system set sessions=555 scope=both sid='*';
sql> alter system set transactions=610 scope=both sid='*';