In this post I’m going to show to synchronize the remote and local repositories after an existing file in local gets modified. What I’ll do is modify the sp_test_git.pls file in our local repository and then push those changes to the remote repository (GitHub).
First, I proceed to open the sp_test_git.pls file using SQL Developer, add another dbms_output line to it and save it. The moment I save the file, the Pending Changes (Git) window gets updated to reflect the change and the icons in the toolbar get enabled.
Now I can include a comment and then add the file to the staging area by clicking on the Add button located on the Pending Changes (Git) window. Notice how the status changes from “Modified Not Staged” to “Modified Staged”.
What if I want to compare versions before doing a commit to the local repository? I just have to click on the Compare with Previous Version icon located on the Pending Changes (Git) window.
The panel on the left displays the version stored in the local repository and the panel on the right displays the version in the Staging Area.
The next step is to commit the changes to the local repository. For that I click on the Commit button located on the Pending Changes (Git) window and then I click on the OK button in the Commit window.
Now the Branch Compare window displays information telling that remote and local are out of sync.
So the final step is to sync up remote and local by pushing the changes to GitHub. For that I go to the main menu and click on Team -> Git -> Push to open the “Push to Git” wizard where I enter the URL for the remote repository, the user name and password to complete the operation. Now I go to GitHub to confirm the changes have been applied.
In my previous post, I showed how to clone a GitHub repository using SQL Developer. In this post I’m going to show to synchronize the remote and local repositories after remote gets modified.
Here I use GitHub to commit a file called sp_test_git.pls. You can create files by clicking on the icon the red arrow is pointing to.
The content of the file is a PL/SQL procedure that prints a message.
At this point, the remote repository and the local repository are out of sync. The first thing that you may want to do before modifying any repository, is to make sure that you have the most current version of it so that it includes the changes made by other developers. Let’s synchronize remote and local.
Make sure you open the Versions window. Go to the main menu click on Team -> Versions.
Open the Local branch and click on master, then go to main menu click on Team -> Git -> Fetch to open the “Fetch from Git” wizard. Fetching a repository copies changes from the remote repository into your local system, without modifying any of your current branches. Once you have fetched the changes, you can merge them into your branches or simply view them. We can see the changes on the Branch Compare window by going to the main menu click on Team -> Git -> Branch Compare.
Branch Compare is showing that sp_test_git.pls has been fetched from the remote master branch. We can right click on this entry and select compare to see the differences.
The window on the left displays the content of the fetched file and the window on right displays the content of the same file in the local repository. In this case the right windows is empty because this is a brand new file that doesn’t exist locally. Let’s accept the changes and merge them into the local repository. We go to the Branch Compare window, right click on the entry, select merge and click on the “Ok” button.
Now the changes should have been applied to the local repository.
We can go to the path where the local repository is located and confirm that sp_test_git.pls is there.
SQL Developer 4 provides an interface that allows us to interact with Git repositories. In this post, I’m going to show how to clone a GitHub (A web based hosting service for software development projects that uses the Git revision control system) repository.
First you need to sign up for a GitHub account. You can skip this step if you already have one.
Your account will give you access to public repositories that could be cloned but I suggest you create your own repository so that you can play with SQL Developer and see what the different options can do.
Once you have an account, click on the green button that says “New Repository”. It will take you to a screen like this:
Give your repository a name, decide if you want it to be public or private (you have to pay), click on the check box and then click on the green button. Now you should be taken to the main repository page.
Pay attention to the red arrow on the previous image. It points to a text box that contains the HTTPS clone URL that we’re going to use in SQL Developer to connect to GitHub.
Let’s go to SQL Developer and click on Team –> Git –> Clone… to open the “Clone from Git Wizard”. Click on the next button and you should see the screen that lets you enter the repository details:
Enter the repository name, the HTTPS clone URL, your GitHub user name and your password. Click on next to connect to the repository and see the remote branches that are available.
The master branch gets created by default for every new repository. Take the defaults on this screen and click on next to get to the screen where you specify the destination for your local Git repository.
Enter the path for the local repository and click on next. A summary screen is displayed and showing the options you chose. Click on finish to complete the setup.
How do we know if it worked? Go to the path of your local repository and it should contain the same structure as in the online repository.
On a next post I’ll show how to commit changes to the local repository and how to push them to GitHub.
The PL/SQL Debugger works pretty much out of the box when used with a previous Oracle version. These are the things we needed in place before we could start debugging PL/SQL:
- A grant of the DEBUG CONNECT SESSION privilege.
- EXECUTE privilege on DBMS_DEBUG_JDWP.
- EXECUTE privilege on the stored procedure you want to debug.
- Make sure the stored procedure is “Compiled for Debug”.
Jeff Smith talks about it in this post.
But what happens when you use Oracle 12c? You still need all the stuff that I mentioned but that’s not enough. See the error I got when I was trying to debug a procedure.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Disconnecting from the database SCOTT – ORA12CPDB1
Starting with Oracle 12c, if you want to debug PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer or JDeveloper, then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host.
This is one way you can configure network access for JDWP operations:
BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE ( host => '192.168.0.10', lower_port => null, upper_port => null, ace => xs$ace_type(privilege_list => xs$name_list('jdwp'), principal_name => 'scott', principal_type => xs_acl.ptype_db) ); END;
Host can can be a host name, domain name, IP address, or subnet.
Principal name in the access control entry (ACE) section is the schema that holds the stored procedures you want to debug.
Now the error goes away and you can start your debugging task.
Connecting to the database SCOTT – ORA12CPDB1.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
Debugger accepted connection from database on port 49428.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Inside the Procedure
Disconnecting from the database SCOTT – ORA12CPDB1.
Debugger disconnected from database.
I hope this post saves you some time when you migrate to 12c.
Yesterday I installed SQL Developer 4 on a Virtualbox VM that also contains Oracle 11g R2 and SQL Developer 3.2.20. I had no issues installing it; but as soon as I started clicking on anything that required a database connection (which is pretty much everything), the following error showed up.
I went to the preferences and under the Database/Advanced section I found the option where you can choose between the OCI Thick driver or the JDBC Thin driver. My configuration after the initial install was set to use the OCI Thick driver as you can see.
So I cleared the checkbox and everything went back to normal. I know the JDBC Thin driver is the default option, so now I was wondering how come I ended up with the OCI Thick driver instead.
After a few minutes, I realized that my other SQL Developer installation (3.2.20) is using the OCI Thick driver and every time you install a new version of SQL Developer a pop-up window displays asking if you want to copy your existing preferences (which I did). That explained part of the puzzle.
Now I still have to figure out why the OCI Thick driver is working with version 3.2.20 but not working with version 4. Hopefully That Jeff Smith will give me a clue about what’s going on :-)
Update 07/15/2013: As Jeff suggested in the comments section, a recent version of ocijdbc11.dll (instant client 184.108.40.206) placed into the /sqldeveloper/bin directory fixed the issue. This is a temporary workaround until this is corrected in the GA version.