Home
Search results “Deterministic oracle functions”
Efficient Function Calls From SQL (Part 2) : The DETERMINISTIC Hint
 
03:05
The second part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at the DETERMINISTIC hint. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#deterministic-hint Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Dana Singleterry : Blog: https://blogs.oracle.com/dana/ Twitter: https://twitter.com/dsingleterry Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2288 ORACLE-BASE.com
Efficient Function Calls From SQL (Part 5) : Pipelined Table Functions
 
03:48
The fifth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we compare the performance of conventions table functions with pipelined table functions. For more information see: https://oracle-base.com/articles/misc/pipelined-table-functions https://oracle-base.com/articles/misc/efficient-function-calls-from-sql Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Mike Dietrich : Blog: https://blogs.oracle.com/UPGRADE Twitter: https://twitter.com/MikeDietrichDE Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 11515 ORACLE-BASE.com
Oracle Function Result Cache | Steven Feuerstein
 
02:35
Returning for his seventh 2 Minute Tech Tip, Oracle architect and "Oracle PL/SQL Programming 6e" author Steven Feuerstein delivers the details on the Function Result Cache, a feature introduced in Oracle Database 11g that can add a performance boost to your applications.
Oracle CAST Function
 
03:23
https://www.databasestar.com/oracle-cast/ The Oracle CAST function allows you to convert one data type to another. It’s similar to the TO_NUMBER, TO_DATE, and TO_CHAR functions, but allows for more functionality, including converting to many other data types. The syntax of the CAST function is: CAST( {expr | MULTISET (subquery) } AS type_name ) The structure of this function is different to other functions, because it uses the word AS to separate parameters rather than a comma. You can run this function like many other functions, on a single row, which would look like this: CAST( expr AS type_name ) Or, you can run it as though it’s a subquery, using the MULTISET feature: CAST( MULTISET (subquery) AS type_name ) The parameters of the CAST function are: - expr (mandatory choice): This is the value or expression that is to be converted to another data type. - MULTISET (optional): This keyword indicates that the subquery results will return multiple rows, and are then CAST into a collection value. If this is omitted, and the subquery returns multiple rows, it will only return a single row. - subquery (mandatory choice): This is the subquery that can be run to be converted into a collection data type. - type_name (mandatory): This is the Oracle data type that the expr or subquery will be converted to. There are several data types that are compatible with the CAST function, both for converting from and to. You can find the full list in the article mentioned below. For more information about the Oracle CAST function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-cast/
Views: 2002 Database Star
Efficient Function Calls From SQL (Part 3) : PL/SQL Function Result Cache
 
02:11
The third part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at the Cross-Session PL/SQL Function Result Cache introduced in Oracle 11g. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#oracle-11g-caching https://oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Auckland Oracle DBAs Meetup : Website: http://www.meetup.com/Auckland-Oracle-DBAs-Meetup/ Sponsors: - DBvisit: http://www.dbvisit.com/ - Datacom: http://www.datacom.co.nz/ - Oracle New Zealand: http://www.oracle.com/ - Pythian: http://www.pythian.com/ - Enterprise IT: http://www.enterpriseit.co.nz/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2960 ORACLE-BASE.com
Getting Started with PL/SQL Table Functions: Pipelined Table Functions
 
14:49
Pipelined table functions are table functions in which rows are returned or "piped" back to the calling query before the function has completed, avoiding the blocking behavior of normal PL/SQL functions and reduce PGA consumption. This video is part of the Oracle Dev Gym class "Getting Started with Table Functions". You can watch it independent of the class, but then you miss out on the tutorials and quizzes! So visit https://devgym.oracle.com/pls/apex/dg/class/getting-started-with-pl-sql-table-functions.html and register for the class! ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
Oracle PL/SQL Tutorials | Difference between Function and Procedure | Mr.Vaman Deshmukh
 
