Hello friends in this video i'm going to demonstrate you how to add virtual column in ORACLE 11g step by step.
Oracle 11g supports virtual columns which are derived from the other columns in the table. You can use the virtual columns in your queries, you can index them, you can collect the statistics, etc.
There are few restrictions on the virtual columns.
You cannot write/insert the data into virtual columns.
There is no support for index_organized, external, object, cluster, temporary tables.
Views: 1374
OCP Technology
This video will show you " Oracle tutorial: What is Virtual column in Oracle PL SQL "
sql calculated field
computed column
oracle-11g-new-feature-virtual-column
Oracle tutorial : What is Virtual column in oracle
Syntax :
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
1)The values of the virtual column are not stored in the database.
2)You can not update the values of virtual column.
3)when you try to modify it will give oracle error.
4)These are read only values.
5)Virtual columns used in the WHERE clause of UPDATE and DELETE statement
6)Constraints can be created on them.
#techquerypond
https://techquerypond.wordpress.com
https://twitter.com/techquerypond
Views: 1424
Tech Query Pond
Views: 473
Kartik Mundhra
How to create Virtual Columns in Oracle Database 12c
When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.
The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;
Querying the table shows the inserted data plus the derived commission-based salaries.
SELECT * FROM employees;
ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOHN DOE 100 5 10 105 110
2 JAYNE DOE 200 10 20 220 240
2 rows selected.
SQL
The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.
COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2)
8 rows selected.
SQL
Notes and restrictions on virtual columns include:
1)Indexes defined against virtual columns are equivalent to function-based indexes.
2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
3)Tables containing virtual columns can still be eligible for result caching.
4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
a)Constraint on the virtual column must be disabled and re-enabled.
b)Indexes on the virtual column must be rebuilt.
c)Materialized views that access the virtual column must be fully refreshed.
d)The result cache must be flushed if cached queries have accessed the virtual column.
e)Table statistics must be regathered.
5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
6)The expression used in the virtual column definition has the following restrictions:
a.It cannot refer to another virtual column by name.
b.It can only refer to columns defined in the same table.
c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Views: 477
OracleDBA
More Details at,
Pseudo Column @ https://www.youtube.com/edit?o=U&video_id=GIGmZwShGh4
Virtual Column @ https://www.youtube.com/edit?o=U&video_id=qUALFDzLDJw
Invisible Column @ https://www.youtube.com/edit?o=U&video_id=nz48CdgDCt4
Unused Column @ https://www.youtube.com/edit?o=U&video_id=GWo4VcNZo6Y
Views: 150
Siva Academy
In part 2 of the JSON Dataguide series, Beda Hammerschmdt, consulting member of technical staff at Oracle, outlines how to auto-genrate views and virtual columns from JSON data.
https://developer.oracle.com/
https://cloud.oracle.com/en_US/tryit
Views: 515
Oracle Developers
Oracle Virtual Column
https://easy-learning-tech.blogspot.in/2018/05/virtual-column-in-oracle.html
Views: 69
Siva Academy
Oracle Virtual Column vs pseudo column
Details on pseudo column @ https://youtu.be/GIGmZwShGh4
Details on Virtual Column @ https://youtu.be/qUALFDzLDJw
Views: 55
Siva Academy
In diesem Video zeige ich euch ein Praxisbeispiel für die Verwendung von Oracle Virtual Columns im Zusammenhang mit einer normierten Textsuche. Dies kann recht hilfreich bei Frontendsuchen - z.B. in APEX - sein.
Ich zeige euch das Beispiel in der Software HORA von der Firma Keeptool. Weitere Informationen zur Software findet ihr in meinem Blog: https://www.besserdich.com/keeptool/
SourceCode unter folgender URL abrufbar:
https://www.besserdich.com/oracle/scripting/oracle-virtual-columns-normierung-und-suche/
Views: 176
Karsten Besserdich
The Best Site to Learn SQL Online
Views: 21515
TechnicalSkills
How to build virtual columns in oracle 11g
Views: 96
Practical World
This is your first stop to get to know Virtual Columns! Quick intro will give you the tools you need to get started! This is the coolest new feature in town, so check it out!
Views: 38
FASTMCSL
Views: 37
Mianhasnain Gulzar
This video shows how to create a computed column in SQL Server database table.
Views: 2840
Gautam Mokal
This is the first video in a series of videos on how to handle JSON data in Oracle 12C. In this video, I'll show you how to create columns in a table that will be used to hold JSON data.
Views: 4846
Pretty Printed
How to column add and delete in Oracle SQL
Views: 71
Tanmun
In this video I create a calculated field based off the data from an existing column. When creating a calculated column you can use addition, subtraction, multiplication, and division. In the video I increased a column by 10 percent, decreased it by 15 percent and divided it in half. The results are not stored in the table, but are created on-the-fly. If you need parenthesis you can use them to change the order of operations in your math calculation. This video is part of a series of videos with the purpose of learning the SQL language. For more information visit Lecture Snippets at http://lecturesnippets.com.
Views: 9423
Lecture Snippets
SQL LTRIM function removes all space characters from the left-hand side of a string or How to use Ltrim Function in Oracle SQL or how to remove spaces using LTRIM Function or How to use LTRIM, RTRIM & TRIM functions step by step.
Assignment link:
https://drive.google.com/open?id=1u4GKcVcBDBdHOmAzHKsfuAuEnPkK5WWe
In this series we cover the following topics:
SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, SubQuery, Set Operator.
follow me on:
Facebook Page:
https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks
Contacts Email:
[email protected]
Instagram:
https://www.instagram.com/equalconnect/
Twitter:
https://twitter.com/LrnWthR
Views: 132
EqualConnect Coach
In this video, I have explained how to add_subtract_multiply the two columns from the particular table.
Comment - Follow - Like : https://www.facebook.com/TechyVickyBlog
https://www.twitter.com/techyvicky
https://www.pinterest.com/techyvicky/techy-vicky-blog
https://plus.google.com/109896475845510580320/
Views: 34345
Vicky's Blog
DURGASOFT is INDIA's No.1 Software Training Center offers
online training on various technologies like JAVA, .NET ,
ANDROID,HADOOP,TESTING TOOLS ,ADF,INFORMATICA,TABLEAU,IPHONE,OBIEE,ANJULAR JS, SAP...
courses from Hyderabad & Bangalore -India with Real Time Experts.
Mail us your requirements to [email protected]
so that our Supporting Team will arrange Demo Sessions.
Ph:Call +91-8885252627,+91-7207212428,+91-7207212427,+91-8096969696.
http://durgasoft.com
http://durgasoftonlinetraining.com
https://www.facebook.com/durgasoftware
http://durgajobs.com
https://www.facebook.com/durgajobsinfo......
Views: 2699
Durga Software Solutions
Speaker(s): Amit Bhattacharya (Bangalore)
Abstract:
Get in depth knowledge of implementation and usage of Generated/Virtual Columns in MySQL(a new feature in MySQL 5.7). * Generated Columns can be used as materialized storage for most used expressions.* It provides a way for supporting indexes for joins with non-relational data(JSON data type) and it gives an option to either store the value of expression or let it be calculated on the fly when the record is read from the table. * Last but not the least we support indexing on these column.
(Type: Talk | Track: Database - MySQL Day | Room: Curie & Hershel (Floor 2))
Event Page: http://2017.fossasia.org
Produced by Engineers.SG
Help us caption & translate this video!
http://amara.org/v/5L1b/
Views: 670
Engineers.SG
https://dbagenesis.com/p/oracle-11g-advanced-dba
DBA Genesis provides all you need to build and manage effective Oracle technology learning. We designed DBA Genesis as a simple to use yet powerful online Oracle learning system for students. Each of our courses is taught by an expert instructor, and every course is available on demand.
Video talks about "what are table clusters inside Oracle database". One of video lessons from our Advanced DBA video course. Have a happy learning!
Views: 3925
DBA Genesis
In this tutorial we'll learn to use the INSERT Query to copy data from one table into another.
Views: 252134
The Bad Tutorials
JSON data is nice, but it is difficult to work with directly in Oracle. By converting it to relational data (rows and columns) we can easily work with it. This video demonstates how to convert JSON data to relational data using json_table.
Views: 5246
Pretty Printed
A computed column or a calculated column in Microsoft SQL server is computed from an expression that can use other columns in the same table.
The expression can be a column name, constant, function, and any combination of these connected by one or more operators.
The computed values are never stored in the tables.
They are actually computed everytime.
Free Business Intelligence Trainings at: http://www.creativecommit.com
Views: 8683
creativecommIT
Held on September 3 2018
Table functions are very cool: they are functions that return collections, and can be called in the FROM clause of a SELECT, as if it were a table or view. In this session, Steven Feuerstein starts with a quick intro to and demos of table functions. We then shift to answering your questions on this useful feature, including (new to 18c) polymorphic table functions and interactions between table functions and the SQL optimizer.
1:54 Whirlwind overview of table functions
9:48 LiveSQL demonstrations
24:42 Q&A: Materialized views and table functions
25:56 Q&A: Recommendations for organizing types needed for table functions
28:44 Introduction and overview of 18c polymorphic table functions
40:30 Table functions and the SQL optimizer
48:17 Table functions and SmartDB
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
https://asktom.oracle.com/
Oracle Developers portal: https://developer.oracle.com/
Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit
music: bensound.com
Views: 966
Oracle Developers
Oracle tutorial : Global Temporary Table In Oracle PL SQL
create temp table sql
This video will explain to you how to create Temporary Tables in Oracle
Oracle tutorial : Global Temporary Table In Oracle
1)The data in such a table is stored only as long as the session or transaction lasts and is private for each session.
2)The definition is visible to all sessions.
3)After commit or disconnection, the data is lost but the definition of the table remains.
4)Indexes created on a temporary.
5)Scope of TRUNCATE command is a single database session. There is no effect of TRUNCATE on other sessions.
6)Foreign key constraints are not applicable in case of Temporary tables
7)Temporary tables cannot contain column of persistent collection type
Syntax :
CREATE GLOBAL TEMPORARY TABLE table_name
( column_name column_data_type
…
…
) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE Emp (
id number(5) NOT NULL,
name varchar2(50) NOT NULL,
city varchar2(50) NOT NULL ) ON COMMIT DELETE ROWS
INSERT INTO Emp VALUES (1, ‘Test1’, ‘city1’);
INSERT INTO Emp VALUES (2, ‘Test2’, ‘city2’);
INSERT INTO Emp VALUES (3, ‘Test3’, ‘city3’);
SELECT COUNT(*) FROM emp;
Result = 3 rows
COMMIT;
SELECT COUNT(*) FROM emp;
Result = 0 rows
For more tutorial please visit #techquerypond
https://techquerypond.com
https://techquerypond.wordpress.com
https://twitter.com/techquerypond
oracle temporary table
create temporary table sql
temp table
Views: 3962
Tech Query Pond
So many new features in Oracle Database 12c allow DBAs and Developers to work magic... Watch this SQL Magic Trick with Chris Saxon, Oracle SQL Developer advocate and member of the AskTOM team
Views: 195
Oracle Developers
You use the update statement in SQL to modify existing values in a row. Normally you have to assign values to each column explicitly. In this video Chris updates the value in one column and also changes the value in another column - without any triggers in sight!
Watch the video to see how it's done!
============================
The Magic of SQL with Chris Saxon
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 6472
The Magic of SQL
How to implement VPD (Virtual Private Database) in Oracle Database(9i,10g,11g,12c) Enterprise Edition.
PART 1 Video
links:
Part 1 https://youtu.be/JVvtvh3cN1I
Part 2 https://youtu.be/xLcin_Wgbvs
Part 3 https://youtu.be/mE1cSbmEwnQ
This is Part of Oracle Advance Security.
Please view my earlier videos on how to create a database , if you are not having a database.
1)First we will create the users needed for our environment
a)Owner of the schema which will have the objects or tables
b)security admin user
c)non owner user1 & user2 which will have limited access as per data in the rows (here user1 will have access to data with HR and user2 with FINANCE)
Object owner creation:
--------------
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
alter user schemaowner quota unlimited on users;
security admin user creation:
--------------
CREATE USER SEC_ADM IDENTIFIED BY sec_adm default tablespace users temporary tablespace TEMP;
GRANT CONNECT, RESOURCE TO sec_adm;
alter user sec_adm quota unlimited on users;
garnts for the sec_adm user:
-------------
GRANT CREATE SESSION,CREATE ANY CONTEXT,CREATE PROCEDURE,CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO SEC_ADM;
GRANT EXECUTE ON DBMS_SESSION TO SEC_ADM;
GRANT EXECUTE ON DBMS_RLS TO SEC_ADM;
create user with restricted access on table:
-----------------------
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user2;
Now we will log in as schemaowner and create one tables :
-------------------
CONN schemaowner/schemaowner
This table is the table which contains the secure as well as un-restricted data
CREATE TABLE user_data
(column1 VARCHAR2(50) NOT NULL,
user_id VARCHAR2(30) NOT NULL);
Now to access this table from other schema we need to give the select and insert privs to:
GRANT SELECT, INSERT ON user_data TO sec_adm,user1, user2;
Views: 892
OracleDBA
Hi guys today we learn about security using VPD in oracle 11g release 2 to hide data from unauthorized users.#VirtualPrivateDatabase #VPD Oracle database Unbeatable,Unbreakable Platform..
Views: 1910
Oracle World
Computed Columns in SQL Server 2017.
Support YPN on Patreon! http://www.patreon.com/YPN
Website and Forum: http://yourprogrammingnetwork.co.uk
Join us on Facebook!
https://www.facebook.com/pages/Your-Programming-Network/280950122022281
Chat on Discord
https://discord.gg/br3ZB3f
Views: 98
YourProgrammingNetwork
This video tells us how to add a column to an existing table
Views: 12011
SAP FREE TRAININGS
Virtual private database, oracle 11g, dbms_session, sys_context, context, dbms_Rls, column masking, row level security, package, function, log on trigger, dr. girija, dr.girija narasimhan, security database,database security
Views: 1014
Dr. Girija Narasimhan
In this video you will learn about Column Alias used in Oracle. I will show you a simple query without column alias and then use a column alias to make it more clear.
For Support
===========
Email: [email protected]
Contact Form: http://www.learninhindi.com/home/contact
Our Social Media
================
Facebook: https://www.facebook.com/LearnInHindi
Twitter: https://twitter.com/LearnInHindi
For Training & Videos
=====================
For more videos and articles visit:
http://www.learninhindi.com
Free Java Programming In Hindi Course
=====================================
https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH
Free Oracle PL/SQL Programming In Hindi Course
==============================================
https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6
Free C Programming In Hindi Course
==================================
https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq
Trips & Tricks Channel
======================
https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw
Programming in Hindi Channel
============================
https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 20170
ITORIAN
a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Views: 4
Koenig Solutions
SQL SELECT INDIVIDUAL FIELDS FROM MULTIPLE TABLES - how to SELECT INDIVIDUAL FIELDS FROM MULTIPLE TABLES
visit Dose for excel Add-In website:
http://www.zbrainsoft.com/
Visit our channel:
https://www.youtube.com/channel/UCI4bMWYzrBTs47KINXL92Kw
Views: 8830
Super Collection Studio
Guide about how to enable in 6 steps the audit trail standard functionality only for a specific table column.
1. AuditTrail:Activate profile
2. Setup Table and column to audit
3. Setup audit group
4. Application (module) is Audit enabled
5. Run AuditTrail Update Tables report to enforce the setup
6. RunAuditTrail Report for Audit Group Validation
Views: 7079
Adrian Argatu
Oracle unused column
https://easy-learning-tech.blogspot.com/2018/05/unused-column-in-oracle.html
Views: 33
Siva Academy
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
By Andrew Gould
https://www.wiseowl.co.uk - Designing basic tables in SQL Server involves deciding which columns the table should include, what the data type of those columns should be (not always an easy choice!) and a few additional properties such as whether the column can be left empty (or NULL) and whether there should be any constraints on the values entered into the column. This video takes you through that process to design and create a simple table to store information about people. The video contains a discussion of the main text, number and date data types in SQL Server - you'll learn the difference between char, varchar, nchar and nvarchar data types, as well as the difference between exact and approximate numerics. The video also covers the basics of inserting and editing data before the final part shows how to automatically generate scripts that will recreate the table and its precious data in the event that you delete it.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Views: 21462
WiseOwlTutorials
Column Alias in Oracle(How to rename the column header in SQL)
Views: 66
Oracle Trainings By Raghavendra Rao
A really quick look at the ability to store JSON in the Oracle Database 12c Release 1 (12.1.0.2) and accessing it using Python and the Oracle_CX open source driver. You can find the Oracle documentation on it here
http://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246
Views: 8397
Dominic Giles
with Arabic content by easy way to learn
شرح بالعربي
Views: 441
Ahmed Samir El Khadrawy
This video shows you how to create an Oracle external table over JSON format data and query the JSON data via the external table using Oracle Big Data SQL.
=================================
To improve the video quality, click the gear icon and set the Quality to
1080p/720p HD.
For more information, see http://www.oracle.com/goto/oll and http://docs.oracle.com
Copyright © 2018 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Views: 231
Oracle Learning Library
Held on September 2018
Beda Hammerschmidt shows how JSON can be be generated form relational tables, for instance to modernize a legacy app. Lots of live demos!
1:40 - 4:00 Introduction to use case: using S JSON form a relational app
4:25-5:05 turning rows into JSON using JSON_OBJECT
6:00 - 6:40 JSON_ArrayAgg to turn multiple rows into one JSON array
10:00-10:45 Generating nested JSON structure using subqueries
15:40-16:30 JSON object with embedded JSON
28:35-30:30 Updating relational columns from JSON payload
34:00 -end Q/A
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
https://asktom.oracle.com/
Oracle Developers portal: https://developer.oracle.com/
Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit
music: bensound.com
Views: 1138
Oracle Developers
Learn how to use Oracle Virtual Private Database (VPD) to secure your data. See all lessons at http://skillbuilders.com/12c-TSDP-Tutorial
First, Virtual Private Database. What is it? Answer: It's a declarative technique for enforcing security. Typically, it's based on user application context. That was the first limitation of 8i. Backed with 8i context were session-specific. The context variables were sort in the PGA. That means you simply couldn't use this in a web environment.
However, global context, with use of 9i, Virtual Private Database became usable for web applications with connection pooling. But whether you're using it to client server or on a three tier environment, it's declarative technique rather than a programmatic technique.
I can go behind the back of the application as a DBA and generate predicates to get appended to all SQLs. Select, insert, update, delete the end users. Issue the SQL and I will generate a predicate that filters the access. It's impossible to bypass. All users will have predicates appended to all their code. It doesn't matter whether they're using SQL Plus, Discoverer, Business Objects, Crystal Reports, eBusiness Suites, whatever they're using, I as DBA can filter their access to roles.
The end result, different users see different data sets while running the same code and accessing the same tables, hence, the name Virtual Private Database. Different users will think they're seeing a totally different set of tables, but in fact it's one column database, one set of tables hosting a single application shared by all the users.
The mechanism - you can apply VPD to tables, to views, to synonyms, and a declarative technique. Once the technique you write a function that generates the predicate, you associate the function with the table. From then on whenever the user issue SQL against that table the Cost-Based Optimizer rewrites the code. The Cost-Based Optimizer rewrites the user SQL to call the function to generate the predicate. The predicate is then appended to the SQL and it's the modified SQL that is parsed and executed.
Let's see how this actually works. I'm working here by the way.
This is just a perfectly normal 12.1 database. But what I'm going to demonstrate at this point will in fact work on 11g as well. I'm not going to be using any of the 12c enhancements to VPD for this little demonstration.
I'll work in the scott schema. What do we have to do? We write a function to generate the predicate string. Create function.
And I'll my function vpdf. The function must conform to a fixed specification. It must take two varchar arguments. One of the arguments is intended to be the schema to which the function will be applied. The other argument is the object within the schema to which the function will be applied. And it must return a varchar2, which is the generated predicate. Let's begin.
Within this function you can do just about anything. There are some limitations. It's impossible for the function to query or update the table against which the VPD calls here is going to be applied or we violate the rules of purity that we come across so often with PL/SQL. But apart from that, it can do just about anything - including a number of dreadful side effects can be introduced by writing VPD functions.
I'm going to do a very simple one on this one. I'll shall simple return immediately a predicate. What predicate? Ename, which is the ename column from this scott.emp table. Ename should equal sys_context.
I'll go to the local context, my user end context, and just extract session user.
We can see that John has tested this earlier today and therefore the object already was suggesting and he's just dropping it now and cleaning up the recreate.
I hope you realize these really are live demonstrations. I'm not just running scripts.
I've created a function. What's that function going to do? It's going to return this value here. Test it. Always test it and see what it's actually going to do. I'll select vpdf. I have to give it a couple of arguments. This one will do for now.
Views: 4507
SkillBuilders
Learn how to connect HR and Sys User of Oracle database 12c With SQL Developer using TNS service. Connect HR user which is in pluggable database to SQL Developer
------------------------------------------------------------------------
►►►LINKS◄◄◄
Blog :
Previous Tutorial
► Tut 61: How To Unlock HR schema in Oracle Database 12c
http://youtu.be/F81gfUnznZY
-------------------------------------------------------------------------
►►►Help Me In Getting A Job◄◄◄
►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company
Thanks
-------------------------------------------------------------------------
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=kb4rc1
--------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
--------------------------------------------------------------------------
Amazon Wishlist: http://bit.ly/wishlist-amazon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on
https://twitter.com/rebellionrider
https://www.facebook.com/imthebhardwaj
http://instagram.com/rebellionrider
https://plus.google.com/+Rebellionrider
http://in.linkedin.com/in/mannbhardwaj/
http://rebellionrider.tumblr.com/
http://www.pinterest.com/rebellionrider/
You can also Email me at
[email protected]
Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 54044
Manish Sharma