Brace {} in shell script

- Brace is used for expansion. If several words are separated by commas and surrounded by brace then in case of expansion the word/words before the brace is added to each word/words within brace and the word after the brace is suffixed to each word/words within the brace.

Following is an example of appending "Vision" keyword to each words within brace at first and " always." keyword appends to each words within brace at last.

$ echo 'The '{'Virtual','only'}' Vision. '
The Virtual Vision.  The only Vision.

- With combination of brace and and cat command we can concatenate several files contents into one. Following is an example.

$ cat >file1.txt
one

$ cat >file2.txt
two

$ cat >file3.txt
three

$ cat {file1.txt,file2.txt,file3.txt} >file5.txt

$ cat file5.txt
one
two
three

- We can take facility of brace expansion while move or copy or rename files. For example the following can be used to rename a .txt file to .bak file.

$ cat file5.txt
one
two
three

$ mv file5.{txt,bak}

$ cat file5.bak
one
two
three

- Note that no spaces are allowed within the braces unless the spaces are quoted or escaped.

- With double dot we can use extended brace expansion construction which is a feature introduced in version 3 of Bash.

Example:

$ echo {a..m}
a b c d e f g h i j k l m

$ echo {5..10}
5 6 7 8 9 10


- The {} double curly brackets are a placeholder for output text. For example, curly brackets are placeholder for the path name output by "find".

DELETE_DIR=/home/test/junk
find "$DELETE_DIR" -type f -atime +30 -exec rm {} \;

The above script deletes the files from "/home/test/junk" that have not been accessed in at least 30 days (plus sign ... +30).
where,
-type indicates filetype", and
f = regular file

Thanks & Regards,
Anto Joe Natesh I

Dot (.) in linux and shell script

- Dot(.) is equivalent the source command in shell script and linux command line. From command line we can execute a shell script using dot (.). Within a script, a "source file_name" or ". file_name" loads the file file_name. The command "source" or "." just imports code into the script, appending to the script (it is same like include command in php or #include in c). The net result is the same as if the "sourced" lines of code were physically present in the body of the script. This technique is useful in situations when multiple scripts use a common data file or function library.

Let's assume that my welcome.sh is like below which later included using . within main.sh file.

$ cat >welcome.sh
echo "Welcome to shell script programing"

$ cat >main.sh
. welcome.sh
echo "This is main file"

$ sh main.sh
Welcome to shell script programing
This is main file


- Dot (.) is the part of the hidden file name. A leading dot is the prefix of a "hidden" file.

$ ls
main.sh  newfile  semicolon_test.sh  welcome.sh

$ touch .hidden_file

$ ls
main.sh  newfile  semicolon_test.sh  welcome.sh

$ ls -a
.  ..  .hidden_file  main.sh  newfile  semicolon_test.sh  welcome.sh


In the example ls -a shows the hidden file .hidden_file

- When working with directory, a single dot (.) represents the current working directory, and two dots (..) represent the parent directory.
Example:

$ pwd
/home/test/t_dir

$ cd .

$ pwd
/home/test/t_dir

$ cd ..

$ pwd
/home/test


- While copying files we can use dot (.) to represent current directory.
The following example will copy the file /home/test/f to present working directory.

$ cp /home/test/f .

Thanks & Regards,
Anto Joe Natesh I

Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.


Type of DB Links
There are 3 types of DB links. They are as follows:


1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.


2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.


3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.


Types of Logins:
In dblink we can use 2 types of login. They are as follows:


1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’


2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’


Note: To create the public DBLINK the user must have create public database link system privileges.

Customize Standard Check Format in R12

Refer Metalink Note 562806.1, 579132.1

A comparative analysis between SQL*LOADER and UTL_FILE utility

In implementing new systems we come across problems of importing "alien" data. This may be coming from a legacy system or an on-going system. This data is transported via extract files from the legacy system to the Oracle system. The gateway to Oracle for this data is SQL*Loader and data is loaded into tables via a control script into tables.

Typically, the older systems do not have very normalized data, nor have they been operating with fully implemented database constraints. The lack of constraints over the years in legacy system can lead to bad data that has crept in. Therefore, while bringing external data into oracle system we need a refined set of checks and balances to ensure that we get good data. This requires a lot of programmatic control in the process of data-loading.

The approach applied in case of SQL* Loader is as follows :
   1. Load the data into temporary tables via SQL*Loader via control file and make the data native to ORACLE.
   2. Write a PL/SQL program to do the processing.
   3. Load the data into live tables.

This approach has a lot of dependencies as well as a strong lack of integration of steps and programmatic control. To overcome this, we have analyzed another facility in that has been release Oracle 7.3.x onwards. It is called the UTL_FILE package. With some creative use of this package we can achieve whatever  SQL*LOADER offers and in addition to that do some high level validation and complex data loading. In the following discussion a study of two tools is done.

A BRIEF OVERVIEW OF SQL*Loader:
SQL*Loader is a server utility for loading data from external data files into Oracle database. The basic advantage of using SQL*Loader is for simple loads and fast loading of data. It can load data into myriad data formats, perform elementary filtering, load data into multiple tables, and create one logical record from one or more physical records.

It creates a detailed log file, a bad file that contains rejected records and a discard file to hold the records that are selectively not loaded. The tool is executed from a command line and a username and password and the control file name and location are required to run it.

A BRIEF OVERVIEW OF UTL_FILE:
PL/SQL does not have text file input output capabilities but acquires it via UTL_FILE package. It provides rudimentary utility for reading ( as well as writing) files from within a PL/SQL program. The lines in the file are read sequentially and hence it effects the performance of the program.

The UTL_FILE package can be wrapped around with a PL/SQL program and since this package is integrated with PL/SQL it provides us the tremendous ability for flexing our "programming muscles." Some procedures and functions can be added to this wrapper program that serve as a handy "tool" for doing normal file reading operations. With this approach we can achieve whatever SQL*Loader can do and much more. The security mechanism for UTL_FILE is achieved by defining a parameter in INIT.ora file called utl_file_dir parameter. The directories that UTL_FILE can read from and write to need to have permissions of Oracle
instance owner and the user running the package.

CONCLUSIONS:
The comparative analysis of SQL*Loader and UTL_FILE reveals that these tools are suitable to our environment subject to the conditions of our needs.

If the data load is complex (as is the case in relational databases) UTL_FILE seems to be the tool of choice. This tool does require programmatic effort in terms of writing a wrapper package but the subsequent effort in this direction is greatly reduced once the initial tool kit is built for your environment.

The UTL_FILE tightly integrates the file input with the programmatic control and the data manipulation inside a single PL/SQL unit. There are disadvantages of speed in loading in case of UTL_FILE but these are totally offset by the programmatic control it offers and the integration it brings in.

Thus we find that UTL_FILE tool bridges the gap left by SQL*Loader for complex data loads.

Thanks & Regards,
Anto Joe Natesh

Dual Table

Cool discussion about Dual on asktom

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Tips in Oracle 6i report customizations:

1. Setup client info if views are involved, setup organization context if inventory items are involved.
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on, snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.