03:29
Oracle PL/SQL Tutorials | Difference between Function and Procedure | Mr.Vaman Deshmukh ** For Online Training Registration: https://goo.gl/r6kJbB ► Call: +91-8179191999 ► Visit Our Website for Classroom Training: https://nareshit.in ► For Online Training: https://nareshit.com/ -------------------------- ► About NareshIT: "Naresh IT is having 14+ years of experience in software training industry and the best Software Training Institute for online training, classroom training, weekend training, corporate training of Hadoop, Salesforce, AWS, DevOps, Spark, Data Science, Python, Tableau, RPA ,Java, C#.NET, ASP.NET, Oracle, Testing Tools, Silver light, Linq, SQL Server, Selenium, Android, iPhone, C Language, C++, PHP and Digital Marketing in USA,Hyderabad, Chennai and Vijayawada,Bangalore India which provides online training across all the locations -------------------------- ► Our Online Training Features: 1.Training with Real-Time Experts 2.Industry Specific Scenario’s 3.Flexible Timings 4.Soft Copy of Material 5. Share Videos of each and every session. -------------------------- Please write back to us at [email protected]/[email protected] or Call us at USA: +1404-232-9879 or India: +918179191999 ** Check The Below Links** ► For Course Reg: https://goo.gl/r6kJbB ► Subscribe to Our Channel: https://goo.gl/q9ozyG ► Circle us on G+: https://plus.google.com/NareshIT ► Like us on Facebook: https://www.facebook.com/NareshIT ► Follow us on Twitter: https://twitter.com/nareshitech ► Follow us on Linkedin: https://in.linkedin.com/company/naresh-i-technologies ► Follow us on Instagram: https://www.instagram.com/nareshitech/ #oraclepl/sqltutorialsvideos #programmingvideos
Rev up your Oracle Functions using Result Cache
 
11:56
Dan shows you how to dramatically increase function response time using a newer Oracle memory structure called Result Cache. Still on Oracle10? Dan has a solution for you as well.
Views: 119 Dan Hotka
What is RANDOM ORACLE? What does RANDOM ORACLE mean? RANDOM ORACLE meaning & explanation
 
04:33
What is RANDOM ORACLE? What does RANDOM ORACLE mean? RANDOM ORACLE meaning - RANDOM ORACLE definition - RANDOM ORACLE explanation. Source: Wikipedia.org article, adapted under https://creativecommons.org/licenses/by-sa/3.0/ license. SUBSCRIBE to our Google Earth flights channel - https://www.youtube.com/channel/UC6UuCPh7GrXznZi0Hz2YQnQ In cryptography, a random oracle is an oracle (a theoretical black box) that responds to every unique query with a (truly) random response chosen uniformly from its output domain. If a query is repeated it responds the same way every time that query is submitted. Stated differently, a random oracle is a mathematical function chosen uniformly at random, that is, a function mapping each possible query to a (fixed) random response from its output domain. Random oracles as a mathematical abstraction were firstly used in rigorous cryptographic proofs in the 1993 publication by Mihir Bellare and Phillip Rogaway (1993). They are typically used when the cryptographic hash functions in the method cannot be proven to possess the mathematical properties required by the proof. A system that is proven secure when every hash function is replaced by a random oracle is described as being secure in the random oracle model, as opposed to secure in the standard model of cryptography. Random oracles are typically used as an ideal replacement for cryptographic hash functions in schemes where strong randomness assumptions are needed of the hash function's output. Such a proof generally shows that a system or a protocol is secure by showing that an attacker must require impossible behavior from the oracle, or solve some mathematical problem believed hard in order to break it. Not all uses of cryptographic hash functions require random oracles: schemes that require only one or more properties having a definition in the standard model (such as collision resistance, preimage resistance, second preimage resistance, etc.) can often be proven secure in the standard model (e.g., the Cramer–Shoup cryptosystem). Random oracles have long been considered in computational complexity theory, and many schemes have been proven secure in the random oracle model, for example Optimal Asymmetric Encryption Padding, RSA-FDH and Probabilistic Signature Scheme. In 1986, Amos Fiat and Adi Shamir showed a major application of random oracles – the removal of interaction from protocols for the creation of signatures. In 1989, Russell Impagliazzo and Steven Rudich showed the limitation of random oracles – namely that their existence alone is not sufficient for secret-key exchange. In 1993, Mihir Bellare and Phillip Rogaway were the first to advocate their use in cryptographic constructions. In their definition, the random oracle produces a bit-string of infinite length which can be truncated to the length desired. According to the Church–Turing thesis, no function computable by a finite algorithm can implement a true random oracle (which by definition requires an infinite description). In fact, certain artificial signature and encryption schemes are known which are proven secure in the random oracle model, but which are trivially insecure when any real function is substituted for the random oracle. Nonetheless, for any more natural protocol a proof of security in the random oracle model gives very strong evidence of the practical security of the protocol. In general, if a protocol is proven secure, attacks to that protocol must either be outside what was proven, or break one of the assumptions in the proof; for instance if the proof relies on the hardness of integer factorization, to break this assumption one must discover a fast integer factorization algorithm. Instead, to break the random oracle assumption, one must discover some unknown and undesirable property of the actual hash function; for good hash functions where such properties are believed unlikely, the considered protocol can be considered secure.
Views: 264 The Audiopedia
Efficient Function Calls From SQL (Part 1) : Scalar Subquery Caching
 
