How to Install and Configure Oracle on Linux

Oracle RDBMS is a full-featured relational database management system from Oracle Corporation. It includes a set of administration tools, and precompilers for most programming languages. This article will cover how to install and configure an Oracle database on Linux.

The installation file for Oracle 8.0.5 Enterprise Edition is named Oracle8051EE_Intel.tgz. It can be downloaded from ftp://technet.oracle.com/. If needed, the glibc patch file glibcpatch.tgz can be downloaded from ftp://ftp.oracle.com/pub/www/otn/linux/.

A multitude of directories are created during the installation process. Some of the more important ones are:

• $ORACLE_HOME/bin contains the executables for the database and administrative software.
• $ORACLE_HOME/rdbms/admin contains the SQL scripts used to create the catalog, and other useful scripts too numerous to cover here. Refer to the Oracle Database Administration Manual for an explanation of the scripts contained in this directory.
• $ORACLE_BASE/admin/SID/bdump contains the alert log for the instance. The name of the alert log is alert_SID.log, where SID is the system identifier for the instance (i.e., alert_greg.log for this installation). This file is extremely important in determining where problems lie. Any time you have an error or database crash, this is the first place to look for information on what might have happened to cause the problem.
• $ORACLE_HOME/network/admin contains the tnsnames.ora and listener.ora files. Both of these files are described in the section on modifying configuration files.
• $ORACLE_HOME/precomp/demo/proc contains several Pro*C example programs.
  
  For More details Check Install and Configure Oracle on Linux

How to set home solaris private network?

Question:

I just purchased 2 Ultra 10 servers and I want to practice with home networking. I want to create a private network where I can connect both boxes to the internet via broadband using my four port adsl modem/router. My ISP assigns me 192.168 addresses via DHCP and I use the ISP's DNS but what I'm interested in is:

1. Configuring my own internal DNS

2. Creating my own internal network with different ip addresses.

I will be adding some windows pc to the network soon but I just need guidance or tips on where to start, ipaddressing, firewalls...routing, setting up internal mail etc. I'm new to this and I'll be glad with all the help I can get. By the way, OS on both boxes is Solaris 10.

How to set ORACLE_HOME Path in Unix and Linux?

ORACLE_HOME refers to either:

1. a directory where the Oracle software is installed; or
2. an environment variable pointing to the directory where the Oracle software is installed. 

Check current value:

echo The current ORACLE_HOME is %ORACLE_HOME%

Set the ORACLE_HOME environment variable:

set ORACLE_HOME=C:\oracle\ora10.2

Unix & Linux

Check current value:

env | grep ORACLE_HOME

Change the ORACLE_HOME environment variable (valid for bash and ksh):

export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

How to Check What Version of Microsoft .NET Framework is Installed using Registry Editor

Method 1 : Using Registry Editor:
f you are familiar with Windows registry editor (by using regedit command), this should be the easier way to find the version of .NET installed.You just have to navigate to the following registry path and check the value present for the key entry “Version” (which is 3.5.30729… in my case) as shown below.
Path: HKEY_LOCAL_MACHINE\ SOFTWARE\ MICROSOFT\ NET Framework Setup\ NDP\ v3.5

Oracle Pseudocoloumns : LEVEL | Oracle PL/SQL Interview Questions

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

 

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

Oracle CURRVAL and NEXTVAL Pseudocoloumns

You can use CURRVAL and NEXTVAL in:

* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view

* The SELECT list of a subquery in an INSERT statement

* The VALUES clause of an INSERT statement

* The SET clause of an UPDATE statement

Restrictions: You cannot use CURRVAL and NEXTVAL:

* A subquery in a DELETE, SELECT, or UPDATE statement

* A query of a view or of a materialized view

* A SELECT statement with the DISTINCT operator

* A SELECT statement with a GROUP BY clause or ORDER BY clause

* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

* The WHERE clause of a SELECT statement

* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

* The condition of a CHECK constraint

Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

Oracle Pseudocolumns : CURRVAL and NEXTVAL

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in sql statements with these pseudocolumns:

CURRVAL

