DEV Community: ANSHUL

Synonym Of Oracle - Get Access To DB From Another Schema Easily

ANSHUL — Wed, 13 Jul 2022 01:37:25 +0000

A great example of a use case of synonym in Oracle PLSQL.

Introduction

Suppose you have dedicated DB at one schema for example stores and on the other hand, you have a different schema of employees and some procedures are made which insert into a table from the employees schema but with some reference of store schema. Then we can use synonyms for accessing tables, packages, procedures, functions and more from another schema with some grants to perform specific actions like execute, insert, delete, and more.

Note: You cannot directly access a procedure or function inside a package. Also, you have different grants for packages/procedures/functions and tables.

As I said, we have one schema Store , we created a procedure add_job_history to insert some records in table job_history like below:

create or replace NONEDITIONABLE PROCEDURE add_job_history  
  (  p_emp_id          job_history.employee_id%type  
   , p_start_date      job_history.start_date%type  
   , p_end_date        job_history.end_date%type  
   , p_job_id          job_history.job_id%type  
   , p_department_id   job_history.department_id%type   
   )  
IS  
BEGIN  
  INSERT INTO job_history (employee_id, start_date, end_date,   
                           job_id, department_id)  
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);  
END add_job_history;

Now we have to create another schema employees (if you don’t have one)

create user employees identified by employees;

Grant to create synonym to schema employees :

grant create synonym to employees;

Note: We can grant different grants to the schema as we wish according to the protocol. Please see Oracle documentation.

Verify if we created a different schema using the below command:

select username, account_status from dba_users;

Giving another grant to execute the procedure to schema employees :

grant execute on add_job_history to employees;

If you still face any issues with privileges, then login to SQLPLUS with any user then:

Connect sys/password as sysdba
grant execute on add_job_history to employees;

In the Employees schema, run the below commands:

How to create a synonym to access the store procedure add_job_history

create synonym ajh_syn
for store.add_job_history;

Now you can create a PLSQL program to execute the procedure with ease.

What I learned from multiple websites, I am putting everything here to make it easier for others to work. Please let me know if you have any doubts, I will try my best to give some resolution steps.

Personal Environment Variables

ANSHUL — Tue, 12 Jul 2022 23:44:19 +0000

**

Use
**

An environment variable is a dynamic "object" on a computer, which refers to the system checking some protocols like where to find the particular program, where to save the temporary files and many more. We set up some environment variables while installing the interpreter or compiler for any language like Java, C++, etc. Generally, it is denoted as $PATH(UNIX & LINUX) or PATH(Windows). The cool part of environment variables is we can make our own like any other programming variables and set some values to them. We can do some automation and some really cool stuff.

**

Benefits
**

Easy to use.
It increases efficiency.

Example

If you are working on a project, where you are writing some python automation script and using some specific folder to create logs, save and move the modified file from one directory to another. So, you can set some environment variables for the same and use them in your script.

How to Make it:

export ORA=$ORA /mnt/c/Users/Admin/Oracle

This will set a variable _$ORA _with the value - /mnt/c/Users/Admin/Oracle

How to check the value:

echo $ORA

This will print the value of the $ORA which is /mnt/c/Users/Admin/Oracle

How to use it:

cd $ORA

cd is known as change directory, so the above command changes the directory to its value(your desired path) and goes to the Oracle folder.

How to delete it:

export ORA=${ORA% /mnt/c/Users/Admin/Oracle}

The above command will delete the value from the variable. I have put this command just because I messed up a big-time and I had to research out how to revert back or change the variable values.

So, this is it from my end till now on this topic. If you want to add something please feel free to share. If I got some more updates then I will update the article. Thanks for reading and enjoy my write-up.

Personal Environment Variables

ANSHUL — Tue, 28 Jun 2022 04:46:50 +0000

Use

Benefits

Easy to use.
It increases efficiency.

Example

How to Make it:

export ORA=$ORA /mnt/c/Users/Admin/Oracle

This will set a variable _$ORA _with the value - /mnt/c/Users/Admin/Oracle

How to check the value:

echo $ORA

This will print the value of the $ORA which is /mnt/c/Users/Admin/Oracle

How to use it:

cd $ORA

cd is known as change directory, so the above command changes the directory to its value(your desired path) and goes to the Oracle folder.

