Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

30 Aug 2012

ORA-01034: Oracle Not Available

It is a simple error meaning your database is down. To fix this, follow the steps given below:

SQL>connect sys/password as sysdba
SQL>startup
SQL>exit

SP2-0110: Cannot create save file "afiedt.buf"

Any query/command (single line or multiple line) we execute resides in the buffer temporarily and it is lost when we execute new query/command because new one takes that place.

In case we try to edit that query/command which is in the buffer we get an error "SP2-0110: Cannot create save file "afiedt.buf"".

There are many ways to fix this, I'm going to show you three different ways.


1. Always run the SQL Command Line as administrator.



Once you start this way, you will not see said error.


2. Sometimes we get this error message because we don't have write permission on the current directory. Either start sqlplus from a different directory, or change the editfile entry in sqlplus:

SQL>set editfile d:/sqledit.sql

In above code, you can use any drive for this and any file name.

3. Create sqledit.sql file and save it in bin directory (sometimes this file misses cause this error) of oracle where sqlplus.exe is resides.Then run sql command line as administrator.

28 May 2012

Oracle Constraints


Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.

The basic structure of an Oracle constraint is defined as:


The CONSTRAINT keyword is followed by a unique constraint name and then the constraint definition. The constraint name is used to manipulate the constraint once the table has been created.


In Oracle, constraints can be defined at the column or table level. An example of defining constraints at table level may be:


CREATE TABLE STUDENT (
STUDENT _ID NUMBER(3) CONSTRAINT S_ID
CHECK (STUDENT _ID > 0),
STUDENT _NAME CHAR(30) CONSTRAINT S_NAME NOT NULL,
MARKS_COUNT NUMBER(6),
CONSTRAINT STUDENT _PRIME PRIMARY KEY (STUDENT _ID))



Column level constraints go directly after the column definition to which they refer and the table level constraints go after the last column definition.


CREATE TABLE CLASS (
ROOM NUMBER(10) CONSTRAINT ID CHECK (ID BETWEEN 1 AND 2000),
SUBJECT VARCHAR2(200) CONSTRAINT S_TITLE NOT NULL,
CODE VARCHAR2(50) CONSTRAINT CODE NOT NULL,
ID NUMBER(8,2) DEFAULT 0.00 DISABLE,
CLASS_DATE DATE,
LAB_DATE DATE,
LECT_TAKEN NUMBER(6),
SUBJECT_ID NUMBER(3),
CONSTRAINT ROOM PRIMARY KEY (ISBN),
CONSTRAINT SUBJECT_SCORE FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID))

Database Not Available Error in Oracle


To fix this error:

(i) Click on Start > Run > Type 'cmd' and press enter

(ii) Now type 'svrmgrl' and press enter

(iii) Type 'connect' and press enter

(iv) Type Username 'internal' and password 'oracle' and press enter

(v) Type 'startup' and press enter

(vi) Now it will take about 1min to process and finally click 'edit' to close cmd

Now start PLSQL, you error is fixed.

Oracle Package


Basically packages is set of procedures, functions, cursors, constraints and exceptions in one unit. To execute the package we write the code as follows:

EXECUTE SALARY_PACKAGE.NEW_WORKER('MICE');

In above example SALARY_PACKAGE is package name and NEW_WORKER is procedure name.

Creating Package

To create package user should have the CREATE PROCEDURE privilege. Two create package two techniques have to be done separetly:

(i) Creating package specification
A package specification includes list of functions, procedures, variables, constraints, 
cursors nd exceptions and will be available for package body.

CREATE OR REPLACE PACKAGE <NAME>
-------
-------

Above code should be saved in saperate file with saperate name.

(ii) Creating package body
The name of package body should be equal to name of package specification but file name should be
different than above file name. 

CREATE OR REPLACE PACKAGE BODY <NAME>
-------
-------

Above code should be saved in saperate file with saperate name. 

Exercise

Exercise to create package specification Type EDIT MPACK and type below code and save it.

CREATE OR REPLACE PACKAGE MICEPACK
AS
FUNCTION NETSALARY
(ID IN SALARY.EMP_NO%TYPE)
RETURN NUMBER;
PROCEDURE TAX
(ID IN SALARY.EMP_NO%TYPE, TAX OUT NUMBER);
PROCEDURE TOTSALES
(CID IN CSTMAST.CSTID%TYPE, SAL OUT NUMBER);
END;

Now create another file type EDIT MPACKB and type the below code and save it.

CREATE OR REPLACE PACKAGE BODY MICEPACK
AS
FUNCTION NETSALARY(ID IN SALARY.EMP_NO%TYPE)
RETURN NUMBER
IS
NETSAL SALARY.BASIC%TYPE;
BEGIN
SELECT SUM(BASIC)+SUM(COMMISSION)-SUM(DEDUCTION)
INTO NETSAL FROM SALARY
WHERE EMP_NO=ID;
RETURN(NETSAL);
END;

Deleting package

To delete the package that is both files specification and body file, use the following code-

DROP PACKAGE <package name>

Viwing all packages, procedures, functions, packages and package body

SELECT * FROM USER_SOURCE;

Oracle Methods


Method is block of PL/SQL code used to encapsulate the data access method for an object, it is also specified as part of the abstract datatype specification as (CREATE OR REPLACE TYPE  MARKS_TY AS OBJECT) and their body declaration as (CREATE OR REPLACE TYPE BODY MARKS_TY AS MEMBER FUNCTION TOTMARKS()). Before to learn methods it is recommanded to get overview the abstract datatype teqniques. 

Creating specification

CREATE OR REPLACE TYPE MARKS_TY AS OBJECT (M1 NUMBER(3), M2 NUMBER(3), M3 NUMBER(3), MEMBER FUNCTION TOTMARKS(M1 IN NUMBER, M2 IN NUMBER, M3 IN NUMBER) RETURN NUMBER);

In above example, TOTMARKS is declaration of function which will be used later in function calling to get defined. 

Defining function (body declaration)

CREATE OR REPLACE TYPE BODY MARKS_TY AS MEMBER FUNCTION TOTMARKS(M1 NUMBER, M2 NUMBER, M3 NUMBER) RETURN NUMBER 
IS 
BEGIN
RETURN(M1+M2+M3);
END;
END;
/

Creating table using above abstract datatype MARKS_TY

CREATE TABLE STDMARKS
(STUDENT_ID NUMBER(4), MARKS MARKS_TY);

Inserting data into table

INSERT INTO STDMARKS VALUES
(1, MARKS_TY(85,84,73));

Selecting data from table

SELECT S.MARKS.TOTMARKS(S.MARKS.M1, S.MARKS.M2, S.MARKS.M3)
FROM STDMARKS S;