The CURRVAL pseudocolumn returns the current value of a sequence.

NEXTVAL

The NEXTVAL pseudocolumn increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Oracle Pseudocolumns CURRVAL ,NEXTVAL,LEVEL,ROWID,ROWNUM

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

* CURRVAL and NEXTVAL

* LEVEL

* ROWID

* ROWNUM

ROWNUM and ORDER BY in Oracle PL/SQL

From the Oracle7 Server SQL Reference Manual: ROWNUM

Oracle7 assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle7 to use an index to access the data, Oracle7 may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM emp WHERE ROWNUM < 10;

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE tabx SET col1 = ROWNUM;

Note that conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM emp WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row
to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition
false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Sleep function in Oralce PL / SQL

The following small function attempts to obtain a system resource, eg if an index should be created on a "busy" table. You wait a while and tried until the counter has reached the maximum.
CREATE OR REPLACE PROCEDURE Sleep_function IS
  GotIt  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/ 

Where are the default parameters defined NLS in Oracle

SELECT * FROM nls_session_parameters;
SELECT * FROM nls_instance_parameters;
SELECT * FROM nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859
NLS_SORT                       BINARY
NLS_CALENDAR                   GREGORIAN
NLS_RDBMS_VERSION              7.3.4.4.0

Oralce Pseudocolumns ROWNUM |SELECT...where ROWNUM > 1 doesn't work??

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
SET column1 = ROWNUM;

DB2 Packages and Plan

Assume PROG A Calling PROG B, C and PROG C calling D and E, Here how many PACKAGES and PLAN will be created? 

Answer : There will be a DB2 Package for every program and only one Plan

COBOL Interview Questions Part 3

21. How do you come out of an EVALUATE statement?
After the execution of one of the when clauses, the control is automatically passed on to the next sentence after the EVALUATE statement. There is no need of any extra code.

22. In an EVALUATE statement, can I give a complex condition on a when clause?
Yes

23. What is a scope terminator? Give examples.
Scope terminator is used to mark the end of a verb e.g. EVALUATE, END-EVALUATE; IF, END-IF.

24. How do you do in-line PERFORM?
PERFORM ... ...

END PERFORM

25. When would you use in-line perform?
When the body of the perform will not be used in other paragraphs. If the body of the perform is a generic type of code (used from various other places in the program), it would be better to put the code in a separate para and use PERFORM paraname rather than in-line perform.

26. What is the difference between CONTINUE & NEXT SENTENCE ?

CONTINUE is like a null statement (do nothing) , while NEXT SENTENCE transfers control to the next sentence (!!) (A sentence is terminated by a period)

27. What does EXIT do ?
Does nothing ! If used, must be the only sentence within a paragraph.

28. Can I redefine an X(100) field with a field of X(200)?
Yes. Redefines just causes both fields to start at the same location. For example:

01 WS-TOP PIC X(1)

01 WS-TOP-RED REDEFINES WS-TOP PIC X(2).

If you MOVE '12' to WS-TOP-RED,

DISPLAY WS-TOP will show 1 while

DISPLAY WS-TOP-RED will show 12.

29. Can I redefine an X(200) field with a field of X(100) ?
Yes.

30.What do you do to resolve SOC-7 error?
Basically you need to correcting the offending data.

Many times the reason for SOC7 is an un-initialized numeric item. Examine that possibility first.

Many installations provide you a dump for run time abends ( it can be generated also by calling some subroutines or OS services thru assembly language). These dumps provide the offset of the last instruction at which the abend occurred. Examine the compilation output XREF listing to get the verb and the line number of the source code at this offset. Then you can look at the source code to find the bug. To get capture the runtime dumps, you will have to define some datasets (SYSABOUT etc ) in the JCL.

If none of these are helpful, use judgement and DISPLAY to localize the source of error.

Some installtion might have batch program debugging tools. Use them.

COBOL Mainframe Interview Questions Part 2

