Posts

CURRENT_SCHEMA Approach

This method uses the CURRENT_SCHEMA session attribute to automatically point application users to the correct schema. First, we create the schema owner and an application user. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT TO app_user; Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects. Next, we create some roles to allow read-write and read-only access. CREATE ROLE schema_rw_role; CREATE ROLE schema_ro_role; We want to give our application user read-wr...

Synonym Approach

This method relies on synonyms owned by the application user to point to the correct location of the schema objects. First, we create the users in a similar way to the previous example. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, CREATE SYNONYM TO app_user; Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms. Next, we create some roles to allow read-write and read-only access and grant the re...

Combine more than one Row in One Row

Working On Oracle Sample Database Tables ( Departments and Employees) 1- Suppose we want to select employees Names in each department Query : SELECT departments.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY departments.department_id;   Results : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein 20 Marketing Pat Fay 30 Purchasing Den Raphaely 30 Purchasing Karen Colmenares 30 Purchasing Alexander Khoo 30 Purchasing Shelli Baida 30 Purchasing Sigal Tobias 30 Purchasing Guy Himuro 40 Human Resources Susan Mavris 50 Shipping Donald OConnell ................................................................. we want to know how we can make the results like : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein, Pat Fay 30 Purchasing Alexander Khoo, Den Raphaely, Guy Himuro, Karen Colmenares, Shelli Baid...

How to Select Random using Oracle

Working on Employees Table : Query : select * from (select * from Employees ORDER BY dbms_random.value ) where rownum <= 10 Results : 143 Randall Matos RMATOS 147 Alberto Errazuriz AERRAZUR 152 Peter Hall PHALL 135 Ki Gee KGEE 193 Britney Everett BEVERETT 122 Payam Kaufling PKAUFLIN 130 Mozhe Atkinson MATKINSO 181 Jean Fleaur JFLEAUR 110 John Chen JCHEN 105 David Austin DAUSTIN Every time you will run same Query you will have new results i wish this can help you in your Development Process Thanks,

SQL Interview Questions

You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin? I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless remarkably effective ways of rating a candidate's comfort-level in SQL, whereas others just wasted precious interview time. Let me save you the latter! First I ask the candidate how they would personally rate their own understanding of SQL. I'm also curious what databases and tools they've used to write their SQL. This gives me a good idea where I should begin my questioning. Basic Questions Asking a question about joins is the common consensus for an opening question. More specifically, does the candidate know the different types of joins, or at least the differenc...