Home
Search results “Sql exec oracle”
Oracle : Stored Procedure with Input and Output Parameters
 
04:04
Java Source Code here: http://ramj2ee.blogspot.com/2015/07/oracle-stored-procedure-with-input-and.html Oracle : Stored Procedure with Input and Output Parameters JavaEE Tutorials and Sample code - Click here : http://ramj2ee.blogspot.in/
Views: 33435 Ram N
Oracle Part 14 Stored Procedure Create and Execute
 
29:43
Create, replace, drop stored procedures, comment, variables, assignment, for loop, serveroutput, in parameter, execute SP
Views: 33790 Free OpenMentor
Oracle PL/SQL - Procedures
 
10:41
Oracle PL/SQL - Procedures
Views: 129396 Chris Ostrowski
Working with PL-SQL in Oracle SQL Developer v4.0
 
10:53
How to navigate your PL-SQL, a few options to consider, and working with execution results of your programs in Oracle SQL Developer version 4.0.
Views: 28181 Jeff Smith
Oracle DBA Justin - How to execute sql and Sqlplus commands from an external script
 
09:33
How to execute sql and Sqlplus commands from an external script
Views: 84224 jbleistein11
Dynamic sql output parameter
 
05:25
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/05/dynamic-sql-output-parameter.html Slides http://csharp-video-tutorials.blogspot.com/2017/05/dynamic-sql-output-parameter_8.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss, how to use output parameters with dynamic sql. Let us understand this with an example. SQL script to create Employees table Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) Go Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) Go We want to write a dynamic sql statement that returns total number of male of female employees. If the gender value is specified as "Male", then the query should return total male employees. Along the same lines, if the the value for gender is "Female", then we should get total number of female employees. The following dynamic sql, will give us what we want. In this case, the query returns total number of "Male" employees. If you want the total number of female employees, simply set @gender='Female'. Declare @sql nvarchar(max) Declare @gender nvarchar(10) Set @gender = 'Male' Set @sql = 'Select Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10)', @gender At the moment we are not using output parameters. If you want the count of employees to be returned using an OUTPUT parameter, then we have to do a slight modification to the query as shown below. The key here is to use the OUTPUT keyword in your dynamic sql. This is very similar to using OUTPUT parameters with a stored procedure. Declare @sql nvarchar(max) Declare @gender nvarchar(10) Declare @count int Set @gender = 'Male' Set @sql = 'Select @count = Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10), @count int OUTPUT', @gender, @count OUTPUT Select @count The OUTPUT parameter returns NULL, if you forget to use OUTPUT keyword.. The following query returns NULL, as we removed the OUTPUT keyword from @count parameter Declare @sql nvarchar(max) Declare @gender nvarchar(10) Declare @count int Set @gender = 'Male' Set @sql = 'Select @count = Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10), @count int OUTPUT', @gender, @count Select @count
Views: 15153 kudvenkat
DIFFERENT WAYS TO CALL / EXECUTE A PROCEDURE IN ORACLE PL/SQL? (Position, Named and Mixed Notations)
 
06:53
This video tutorial is a demonstration on different ways that a subroutine can be called / executed in Oracle PL/SQL. The video demonstrates the Position notation call, Named notation call and Mixed Notation call with proper examples.
Views: 3263 Kishan Mashru
SQL Error ORA-00942 Table or View Does Not Exist in Oracle Database
 
05:03
How to use Tab View (Data Dictionary) of Oracle Database to solve the SQL Error ORA-00942 Table or View Does Not Exist error in Oracle Database ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/ora-00942 Previous Tutorial ► ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section 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: 6493 Manish Sharma
PL/SQL tutorial 82: How to Execute DDL statements with Execute Immediate Dynamic SQL
 
10:32
Native Dynamic SQL - Learn how to execute CREATE TABLE DDL statement using EXECUTE IMMEDIATE in Oracle Database by Manish Sharma ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/NDS-3 What Is A #Data Warehouse? http://bit.ly/the-data-warehouse Previous Tutorial ► Introduction to Dynamic SQL: https://youtu.be/Yzk1zENbhg4 ► Introduction to Execute Immediately: https://youtu.be/47KzYVBNbIs ► Create Table: https://youtu.be/UU0EEfpa-2c ►Alter Table: https://youtu.be/8yAaO_ySTkw ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section 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: 3797 Manish Sharma
Oracle 11G Tutorial | Dynamic SQL | InfiniteSkills Training
 