11. What should be the sorting order for SEARCH ALL?
It can be either ASCENDING or DESCENDING. ASCENDING is default. If you want the search to be done on an array sorted in descending order, then while defining the array, you should give DESCENDING KEY clause. (You must load the table in the specified order).
12. What is binary search?
Search on a sorted array. Compare the item to be searched with the item at the center. If it matches, fine else repeat the process with the left half or the right half depending on where the item lies.
13. My program has an array defined to have 10 items. Due to a bug, I find that even if the program access the 11th item in this array, the program does not abend. What is wrong with it?
Must use compiler option SSRANGE if you want array bounds checking. Default is NOSSRANGE.
14. How do you sort in a COBOL program? Give sort file definition, sort statement syntax and meaning.
Syntax:
SORT file-1 ON ASCENDING/DESCENDING KEY key....
USING file-2
GIVING file-3.
USING can be substituted by INPUT PROCEDURE IS para-1 THRU para-2
GIVING can be substituted by OUTPUT PROCEDURE IS para-1 THRU para-2.
file-1 is the sort workfile and must be described using SD entry in FILE SECTION.
file-2 is the input file for the SORT and must be described using an FD entry in FILE SECTION and SELECT clause in FILE CONTROL.
file-3 is the outfile from the SORT and must be described using an FD entry in FILE SECTION and SELECT clause in FILE CONTROL.
file-1, file-2 & file-3 should not be opened explicitly.
INPUT PROCEDURE is executed before the sort and records must be RELEASEd to the sort work file from the input procedure.
OUTPUT PROCEDURE is executed after all records have been sorted. Records from the sort work file must be RETURNed one at a time to the output procedure.
15. How do you define a sort file in JCL that runs the COBOL program?
Use the SORTWK01, SORTWK02,..... dd names in the step. Number of sort datasets depends on the volume of data being sorted, but a minimum of 3 is required.
16. What are the two ways of doing sorting in a COBOL program? Give the formats.
See question 17.
 
17. Give the format of USING and GIVING in SORT statement. What are the restrictions with it?
See question 16. Restrictions - Cannot massage records, canot select records to be sorted.
18. What is the difference between performing a SECTION and a PARAGRAPH?
Performing a SECTION will cause all the paragraphs that are part of the section, to be performed.
Performing a PARAGRAPH will cause only that paragraph to be performed.
19. What is the use of EVALUATE statement?
Evaluate is like a case statement and can be used to replace nested Ifs. The difference between EVALUATE and case is that no 'break' is required for EVALUATE i.e. control comes out of the EVALUATE as soon as one match is made.
20. What are the different forms of EVALUATE statement?
EVALUATE EVALUATE SQLCODE ALSO FILE-STATUS
WHEN A=B AND C=D WHEN 100 ALSO '00'
imperative stmt imperative stmt
WHEN (D+X)/Y = 4 WHEN -305 ALSO '32'
imperative stmt imperative stmt
WHEN OTHER WHEN OTHER
imperative stmt imperative stmt
END-EVALUATE END-EVALUATE
EVALUATE SQLCODE ALSO A=B EVALUATE SQLCODE ALSO TRUE
WHEN 100 ALSO TRUE WHEN 100 ALSO A=B
imperative stmt imperative stmt
WHEN -305 ALSO FALSE WHEN -305 ALSO (A/C=4)
imperative stmt imperative stmt
END-EVALUATE END-EVALUATE

COBOL Mainframe Interview Questions Part 1

Name the divisions in a COBOL program.
IDENTIFICATION DIVISION, ENVIRONMENT DIVISION, DATA DIVISION, PROCEDURE DIVISION.

1. What are the different data types available in COBOL?
Alpha-numeric (X), alphabetic (A) and numeric (9).

2. What does the INITIALIZE verb do? –
Alphabetic, Alphanumeric fields & alphanumeric edited items are set to SPACES.

Numeric, Numeric edited items set to ZERO.

FILLER , OCCURS DEPENDING ON items left untouched.

3. What is 77 level used for ?
Elementary level item. Cannot be subdivisions of other items (cannot be qualified), nor can they be subdivided themselves.

4. What is 88 level used for ?
For condition names.

5. What is level 66 used for ?
For RENAMES clause.

