Stay at Home!! Be Safe!! Take Care!!

Thanks all my viewers for your support, I am an Oracle Ace now. For Any queries, please mail us at support@funoracleapps.com

SQLPlus Prompt with glogin.sql and login.sql

No comments

SQLPlus Prompt with glogin.sql and login.sql






When SQL*Plus starts, and after CONNECT commands, the two site profile files are read, and based on the sql commands mention in them the prompt is set.

GLOGIN.sql


It is the global profile and will be used for all the instances running from a particular Oracle Home

Path:  $ORACLE_HOME/sqlplus/admin/glogin.sql

This helps when we have multiple instances running and we want to be sure to connect to the correct database. This will be very useful and can avoid Human errors and help in the customization of prompts.

In my example, I added a prompt which will show me the user name and the SID.


[oracle@ebs122 ~]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set sqlprompt "_user'@'_connect_identifier >"

So now my SQL prompt will look like

[oracle@ebs122 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 24 18:23:25 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@GOLD >



SYS is my user and GOLD is the SID.


Login.sql


User profile script that is intended to allow users to specifically customize their session.

Prior to SQL*Plus version 12.2.0.1.0, SQL*Plus searches for the User Profile in your current directory first, and then the directories you specify with the ORACLE_PATH/SQLPATH environment variable.


From 12.2.0.1.0, SQL*Plus will only search for the User Profile in the directories you specify with the ORACLE_PATH environment variable on Linux and SQLPATH on Windows.

use SQLPATH if you are on Windows, and use ORACLE_PATH if you are on UNIX.

I have mentioned time and timing in my login.sql and for the session exported it and now the when the SQLplus connects it picks up the time from login.sql
[oracle@ebs122 ~]$ export ORACLE_PATH=/home/oracle/Music
[oracle@ebs122 ~]$ cd /home/oracle/Music
[oracle@ebs122 Music]$ cat login.sql
set time on timing on
[oracle@ebs122 Music]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 24 18:32:42 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

18:32:43 SYS@GOLD >



If you like please follow and comment

No comments :

Post a Comment

RMAN-11003: failure during parse/execution of SQL statement: alter system reset db_unique_name scope=spfile

No comments

RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile



While doing RMAN duplicate the process was failing with the below error.

Error:

Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/23/2020 09:54:16
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile
RMAN-11001: Oracle Error:
ORA-32010: cannot find entry to delete in SPFILE
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/23/2020 09:54:06
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'TEST' comment= 'Modified by RMAN duplicate' scope=spfile
RMAN-11001: Oracle Error:
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/23/2020 09:54:16
RMAN-05501: aborting duplication of target database


BackGround:

The duplicate was failing and when we already have dropped TEST database and no database was running with that particular SID. This was quite strange to find why RMAN was failing.

Solution:

After a lot of investigation, I found that the database was dropped but it was still registered in srvctl due to which RMAN was failing.

test--> Is my Oracle database SID

cd $ORACLE_HOME/bin

$ ./srvctl config database -d test
Database unique name: test
Database name: test
Oracle home: /d01/oracle/12.1.0/dbhome
Oracle user: oracle
Spfile: /d01/oracle/12.1.0/dbhome/dbs/spfiletest.ora
Password file:
Domain: lab
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: TEST_DATA
Services: test
OSDBA group: dba
OSOPER group: dba
Database instance: test


./srvctl remove database -d test


Re-run RMAN duplicate 




If you like please follow and comment

No comments :

Post a Comment

How To Enable or Disable Submit Another Request in Oracle Apps

No comments

How To Enable or Disable Submit Another Request

If we want to enable or disable concurrent Submit Another request we can use Profile Option "Concurrent: Show Requests Summary After Each Request" which controls the behaviour of the application after request submission

Sysadmin: Profile> System > Query for “Concurrent: Show Requests Summary After Each Request”

If set to Yes, the View Requests form displayed.

If set to No, the Decision Window displayed


If you like please follow and comment

No comments :

Post a Comment

How to find MAXxxxxxx Control file parameters in Data Dictionary in Oracle

No comments

How to find MAXxxxxxx Control file parameters in Data Dictionary in Oracle

How to  find information about the following control file parameters in the data dictionary:

   - MAXLOGFILES                  
   - MAXDATAFILES
   - MAXLOGHISTORY
   - MAXLOGMEMBERS
   - MAXINSTANCES
 
The values of these parameters are set either during CREATE DATABASE or CREATE CONTROLFILE scripts.

In all Oracle versions, the CREATE CONTROLFILE syntax can be regenerated
from the data dictionary using the below command.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The trace file will be generated in the location of the diagnostics and will contain the current MAX values for the database.

Example:

CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     1168
 LOGFILE
  GROUP   1  SIZE 200 M ,
  GROUP   2  SIZE 200 M ,
  GROUP   3  SIZE 200 M ,
  GROUP   4  SIZE 200 M ,
  GROUP   5  SIZE 200 M


We can also find using below view 

v$controlfile_record_section 
and x$kccdi 


SQL> select TYPE,RECORDS_TOTAL from v$controlfile_record_section;            

TYPE              RECORDS_TO                                                    
----------------- ----------                                                    
REDO LOG                  32      --> MAXLOGFILES                    DATAFILE                  30      --> MAXDATAFILES  
CKPT PROG                  8      --> MAXINSTANCES
REDO THREAD                8      --> MAXINSTANCES                   
LOG HISTORY             3317      --> MAXLOGHISTORY


MAXLOGMEMBERS:
--------------

Only MAXLOGMEMBERS is available via "x$kccdi.dimlm".

Query:
select 'MAXLOGMEMBERS'|| dimlm from x$kccdi;



If you like please follow and comment

No comments :

Post a Comment

Shell script to DROP an Oracle Database

No comments

Shell script to DROP an Oracle Database


Many times when cloning a database we might need to drop the previous database and recreate it using the clone. For this, I am sharing a simple shell script to drop the database easily. You can change the code as per your requirement and customize it.



Note: Using the script is totally at your discretion, or you can use individual steps to drop the database manually. Do not use if you are not sure about Oracle database drop.


cat drop_database.sh

echo "Sourcing the environment"
. .bash_profile
echo "Check the Database Name $ORACLE_SID"
echo "ctrl + c to cancel this script"
sleep 30
sqlplus /nolog <<  EOF
connect / as sysdba
shutdown abort
startup mount exclusive
alter system enable restricted session;
exit
EOF
rman target / <<EOF
drop database NOPROMPT;
exit
EOF
lsnrctl stop $ORACLE_SID
lsnrctl start $ORACLE_SID



If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 11

No comments

LINUX AWK Tutorial Series | Chapter 11

In this post, I am going to share the functions in AWK.

Functions in AWK can be built-in functions and user-defined functions

Built-In Functions: These are pre-defined functions that are already available. We can directly call and use them.

Let's see some of them

sqrt(x): positive square root of a number x
init(x): Truncate x to an integer value for any positive and negative number.
exp(x): Gives exponential of x
rand(): it returns any random value between 0 and 1. But will not return 0 or 1. It will random in one awk execution.
srand(x): Provide seed value of x random function. if x is not present date and time is used as an seed value.
length(x) : Length of particular string
index(s1,s2): Position of a string s2 in larger string s1.
substr(s,a,b): Extract a substring from larger string s. a is the index position to start reading and b is the total characters to be read starting from position a.
system(): Run unix command within AWK



We can see many arithmetic functions, I will not mention all but a few more which you can look out for is log(x), sin(x), cos(X)

Example 1:

Let's perform the basic operations


It's very easy. Try more at Home

Example 2:

Random number generation. If you observe I have used the rand() and srand() function to generate random numbers.

[himanshu@oel7 AWK]$ cat random_funtion
BEGIN{
srand()
print rand()
}
[himanshu@oel7 AWK]$ awk -f random_funtion
0.0376296

Try at Home: If you remove srand from the above script file what will happen.


Example 3:


Using a string function.

[himanshu@oel7 AWK]$ cat string_function
BEGIN{
print length("Himanshu");
print index("Himanshu","man")
}
[himanshu@oel7 AWK]$ awk -f string_function
8
3
Example 4:

Display date using AWK.

[himanshu@oel7 AWK]$ awk 'BEGIN{system("date")}'
Thr Nov 5 07:28:49 IST 2020

Next chapter I will tell how to create user-defined function in AWK.

If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 10

No comments

LINUX AWK Tutorial Series | Chapter 10

Hello and Welcome to Chapter 10 of this series.

In this post, I am going to talk about the Multi-Dimensional Array. Please study previously posted sessions to understand better.

Multi-Dimensional Array is defined by a sequence of multiple indices. So, If I say 2-dimensional array so it would be having 2 indices.

The indices are concatenated with a separator. This separator is a built-in variable as SUBSEP.
Indices are converted as single strings.

example:

fun[1,2]="Value"

Numbers 1 and 2 are converted as strings.

If value of SUBSEP is # , it will set fun["1#2"] to "value"


Default value fo SUBSEP is "\034". It is a not printing character. It will not appear in data.

Example1:

How to declare a 2-dimensional array.

            Col1     Col2
Row1   X           A  
Row2   Y           B


Syntax:

ma is my array name


awk 'BEGIN {ma[1,1]="X";ma[1,2]="A";ma[2,1]="Y";ma[2,2]="B"}'


Example2:

Print an array value


[himanshu@oel7 AWK]$ awk 'BEGIN {ma[1,1]="X";ma[1,2]="A";ma[2,1]="Y";ma[2,2]="B"; print ma[2,1]}'
Y


Example3:

How to find out if an index sequence is present in a Multi-dimensional array.

We will use IN operator, I have explained that in the previous single dimensional array.
I have created an AWK script and executed it. The indexes should be parenthesis and separated by comma(,)

[himanshu@oel7 AWK]$ cat multi_array
BEGIN {
ma[1,1]="X";ma[1,2]="A";ma[2,1]="Y";ma[2,2]="B"; print ma[2,1]
if((2,1) in ma)
print "(2,1) present in MA"
else
print "(2,1) not present in MA"
}
[himanshu@oel7 AWK]$ awk -f multi_array
Y
(2,1) present in MA


Example4:

Create Transpose of a Matrix. We have studied that in Maths. It is created by converting the rows to columns and columns to rows.

AWK script: 

[himanshu@oel7 AWK]$ cat transpose_array
{
     if (max_nf < NF)
          max_nf = NF
     max_nr = NR
     for (x = 1; x <= NF; x++)
          matrix[NR, x] = $x
}

END {
     for (col = 1; col <= max_nf; col++) {
          for (row = 1; row <= max_nr; row++)
               printf("%s ", matrix[row, col])
          printf("\n")
     }
}

The file containing 2D array


[himanshu@oel7 AWK]$ cat 2d-array
1 2 3 4
5 5 7 9
9 10 14 12

Executing AWK now


if you observe array has been transposed.



Split Function in Array

It is used to split the string into pieces. Place various pieces into an array.

Syntax: split(string,arr,sep)

String: String to be chopped
arr: name of the array
sep: separator

Try to understand the below at home now

[himanshu@oel7 AWK]$ cat split_array
BEGIN{
a[1,1]="A";a[1,2]="B";a[2,1]="C"; a[2,2]="D"

for (iterator in a) {
    split(iterator,arr,SUBSEP)
    print arr[1] "," arr[2]
   }
}
[himanshu@oel7 AWK]$ awk -f split_array
2,1
2,2
1,1
1,2

More chapters to follow


If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 9

No comments

LINUX AWK Tutorial Series | Chapter 9

Hello, In this chapter I will continue the concepts of arrays in AWK.

Now I am going share, how are we going to use the arrays with the file. I will use the same file Employee data. I will mostly be using awk scripts in the examples.

Let's start with an example


Example 1: 

We want to calculate the food amount for each employee. That will be calculated based on the salary of the employee. That will be 1% of the total salary.

    
[himanshu@oel7 AWK]$ cat array_food
BEGIN{FS=","}
{
food[$1]=$4*.01
}
END{
for(i in food)
print i "==" food[i]
}

Confused about what for will do here. For loop will execute for each index in the array.




So here the array index would the name of the employees (food[$1]) and calculating the food amount based on the salary which is the 4th field.



Example 2: 

Now I want to delete the array for Robert in output.

Syntax

delete array_name[index]  ==> This will delete one array element
delete array_name[index]  ==> Delete entire array


[himanshu@oel7 AWK]$ cat array_food
BEGIN{FS=","}
{
food[$1]=$4*.01
}
END{
delete food["Robert"]
for(i in food)
print i "==" food[i]
}






Try at Home: With the below awk script please try to execute and see what output we get.

[himanshu@oel7 AWK]$ cat array_food
BEGIN{FS=","}
{
food[$1]=$4*.01
}
END{
delete food
for(i in food)
print i "==" food[i]
}




Example 3:

In earlier chapters, we have discussed built-in variables related to arrays, Lets see them in our scripts.

ARGC: Number of command-line arguments.  
ARGV: Array which stores command line arguments.

[himanshu@oel7 AWK]$ cat builtin_arry_var
BEGIN{
print ("Arguments total" ARGC);
for (i=0;i<=ARGC-1;i++)
print ARGV[i]
}


Here I am using both ARGC and ARGV. The first index at i=0 will take the awk as the first argument. so the total argument here would be 2.






Example 4:

One more built-in variable is ENVIRON. It is also an array of environmental variables.
The array index would be the environment variables name.





Here I am printing value for the environment variable USER using the normal method and using AWK also doing the same.


Hope these would be clear.



More chapters will continue.



If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 8

No comments

LINUX AWK Tutorial Series | Chapter 8

How are you liking the series, please do provide your feedback?

In this chapter, I am going to discuss, arrays in AWK.

Array

It is a collection of related elements. It avoids using multiple variables and use one array in place of it. It can store both numbers and strings.
Array element values are mapped as index and value mapping.
No array declaration is required. No initialization of the number of elements stored in an array is required.

The syntax for assigning array is 

array_name[index]=value

An index can be a number or string. The string has to be enclosed within double quotes("")

Sample file:

[himanshu@oel7 AWK]$ cat array_file
BEGIN
{
month[1]="January"
month["first"]="January"
month[2]="Febuary"
month["second"]="Febuary"
month[12]="December"
month["twelth"]="December"
}

In array, index need not be defined in sequence. Any new index in the array can be added.

If we need to access an array element then use the following

arrayname[index]


Example 1:

Printing a simple array



I am just printing the array value. I have created and used awk scripts.

Now let's say I want to check whether an index has a value defined or not.

Here we will use In operator in an array.

Example 2:



I have mentioned if condition which will match if an index is present in an array. If the index is not present then it will not print any thing.

Example 3:

Check for the value on an index is null or not.



I am matching value as month[11] != "" for  not null.



As index 11 is not defined, it will not print anything. But there is a problem with this method, It will actually create an index 11 with a null value if we are comparing in the above-given method.

That's it preferred to use In operator for checking.

See and understand what was done here.

[himanshu@oel7 AWK]$ cat array_file
BEGIN{
month[1]="January";
month["first"]="January";
month[2]="Febuary";
month["second"]="Febuary";
month[12]="December";
month["twelth"]="December";
print month[12] 
if (2 in month)
print "Index 2 has been defined"
if (12 in month)
print "Index 12 has been defined"
if (month[11] != "" )
print "Index 11 is not null"
if (11  in month)
print "Index 11 is null"
}
[himanshu@oel7 AWK]$ awk -f array_file
December
Index 2 has been defined
Index 12 has been defined
Index 11 is null



More chapters to continue.

If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 7

No comments

LINUX AWK Tutorial Series | Chapter 7

Hello and welcome to AWK tutorial series, Hope till now you would have found the series interesting.

In this chapter, I am going to discuss loops in awk.

Looping

Looping helps to do a particular set of actions repeatedly until a certain condition is met.

Types of Loop in AWK

  • while
  • do .. while
  • for

Syntax

While Loop: until the condition is true the body of the while will be executed.

while (condition)
{
body
}

Do While Loop: Do until the condition is not met. So this means the code body will always be executed once every time.

do
{
body
}
while (condition)

For Loop: Condition and variable initialization is done at the time of the start of the loop.

for (init;condition;increment)
{
body
}


Remember that the loop will be executed on all records one by one.


Some important looping constructs are 

  • break
  • continue
  • exit

Example 1:

From the employee data file, I want to print only 2 fields for each line twice every time. I can use while loop in this scenario.

I am using a script file. i++ is used to increment the value of i with one.

[himanshu@oel7 AWK]$ cat command_file
BEGIN{FS=","}
{
i=1
while(i < 3){
print $1,$2
i++
}
}






Example 2:

Now same first example if I need to do using do-while

[himanshu@oel7 AWK]$ cat command_file
BEGIN{FS=","}
{
i=1
do
{
print $1,$2
i++
}
while(i < 3)
}





Try at home: Now in both the above examples try to change the value of i=3 rather than i=1 and then run both with a while and do-while loop. Observe whats the change. Any doubts, please ask in the comment sections.


Example 3:

Now the same examples if I need to do using for loop.


[himanshu@oel7 AWK]$ cat command_file
BEGIN{FS=","}
{
for (i=1;i<3;i++)
{
print $1,$2
}
}




Tips:

let say now in my file I have some comment lines and I want to skip them. and print other lines. 

Example below, I don't want to print lines which start with "#"


[himanshu@oel7 AWK]$ awk -F "," '{print}' Employee_Data.csv
#This is employee data
Bob,M,Human Resources,30000
Alice,F,Human Resources,45000
Mark,M,Human Resources,30000
Robin,M,Human Resources,20000
Maria,F,Human Resources,30000
Kevin,M,Human Resources,60000
Robert,M,Human Resources,420000
#End of employee data file




^#--> this means records starting with #.
{next} --> Skip that record


More chapters to continue

If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 6

No comments

LINUX AWK Tutorial Series | Chapter 6

Hello Guys.. Welcome

In this series, we are in chapter 6. Now I am going to discuss the conditional statements in AWK.


Now, what are these conditional statements? 

Conditional statements help to process a set of activities based on a certain condition which is defined.

The type of conditional statements which we see across the most programming language are here as well

  • if
  • else
  • else if
Sample syntax of if else

if (conditon true)
then actions;
else if (condition1 true)
then actions;
else
actions


Now let's see how to use them in AWK.


Example 1:

Now let's say I want to give a 10% appraisal to all employees having a salary of less than 30000.
I am going to use my Employee data csv file.




If you understand the previous chapters it would be easy to find what each part is doing. The only addition here is that I have put if (condition) and based on that printing my data.

Try at Home: Try to do the same with AWK  scripts


Example 2:

Now let's say I want to give a 10% appraisal to all employees having a salary of less than 30000 and 5% to all having greater than that.

Here I can make use of if/else blocks.




if and else will get separated by a semicolon(;) in between.


Try at Home: Try to do the same with AWK  scripts

Example 3:

Adding one more condition

 I want to give a 10% appraisal to all employees having a salary of less than 30000 and 5% to all having greater than that and 1 percent to all having more than 200000.

Now here I can make use of if, else if, and else. All three at one time




so I am checking that in 3 parts,
1) salary of less than 30000
2) salary of more than 200000
3) all other salaries