02:33
The first part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at Scalar Subquery Caching. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#scalar-subquery-caching Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Bruno Borges : Blog: https://blogs.oracle.com/brunoborges/ Website: http://brunoborges.com/ Twitter: https://twitter.com/brunoborges Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2361 ORACLE-BASE.com
Deterministic Approximate Counting for Degree-2 Polynomial Threshold Functions
 
33:09
Rocco Servedio, Columbia University Real Analysis in Testing, Learning and Inapproximability http://simons.berkeley.edu/talks/rocco-servedio-2013-08-28
Views: 249 Simons Institute
WITH Clause : PL/SQL Declaration Section in Oracle Database 12c
 
03:59
An explanation of how to use the PL/SQL declaration section in the WITH clause in Oracle Database 12c. For more information see: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Chris Muir Blog: https://blogs.oracle.com/onesizedoesntfitall/ Twitter: https://twitter.com/chriscmuir Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 15244 ORACLE-BASE.com
Data 101: Probabilistic vs  Deterministic Data
 
00:57
Everyone talks about data-driven marketing, but what does that really mean? Is one audience truly different from the next? Join the Oracle Data Cloud team as we begin to explore today’s best ways to think about and utilize data. Together, we will use real world examples and insights to enable you to connect with your customers in a more genuine, personal way. Our goal is simple - to help brands better connect with their customers. Follow us on Twitter.com/OracleDataCloud Like us on Facebook.com/OracleDataCloud Read more at Blogs.Oracle.com/OracleDataCloud
Views: 662 Oracle Data Cloud
Oracle 12C Pragma UDF Function
 
08:10
Oracle 12C Pragma UDF Function https://easy-learning-tech.blogspot.com/2018/06/pragma-udf-user-defined-function.html oracle 12c new features for developers
Views: 167 Siva Academy
Oracle Hints explained
 
42:56
Oracle Hints
Views: 420 Telugu Blast News
Deterministic Encryption: Theory and Applications
 
01:04:59
The focus of the talk is deterministic public-key encryption schemes. Besides being interesting from theoretical and historical perspectives, the deterministic encryption primitive has applications to fast and secure search on remote data. We study several new notions of security for deterministic encryption and relations among them. We present several very efficient deterministic encryption schemes that provably satisfy the strongest-possible security definition (in the random oracle model). We finally provide the constructions that achieve security for many practical settings, without relying on the idealized random oracle model. The talk is based on joint papers with Mihir Bellare, Serge Fehr and Adam O'Neill.
Views: 176 Microsoft Research
Oracle Function Pipelined
 
13:21
Como criar function pipelined
Views: 2852 MasterTraining
Efficient Function Calls From SQL (Part 4) : Scalar Subquery Caching vs PL/SQL Function Result Cache
 
02:09
The fourth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we compare Scalar Subquery Caching with the the Cross-Session PL/SQL Function Result Cache to see which we should use for best performance. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#scalar-subquery-caching-revisited Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Alex Gorbachev : Blog: http://www.oracloid.com/ Twitter: https://twitter.com/alexgorbachev Website: http://www.pythian.com/about/alex-gorbachev/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 1551 ORACLE-BASE.com
Optimizing SQL Performance
 
51:42
Held on July 12 2018 In July's session we mainly looked at performance. Highlights include: 1:30 How does the database process subqueries? 5:20 Performance: comparing insert ... select to create tmp table, insert select from tmp; DDL in PL/SQL; dynamic SQL problems 12:45 18c private temporary tables; tables specific to a session; DDL you can rollback across! 21:00 Improving update performance: things to watch for; insert vs. update; "join-update" - create a view instead; create table as select "update" 34:05 Analytic function performance: first_value non-determinism; min keep vs first_value; computing function in a subquery; indexes for analytic functions 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: 424 Oracle Developers
EXAMPLE FOR AUTHID CURRENT USER AND AUTHID INVOKER
 