07:20
Want all of our free Oracle videos? Download our free iPad app at http://itunes.apple.com/us/app/video-training-courses-from/id418130423?mt=8 http://www.infiniteskills.com/training/learning-oracle-database-11g.html Oracle trainer Lewis Cunningham offers professional tips on Dynamic SQL in this hands-on tutorial from InfiniteSkills. More free content can be found on the product page for the 8-hour 11g course. YouTube: https://www.youtube.com/user/OreillyMedia Facebook: https://www.facebook.com/OReilly/?fref=ts Twitter: https://twitter.com/OReillyMedia Website: http://www.oreilly.com/
Calling a Stored Procedure from another Stored Procedure
 
11:55
Calling a Stored Procedure from another Stored Procedure - DB2 UDB, Oracle, SQL Server, MySQL
Views: 5603 Professor Saad
Урок10a.PL SQL.Циклы в PL SQL
 
11:09
Практически в любом алгоритмическом языке программирования существуют синтаксические конструкции, отвечающие за организации повторения одинаковых частей программы. Такие повторения назеваются циклами, а операторы – операторами для организации циклов. В отличие от других языков с их repeat while в ORACLE PL SQL циклы упрощены, но вместе с тем удобны, хорошо воспринимаются и позволяют реализовать практически любой сложный алгоритм. Oracle PL SQL. учебная среда https://apex.oracle.com/pls/apex/f?p=4550:1:103440124..::::: схема: SQLADV польз: student1 пароль: student1 будут работать (student2/ student2, student3/ student3 .... student11/ student11) меню SQL Workshop , SQL Command или используйте http://sqlfiddle.com/ скрипт бд для загрузки в sqlfiddle http://sqladv.ru/dev/sql.txt урок и задания http://sqladv.ru/dev/plsql/l10a.txt
Dynamic sql table name variable
 
11:59
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable_20.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss how to pass table name dynamically for stored procedure in sql server. This is one of the sql questions that is very commonly asked. I have a web page with a textbox. When I enter a table name in the textbox and when I click "Load Data" button, we want to retrieve data from that respective table and display it on the page. Copy the SQL Script to create the tables from my blog using the link below http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Create the following stored procedure. Notice we are passing table name as a parameter to the stored prcoedure. In the body of the stored procedure we are concatenating strings to build our dynamic sql statement. In our previous videos we discussed that this open doors for SQL injection. Create procedure spDynamicTableName @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from ' + @TableName Execute sp_executesql @sql End So the obvious question that comes to our mind is, why are we not creating parameterised sql statement instead. The answers is we can't. SQL Server does not allow table names and column names to be passed as parameters. Notice in the example below, we are creating a parameterised query with @TabName as a parameter. When we execute the following code, the procedure gets created successfully. Create procedure spDynamicTableName1 @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from @TabName' Execute sp_executesql @sql, N'@TabName nvarchar(100)', @TabName = @TableName End But when we try to execute it we get an error - Must declare the table variable "@TabName" Execute spDynamicTableName1 N'Countries' Add a Web Page to the project that we have been working with in our previous video. Name it "DynamicTableName.aspx". Copy and paste the HTML from my blog using the link below http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html Copy and paste the code from my blog in the code-behind page http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html At this point, run the application and type the following text in the "Table Name" textbox and click "Load Data" button. Notice "SalesDB" database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters. Employees; Drop database SalesDB One way to prevent SQL injection in this case is by using SQL Server built-in function - QUOTENAME(). We will discuss QUOTENAME() function in detail in our next video. For now understand that by default, this function wraps that string that is passed to it in a pair of brackets. SELECT QUOTENAME('Employees') returns [Employees] Modify the stored procedure to use QUOTENAME() function as shown below. Alter procedure spDynamicTableName @TableName nvarchar(100) As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from ' + QUOTENAME(@TableName) Execute sp_executesql @sql End At this point, type the following text in the "Table Name" textbox and click "Load Data" button. Notice you will see a message - Invalid object name 'Employees; Drop database SalesDB'. Also "SalesDB" database is not dropped. Employees; Drop database SalesDB The entire text in "Table Name" textbox is wrapped in a pair of brackets by the QUOTENAME function and is treated as table name. Since we do have a table with the specified name, we get the error - Invalid object name.
Views: 23832 kudvenkat
Oracle tutorial : Oracle DBA  How to execute sql script  using sqlplus command line
 
04:59
Oracle tutorial : Oracle DBA How to execute sql script using sqlplus command line oracle tutorial for beginners This Oracle tutorial video will show you how to run sqlplus script from command line. You can execute the script from an external file using sqlplus commands. To run a script from command line you have to path of the script file.
Views: 4918 Tech Query Pond
Oracle and Java tutorial. Java Stored Procedure
 
