Friday, November 30, 2007

SQL*Plus and Oracle

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server.

Connect to a Oracle (10g) Database using SQL*Plus

  • From the Start menu find SQL*Plus in oracle home (If you are going to connect from a remote machine you need to have an oracle client installed on it).
  • Give User Name, Password and Host string
    • If you haven’t created specific users, you can log using System or Sys accounts. Password is what you given in the installation. Host String is also asking from you in the configuration just after installation.
    • Anyhow if you forgot the Host String you can find it on the following location. “\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora” Here as I have noticed many times this oracle folder is not in the local drive which your Program Files folder exists. Just check other Local Drives as well. Inside tnsnames.ora file there should be a segment like follows.

      # tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.

      ORCL =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = XE)
      )
      )

    • Here ORCL is the Host String. You can give any name for it as you like. SERVICE_NAME is XE for oracle express edition and orcl for oracle enterprise edition in default.
    • You can find exact value from Registry Editor of server machine in the following location.
    • HKEY_LOCAL_MACHINE – SOFTWARE – ORACLE – SYSMAN – OracleDBConsoleorcl – ORACLE_SID
  • After you connected to server you can get a list of tables using following command.

    • SQL> select * from tab;
  • 3 kinds of commands can be executed from SQL*Plus
    • SQL*Plus commands - used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on
      • SHOW USER
    • SQL commands
      • SELECT * FROM TAB;
    • PL/SQL blocks
      • BEGIN
        DBMS_OUTPUT.PUT_LINE (‘Hello World!’);
        END;
        /

Thursday, November 22, 2007

Development on Oracle 10g Packages, Stored procedures, Functions

An Oracle package is a schema object that groups logically related PL/SQL types, items and subprograms.

Packages have two parts, the specification and the body.

  • The specification is the interface into the package. It describes all the types, variables, procedures, etc within the package.
  • The body has all the implementation. Inside the body you would actually write the procedures, assign the values, and have all the details and logic behind the package.

Sample oracle package

Specification


create or replace PACKAGE ChallengePkg
IS

TYPE ref_cur_type

IS REF CURSOR;


PROCEDURE GetChallenge(ChallengeId IN CHALLENGE.ID%TYPE, challenge OUT ref_cur_type);


PROCEDURE GetChallenges(challenges OUT ref_cur_type);
PROCEDURE InsertChallenge(challengeId OUT CHALLENGE.ID%TYPE,
creationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
publicId IN CHALLENGE.PUBLICID%TYPE,
creationDate IN CHALLENGE.CREATIONDATE%TYPE);


PROCEDURE UpdateChallenge(challengeId IN CHALLENGE.ID%TYPE,
argCreationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
argPublicId IN CHALLENGE.PUBLICID%TYPE,
argCreationDate IN CHALLENGE.CREATIONDATE%TYPE);


PROCEDURE DeleteChallenge(challengeId IN CHALLENGE.ID%TYPE);


END ChallengePkg;


Body

create or replace PACKAGE BODY ChallengePkg
IS

PROCEDURE GetChallenge(ChallengeId IN CHALLENGE.ID%TYPE, challenge OUT ref_cur_type)
IS
BEGIN
OPEN challenge
FOR SELECT CHALLENGE.ID,CHALLENGE.CREATIONUSERID,CHALLENGE.PUBLICID, CHALLENGE.CREATIONDATE
FROM CHALLENGE
WHERE CHALLENGE.ID = ChallengeId;
END GetChallenge;


PROCEDURE GetChallenges(challenges OUT ref_cur_type)
IS

BEGIN
OPEN challenges
FOR SELECT CHALLENGE.ID,CHALLENGE.CREATIONUSERID,CHALLENGE.PUBLICID, CHALLENGE.CREATIONDATE
FROM CHALLENGE;
END GetChallenges;


PROCEDURE InsertChallenge(challengeId OUT CHALLENGE.ID%TYPE,
creationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
publicId IN CHALLENGE.PUBLICID%TYPE,
creationDate IN CHALLENGE.CREATIONDATE%TYPE)

IS
BEGIN
INSERT INTO CHALLENGE(CREATIONUSERID,PUBLICID,CREATIONDATE)
VALUES (creationUserId,publicId,creationDate);
COMMIT;

SELECT CHALLENGE_SEQ.currval INTO challengeId FROM dual;

END InsertChallenge;


PROCEDURE UpdateChallenge(challengeId IN CHALLENGE.ID%TYPE,
argCreationUserId IN CHALLENGE.CREATIONUSERID%TYPE,
argPublicId IN CHALLENGE.PUBLICID%TYPE,
argCreationDate IN CHALLENGE.CREATIONDATE%TYPE)
IS
BEGIN
UPDATE CHALLENGE SET CREATIONUSERID = argCreationUserId, PUBLICID= argPublicId, CREATIONDATE = argCreationDate
WHERE ID = challengeId;
COMMIT;
END UpdateChallenge;


PROCEDURE DeleteChallenge(challengeId IN CHALLENGE.ID%TYPE)
IS
BEGIN
DELETE FROM CHALLENGE
WHERE ID = challengeId;
COMMIT;
END DeleteChallenge;

END ChallengePkg;


Some benefits of using packages

1. Objects don't get invalidated when you make changes to the body. That saves a lot of recompilation and makes changing the implementation much more painless. You will still have to recompile if you change the specification, but that's not something you should be doing very often.

2. You can "overload" subprograms (procedures/functions). You can have several subprograms with the same name, but with a different number of parameters, or different types.

3. You can have persistent variables throughout a session without storing anything in a database table. Packages can have variables and constants that are initialized when the package is first used within a session, and then they are available for the remainder of the session for all future references to anything within that package. That comes in very handy.

4. Speaking of initialization, being able to call a procedure automatically the first time a package is used within a session can also come in very handy.

5. You can take advantage of "encapsulation." In essence, you can hide the implementation details from users but still give them all the information they need to use the package. Since they aren't aware of the details, that means you can change them with minimal impact or risk. Packages also support private subprograms and variables which are available only to other subprograms within the package, and remain completely hidden and inaccessible to anything outside the package.

6. You may notice some performance improvement when using packages. When you first use a package, the entire package may be loaded into memory, meaning fewer disk I/Os as you use the related items within.