12:08
This is an example to show that how you can use invoker rights in procedure . We user authid current_user and authid invoker clause.
Views: 1226 Pushpjeet Cholkar
SQL Model Clause - 01 - An Introduction
 
10:48
SQL Model Clause - Series 01
Views: 315 Rajeshwaran Jeyabal
Oracle Performance - Result Cache Exercises
 
11:09
Oracle Performance - Result Cache Exercises
Views: 696 The Silent DBA
Oracle LOBS
 
31:33
Oracle LOBS
Views: 1866 The Silent DBA
Oracle SUBSTR and INSTR example
 
06:00
In this video, we will see how to use Oracle SUBSTR and INSTR functions in a nested manner.
Views: 2288 Ganesh Anbarasu
Efficient Function Calls From SQL (Part 6) : Function-Based Indexes
 
02:20
The sixth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we look at using function-based indexes to improve the performance of queries that use function calls in the WHERE clause. For more information see: https://oracle-base.com/articles/8i/function-based-indexes https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#functions-in-the-where-clause Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Ronald Bradford : Blog: http://ronaldbradford.com/blog/ Twitter: https://twitter.com/RonaldBradford Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 1429 ORACLE-BASE.com
COL11: Set-Level Manipulation of Nested Tables (MULTISET)
 
22:23
Oracle offers set-level operators and functions to manipulate nested tables. You can perform unions and intersects, check nested tables for equality and more. This lessons explores the MULTISET features of nested tables in detail. This video was taken from PLSQLChannel.com, originally recorded before Steven Feuerstein re-joined Oracle in March 2014. ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
Oracle Function Pipelined
 
13:21
what is oracle PIPELINED FUNCTION
Views: 493 Gees info
Tim Hall - Oracle Database expert
 
01:40
Tim is an ACE Director and he usually present at the Oracle Technology tour Latin America about his latest experiences working with SQL and Databases.
Views: 187 OracleAmericaLatina
PL/SQL Tutorial  4 :  PLSQL Procedures %Type, %ROWTYPE & NOCOPY
 
09:23
PLSQL Tutorial For Beginners 4 : PLSQL Procedures %Type, %ROWTYPE & NOCOPY SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 893 TechLake
Delete vs Truncate vs Drop :Oracle Interview Questions - Part 3
 
19:04
Delete, Truncate and Drop all can be used to remove the contents of a table but they have significant differences and unique properties. While Delete is a DML statement, truncate and DROP are DDL. Delete can be used to remove partial/Selective data. While truncate removes the entire content of the table.
Views: 999 Tech Coach
PL/SQL Tutorial : RETURNING INTO Clause examples
 
04:16
PL/SQL Tutorial : PLSQL RETURNING Clause examples PLSQL Tutorial SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 244 TechLake
Join Elimination in Oracle
 
03:11
What better way to improve performance than to actually remove some of your code? That's exactly what Oracle can do in some circumstances using join elimination. For more information see: https://oracle-base.com/articles/misc/join-elimination Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Pablo Ciccarello : Website: http://www.iristools.com/en/home/ Twitter: https://twitter.com/pablociccarello Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 904 ORACLE-BASE.com
108/125 Oracle PLSQL: Managing PLSQL / Conditional Complication 2
 
12:00
Learn Oracle PLSQL EXAM 1Z0-144 dbms_db_version
Views: 334 khaled alkhudari
Webinar: All About the Function Result Cache
 
01:08:47
Presented by Steven Feuerstein, Oracle Corporation The function result cache, added in Oracle Database 11g, offers developers a way to dramatically reduce the amount of time it takes to retrieve data requested repeatedly by database sessions (specifically, when the same rows of a table are fetched much more frequently than they are changed). This cache is stored in the SGA, shared across all sessions in the instance, and automatically purged of "dirty data" by the Oracle Database. Every application running on 11.1 or higher should be taking advantage of this feature. Best of all, there is minimal impact on your code to start using this feature.
Views: 1057 ODTUG
JavaScript Module as UDF in Oracle SQL
 