More chapters to continue.

If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 5

No comments

LINUX AWK Tutorial Series | Chapter 5


Hello Welcome... to this series. In this chapter, I am going to discuss the built-in variables in AWK

Built-In Variables


These variables are pre-defined and we can directly use them. Also, remember don't give user-defined variables the same as built-in variables.

Below are the built-in variables available in awk.

RS: record separator in the file being processed. The default value is a new line.
FS: Field separator. The default value is white space.
ORS: output record separator. The default value is a new line.
OFS: Output field separator. The default value is white space.
NR: Number of records processed by awk
NF: Number of fields in the current record
FNR: Current record number in each file. It is incremented each time a new record is read. It will be initialized to 0 once a new file is read
FILENAME: Name of file being read
ARGC: Number of arguments provided at the command line.
ARGV: Array that store command line arguments.
ENVIRON: Array of environnment varaibles and its values.


Remember they are case sensitive and have to be used in Capital only.

In this chapter, I am going to use a new file as well. You can create the same for practice.

random_file.txt

Name
Gender
Dept
Salary

Bob
M
HR
20000

Marlin
F
IT
300000

Peter
F
ADMIN
34455

Rosy
F
HR
78098

Pete
M
IT
89023



In the previous example for Employee Data, the field separator as comma(,) and record separator was a new line.



