O Apex

Wednesday, June 29, 2016

UNIX Commands

Shell Commands
=============

ls *.tar.gz*|wc -l # Count

cp -r FILE_NAME/DIRECTORY_PATH # Copy e.g., " cp -r Scripts /c/my_dir/my_proj "

cp -r * FILE_NAME/DIRECTORY_PATH # Copy_all e.g., " cp -r * /c/my_dir/my_proj "

rm *.gz # Remove files

rm -rf DIR_NAME # Remove DIR with sub-contents

cd ../AU    # go directly to AU Folder

ls -d */ OR ls -ltr -d */     # list only directories

find . -maxdepth 1 -type f       # list only files in current directory

find . -maxdepth 1 -type f > shivam.txt # print output in a file

find . -type d -name "HK_SIT" # search for a directory, ".(dot)" means current dir

ls -d HK_*       # show all dir's starting with HK_

tar -xvf filename       # untar files

$?       # returns the status of the last finished command.          Status 0 tells you that everything finished w/o error.

if [ $val -eq $? ]       # $val extract the value that is hold by the val

grep 'abc_xyz' test.txt |cut -d, -f2 # search for the string "abc_xyz" inside          test.txt & fetch 2nd field as ouput i.e, f2

tar -tvf file_name |grep '.xcl' | wc -l # get count without untar

:1,$d       # ':' colon command (moves the cursor                to the bottom), The 1,$ is an indication of which          lines the following command (d) should work on.

:4,$-2d       # leaving only the first 3 and last 2 lines, deleting the rest.

:%d       # delete all text from file

grep 'SG/AU/SIT' *.*       # search string in all files of current directory

echo 'MY_PASSWORD_2016'|base64 # ENCRYPT

echo 'Q1NBX0NUTF9JE1Cg' |base64 -d # DECRYPT

mailx -s "[$value_1|$value_2|$value_3] "TEXT......" $v_grp_name is corrupted" $GEM_XCL_EMAIL <<EOM

How to get more than 4000 character like LISTAGG?

In case we want to list more than 3999 characters in one column-

SELECT TBL1.COL1,
SUBSTR(XMLCAST(XMLAGG(XMLELEMENT(E,' | ' || TBL1.COL2)
ORDER BY TBL1.COL3) AS CLOB),
4) AS COL2
FROM
(
SELECT DISTINCT COL1, COL2
FROM TABLE_1 TB1,
TABLE_2 TB2
WHERE TB1.COL = TB2.COL
) TBL1
WHERE ...
GROUP BY TBL1.COL1

Friday, April 15, 2016

How to generate HTML output from UNIX?

To generate HTML output from UNIX-

printf "<html>\n<head>\n<title>MyDOC - Shivam</title>\n<style>#p1{color:red;}#p2{color:orange;}#p3{color:#cccc00;}</style></head>\n<body>\n<span id="p1">HIGH</span><br />\n<span id="p2">MEDIUM</span><br />\n<span id="p3">LOW</span><br />\n</body></html>" > /cs/csaapp/CSA_APAC_SIT/DataLoad/GEM/SG/AU_SIT/shivam_test/myHTML.html

Friday, September 4, 2015

How to get every first letter from a string ?

SELECT REGEXP_REPLACE('KUMAR SHIVAM MYTHASS','(^| )([^ ])([^ ])*','\2') FIRST_LETTERS FROM DUAL;

FIRST_LETTERS
-------------
KSM

Tuesday, August 4, 2015

How to CREATE USER in Oracle 12C ?

In oracle 12c there is two types of users: common user and local user.

Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

Common Role : The role is present in all containers (root and all PDBs).
Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Common users belong to Container Databases (CDB) as well as current and future Pluggable Databases (PDB). It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

Create Common Users

Create user start with C## and c##, as follows:

CREATE USER c##test_user IDENTIFIED BY password1; -- CONTAINER=ALL;

Create Local Users

ALTER SESSION SET CONTAINER = pdb1;

CREATE USER test_user IDENTIFIED BY password1; -- CONTAINER=CURRENT;

Friday, July 31, 2015

What is INSTR & SUBSTR in Oracle ?

SUBSTR

To extract the substring from any String we use SUBSTR
for e.g., mythass → thass

SUBSTR (STRING, START, LENGTH);

select SUBSTR('mythass', 3, 5) from dual;

------------------------
output: thass

INSTR

To get the position of any string we use INSTR
for e.g., mythass → thass

INSTR (STRING, SUBSTR, BEGIN_with_Nth_character_for_SUBSTR, RETRN_POS_Nth_OCCURENCE_OF_SUBSTR);
/* N IS NUMBER */

SELECT INSTR('mythass','thass', 1, 1) FROM DUAL;
------------------------

output: 3

Some other examples-

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 1) FROM DUAL;

-- starting from the 1st character of substring, for 1st occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 2) FROM DUAL;

-- starting from the 1st character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 2, 2) FROM DUAL;

-- starting from the 2nd character of substring, for 2nd occurence

-- same output as above query because it is getting the same position of OR

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 2) FROM DUAL;

-- starting from the 3rd character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 3) FROM DUAL;

-- starting from the 3rd character of substring, for 3rd occurence

Thursday, July 30, 2015

How to use & When to use Invoker Rights, Definer Rights in Stored Procedures & SQL methods ?

Sub-programs by default i.e., without AUTHID clause are called "Definer Rights" sub-programs.

Sub-programs with AUTHID clause are called "Invoker Rights" sub-programs.

How to use? Let see-

Assume you have two Schemas - MySchema_1, MySchema_2.

Both the Schemas are having table called EMP. Now, create a standalone procedure in MySchema_1.

CREATE PROCEDURE emp_details (

p_emp_no NUMBER

,p_emp_name VARCHAR2

,p_emp_email VARCHAR2) AS

BEGIN

UDPATE EMP

SET emp_email = p_emp_email

WHERE emp_no = p_emp_no;

END;

The above written is a "Definer Rights" sub-program.

Assume that user MySchema_1 has granted the EXECUTE privilege on this procedure to user MySchema_2.

This will execute with the privileges of their owner (MySchema_1), not their current user (MySchema_2). So, it will update the EMP table of MySchema_1.

One way is to fully qualify references to the objects, as in

INSERT INTO MySchema_1.EMP...

CREATE PROCEDURE emp_details (

p_emp_no NUMBER

,p_emp_name VARCHAR2

,p_emp_email VARCHAR2) AUTHID CURRENT_USER AS

BEGIN

UDPATE EMP

SET emp_email = p_emp_email

WHERE emp_no = p_emp_no;

END;

The above written is an "Invoker Rights" sub-program.

Such invoker-rights subprograms are not bound to a particular schema.

When to use? Let see-

Invoker-rights subprograms let you reuse code and centralize application logic.

They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.

e.g.,

Consider a company that uses a definer-rights (DR) procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, the procedure must also reside at each regional site. This causes a maintenance problem.

To solve the problem, the company installs an invoker-rights (IR) version of procedure analyze at headquarters. Now, all regional sites can use the same procedure to query their own sales tables.

To restrict access to sensitive data, you can have an invoker-rights subprogram call a definer-rights subprogram. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table.

That presents a problem because current users of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. The solution is to have procedure analyze call definer-rights procedure calc_comm, which in turn updates the payroll table.