05:24
This is a demonstration of Oracle's experimental Multilingual Engine (MLE) technology. A module from NPM is installed in the client, uploaded to the database, and then called as a user-defined function from SQL. Learn more here: http://www.oracle.com/technetwork/database/multilingual-engine/overview/index.html ============================ Copyright © 2017 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.
79/125 Oracle PLSQL: Design consideration  / parallel_enable/ result_cache
 
13:48
تعلم اوراكل حتى الاحتراف Learn Oracle PLSQL EXAM 1Z0-144 https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC116
Views: 498 khaled alkhudari
Niv Buchbinder: Deterministic Algorithms for Submodular Maximization Problems
 
29:52
Randomization is a fundamental tool used in many theoretical and practical areas of computer science. We study here the role of randomization in the area of submodular function maximization. In this area most algorithms are randomized, and in almost all cases the approximation ratios obtained by current randomized algorithms are superior to the best results obtained by known deterministic algorithms. Derandomization of algorithms for general submodular function maximization seems hard since the access to the function is done via a value oracle. This makes it hard, for example, to apply standard derandomization techniques such as conditional expectations. Therefore, an interesting fundamental problem in this area is whether randomization is inherently necessary for obtaining good approximation ratios. In this work we give evidence that randomization is not necessary for obtaining good algorithms by presenting a new technique for derandomization of algorithms for submodular function maximization. Our high level idea is to maintain explicitly a (small) distribution over the states of the algorithm, and carefully update it using marginal values obtained from an extreme point solution of a suitable linear formulation. We demonstrate our technique on two recent algorithms for unconstrained submodular maximization and for maximizing submodular function subject to a cardinality constraint. In particular, for unconstrained submodular maximization we obtain an optimal deterministic 1/2-approximation showing that randomization is unnecessary for obtaining optimal results for this setting. The lecture was held within the framework of the Hausdorff Trimester Program: Combinatorial Optimization.
User defined functions SQL server 2008, R2, 2012
 
01:00:26
What is a user defined function (UDF) Different types of UDF's Scalar Functions Table valued Functions Inline Table Valued Functions Multi statement Table Valued Functions Properties of UDF's How develop and execute UDF's Advantages Limitations How to use Scalar Udf's in computed columns What are Deterministic functions and Non Deterministic functions how do they effect UDF's How do they effect performance of the UDF's Creating indexes on the Computed columns
Views: 12944 SQL JUNKIESHARE
MNG3: Compile-Time Warnings in PL/SQL (PL/SQL Channel)
 
29:27
In this video, I explore compile-time warnings, a fantastic feature of the PL/SQL compiler that will help you improve the quality and performance of your code, and increase your awareness of the full feature set of the PL/SQL language. This video was taken from PLSQLChannel.com, originally recorded before Steven Feuerstein re-joined Oracle in March 2014. ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
MNG4: Conditional Compilation in PL/SQL (PL/SQL Channel)
 
28:27
In Oracle Database 10g and higher, you can use "$" syntax to affect which lines of code in your source program will be compiled and which ignored. Conditional compilation feature can be of great assistance when writing code to run on multiple versions of Oracle and can be used to enforce at compile time code quality rules, among many applications of this feature. This video was taken from PLSQLChannel.com, originally recorded before Steven Feuerstein re-joined Oracle in March 2014. ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
29. SQL for Beginners - REGEXP_COUNT( ) (Hindi) | Regular Expressions
 
03:26
PL/SQL Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY1I1I8ACgWYjI5WyFlV4Nin SQL Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2q8cobbotVbn36XOskQR2F JavaScript Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2tLfvp7bCL3c-psMH9Czs6 HTML5 Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2_-PqGSrVTgBpVshxpqQlw Mobile Responsive Form using HTML5 & CSS: https://youtu.be/SECTfL3R0B0 MS SQL Server Installation: https://youtu.be/rZRA692RmuU Clock Animation in PowerPoint : https://youtu.be/LM5iwzNqQQo Envelope Animation in PowerPoint : https://youtu.be/HWlqGGleQqs FACEBOOK PAGE https://www.facebook.com/ishwaracademy/
Views: 73 Ishwar Academy
How to create Virtual Columns in Oracle Database
 
09:02
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: 458 OracleDBA