How to use value of FS variable

By default, the field separator is whitespace(which can be multiple space/tabs)
To overcome this we use -F (explained in earlier chapters)
But now we can use FS along with begin section to use it as a field separator.

Example 1:
In the previous chapter, we used an example to display the total salary of employees. There if you notice, I used "-F".

Now that can be replaced by using the FS built-in variable as below.



FS is used inside Begin in this example as a variable and assigned a value as comma(,).

Example 2:
The question here will arise that if FS is a variable then why only to be used with BEGIN.
See the below example and try to understand what happens when using FS without begin.



In the first awk command, I used FS="," as field separator and printing name and gender. But if you see the output the first line was printed fully from Employee data and then only name and gender were printed. So what AWK did here first it ran print(action) then came to FS to identify the separator and then from the second line the output was correct.

So in these scenarios either you should use BEGIN as it is pre-processing before the main action or use -F option of AWK command.

Example 3:

I want my records output to be separated by "|" then what can we do. 
So here we can use the OFS variable.



Please also note from above that BEGIN can be used standalone as well without END.
In the Begin section, I have defined FS and OFS and then doing an action.

If I need to have a blank line after each record in output then I will use ORS as below. By default, the ORS value is a new line so I have used \n\n twice. 


 
Example 4:

I have created a new random_file. In that file, if you see the fields are separated by a new line and records are separated by 2 new lines.
Now in this scenario, I will use FS and RS variables.


