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.