Friday, October 7, 2011

Oracle Stored Procedure to return multiple records and call it in SQLPLUS

To return multiple records we need a REF CURSOR type variable to assign each record. The following is a script which return a REF CURSOR  as OUT parameter.

--OracleStroredProc.sql

CREATE OR REPLACE PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR;
END Types;
/

-------

CREATE OR REPLACE PROCEDURE getEmployeeInfo(p_recordset1 OUT  Types.cursor_type) AS

BEGIN
    OPEN p_recordset1 FOR
    SELECT empNumber, empName, phone, address FROM Employees;
   
END;

/

We can run the above script in SQLPLUS as below.

SQL> START OracleStroredProc.sql

So procedure will be created by now. To call the procedure we have to create a variable of type REFCURSOR and pass it as a parameter to procedure call. Then we can
print the variable to see the result.


SQL> var a REFCURSOR;
SQL> call getEmployeeInfo(:a);

Call completed.

SQL> print :a;

Executing the Oracle PL/SQL returns a number?

When you execute a PL/SQL script through sqlplus, if it returns a number at each line you press enter for a new line, it is beacuse you have forgot
to include '/' end of the script file for execute.

ex:

SQL> OracleScript.sql
       > 34
       > 35

So at this situation just enter '/'. Script will execute.

SQL> OracleScript.sql
       > 34 /


If you don't want to manually enter '/' here, place a '/' end of the script file. The script in here has a '/' end of the file which tells to execute it.

Oracle change the default date format before insert data

Oracle accepts date in the default format of YYYY-MON-DD. Here month is accepted as three characters of month name. Ex: January-JAN, February: FEB, etc

Now before executing a bulk records to a table with a required date format such as YYYY-MM-DD, we can alter the session to accept date in that format from the following query.

SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD';

Now we can insert records with date represented in YYYY-MM-DD format.

Oracle drop a schema with all the objects (Similar to database drop in MySQL)

Here we have to drop the user who is the owner of the schema objects with CASCADE option which will result in droping user with the all objects created by that user.

SQL> Drop user username CASCADE;

Oracle PL/SQL script to drop a user if not exists

This script will run a query to count the number of users with a specified user name and if the count!=0 then drop user query is executed.


DECLARE
    u_count number;
    user_name VARCHAR2 (50);

    BEGIN

        u_count :=0;
        user_name :='datauser';
           
        SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (user_name);

             IF u_count != 0
             THEN
                 EXECUTE IMMEDIATE ('DROP USER '||user_name||' CASCADE');
              END IF;

              u_count := 0;
       
        EXCEPTION
           WHEN OTHERS
              THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
                     DBMS_OUTPUT.put_line ('   ');

    END;

/



Include the above script in a file .sql (Ex: DropOracleUser.sql). Make sure to replace the username with correct name which is 'datauser' in above script. You can run the above oracle PL/SQL script in sqlPlus as follows.

sql> START DropOracleUser.sql