6. What does the IS NUMERIC clause establish ?
IS NUMERIC can be used on alphanumeric items, signed numeric & packed decimal items and usigned numeric & packed decimal items. IS NUMERIC returns TRUE if the item only consists of 0-9. However, if the item being tested is a signed item, then it may contain 0-9, + and - .

7. What does the IS NUMERIC clause establish ?
IS NUMERIC can be used on alphanumeric items, signed numeric & packed decimal items and usigned numeric & packed decimal items. IS NUMERIC returns TRUE if the item only consists of 0-9. However, if the item being tested is a signed item, then it may contain 0-9, + and - .

8. Can the OCCURS clause be at the 01 level?
No.

9. What is the difference between index and subscript?
Subscript refers to the array occurrence while index is the displacement (in no of bytes) from the beginning of the array. An index can only be modified using PERFORM, SEARCH & SET.

Need to have index for a table in order to use SEARCH, SEARCH ALL.

10. What is the difference between SEARCH and SEARCH ALL?
SEARCH - is a serial search.

SEARCH ALL - is a binary search & the table must be sorted ( ASCENDING/DESCENDING KEY clause to be used & data loaded in this order) before using SEARCH ALL

Difference between dbrm.lib and loadlib in COBOL

Normally when a COBOL-DB2 code is compiled, during pre-compilation process, all the SQL codes embedded in the COBOL code will be extracted and moved into a DBRM. The DBRM will be situated in a system library called DBRM.lib. Once the DBRM is created then binding of the DRBM happens where a package or a plan is created for a particular DBRM or set of DBRM's. After the binding is done. The COBOL code is compiled and the system generated a Object module. After the compilation, link editing takes place where all the sub modules called and copybooks used by this COBOL code will be linked to this Object module, with this a Load module is generated and this is situated in a System library like LOADLIB.LIB.

Mainframes Architecture

Mainframes Architecture

Mainframe vs. Client/Server

In a client/server architecture, multiple computers typically cooperate to do the same task. For example, in Figure 1.1 the application uses a Web server, a database server, and an LDAP server.

Figure 1.1 Client/server architecture

On a mainframe, the same computer does everything. One security package (RACF, in most cases) protects one operating system kernel. Mainframe subsystems do everything else, as you can see in Figure 1.2.

Figure 1.2 Mainframe architecture

That's a little of the "why" of mainframes. Now let's get started with the "how."

Introduction to Mainframe Basics for Security Professionals

1.1 Why Use a Mainframe?

Where do mainframes fit in? The mainframes we use today date back to April 7, 1964, with the announcement of the IBM System/360™. System/360 was a revolutionary step in the development of the computer for many reasons, including these:

• System/360 could do both numerically intensive scientific computing and input/output intensive commercial computing.
• System/360 was a line of upwardly compatible computers that allowed installations to move to more powerful computers without having to rewrite their programs.
• System/360 utilized dedicated computers that managed the input/output operations, which allowed the central processing unit to focus its resources on the application.

These systems were short on memory and did not run nearly as fast as modern computers. For example, some models of the System/360 were run with 32K (yes, K, as in 1,024 bytes) of RAM, which had to accommodate both the application and the operating system. Hardware and software had to be optimized to make the best use of limited resources.

 

Pragma EXECPTION_INIT and its usage ?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

 

     e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

 

Pragma exception_init Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program and handle it accoding to your own way.
It should be declare at the DECLARE section.
example
declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING 100);
begin
select sal in to salaryfrom emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;

difference between explicit cursor and implicit cursor in oracle

When a single insert, delete or update statement is executed within PL/SQL program then oracle creates an implicit cursor for the same, executes the statement, and closes the cursor. You can check the result of execution using SQL%ROWCOUNT function.

Explicit cursors are created programmatically. The cursor type variable is declared and associated with SQL query. The program then opens a cursor, fetches column information into variables or record type variable, and closes cursor after all records are fetched. To check whether cursor is open or not use function SQL%ISOPEN and to check whether there are any records to be fetched from the cursor use function SQL%FOUND.

Difference between a PROCEDURE & FUNCTION in oracle

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.