I hope this would be clear to understand. Any doubt, please update in the comments sections.


Example 5:

How we use NR variable




Here NR is printing the line numbers for each record displayed in output.

How to use NF variable.



This will display the total fields present in each record.

Example 6:

I want to print just the last field.
What should I do?? NF is the solution. print $NF and it prints the last fields of each record.




Find the total number of fields is less than 5.



Try at home: Write awk command to print first and last field of each record.


Example 7:


How to print the first 3 lines from a file.



I have used NR<4


Example 8:


How to read the first 3 lines from 2 different files.
Now I should use FNR



If observed, you can see FNR value was reset both times. But NR value will not reset with a new file and it holds a value from one file and increases the counter for the next file.

Example 9:

How to print file name being processed. I will use FILENAME variable. I am showing various methods below.



By now you should be easily understanding the commands used.


Try at Home: Print all employees data who are female and have a salary of more than 1000. Output fields should be separated by "|". Also, print the file name at the last.


The commands are getting bigger now, I am feeling difficult to write on the terminal. Is there any way to make a little easy ???

Yes, Try AWK scripts

AWK Scripts

In awk scripts, I will create a separate file with the awk command and actions and it will be passed to awk using the option "-f".


The command section is the part which we are writing in single quotes.

Example 10:


Here I have created a com.awk file and written the command which I was writing on the terminal and executing it by the following command.