How to delete it:

export ORA=${ORA% /mnt/c/Users/Admin/Oracle}

The above command will delete the value from the variable. I have put this command just because I messed up a big-time and I had to research out how to revert back or change the variable values.

Sql Joins And How To Join?

ANSHUL — Thu, 16 Dec 2021 04:11:37 +0000

Hi, You may come here to get a tutorial on Join like different types of join and how to use it, but I wanted to tell you that I will cover that topic in future and you can find very good tutorials elsewhere.

Over here I am going to illustrate different ways to join different tables. My intention for this post is to let programmers know about a few missed topics(maybe not).

Let get into the topic
There are 3 different ways to join tables:

JOIN-ON Keyword
USING Keyword
WHERE Keyword

Yes, there are 3 different ways. I will go one by one with an example.

JOIN-ON

We can join 2 tables using the Join-On keywords. It is considered as the standard way and most common way. In this case, we take two tables and place join in between them and use on later where we have to mention on which columns it is joining. This means where you can find similar records in 2 tables.

NOTE: you can join a table to itself. (Yes, it is possible and it is not a JOKE.)

Example:

SELECT * FROM employees e JOIN department d ON e.department_id = d.department_id;

SELECT * FROM employees e JOIN employees d ON e.employee_id = d.manager_id;

USING

Now we will join tables where the column name is the same in these different tables. This is the smart A$$ and easy feature in Oracle SQL. For instance, employees table and department table both has department_id column.

Example:
SELECT * FROM employees JOIN department USING department_id;

It will print the same as from the first example from JOIN-ON.

WHERE

Simple and Beautiful, here you can join tables using WHERE clause in which you simply mention about the columns which are equal to each other.

Examples:

SELECT * FROM employees e JOIN department d WHERE e.department_id = d.department_id;

SELECT * FROM employees e JOIN employees d WHERE e.employee_id = d.manager_id;

These examples will fetch the same results as the JOIN-ON examples.

Please use relevant example to practice and let me know if you find any other way.

Oracle SQL - Ways to Work on it

ANSHUL — Mon, 01 Nov 2021 03:26:01 +0000

Introduction

SQL, yes and it's not MYSQL. SQL is a Structured Query Language, it is a language that is used to communicate with the database whereas MYSQL is an open-source database product(RDBMS) that allows users to keep the data organized in a database. I will write another article on this topic specifically. Let's move on.

Oracle has different products and one of the famous ones other than JAVA is SQL. Some call it 'S' 'Q' 'L' and some call it 'cequel'. I am the first kind of person.

When I started practicing the SQL, I discussed it with my friends/seniors and they recommended my w3schools.com. I moved to MYSQL Workbench to get some hands-on experience on it but it is slightly different from SQL.

Lets Move Directly on Main Thing

So after a quite research and help from the Oracle Twitter team, I explored many ways to work on it. The best part is all of them are free. They are listed below.

Online Yes, we can practice online using the Oracle Apex tool. It is an online tool where you can do almost everything possible related to SQL.

https://livesql.oracle.com/apex

2.Application I have mentioned that a few things that you cannot do in an online tool but do not restrict yourself and try another great application where you can become a master in SQL. So for that, you have to download the tool called Oracle Database Express Edition(XE). In order to get the latest database, you can download Database 19c or 21c from Oracle official website. Links are as follows.

https://www.oracle.com/ca-en/database/technologies/

Virtualization Another great tool, I like the most(WHY? I will tell you in the end). First of all, I hope you have heard of another Oracle free application VirtualBox, if not then no problem. Now you read it, go search it online. It is really cool.

https://www.oracle.com/database/technologies/databaseappdev-vm.html

Note: It's taking too much space on SSD/HDD so prefer not to save on your System Drive(C Drive).

Final Note I prefer Virtualization as it has many good features and safety. DataBase XE was heavy on memory and occupied a lot of space on the C drive so I had to remove it. Sad Note for MAC users, Database XE will not work for you as well as M1 chip from 2020 as has some issues with virtualization(Please try once on your system, may it works for you). For some reference, please follow this Twitter Thread
https://twitter.com/iamanshultweet/status/1445055709419290633

Please support the community and my Twitter account(https://twitter.com/iamanshultweet) for another awesome article on SQL/PL-SQL.