08:18
More lessons: http://www.learn-with-video-tutorials.com/oracle-and-java-tutorial-video Creating Java class, Loading the Class into the Oracle Database, Java Stored Procedure
DYN1: Introduction to Dynamic SQL in PL/SQL (PL/SQL Channel)
 
14:32
This video explains the difference between static and dynamic SQL, reviews the four methods of dynamic SQL, and lays out the plan for this series. It 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.
Debugging PL/SQL with Oracle SQL Developer
 
02:37
Quick 2 minute video showing how to open a PL/SQL program in Oracle SQL Developer and debug it, line by line.
Views: 34122 Jeff Smith
Dynamic SQL in SQL Server
 
12:12
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2017/03/dynamic-sql-in-sql-server_27.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss 1. What is Dynamic SQL 2. Simple example of using Dynamic SQL What is Dynamic SQL Dynamic SQL is a SQL built from strings at runtime. Simple example of using Dynamic SQL Here is the SQL Script to create Employees table and populate it with data Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) Go Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) Go One way to achieve this is by implementing a stored procedure as shown below that this page would call. Create Procedure spSearchEmployees @FirstName nvarchar(100), @LastName nvarchar(100), @Gender nvarchar(50), @Salary int As Begin Select * from Employees where (FirstName = @FirstName OR @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND (Gender = @Gender OR @Gender IS NULL) AND (Salary = @Salary OR @Salary IS NULL) End Go The stored procedure in this case is not very complicated as we have only 4 search filters. What if there are 20 or more such filters. This stored procedure can get complex. To make things worse what if we want to specify conditions like AND, OR etc between these search filters. The stored procedure can get extremely large, complicated and difficult to maintain. One way to reduce the complexity is by using dynamic SQL as show below. Depending on for which search filters the user has provided the values on the "Search Page", we build the WHERE clause dynamically at runtime, which can reduce complexity. However, you might hear arguments that dynamic sql is bad both in-terms of security and performance. This is true if the dynamic sql is not properly implemented. From a security standpoint, it may open doors for SQL injection attack and from a performance standpoint, the cached query plans may not be reused. If properly implemented, we will not have these problems with dynamic sql. In our upcoming videos, we will discuss good and bad dynamic sql implementations. For now let's implement a simple example that makes use of dynamic sql. In the example below we are assuming the user has supplied values only for FirstName and LastName search fields. To execute the dynamicl sql we are using system stored procedure sp_executesql. sp_executesql takes two pre-defined parameters and any number of user-defined parameters. @statement - The is the first parameter which is mandatory, and contains the SQL statements to execute @params - This is the second parameter and is optional. This is used to declare parameters specified in @statement The rest of the parameters are the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure Declare @sql nvarchar(1000) Declare @params nvarchar(1000) Set @sql = 'Select * from Employees where [email protected] and [email protected]' Set @params = '@FirstName nvarchar(100), @LastName nvarchar(100)' Execute sp_executesql @sql, @params, @FirstName='Ben',@LastName='Hoskins' This is just the introduction to dynamic SQL. If a few things are unclear at the moment, don't worry. In our upcoming videos we will discuss the following 1. Implementing a real world "Search Web Page" with and without dynamic SQL 2. Performance and Security implications of dynamic sql. Along the way we will also discuss good and bad dynamic sql implementations. 3. Different options available for executing dynamic sql and their implications 4. Using dynamic sql in stored procedures and it's implications Once we discuss all the above, you will understand 1. The flexibility dynamic sql provides 2. Advantages and disadvantages of dynamic sql 3. When and when not to use dynamic sql
Views: 39434 kudvenkat
CREATE, ALTER, EXECUTE and DROP a stored Procedure in SQL
 
09:15
This video explains how to Create, Alter, Execute a Stored Procedure in SQL. First I create a simple stored procedure to return all rows from a table. Then I modify the Stored Procedure to return value based on a given parameter. Then I Drop the Procedure. I have tried to keep this video simple. Prerequisites: 1. SQL knowledge 2. A little idea about Stored Procedures.
Views: 10385 Curious me
SQLTools and Sublime Text - Executing SQL, directly in your editor
 
05:07
In this tutorial, I show you the excellent SQLTools package for Sublime Text and how it can be used to connect to your databases and run SQL commands directly in your favourite editor
Views: 8546 Greg Bird
PL/SQL tutorial 6: Bind Variable in PL/SQL By Manish Sharma RebellionRider.com
 
07:56
Watch and learn what are bind variables in PL/SQL how to declare or create them using Variable command, Initialize them using Execute (exec)command and different ways of displaying current values of a bind variable for example using AutoPrint parameter. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/bind-variable Previous Tutorial ► Constants in PL/SQL https://youtu.be/r1ypg7WH4GY ►User Variables :https://youtu.be/2MNmodawvnE ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►►►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 ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►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 for E-mail address please check About section 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: 90246 Manish Sharma
SSIS "Execute SQL Task" - stored procedure with input parameters
 
17:42
Demonstration of how to call a stored procedure to update a table with input parameters from a SSIS Execute SQL Task. Covers error handling of update failures. Examples cover OLE DB and ADO.NET.
Views: 9054 Steden Videos
Dynamic SQL in Stored Procedure
 
09:32
In this video we will discuss, using dynamic sql in a stored procedure and it's implications from sql injection perspective. We will discuss performance implications of using dynamic sql in a stored procedure in a later video. Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure_11.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists Consider the following stored procedure "spSearchEmployees". We implemented this procedure in Part 139 of SQL Server tutorial. This stored procedure does not have any dynamic sql in it. It is all static sql and is immune to sql injection. Create Procedure spSearchEmployees @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Select * from Employees where (FirstName = @FirstName OR @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND (Gender = @Gender OR @Gender IS NULL) AND (Salary = @Salary OR @Salary IS NULL) End Go Whether you are creating your dynamic sql queries in a client application like ASP.NET web application or in a stored procedure, you should never ever concatenate user input values. Instead you should be using parameters. Notice in the following example, we are creating dynamic sql queries by concatenating parameter values, instead of using parameterized queries. This stored procedure is prone to SQL injection. Let's prove this by creating a "Search Page" that calls this procedure. Create Procedure spSearchEmployeesBadDynamicSQL @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Declare @sql nvarchar(max) Set @sql = 'Select * from Employees where 1 = 1' if(@FirstName is not null) Set @sql = @sql + ' and FirstName=''' + @FirstName + '''' if(@LastName is not null) Set @sql = @sql + ' and LastName=''' + @LastName + '''' if(@Gender is not null) Set @sql = @sql + ' and Gender=''' + @Gender + '''' if(@Salary is not null) Set @sql = @sql + ' and Salary=''' + @Salary + '''' Execute sp_executesql @sql End Go Add a Web Page to the project that we have been working with in our previous video. Name it "DynamicSQLInStoredProcedure.aspx". Copy and paste the HTML and code available on my blog at the following link http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html At this point, run the application and type the following text in the "Firsname" text and click "Search" button. Notice "SalesDB" database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters. ' Drop database SalesDB -- In the following stored procedure we have implemented dynamic sql by using parameters, so this is not prone to sql injecttion. This is an example for good dynamic sql implementation. Create Procedure spSearchEmployeesGoodDynamicSQL @FirstName nvarchar(100) = NULL, @LastName nvarchar(100) = NULL, @Gender nvarchar(50) = NULL, @Salary int = NULL As Begin Declare @sql nvarchar(max) Declare @sqlParams nvarchar(max) Set @sql = 'Select * from Employees where 1 = 1' if(@FirstName is not null) Set @sql = @sql + ' and [email protected]' if(@LastName is not null) Set @sql = @sql + ' and [email protected]' if(@Gender is not null) Set @sql = @sql + ' and [email protected]' if(@Salary is not null) Set @sql = @sql + ' and [email protected]' Execute sp_executesql @sql, N'@FN nvarchar(50), @LN nvarchar(50), @Gen nvarchar(50), @sal int', @[email protected], @[email protected], @[email protected], @[email protected] End Go On the code-behind page, use stored procedure spSearchEmployeesGoodDynamicSQL instead of spSearchEmployeesBadDynamicSQL. We do not have to change any other code. At this point run the application one more time and type the following text in the "Firstname" textbox and click the "Search" button. ' Drop database SalesDB -- Notice "SalesDB" database is not dropped, So in this case our application is not succeptible to SQL injection attack. Summary : Whether you are creating dynamic sql in a client application (like a web application) or in a stored procedure always use parameters instead of concatnating strings. Using parameters to create dynamic sql statements prevents sql injection.
Views: 31068 kudvenkat
Oracle Dynamic SQL (Update Table with DBMS_SQL Package)
 
12:08
Selain menggunakan EXECUTE IMMEDIATE, Oracle DBMS menyediakan package terintegrasi untuk melakukan dynamic sql saat runtime. DBMS_SQL Package merupakan package yang sudah tersedia pada Oracle DBMS. Untuk dapat melakukan dynamic sql saat runtime, DBMS_SQL memiliki beberapa aturan penggunaannya. Perlu digunakan sintaks berikut: DBMS_SQL.OPEN DBMS_SQL.FETCH DBMS_SQL.EXECUTE DBMS_SQL.CLOSE
Views: 710 Boby Siswanto
HOW TO EXECUTE SQL AND SQLPLUS COMMANDS FROM EXTERNAL SCRIPT
 
04:10
HOW TO EXECUTE SQL AND SQLPLUS COMMANDS FROM EXTERNAL SCRIPT ============================================================ @"script location" start "script location" how to find name of the oracle database? ======================================== select name,open_mode from v$database; how to check oracle database version? ====================================== select version from v$instacne; select * from v$version; how to check database memory? ============================= select * from v$sga;
Views: 1837 Praveen Biyyapu
16-How to create/execute/test a stored procedure using Microsoft SQL Server Database
 
02:42
Original high quality video: http://www.dotnetcodecentral.com/Post/135/microsoft-sql-server-quick-learn-how-to/How-To-Create-And-Execute-Stored-Procedure-in-SQL-Server All high quality videos on SQL Server (FREE to download): http://www.dotnetcodecentral.com/Technology/microsoft-sql-server-quick-learn-how-to Demonstrates the following: -Create a stored procedure (with a single parameter) from the scratch using SSMS. You can also have stored procedures with no/multiple parameters -Execute the newly created stored procedure using SSMS (this generates code to execute a stored procedure automatically) -Modify the generated code (to execute stored procedure) and re-execute the same (multiple number of times).
Views: 40011 Jagadish Pulakhandam
DYN3: Method 2 Dynamic SQL - Non-query DML with bind variables (PL/SQL Channel)
 
26:03
One of the most common forms of dynamic SQL is method 2: non-query DML (constructed at runtime) with a fixed number of bind variables. With method 2, you need the USING clause of EXECUTE IMMEDIATE and this video shows you how. 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.
How to create and execute a Procedure in sql command prompt
 
03:35
How to create and execute a Procedure in sql command prompt..
Views: 9220 pathi s
PL/SQL tutorial 41: How To Create PL/SQL Stored Procedure Without Parameters in Oracle Database
 
06:30
RebellionRider.com present you the PL/SQL Tutorial 41 on How To Create PL/SQL Stored Procedure Without Parameters in Oracle Database By Manish Sharma ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/create-procedure-1 Previous Tutorial ► PL/SQL Blocks: https://youtu.be/rbarR4_gaH8 ► Introduction To Stored Procedures: https://youtu.be/buaSuEMi4lw ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►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/ ___Facebook Official Page___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the "About" section 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: 40121 Manish Sharma
SSIS "Execute SQL Task" - stored procedure with input parameters, output parameters & return value.
 
15:57
Demonstration of using SSIS Execute SQL Task to call a stored procedure to set SSIS variables from the stored procedure output parameter values and return value. No result set involved. Examples cover both OLEDB and ADO.NET.
Views: 13477 Steden Videos
SQL Server Integration Services - How to use Stored Procedure in OLE DB Source in SSIS Package
 
06:19
In this video we will learn the answer of SQL Server Integration Services(SSIS) Interview Question " Can we use Stored Procedure in OLE DB Source? If yes, how do you map the parameters?" How to use Stored Procedure in OLE DB Source in SSIS Package How to map parameters in OLE DB Source in SSIS Package How to pass value of variable to Stored Procedure parameter in OLE DB Source in Data Flow in SSIS Package To Follow Step by Step this SSIS video tutorial By Tech Brothers http://sqlage.blogspot.com/search/label/SSIS%20Video%20Tutorial
Views: 27103 TechBrothersIT
HOW TO WRAP PLSQL CODE IN ORACLE DATABASE
 
05:55
HOW TO WRAP A PL/SQL CODE IN ORACLE DATABASE ============================================ wrap iname=testproc.sql oname=testproc.plb HOW TO CREATE A PROCEDURE ========================== create or replace procedure test as begin dbms_output.put_line('Welcome to ORACLE DBA'); end; / HOW TO CHECK THE CONTENT OF PROCEDURE ======================================== select owner,object_name from dba_objects where object_name like '&object_name'; select text from dba_source where name='&name'; HOW TO EXECUTE A PROCEDURE ========================== set serveroutput on; exec test HOW TO READ A FILE FROM COMMAND PROMPT ====================================== type testproc.sql type testproc.plb PL/SQL files extension ======================= .pls - PL/SQL source .plb - PL/SQL binary .pks - Package source or package specification .pkb - Package binary or package body .pck - Combined package specification plus body
Views: 1876 Praveen Biyyapu
73/125 Oracle PLSQL: Dynamic SQL / DBMS_SQL
 
15:42
تعلم اوراكل حتى الاحتراف Learn Oracle PLSQL EXAM 1Z0-144
Views: 693 khaled alkhudari
Job In Oracle : pl sql create job in Oracle Scheduler
 
08:09
Job In Oracle : How to Create and Run Job in Oracle Scheduler This video will show you how to Scheduling Jobs with Oracle Scheduler. pl sql create job dbms job scheduler example DBMS_SCHEDULER ---------------------- A job object (job) is a collection of metadata that describes a user-defined task that is scheduled to run one or more times. It is a combination of what needs to be executed (the action) and when (the schedule). CREATE OR REPLACE PROCEDURE myproc AS BEGIN INSERT INTO MYTEST(CREATED_ON) VALUES (sysdate); commit; END myproc; / BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name = ‘My_job’, job_type = ‘STORED_PROCEDURE’, job_action = ‘MYPROC’, start_date = ’07-AUG-16 07.00.00 PM’, repeat_interval = ‘FREQ=SECONDLY;INTERVAL=5', end_date =’20-NOV-18 07.00.00 PM’, auto_drop = FALSE, comments = ‘My new job’); END; / EXEC DBMS_SCHEDULER.ENABLE(‘My_job’); Subscribe on youtube: https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA For more tutorial please visit #techquerypond https://techquerypond.com https://techquerypond.wordpress.com https://twitter.com/techquerypond oracle job scheduler
Views: 17230 Tech Query Pond
PL/SQL: Dynamic SQL part-1
 
06:51
In this tutorial, you'll learn what is dynamic SQL and how to implement it..
Views: 22609 radhikaravikumar
Part-3 (Oracle Procedures) Oracle PL SQL Training - Fast Track Series
 
18:26
Oracle Procedures Is a group of PL SQL statement that can call by name. Syntax CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]; Example:1 of procedure having Only parameter procedure. create or replace procedure insert_employee ( p_emp_name varchar2, p_deptno IN number ) is begin Insert into emp (id,name,deptno) values (emp_id_seq.nextval, p_emp_name, p_deptno); commit; end insert_employee; / Prerequisite for the Example:1 1. Need create emp table create table emp( id number, name varchar2(200), deptno number ); 2. Create sequence object. create sequence emp_id_seq start with 1 Increment by 1 nomaxvalue nocycle; How to call procedure created in Example:1 exec insert_employee('sanket',10); Or begin insert_employee('sanket',10); end; set pagesize 100 set linesize 100 column id format 999 column name format a6 column deptno format 999 select * from emp; ID NAME DEPTNO ---- ------ ------ 1 sanket 10 Example:2 of procedure having In/Out parameter procedure create or replace procedure insert_employee ( p_emp_name varchar2, p_deptno IN number, p_message OUT varchar2 ) is begin Insert into emp (id,name,deptno) values (emp_id_seq.nextval, p_emp_name, p_deptno); commit; p_message:= 'one row inserted...'; end insert_employee; / How to call procedure created in Example:2 set serveroutput on; declare v_message varchar2(100); begin insert_employee(‘',20,v_message); dbms_output.put_line(v_message); end; select * from emp; ID NAME DEPTNO ---- ------ ------ 1 sanket 10
Views: 1566 Sanket Patel
Statistics in Oracle and SQL Server
 
01:28:28
The better the information that Oracle and SQL Server have about the data in a database, the better choices they can make on how to execute the SQL. Statistics are Oracle's and SQL Server's chief source of information. If this information is out of date, performance of queries will suffer. In their third live 'Oracle vs. SQL Server' discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at statistics in Oracle and SQL Server. Do Oracle and SQL Server gather the same information? What does each optimizer use this information for? And how can Oracle and SQL Server administrators override the defaults for better (or worse) performance? These are just some of the questions that Jonathan and Grant will try to answer in another not-to-be-missed session. As before, this will be a live discussion with limited supporting slides, and will conclude with a Q+A session with Jonathan and Grant. Be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server. For our complete archive please go to http://www.red-gate.com/oracle-webinars
Views: 2365 Redgate Videos
Create Stored procedure in Oracle
 
08:51
Visit : http://informatica4dummys.blogspot.in/
Views: 42018 Srivatsan Ganesh
Oracle sql and pl/sql Interview question : Difference between %type and %rowtype in oracle pl/sql
 
11:37
This video tutorial explains the usage of %type variable declaration and %rowtype variable declaration. It also states the advantages of using %type and %rowtype variable declaration. At the end the viewes can easily identify the difference between %type and %rowtype variable declaration If you want more such videos of exciting and amazing 'difference between' concepts, check out the links below : char and varchar2 : https://youtu.be/039qzwjWf4k replace and translate : https://youtu.be/HKYF77BGzOE procedure and function : https://youtu.be/q3LmOenL120 in and exists : https://youtu.be/REX4IjRYlFw rank and dense_rank : https://youtu.be/WGSX998hZ9M delete and truncate : https://youtu.be/u76wMm2byXo
Views: 541 Kishan Mashru
Migration from Non-Container to PDB database-PART I- Oracle 12C Administration
 
09:30
Non-Container Name =NCDB convert to PDB-NCDB Container DB Name=CDB =========================== Steps for Conversion. Step 1. Cleanly Shutdown the Non-CDB Database Instance. ==- set environment to NCDB ==- sqlplus / as sysdba ==- Shutdown immediate Step 2. Once You Have Cleanly Shutdown the Database, Start Up the database in Mount Exclusive Mode and Open the Database in Read-Only Mode. ==- set environment to NCDB ==- sqlplus / as sysdba ==- startup mount exclusive ==- alter database open read only; Step 3. Generate a Pluggable Database Manifest File from the Non-Container Database. ==- set environment to NCDB ==- sqlplus / as sysdba ==- exec dbms_pdb.describe (pdb_descr_file=-'E:\app\oracle\manifest\NCDB_manifest_file.xml'); Step 4. Shutdown the NON-CDB file Once Step 3 Completes Successfully. ==- Set environment to NCDB ==- sqlplus / as sysdba ==- shutdown immediate Step 5. Start the CDB (CDB) if it’s Not Already Up and Check the Compatibility with CDB. ==- set environment to CDB ==- sqlplus / as sysdba ==- startup (If not up) ==- Run below at SQL prompt. SET SERVEROUTPUT ON; DECLARE Compatible CONSTANT VARCHAR2(3) :=CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY (pdb_descr_file =- 'E:\app\oracle\manifest\NCDB_manifest_file.xml') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / Step 6. Once it Completes Successfully, Query PDB_PLUG_IN_VIOLATIONS View from CDB Database for Any Errors. ==- set environment to CDB ==- sqlplus / as sysdba ==- select name, cause, type, message, status from PDB_PLUG_IN_VIOLATIONS where name='NCDB'; Note: "There should be no violations reported. If there are any, you need to fix it before proceeding". Step 7. Connect to the CDB Where Database has to be Plugged in Using the Noncdb Manifest File and Plug the PDDB12C Database. ==- set environment to CDB ==- sqlplus / as sysdba ==- CREATE PLUGGABLE DATABASE NCDB USING 'E:\app\oracle\manifest\NCDB_manifest_file.xml' COPY FILE_NAME_CONVERT = ('E:\APP\ORACLE\ORADATA\NCDB\', 'E:\app\oracle\oradata\CDB1\'); Note: "Below options are supported and you can chose one based on the env" 1) COPY: The datafiles of noncdb remains intact and it is copied to create PDBs at new locations and keep original datafiles intact at the original location. (This would mean that a noncdb database would still be operational after the creation of a PDB). 2) MOVE: The datafiles of noncdb are moved to a new location to create a PDB. In this case, noncdb database would not be available after a PDB is created. NOCOPY: The datafiles of noncdb are used to create a PDB2 and it uses same existing location. In this case, a noncdb database would not be available after a PDB is created. You can use FILE_NAME_CONVERT parameter to specify the new location of the datafiles while using COPY or MOVE option. Step 8. Once Step 7 Completes Successfully, Switch to the PDB Container and Run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql". ==- set environment to CDB ==- sqlplus / as sysdba ==- alter session set container=NCDB ==- @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql Step 9. Startup the PDB and Check the Open Mode. ==- set environment to CDB ==- sqlplus / as sysdba ==- ALTER PLUGGABLE DATABASE OPEN; ==-SELECT name, open_mode FROM v$pdbs;
Views: 730 ANKUSH THAVALI
Prevent sql injection with dynamic sql
 
10:28
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Slides http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql_5.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss, how to prevent SQL injection when using dynamic SQL. In Part 140, we have implemented "Search Page" using dynamic SQL. Since we have used parameters to build our dynamic SQL statements, it is not prone to SQL Injection attack. This is an example of good dynamic SQL implementation. I have seen lot of software developers, not just the beginners but even experienced developers, buidling their dynamic sql queries by concatenating strings instead of using parameters without realizing that they are opening the doors for SQL Injection. Please check my blog at the following link for code used in the demo. http://csharp-video-tutorials.blogspot.com/2017/04/prevent-sql-injection-with-dynamic-sql.html Since we are concatenating the user input values to build the dynamic sql statement, the end user can very easily inject sql. Imagine, what happens for example, if the user enters the following in the "Firstname" textbox. ' Drop database SalesDB -- With the above SQL injected into the "Firstname" textbox, if you click the "Search" button, the following is the query which is sent to SQL server. This will drop the SalesDB. Select * from Employees where 1 = 1 AND FirstName = '' Drop database SalesDB --' On the other hand, if you use parameters to build your dynamic SQL statements, SQL Injection is not an issue. The following input in the "Firstname" textbox, would not drop the SalesDB database. ' Drop database SalesDB -- The text the user has provided in the "Firstname" textbox is treated as the value for @Firstname parameter. The following is the query that is generated and executed. exec sp_executesql N'Select * from Employees where 1 = 1 AND [email protected]',N'@FirstName nvarchar(26)',@FirstName=N''' Drop database SalesDB --' We don't have this problem of sql injection if we are using stored procedures. "SearchPageWithoutDynamicSQL.aspx" is using the stored procedure "spSearchEmployees" instead of dynamic SQL. The same input in the "Firstname" texbox on this page, would generate the following. Notice, whatever text we typed in the "Firstname" textbox is treated as the value for @FirstName parameter. exec spSearchEmployees @FirstName=N''' Drop database SalesDB --' An important point to keep in mind here is that if you have dynamic SQL in your stored procedure, and you are concatenating strings in that stored procedure to build your dynamic sql statements instead of using parameters, it is still prone to SQL injection. If this is not clear at the moment don't worry, we will discuss an example of this in out next video. So in summary, while dynamic sql provides great flexibility when implementing complicated logic with lot of permutations and combinations, if not properly implemented it may open doors for sql injection. Always use parameters to build dynamic sql statements, instead of concatenating user input values. Another benefit of using parameters to build dynamic sql statements is that it allows cached query plans to be reused, which greatly increases the performance. We will discuss an example of this in our upcoming videos.
Views: 19125 kudvenkat
Database - Advanced Stored Procedures Cursors and Dynamic SQL
 
13:19
A demonstration of using cursors and dynamic SQL to solve more complex reporting and stored procedure requirements. Code is available at https://cop4709.pbworks.com/w/page/63343020/Lecture%20-%20Advanced%20Stored%20Procedures
Linked Server in SQL Server 2012 to Another SQL Server and Excel File
 
18:33
Creating a Linked Server in SQL Server 2012 to Another SQL Server and Excel File. To Learn or Teach Linux visit www.theskillpedia.com, A Marketplace for Learners and Trainers. To know about me visit my profile at www.rnsangwan.com. I deliver online training on Business Analysis, Linux, Unix, MySQL, Sybase, SQL Server, Perl, Python, R Data Analysis, Scala etc. We can Create a Linked Server in SQL Server 2012 to Any other RDBMS provided we have the appropriate driver. In this video I have demonstrated Link Server creation in SQL Server 2012 to Another SQL Server and Link Server to an Excel File. Once Linked Server is created, you can issue queries in the same manner as if you are issuing them on a local table in a database. The Concept is similar to "Link Database" in Oracle.
Views: 34463 TheSkillPedia
exec PlSql
 
02:45
exec PlSql
Views: 11 Digital Megatrends
Install oracle apex 5
 
28:25
To install oracle apex version 5 you must install oracle database 11g. Software download link bellow: http://www.oracle.com/technetwork/developer-tools/apex/downloads/download-085147.html Follow the installation steps: CREATE TABLESPACE apex DATAFILE 'E:\APP\DBA\ORADATA\ORACLE\apex.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M; @apexins.sql APEX APEX TEMP /i/ @apxchpwd.sql password: Oracle123# @apex_epg_config.sql D: ALTER USER ANONYMOUS ACCOUNT UNLOCK; @apxldimg.sql D: EXEC DBMS_XDB.sethttpport(8080);
Views: 4859 IT WORLD
Stored procedures in sql server   Part 18
 
20:11
In this video we will learn 1. What is a stored procedure 2. Stored Procedure example 3. Creating a stored procedure with parameters 4. Altering SP 5. Viewing the text of the SP 6. Dropping the SP 7. Encrypting stored procedure Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedures-part-18.html Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-18-stored-procedures.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Views: 725552 kudvenkat

Indocin 75 mg drugs
Zapiz 2mg abilify
Flomax generic best price
Voltaren gel asda online
Synthroid 25 mcg reviews