awk -f <command_file> <file_name_for_processing>

No restriction on the command file extension is there.


More chapters will continue.



If you like please follow and comment

No comments :

Post a Comment

LINUX AWK Tutorial Series | Chapter 4

No comments

LINUX AWK Tutorial Series | Chapter 4



Hello Guys, Hope you are enjoying the series. Remember practice will only help you grow and learn. So keep practicing!!

In this post, I am going to discuss the Variables and Operators in AWK command.


Variables and Operators

1) User Define Variables

So let's discuss the User-Defined Variables, As mentioned earlier AWK is a combination of a filtering tool and a programming language. So same as other programming languages it will also support Variables, Constants, operation, loops, etc..

Variable is a representation of a value referring to a variable name.
We will see both built-in and user-defined variables. 
I am going to focus on user-defined variables here.

Important Note:
  • No variable declaration is required in AWK same as shell scripting.
  • Variables will be initialized to null string or zero automatically.
  • Variable should begin with letter and can be followed by letters, numbers, underscores.
  • Variable are case sensitive. So be careful. Example: variable "Him" and variable "him" both will be treated separately.

Example 1:
Defining and variable and printing them




 So what we did here, Any Guesses...

I have defined a variable "a" which is storing a number, so no need for any quotes. (even if you give quotes no impact)
Variable "b" and "c" are storing string so they are enclosed within double-quotes.
All variables are separated by a semicolon(;)
Then I am printing the variables a b and c, but remember I used comma(,) between them so that they can use default delimiter to print the data.
Else the output will be like below. Why--It will work as a concatenation on the variables. Numbers and string will concatenate and will become a string.




