<?xml version="1.0" encoding="UTF-8" standalone="no"?><rss xmlns:atom="http://www.w3.org/2005/Atom" xmlns:blogger="http://schemas.google.com/blogger/2008" xmlns:gd="http://schemas.google.com/g/2005" xmlns:georss="http://www.georss.org/georss" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/" xmlns:thr="http://purl.org/syndication/thread/1.0" version="2.0"><channel><atom:id>tag:blogger.com,1999:blog-811495802825588325</atom:id><lastBuildDate>Thu, 29 Aug 2024 13:53:24 +0000</lastBuildDate><title>Developers Tips and Tricks</title><description>This blog to exchange Knowledge with Professional Developers and to transfer Knowledge for Beginners.</description><link>http://developertricks.blogspot.com/</link><managingEditor>noreply@blogger.com (Mohamed Sami)</managingEditor><generator>Blogger</generator><openSearch:totalResults>8</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><openSearch:itemsPerPage>25</openSearch:itemsPerPage><language>en-us</language><itunes:explicit>no</itunes:explicit><itunes:keywords>Developer,Tips,Oracle,Tips</itunes:keywords><itunes:subtitle>This blog to exchange Knowledge with Professional Developers and to transfer Knowledge for Beginners.</itunes:subtitle><itunes:category text="Technology"><itunes:category text="Software How-To"/></itunes:category><itunes:category text="Technology"><itunes:category text="Gadgets"/></itunes:category><itunes:author>Mohamed Sami</itunes:author><itunes:owner><itunes:email>noreply@blogger.com</itunes:email><itunes:name>Mohamed Sami</itunes:name></itunes:owner><xhtml:meta content="noindex" name="robots" xmlns:xhtml="http://www.w3.org/1999/xhtml"/><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-3651641783077479343</guid><pubDate>Fri, 06 Jun 2008 16:03:00 +0000</pubDate><atom:updated>2018-07-04T01:00:21.823+02:00</atom:updated><title>How to Create Connection String Easily</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
will explain to you an easy way to get any Database Connection String so you can use this String to Connect to your database using any developing Language.&lt;br /&gt;
&lt;br /&gt;
&lt;ul&gt;
&lt;li&gt;Right Click on your Desktop&lt;/li&gt;
&lt;li&gt;Then new Text Document.&lt;/li&gt;
&lt;li&gt;Change File extension from .txt to .udl file.&lt;/li&gt;
&lt;li&gt;UDL file is Data Link File, Opened by Microsoft Data Access.&lt;/li&gt;
&lt;li&gt;Double Click on the file after Changing the Extension.&lt;/li&gt;
&lt;/ul&gt;
&lt;br /&gt;
&lt;a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-JMWCnIpOsIHCpvESV9oGxv8Fz6dl8tXx7kb-o1wMLEURu_EuqA-2ry9bG2Vc9hB4mh-VZ3fIDojwZp-SFgsnzHxbaFvZzZqbdYcYHa5nPmF4GyE_8utQJyCKji64ZVic0UkP0vDukl0/s1600-h/1udl.JPG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"&gt;&lt;img alt="" border="0" id="BLOGGER_PHOTO_ID_5208801773578806786" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-JMWCnIpOsIHCpvESV9oGxv8Fz6dl8tXx7kb-o1wMLEURu_EuqA-2ry9bG2Vc9hB4mh-VZ3fIDojwZp-SFgsnzHxbaFvZzZqbdYcYHa5nPmF4GyE_8utQJyCKji64ZVic0UkP0vDukl0/s320/1udl.JPG" style="cursor: pointer; display: block; margin: 0px auto 10px; text-align: center;" /&gt;&lt;/a&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;ul&gt;
&lt;li&gt;You will see Form Similar to this Form.&lt;/li&gt;
&lt;li&gt;From Provider Tab, You can choose a type of Database Provider you want to use (Access, SQL Server, Oracle …).&lt;/li&gt;
&lt;li&gt;Connection Tab will be different according to the provider you choose before from provider tab.&lt;/li&gt;
&lt;li&gt;The Connection here for SQL Server Provider.&lt;/li&gt;
&lt;li&gt;The server name is the Database instance Name plus Machine Name.&lt;/li&gt;
&lt;li&gt;Logging to database require password and username that's only if you use Mixed Mode in Installing SQL Server Database&lt;/li&gt;
&lt;li&gt;If you don't know the user and password user windows Integrated Security.&lt;/li&gt;
&lt;li&gt;Then if the information above is correct you will get all databases you created in "Select the database on the Server "Tab.&lt;/li&gt;
&lt;li&gt;Choose the one you want to connect to it&lt;/li&gt;
&lt;li&gt;Test your Connection.&lt;/li&gt;
&lt;li&gt;Close the file by pressing OK button.&lt;/li&gt;
&lt;li&gt;Open the file with notepad.&lt;/li&gt;
&lt;li&gt;You will find the Connection String.&lt;/li&gt;
&lt;li&gt;Copy and paste in your Code.&lt;/li&gt;
&lt;/ul&gt;
Thanks,&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/06/how-to-create-connection-string-easily.html</link><media:thumbnail xmlns:media="http://search.yahoo.com/mrss/" height="72" url="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-JMWCnIpOsIHCpvESV9oGxv8Fz6dl8tXx7kb-o1wMLEURu_EuqA-2ry9bG2Vc9hB4mh-VZ3fIDojwZp-SFgsnzHxbaFvZzZqbdYcYHa5nPmF4GyE_8utQJyCKji64ZVic0UkP0vDukl0/s72-c/1udl.JPG" width="72"/><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-5608322158953711972</guid><pubDate>Sun, 25 May 2008 18:43:00 +0000</pubDate><atom:updated>2018-07-04T01:00:55.432+02:00</atom:updated><title>Number of Days in Current Month</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span style="font-size: 130%;"&gt;&lt;span style="font-family: &amp;quot;georgia&amp;quot;;"&gt;This Query to get number of days in Specific month at Specific Year :&lt;/span&gt;&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="font-size: 85%;"&gt;&lt;span style="color: rgb(102 , 0 , 0); font-family: &amp;quot;courier new&amp;quot;;"&gt;&lt;span style="font-size: 100%; font-weight: bold;"&gt;select to_number(to_char(last_day(to_date('01/02/2008','DD/Mm/yyyy')),'DD')) as days from dual&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style="color: rgb(0 , 0 , 0); font-family: &amp;quot;georgia&amp;quot;; font-size: 130%;"&gt;you have to replace date '01/02/2008' with the date you want or the date from sysdate&lt;/span&gt;&lt;span style="color: rgb(0 , 0 , 0);"&gt;&lt;span style="font-family: &amp;quot;georgia&amp;quot;; font-size: 130%;"&gt;&lt;br /&gt;&lt;br /&gt;Thanks,&lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/05/number-of-days-in-current-month.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-3363639788212344884</guid><pubDate>Tue, 13 May 2008 18:12:00 +0000</pubDate><atom:updated>2018-07-04T01:01:27.320+02:00</atom:updated><title>Manage Your Database Connection</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
This is an Example for how to manage a SQL Server database Connection using Visual Web Developer 2005&lt;br /&gt;
&lt;br /&gt;
Dim _CON As String = "Data Source=MOHAMEDSAMI-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True"&lt;br /&gt;
&lt;br /&gt;
Here this is the Connection String and I will tell you in another blog how you can get this String easily, cause it is the most important thing, if you write the right connection you will have a successful connection to the database.&lt;br /&gt;
&lt;br /&gt;
&lt;ul&gt;
&lt;li&gt;Data Source = Your Database Instance, you can type it in many ways like (. /SQLEXPRESS, localhost/SQLEXPRESS, machine name/SQLEXPRESS).&lt;/li&gt;
&lt;li&gt;Initial Catalog = Your Database Name.&lt;/li&gt;
&lt;/ul&gt;
&lt;br /&gt;
Public newSqlConnection As SqlConnection = New SqlConnection(_CON)&lt;br /&gt;
&lt;div&gt;
&lt;br /&gt;
I defined a new Object from SqlConnection so I can manage and establish the Connection.&lt;br /&gt;
I have to open the Connection so I can execute any Query I want.&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
&lt;span class="Apple-style-span"&gt;&lt;span class="Apple-style-span" style="color: rgb(102 , 102 , 102);"&gt; Private Function OpenConnection() As Boolean&lt;br /&gt;Try&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; newSqlConnection.Open()&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Return True&lt;br /&gt;Catch ex As Exception&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Return False&lt;br /&gt;Finally&lt;br /&gt;End Try&lt;br /&gt;End Function&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
You can only just type newSqlConnection.Open() and it will do the function but I would like to make it in Function as a type of Validation.&lt;br /&gt;
After executing any query we have to close the connection so I created a Procedure to close the connection with the database.&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
&lt;span class="Apple-style-span"&gt;&lt;span class="Apple-style-span" style="color: rgb(102 , 102 , 102);"&gt; Private Sub CloseConnection()&lt;br /&gt;Try&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; newSqlConnection.Close()&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
&lt;div&gt;
&lt;span class="Apple-style-span"&gt;&lt;span class="Apple-style-span" style="color: rgb(102 , 102 , 102);"&gt;&lt;br /&gt;Catch ex As Exception&lt;br /&gt;Finally&lt;br /&gt;End Try&lt;br /&gt;End Sub&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
the execute function you will use to select any data from your database&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
&lt;span class="Apple-style-span"&gt;&lt;span class="Apple-style-span" style="color: rgb(102 , 102 , 102);"&gt; Public Function ExecutemyQuery(ByVal Query As String) As DataTable&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Dim dt_reults As New DataTable&lt;br /&gt;If OpenConnection() Then&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; Try&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Dim newSqlCommand As SqlCommand = New SqlCommand(Query, newSqlConnection)&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Dim newSqlAdapt As New SqlDataAdapter(Query, newSqlConnection)&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; newSqlCommand.ExecuteNonQuery()&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; newSqlAdapt.Fill(dt_reults)&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;CloseConnection()&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; Catch ex As Exception&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp;End Try&lt;br /&gt;End If&lt;br /&gt;Return dt_reults&lt;br /&gt;End Function&lt;/span&gt;&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
I defined an object from SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.&lt;br /&gt;
SqlDataAdapter: Represents a set of data commands and a database connection that are used to fill the Data table or Dataset and update a SQL Server database.&lt;br /&gt;
So this is the function but how we will call it?&lt;br /&gt;
In Any Event handler like Button_Click write This Code&lt;/div&gt;
&lt;div&gt;
&lt;br /&gt;
&lt;span class="Apple-style-span"&gt;&lt;span class="Apple-style-span" style="color: rgb(102 , 102 , 102);"&gt; Dim dt As New DataTable&lt;br /&gt;dt = _Class.ExecutemyQuery(“Select * from Employee”)&lt;br /&gt;&amp;nbsp;&amp;nbsp; If dt.Rows.Count &amp;gt; 0 Then&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;GridView1.DataSource = dt&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;GridView1.DataBind()&lt;br /&gt;&amp;nbsp;&amp;nbsp;Else&lt;br /&gt;&amp;nbsp;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;Lb_MSG.Text = "There is No Data"&lt;br /&gt;&amp;nbsp;End If&lt;/span&gt;&lt;/span&gt;&lt;br /&gt;
_Class: is an Object from my Class which I Write all my Functions and Procedures on it&lt;br /&gt;
Thanks for Reading my Post, I hope this post will help you.&lt;br /&gt;
Salam&lt;/div&gt;
&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/05/manage-your-database-connection_13.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-2952772799978538653</guid><pubDate>Mon, 28 Apr 2008 17:13:00 +0000</pubDate><atom:updated>2018-07-04T01:01:42.180+02:00</atom:updated><title>CURRENT_SCHEMA Approach</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span xmlns=""&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;This method uses the CURRENT_SCHEMA session attribute to automatically point application users to the correct schema.&lt;br /&gt;&lt;br /&gt;First, we create the schema owner and an application user.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CONN sys/password AS SYSDBA&lt;br /&gt;&lt;br /&gt;-- Remove existing users and roles with the same names.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;DROP USER schema_owner CASCADE;&lt;br /&gt;DROP USER app_user CASCADE;&lt;br /&gt;DROP ROLE schema_rw_role;&lt;br /&gt;DROP ROLE schema_ro_role;&lt;br /&gt;&lt;br /&gt;-- Schema owner.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE USER schema_owner IDENTIFIED BY password&lt;br /&gt;DEFAULT TABLESPACE users&lt;br /&gt;TEMPORARY TABLESPACE temp&lt;br /&gt;QUOTA UNLIMITED ON users;&lt;br /&gt;&lt;br /&gt;GRANT CONNECT, CREATE TABLE TO schema_owner;&lt;br /&gt;&lt;br /&gt;-- Application user.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE USER app_user IDENTIFIED BY password&lt;br /&gt;DEFAULT TABLESPACE users&lt;br /&gt;TEMPORARY TABLESPACE temp;&lt;br /&gt;&lt;br /&gt;GRANT CONNECT TO app_user;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects.&lt;br /&gt;&lt;br /&gt;Next, we create some roles to allow read-write and read-only access.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE ROLE schema_rw_role;&lt;br /&gt;CREATE ROLE schema_ro_role;&lt;br /&gt;We want to give our application user read-write access to the schema objects, so we grant the relevant role.&lt;br /&gt;GRANT schema_rw_role TO app_user;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;We need to make sure the application user has its default schema pointing to the schema owner, so we create an AFTER LOGON trigger to do this for us.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE OR REPLACE TRIGGER app_user.after_logon_trg&lt;br /&gt;AFTER LOGON ON app_user.SCHEMA&lt;br /&gt;BEGIN&lt;br /&gt;DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');&lt;br /&gt;EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';&lt;br /&gt;END;&lt;br /&gt;/&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;Now we are ready to create an object in the schema owner.&lt;br /&gt;CONN schema_owner/password&lt;br /&gt;&lt;br /&gt;CREATE TABLE test_tab (&lt;br /&gt;id NUMBER,&lt;br /&gt;description VARCHAR2(50),&lt;br /&gt;CONSTRAINT test_tab_pk PRIMARY KEY (id)&lt;br /&gt;);&lt;br /&gt;&lt;br /&gt;GRANT SELECT ON test_tab TO schema_ro_role;&lt;br /&gt;GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;Notice how the privileges are granted to the relevant roles. Without this, the objects would not be visible to the application user. We now have a functioning schema owner and application user.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CONN app_user/password&lt;br /&gt;Connected.&lt;br /&gt;DESC test_tab&lt;br /&gt;Name Null? Type&lt;br /&gt;ID NOT NULL NUMBER&lt;br /&gt;DESCRIPTION VARCHAR2(50)&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;This method is ideal where the application user is simply an alternative entry point to the main schema, requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms. I don't find it very useful for developers who need to make copies or modify schema objects during development.&lt;/span&gt;&lt;br /&gt;
&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/04/currentschema-approach.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-694250098294440487</guid><pubDate>Mon, 28 Apr 2008 17:12:00 +0000</pubDate><atom:updated>2018-07-04T01:01:59.148+02:00</atom:updated><title>Synonym Approach</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span xmlns=""&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;This method relies on synonyms owned by the application user to point to the correct location of the schema objects.&lt;br /&gt;&lt;br /&gt;First, we create the users in a similar way to the previous example.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CONN sys/password AS SYSDBA&lt;br /&gt;&lt;br /&gt;-- Remove existing users and roles with the same names.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;DROP USER schema_owner CASCADE;&lt;br /&gt;DROP USER app_user CASCADE;&lt;br /&gt;DROP ROLE schema_rw_role;&lt;br /&gt;DROP ROLE schema_ro_role;&lt;br /&gt;&lt;br /&gt;-- Schema owner.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE USER schema_owner IDENTIFIED BY password&lt;br /&gt;DEFAULT TABLESPACE users&lt;br /&gt;TEMPORARY TABLESPACE temp&lt;br /&gt;QUOTA UNLIMITED ON users;&lt;br /&gt;&lt;br /&gt;GRANT CONNECT, CREATE TABLE TO schema_owner;&lt;br /&gt;&lt;br /&gt;-- Application user.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE USER app_user IDENTIFIED BY password&lt;br /&gt;DEFAULT TABLESPACE users&lt;br /&gt;TEMPORARY TABLESPACE temp;&lt;br /&gt;&lt;br /&gt;GRANT CONNECT, CREATE SYNONYM TO app_user;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms.&lt;br /&gt;&lt;br /&gt;Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CREATE ROLE schema_rw_role;&lt;br /&gt;CREATE ROLE schema_ro_role;&lt;br /&gt;GRANT schema_rw_role TO app_user;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;Now we are ready to create an object in the schema owner in the same way we did in the previous example.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CONN schema_owner/password&lt;br /&gt;&lt;br /&gt;CREATE TABLE test_tab (&lt;br /&gt;id NUMBER,&lt;br /&gt;description VARCHAR2(50),&lt;br /&gt;CONSTRAINT test_tab_pk PRIMARY KEY (id)&lt;br /&gt;);&lt;br /&gt;&lt;br /&gt;GRANT SELECT ON test_tab TO schema_ro_role;&lt;br /&gt;GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;If we now connect to the application user we are not able to see the object without qualifying it with a schema name. We can either proceed in this fashion or use a synonym to point to the correct object.&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;CONN app_user/password&lt;br /&gt;Connected.&lt;br /&gt;DESC test_tab&lt;br /&gt;ERROR:&lt;br /&gt;ORA-04043: object test_tab does not exist&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;DESC schema_owner.test_tab&lt;br /&gt;Name Null? Type&lt;br /&gt;&lt;br /&gt;ID NOT NULL NUMBER&lt;br /&gt;DESCRIPTION VARCHAR2(50)&lt;br /&gt;&lt;br /&gt;CREATE SYNONYM test_tab FOR schema_owner.test_tab;&lt;br /&gt;&lt;br /&gt;Synonym created.&lt;br /&gt;&lt;br /&gt;DESC test_tab&lt;br /&gt;Name Null? Type&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;ID NOT NULL NUMBER&lt;br /&gt;DESCRIPTION VARCHAR2(50)&lt;br /&gt;&lt;br /&gt;I find this method rather cumbersome due to the sheer number of synonyms required, especially when there are a large number of application users. Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance. I only use this method when I have developers who need to create their own schema objects for testing.&lt;br /&gt;&lt;br /&gt;Hope this helps.&lt;br /&gt;Regards&lt;/span&gt;&lt;br /&gt;
&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/04/synonym-approach.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-7071216333067590218</guid><pubDate>Sun, 27 Apr 2008 15:32:00 +0000</pubDate><atom:updated>2018-07-04T01:02:09.191+02:00</atom:updated><title>Combine more than one Row in One Row</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span xmlns=""&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;Working On Oracle Sample Database&lt;br /&gt;Tables ( Departments and Employees)&lt;br /&gt;&lt;br /&gt;1- Suppose we want to select employees Names in each department&lt;br /&gt;&lt;br /&gt;   &lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;Query :&lt;/span&gt;&lt;br /&gt;
&lt;div style="margin-left: 45pt;"&gt;
&lt;table border="0" style="background: #4f81bd; border-collapse: collapse;"&gt;&lt;colgroup&gt;&lt;col style="width: 530px;"&gt;&lt;/col&gt;&lt;/colgroup&gt;&lt;tbody valign="top"&gt;
&lt;tr&gt;&lt;td style="border-bottom: solid 2.25pt; border-left: none; border-right: none; border-top: solid 2.25pt; padding-left: 7px; padding-right: 7px;"&gt;&lt;span style="color: white; font-family: &amp;quot;georgia&amp;quot;; font-size: 9pt;"&gt;SELECT departments.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name&lt;br /&gt;FROM employees JOIN departments ON departments.department_id = employees.department_id&lt;br /&gt;ORDER BY departments.department_id;&lt;/span&gt;&lt;/td&gt;&lt;/tr&gt;
&lt;/tbody&gt;&lt;/table&gt;
&lt;/div&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;&amp;nbsp;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;Results :&lt;br /&gt;10 Administration Jennifer Whalen&lt;br /&gt;20 Marketing Michael Hartstein&lt;br /&gt;20 Marketing Pat Fay&lt;br /&gt;30 Purchasing Den Raphaely&lt;br /&gt;30 Purchasing Karen Colmenares&lt;br /&gt;30 Purchasing Alexander Khoo&lt;br /&gt;30 Purchasing Shelli Baida&lt;br /&gt;30 Purchasing Sigal Tobias&lt;br /&gt;30 Purchasing Guy Himuro&lt;br /&gt;40 Human Resources Susan Mavris&lt;br /&gt;50 Shipping Donald OConnell&lt;br /&gt;.................................................................&lt;br /&gt;&lt;br /&gt;we want to know how we can make the results like :&lt;br /&gt;&lt;br /&gt;10 Administration Jennifer Whalen&lt;br /&gt;20 Marketing Michael Hartstein, Pat Fay&lt;br /&gt;30 Purchasing Alexander Khoo, Den Raphaely, Guy Himuro, Karen Colmenares, Shelli Baida, Sigal Tobias&lt;br /&gt;40 Human Resources Susan Mavris&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;Here is the way to do that without making any Functions or Procedures&lt;br /&gt;&lt;br /&gt;   &lt;/span&gt;&lt;br /&gt;
&lt;div style="margin-left: 45pt;"&gt;
&lt;table border="0" style="background: #4f81bd; border-collapse: collapse;"&gt;&lt;colgroup&gt;&lt;col style="width: 530px;"&gt;&lt;/col&gt;&lt;/colgroup&gt;&lt;tbody valign="top"&gt;
&lt;tr&gt;&lt;td style="border-bottom: solid 2.25pt; border-left: none; border-right: none; border-top: solid 2.25pt; padding-left: 7px; padding-right: 7px;"&gt;&lt;span style="color: white; font-family: &amp;quot;georgia&amp;quot;; font-size: 9pt;"&gt;SELECT department_id, department_name, LTRIM(MAX(sys_connect_by_path(emp_name, ',')) keep(dense_rank LAST ORDER BY curr), ',') AS employees_names &lt;/span&gt;&lt;br /&gt;
&lt;span style="color: white; font-family: &amp;quot;georgia&amp;quot;; font-size: 9pt;"&gt;FROM&lt;br /&gt;(&lt;br /&gt;SELECT employees.department_id, department_name, employees.first_name || ' ' || employees.last_name AS emp_name,&lt;br /&gt;row_number() over(PARTITION BY employees.department_id ORDER BY first_name) AS curr,&lt;br /&gt;row_number() over(PARTITION BY employees.department_id ORDER BY first_name) -1 AS prev&lt;br /&gt;FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY employees.department_id&lt;br /&gt;)&lt;br /&gt;GROUP BY department_id, department_name CONNECT BY prev = PRIOR curr&lt;br /&gt;AND department_id = PRIOR department_id START WITH curr = 1;&lt;/span&gt;&lt;/td&gt;&lt;/tr&gt;
&lt;/tbody&gt;&lt;/table&gt;
&lt;/div&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;I hope that this Query will help you&lt;br /&gt;&lt;br /&gt;Thanks&lt;br /&gt;&lt;br /&gt;Salaam&lt;/span&gt;&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/04/combine-more-than-one-row-in-one-row_27.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-1863261296565053505</guid><pubDate>Sun, 27 Apr 2008 14:48:00 +0000</pubDate><atom:updated>2018-07-04T01:02:17.886+02:00</atom:updated><title>How to Select Random using Oracle</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span xmlns=""&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span xmlns=""&gt;Working on Employees Table :&lt;br /&gt;&lt;br /&gt;Query :&lt;br /&gt;&lt;br /&gt;   &lt;/span&gt;&lt;br /&gt;
&lt;div style="margin-left: 27pt;"&gt;
&lt;table border="0" style="border-collapse: collapse;"&gt;&lt;colgroup&gt;&lt;col style="width: 470px;"&gt;&lt;/col&gt;&lt;/colgroup&gt;&lt;tbody style="color: black;" valign="top"&gt;
&lt;tr style="height: 21px;"&gt;&lt;td color="black -moz-use-text-color" style="border-style: solid none; padding-left: 7px; padding-right: 7px;"&gt;&lt;strong&gt;select * from (select * from Employees ORDER BY dbms_random.value ) where rownum &amp;lt;= 10&lt;/strong&gt;&lt;/td&gt;&lt;/tr&gt;
&lt;/tbody&gt;&lt;/table&gt;
&lt;/div&gt;
&lt;span xmlns=""&gt;&lt;br /&gt;&lt;/span&gt;
&lt;span xmlns=""&gt;Results :&lt;br /&gt;&lt;br /&gt;&lt;span style="color: silver; font-size: 10;"&gt;143 Randall Matos RMATOS&lt;br /&gt;147 Alberto Errazuriz AERRAZUR&lt;br /&gt;152 Peter Hall PHALL&lt;br /&gt;135 Ki Gee KGEE&lt;br /&gt;193 Britney Everett BEVERETT&lt;br /&gt;122 Payam Kaufling PKAUFLIN&lt;br /&gt;130 Mozhe Atkinson MATKINSO&lt;br /&gt;181 Jean Fleaur JFLEAUR&lt;br /&gt;110 John Chen JCHEN&lt;br /&gt;105 David Austin DAUSTIN&lt;br /&gt;&lt;/span&gt;Every time you will run same Query you will have new results&lt;br /&gt;&lt;br /&gt;i wish this can help you in your Development Process&lt;br /&gt;&lt;br /&gt;Thanks,&lt;/span&gt;&lt;br /&gt;
&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/04/how-to-select-random-using-oracle_354.html</link><thr:total>0</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item><item><guid isPermaLink="false">tag:blogger.com,1999:blog-811495802825588325.post-6478022226679609913</guid><pubDate>Fri, 18 Apr 2008 14:09:00 +0000</pubDate><atom:updated>2018-07-04T01:02:40.791+02:00</atom:updated><title>SQL Interview Questions</title><description>&lt;div dir="ltr" style="text-align: left;" trbidi="on"&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless remarkably effective ways of rating a candidate's comfort-level in SQL, whereas others just wasted precious interview time. Let me save you the latter!&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;First I ask the candidate how they would personally rate their own understanding of SQL. I'm also curious what databases and tools they've used to write their SQL. This gives me a good idea where I should begin my questioning.&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0); font-size: 130%;"&gt;Basic Questions&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;Asking a question about joins is the common consensus for an opening question. More specifically, does the candidate know the different types of joins, or at least the difference between an inner join and an outer join?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;Assuming that goes well, I follow up with a question that involves using GROUP BY and/or HAVING. For example, given an EMPLOYEE table and a DEPARTMENT table, how would you select the combined salary of each department that has a higher combined salary than mine?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0); font-size: 130%;"&gt;Intermediate Questions&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;With the sanity checks out of the way, we can get into some more meaningful technical discussions. Among my favorites is to solicit their perspective of what NULL is and what it means. After all, to write correct SQL queries, isn't this understanding important? How many queries have you read that caused problems because the author mishandled nulls? Will this candidate make the same mistake? Do they know that NULL and nothing are different, and in what ways? Do they know that neither NULL = NULL nor NULL &amp;lt;&amp;gt; NULL are true?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;Before proceeding to the grand finale, I pepper the candidate with variations of the following three questions that, though simple, seem to have done an exemplary job narrowing down someone's level of experience. First, do they know the four isolation levels? Secondly, do they know what a bind variable is and why they should be used (do they even know what a soft parse is)? Finally, what is the DUAL table?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0); font-size: 130%;"&gt;Advanced Questions&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;All of this is leading up to analytic functions, one of my true litmus tests to the proficiency of one's knowledge of SQL. There are literally dozens of questions I have asked: YOU need to pick one that specifically relates to the type of work relevant to the open position. Here are some possibilities to choose from:&lt;/span&gt;&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;- Open with the basic concept of selecting data over a partition (a running total, perhaps)&lt;/span&gt;&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;- Follow-up by writing a Top-N query&lt;/span&gt;&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;- Put together a standard pivot/crosstab query&lt;/span&gt;&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;- Try handling hierarchical/recursive queries (using CONNECT BY)&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;I feel there is no real need to know the exact analytic function and write out the precise syntax, but to truly qualify your SQL at the higher level requires the ability to know how to truly unleash the power of an Oracle database. So tell me, did they head in the right direction, or ask for a glass of water and slip out?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0); font-size: 130%;"&gt;Wrapping Up&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;Generally I have managed to keep the discussion under the previously unrealistic 15-minute limit that's imposed upon me. On rare occasions I'm treated to the delightful combination of a candidate that has breezed through the interview and a boss taking his time re-joining us. In that case, I've been known to serve up some advanced history questions. Who is Ted Codd? Did they know about his 12 rules? Do they get a blank expression on their face when I ask them what "System R" is?&lt;/span&gt;&lt;br /&gt;
&lt;br /&gt;
&lt;span style="color: rgb(0 , 0 , 0);"&gt;At the very least, even if they know SQL backwards and forwards, they'll still have something interesting to look up when they get home.&lt;br /&gt;&lt;span style="color: rgb(255 , 255 , 51); font-size: 130%; font-weight: bold;"&gt;&lt;br /&gt;&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
</description><link>http://developertricks.blogspot.com/2008/04/sql-interview-questions.html</link><thr:total>2</thr:total><author>noreply@blogger.com (Mohamed Sami)</author></item></channel></rss>