Now please note you need to pass a file name as well, If you don't pass the system will keep waiting.
But why we have 7 lines in the output??
Reason: AWK processes each line by line of the file and every time, but does an action for printing only variable values.

Try at home: How to print only one line in this output. Feel free to write your answer in the comments section.

What happens when I only give print rather than print a,b,c. The system will not print variable values and just print normal file contents.


Example 2:

See the below screenshot, Awwww What happened here?




If I am doing an arithmetic operation between a number and string "a+b", then it will treat the variable having a string like "0".So in the output 20+0=20 is displayed.

If both are numbers then no issues, It will perform arithmetic operations. Please see the below screenshot.




2) Operators

In the above example, I used a "+" operator. There are multiple operators that can be used for various different purposes.


  • Arithmetic Operators: +,-,*,/,%,++,--
  • Assignment Operators:=,+=,-=,*=,/=
  • Relational Operators: <,>,>=,<=,!=
  • Logical operators: &&,||,!
  • String Comparison: ~,!~
  • String Concatenate: Blank Space

Example 3:

If we want to find all the employees who are Male and having a salary of less than 25000.


See the above screenshot, what I did.

-F--> used because the delimiter for my fields are comma
$2--> My second field contains the M/F column so matching it with "M"
&&--> Using logical operator for joining 2 conditions.
$4--> This is my salary column in the file which has to be less than  25000
print $1--> Printing the name of the employee as it is stored in field one.


Example 4:

Print the records from the employee file along with the sequence number using user-defined variables.



Explanation:

-F--> used because the delimiter for my fields are comma
++x--> Variable x along with arithmetic operator. As mentioned earlier by default variable initializes with 0 and I am adding 1 each time before printing variable x.
print $0--> print all fields


Example 5:

Compare name which starts with Rob and is Male.



I will not explain all syntax, I hope by now you would have understood all other parts. What new I have used here !!

I used $1~"Rob*" or $1~/Rob*/  --> If you remember // was used for pattern search same can be used or you can use double quotes ""
~ --> Is the string comparison operator.


If I want to print all employee name other than starting with Rob



Example 6:

Let's say if I want to find empty files in my employee data file.
Note: I am adding a few empty lines in my file using vi editor.



Explanation:

^$ --> This will search all patterns where the line is empty.^--> start of the line and $--> end of the line. so together it means no data in between.
x=x+1 --> Defining variable which will increase its value every time. By default, the variable value will be 0.
print x --> print value of x.

The same can be done via the below as well.





But the above output is not neat, I only want to display the total count once.

So that introduces our next concept of Begin and End

Begin and End

Begin Meaning: 

It sets an action on pre-processing. This will be executed first before the main execution takes place of the file processing.

End Meaning:

It sets an action on post-processing. This will be executed after the main execution takes place of the file processing.

These are optional procedures. Not required every time.  BEGIN and END keywords has to be written in Capital letters only.


Example 7: 


The total number of empty lines are displayed.

Begin --> WIll just print the string
/^$/{x=x+1} --> will keep adding each line
END --> Once end is encountered then print x will display the last value of x.


Example 8: 

Based on the same let's find the total salary of all employees.




I suppose this is self-explanatory, Please try to understand, if any doubts, feel free to mention in the comment section.

The next session will continue further


If you like please follow and comment

No comments :

Post a Comment