Feed aggregator

New Oracle and .NET beta available now!

Christian Shay - Sun, 2007-06-03 23:06
A major beta release is available now for ODP.NET as well as Oracle Developer Tools for Visual Studio .NET. This 11g beta release also marks the first release of a new product, Oracle Providers for ASP.NET. I've written a new white paper (with lots of screen shots) that illustrates the new Visual Studio enhancements. Keep an eye on this blog in the weeks ahead as I will be writing articles explaining how to take advantage of some of the new features such as ASP.NET code generation and User-Defined Type (UDT) support...

We also have some new Oracle by Example walkthroughs that can help get you started:
Oracle by Example: Building ASP.NET Web Applications with ODT
Oracle by Example: Building .NET Applications Using ODT

Here's a summary of the new features:

Oracle Developer Tools for Visual Studio .NET New Features:

  • Visual Studio 2005 integration with Server Explorer, Data Sources Window, Dataset Designer, TableAdapter Configuration Wizard and more
  • Improved ASP.NET web developer support
  • Oracle Database script project to provide source control of Oracle scripts
  • SQL script editor
  • Built in support for executing SQL*Plus scripts
  • Explain plan support
  • Integration with Query Designer
  • New designer for granting/revoking privileges on schema objects
  • User-Defined Types: Create, explore, modify UDTs; custom class code generation for .NET applications
  • Data Window and Query Window enhancements


  • Oracle Data Provider for .NET:

  • Instant Client Support: Smaller ODP.NET client installation
  • User-Defined Types: Map Oracle objects and collections to .NET custom types and support REFs to object types
  • Process Database Down Events: ODP.NET automatically frees connections of a downed Data Guard instance
  • Windows-Authenticated User Connection Pooling: Windows-authenticated connections can now be pooled.
  • Connection Pool Performance Counters: Monitor connection pool status

  • Oracle Providers for ASP.NET:

  • Membership Provider: Stores and retrieves registered user information in the Oracle database
  • Role Provider: Stores and retrieves user role information in the Oracle database
  • Site Map Provider: Stores and retrieves site map information in the Oracle database
  • Session State Provider: Stores and retrieves session state information in the Oracle database
  • Profile Provider: Stores and retrieves user profile information in the Oracle database
  • Web Events Provider: Stores and retrieves ASP.NET health monitoring event information in the Oracle database
  • Web Parts Personalization Provider: Stores and retrieves personalization data in the Oracle database
  • Cache Dependency Provider: Automatically invalidates ASP.NET cached data based on changes made to the base data in the Oracle database
  • Exception Handling in BPEL

    Peeyush Tugnawat - Sun, 2007-06-03 14:47

    I did not realize that its been a while since I posted anything on my blog. Well we all get busy once in a while and that was the case with me as I got really busy with my current project. The good thing is I have few things to share that came out of my experience working with BPEL.

    For exception handling, I learned that the function called ora:getFaultAsString was missing from the bpel designer (10.1.3) and it was not returning the detailed information about the fault. There are two patches that can help on this issue 5672007 & 5841736.


    Digg!

    SQL Interview Questions

    Robert Vollman - Sun, 2007-06-03 11:59
    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?I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com30

    Conditionally showing fields based upon attribute value part deux

    John Stegeman - Sun, 2007-06-03 08:59

    This is the second in a two-part series showing how to conditionally show/hide fields based upon the value of another field. In case you haven’t read the first part, start here.

    As we left before the commercial break, we had just set up the navigation links between our emplist and empedit pages. Let’s first fix up the “Edit” and “New Employee” buttons on the emplist page so that clicking them will take us to the edit page. To do this is really quite simple. For the edit button, select the button using either the visual editor or the structure window; then, use the property inspector to set the Action property to “edit,” which is the navigation case you created. There’s even a drop-down list in the property inspector – JDeveloper knows about the navigation cases, and thus can give you a drop-down list. Now, just do the same thing for the “New Employee” button; in this case, at run-time, the action listener (which creates the new employee record) will fire first, and then the action property causes the navigation case “edit” to fire, taking you to the empedit page.

    Now, let’s create the skeleton of the empedit page. First, ensure empedit.jspx is shown in the visual editor. Then, in the data control palette, drag MyEmpView1 to the empedit.jspx page and drop it. In the pop-up menu, select “Forms” and then “ADF Form” from the sub-menu. Ensure the “Include Submit Button” checkbox is selected and click “OK.”

    Let’s next add a commit button to the page so that the changes can be saved to the database. In the data control palette, expand the AppModuleDataControl and then expand the “operations” folder from the data control – be sure it’s the AppModuleDataControl’s operations folder, and not the MyEmpView1’s. If you have the correct one, you’ll see Commit and Rollback operations. Drag the commit operation and drop it on to the empedit.jspx page next to the submit button. In the pop-up menu, choose “ADF Command Button.” Finally, select the “Commit” button and use the property inspector to set the Action property to “returntolist.” This will make clicking the commit button take us back to the list page as well as committing the data to the database.

    Now, we’ve got a basic working application. Let’s test it; right-click the emplist.jspx page in the applications navigator and choose “run.” You should see an (empty) list of employees. Click the “New Employee” button, fill in some values, and click commit. You can also test editing an employee you create (use the “submit” button to submit changes and make ADF enable the commit button). For now, create at least two employees, one with emptype of “H” and one with emptype of “S.” Here’s what my emplist.jspx looks like after I did that:


    There’s a lot more we should to to make this a real application (adding a “Cancel” button, for example), but I’ll leave that to you. What we really want to show now is how to make the Salary field hidden (and set to null in the database) when the employee type is “hourly” and make the Hourly Rate field hidden (and set to null in the database) when the employee type is “salaried.” There are a number of ADF Faces controls we could use to implement this (they all start with “ShowOne”), but for this example, let’s use a ShowOneRadio.

    In the Component Palette, ensure the ADF Faces Core components are showing and locate the ShowOneRadio. Drag it to the empedit page and drop it between the Emptype and Salary fields (it’s easiest if you use the Structure window). Use the property editor to change the label to “Employee Type.” You should now have something that looks like this:


    The “ShowOne” components work by having “ShowDetail” components as their children. So, locate the ShowDetail component in the Component Palette and drag one to the af:showOneRadio you just created; again, it’s easiest if you use the Structure window to ensure you are dropping it on the right component. Use the property inspector to change the Text property to “Hourly.” Then, repeat the same process to create another ShowDetail component with its text set to “Salaried.” When you are done, the Structure should look something like this:


    The next step is to put the Salary and HourlyRate fields into the appropriate ShowDetail component. To do this, just use the structure window to drag the Salary’s af:inputText component to the “Salaried” showDetailItem and the Hourly Rate’s af:inputText component to the “Hourly” showDetailItem. Your structure now should look like this:


    If you run the application now, you’ll see that clicking the Hourly or Salaried radio buttons automatically hide/show the appropriate fields; however, the value of the EMPTYPE field doesn’t have anything to do with the radio buttons at this point. We’ll need to write some code to make that part work.

    The code that you will write needs to be in a java class known as a “managed bean.” We’ll let JDeveloper create the managed bean for us. If you click one of the af:showDetailItem components and look in the Property Inspector, you’ll see a property called the “Disclosure Listener.” This is where we will write our Java code (for you experts out there, yes, the af:showOneRadio has an attribute change listener that is a better place for the code, but for some reason, it does not fire correctly in JDev 10.1.3.1). To create the managed bean, select the Hourly showDetailItem in the Structure window and then click in the blank field next to “DisclosureListener” in the Property Inspector. You should see a button appear with an elipsis (…) in the label – click this button. You’ll get a dialog that looks like this:


    Click the “New…” button to create a new managed bean. Use “empedit_bean” for both the name and class and leave the scope set at request:


    After clicking “OK”, click the “New…” button next to the Method drop-down list. Provide a method name of “hourly_disclosed” and click “OK.” The dialog now looks like this:


    Click “OK” in the managed bean window. You should see a new tab at the top of the editor for the empedit_bean.java file that was just created. The code looks like this:

    import oracle.adf.view.faces.event.DisclosureEvent;
    public class empedit_bean
     {
     public empedit_bean()
     {
     }
    public void hourly_disclosed(DisclosureEvent disclosureEvent)
     {
     // Add event code here...
     }
     }

    Let’s add some simple code to the disclosure listener so that we can see how it works. Change the hourly_disclosed method so that it looks like this:

    public void hourly_disclosed(DisclosureEvent disclosureEvent)
    {
    if (disclosureEvent.isExpanded())
    {
    System.out.println(“The hourly type was selected”);
    }
    else
    {
    System.out.println(“The hourly type was not selected”);
    }
    }

    Now, let’s create a disclosure listener for the Salary radio button. Just type this code into the editor:

    public void salaried_disclosed(DisclosureEvent disclosureEvent)
    {
    if (disclosureEvent.isExpanded())
    {
    System.out.println(“The salaried type was selected”);
    }
    else
    {
    System.out.println(“The salaried type was not selected”);
    }
    }

    Finally, go back to the empedit.jspx page and select the af:showDetailItem for Salaried. In the property inspector, click the elipsis-button next to the DisclosureListener property and pick the empedit_bean managed bean and salaried_disclosed method:


    Now, when you run the application and click the Hourly and Salaried radio buttons, you should see a pair of messages in the log window for each click. This shows us that our code is running correctly. We now just have two steps remaining. The first is to make the value in the database field EMPTYPE determine which radio button is selected. This is really quite simple. If you go back to the empedit.jspx page and select the “Hourly” af:showDetailItem, you’ll notice a property in the property inspector called “Disclosed,” which is set to false. What we will do is to use an Expression Language (EL) expression to set the item to be disclosed if the value of the EMPTYPE field in the database is “H.” To do so, click the Disclosed property in the inspector. You should see the “Bind to data” button become available in the property inspector toolbar – it looks like this:

    Click the Bind to data button; this displays JDeveloper’s EL editor. In the editor, expand the ADF Bindings folder, then the bindings container. You should see an attribute binding for “Emptype.” Finally, expand that binding and double-click on the InputValue property. The Expression builder should now look like this:


    Complete the expression by typing so that it reads:

    #{bindings.Emptype.inputValue == “H”}

    Remember before when we set the default value of the field EMPTYPE to “H?” This is so we didn’t have to code for the special NULL case here, although it’s not too difficult. Now, repeat the same process for the Salaried af:showDetailItem, except the “disclosed” property should be:

    #{bindings.Emptype.inputValue == “S”}

    At this point, if you run your application and edit the two employees you created earlier (one hourly and one salaried), you should see the correct radio button selected and the correct field shown.

    We now just have some cosmetic issues to clean up (we still have the EMPTYPE field on the screen) and one remaining problem: clicking the Hourly and Salaried radio buttons do not change the value of the EMPTYPE field – we haven’t written the code to make this happen yet. Let’s start by getting rid of the EMPTYPE field. If you just click on the EMPTYPE af:inputText and delete it, it will also delete the binding in the underlying page definition, which will make our page break, because we use that binding. Here’s a little trick that I use to avoid this behavior. Before you delete the af:inputText item, open the page definition for empedit.jspx by right-clicking in an empty area of the empedit.jspx page and choosing Go to Page Definition. Now, you can go back to the empedit.jspx page and delete the EMPTYPE field. Now, just switch back to the page definition and close it, answering “no” when prompted to save changes.

    The last item remaining is to set the value of the EMPTYPE field as we click the radio buttons for Hourly and Salaried employees. This code will go into the disclosure listeners. Let’s code a simple utility method into our backing bean that will allow us to set the employee type, and we can call that utility method from the disclosure listeners. For good measure, let’s also NULL out the salary for hourly employees and null out the hourly rate for salaried employees. I provide the code here with no explanation; this is pretty straightforward JSF code at this point:

    private void setEmpType(String type)
    {
    FacesContext ctx = FacesContext.getCurrentInstance();
    Application app = ctx.getApplication();
    ValueBinding bind = app.createValueBinding("#{bindings.Emptype.inputValue}");
    bind.setValue(ctx, type);

    if (“S”.equals(type))
    {
    bind = app.createValueBinding(“#{bindings.Hourlyrate.inputValue}”);
    }
    else
    {
    bind = app.createValueBinding(“#{bindings.Salary.inputValue}”);
    }

    bind.setValue(ctx, null);
    }

    Now, it’s simply a matter of fixing up the disclosure listeners to look like this:

    public void hourly_disclosed(DisclosureEvent disclosureEvent)
    {
    if (disclosureEvent.isExpanded())
    {
    setEmpType(“H”);
    }

    }

    public void salaried_disclosed(DisclosureEvent disclosureEvent)
    {
    if (disclosureEvent.isExpanded())
    {
    setEmpType(“S”);
    }
    }

    There we are…. there are lots of little things that can be cleaned up and made more robust, but that’s the technique.


    Conditionally showing fields based upon attribute value

    John Stegeman - Sun, 2007-06-03 06:22

    A recent post on the OTN JDeveloper Forum asks how to hide/show fields on an ADF Faces page depending upon the value of another attribute. I’ve concocted a simple example to show the technique I used in a recent project to accomplish this.

    The business case in the example is quite simple: we want to create a page to allow us to create and edit employee information. There are two types of employees: salaried employees and hourly employees. Salaried employees earn a yearly salary regardless of how many long hours they work (sounds familiar), whereas hourly employees earn an hourly rate for each hour they work. Our database table looks like this:

    create table my_emp
    (empno number not null primary key,
    emptype varchar2(1) not null,
    salary number,
    hourlyrate number);

    On our employee edit screen, we want to show the salary field if the employee type is “S” (for salaried) and show the hourlyrate field if the employee type is “H”. In both cases, the field that does not apply to the particular employee should be hidden AND should be automatically set to null in the database. We’ll also create a typical list screen that lets us browse through the employees and select one to edit, and also to create new employees.

    I will walk quickly through the preliminary steps, and slow down later on to show the details. This example should work with any of the point releases of JDeveloper 10g R3 (10.1.3.x).

    To get started, use your favorite tool (SQL*Plus, SQL Developer, JDeveloper, etc) to create the table as shown above. Then, in JDeveloper, create a database connection to the database/account that owns the table you created. Finally, create a new application, selecting the “Web Application [JSF, ADF BC]” template in the directory of your choice:

    In your newly created application, you should have two projects – “Model” and “ViewController.” The next step is to create some ADF Business Components for the my_emp table. In this example, we’ll just create some basic stuff (a single Entity Object, a default editable View Object, and a default Application Module). To do this, right-click the Model project and choose “New…” In the dialog that appears, choose “ADF Business Components” in the categories pane and “Business Components from Tables” in the items pane:


    You’ll be prompted to initialize your project for business components. Pick the connection you created earlier (and the SQL flavor and type map if you are not using an Oracle database) and click “OK.” Now, you’ll quickly walk through the wizard to create the default business components. Click “Next” to dismiss the welcome screen, if it is shown. On the next screen, type “my_emp” in the name filter and press “Query” to display the table you created earlier; use the shuttle controls to add the MY_EMP table to the selected list:


    Click “Next” to display the “Updatable View Objects” page of the wizard; use the shuttle controls to add the MyEmp Entity Object to the selected list:


    From here on out, we will just be taking the defaults for the wizard, so you can click “Finish.” If you’d like to see the steps one-by-one, you can click “Next” on each page to see them. Once you are done, you will have a single Entity Object called “MyEmp,” an updatable View Object called “MyEmpView,” and an Application Module called “AppModule,” which has MyEmpView in its data model. You can see these objects by expanding the folders under the Model project in the Applications Navigator. To make things a bit easier down the road, let’s set a default for the employee type to “H” for hourly. To do this, double-click on the MyEmp entity object. In the dialog that appears, expand the Attributes tree on the left side and click the “EmpType” attribute. Now, in the right pane, enter “H” into the default value field and press “OK” when done:


    That’s it for the data model; in real life, you’d of course give some nice attribute names, display hints, etc, etc, but I’ll leave that to you. The next step for this exercise is to create the JSF pages. We will first create a simple list-type page that lists the employees and allows us to select an employee to edit or to create a new employee. To do this, right-click the “ViewController” project and select “New…” In the dialog that appears, select “JSF” from the categories pane and JSF JSP from the items pane. Click “Next” to dismiss the welcome page, if it is shown; this will display the “JSP File” step of the wizard. Let’s call our page “emplist.jspx” Make sure the “JSP Document” radio button is selected for the document type:


    Click “Next” to display the component binding page. As we do not need component bindings, click “Next” to display the tag libraries page. Ensure that you have the 4 libraries selected as shown here:

    Now, you can click “Finish” to complete the wizard. While we are at it, follow the same steps to create an “empedit.jspx” page. Now, let’s finish the emplist page – it’s the simpler of the two. To create an ADF Faces table, go to the Data Control Palette window in JDeveloper. You should see a single data control called “AppModuleDataControl.” Expand the data control, and you’ll find your single view object, “MyEmpView1.” Ensure that you have the emplist.jspx page open in the visual editor and drag the “MyEmpView1” view object on to the emplist.jspx page. In the pop-up menu that appears, select the “Tables” menu and then the “ADF Read-only” table from the sub-menu. Ensure that you click the “Enable Selection” checkbox and click “OK.” You’ll now see the af:table added to your page. If you run the page now, you’ll see an empty table because there are no rows in the database.

    Next, let’s add “Edit” and “New Employee” buttons to the page. First, get rid of the “Submit” button that JDeveloper created for you automatically by selecting it and hitting the Delete key. The “Edit” button that we are going to add must be inside of the af:tableSelectOne object that exists inside of the “selection” facet of the table. My preferred way to drag-and-drop components is to use the structure window in JDeveloper, because it gives me the most precision. So, expand the items in the structure window until you get to the af:tableSelectOne object (you can also click on the radio buttons in the “Select” column of the table to do this automatically):


    Now, find the Control Palette window in JDeveloper. Select “ADF Faces Core” from the drop-down at the top of the palette and find the “CommandButton” component. Drag this component and drop it into the af:tableSelectOne on your page. If you’ve done it correctly, you’ll se an af:commandButton inside of the af:tableSelectOne in the structure window, and your page will look like this:


    Use the Property Inspector window of JDeveloper to change the text of the button to “Edit.” Now, let’s add the “New Employee” button. Because the “New Employee” button does not require an employee to be selected first, it doesn’t belong in the selection facet of the table, but the “actions” facet. First, make sure that you can see the actions facet in JDeveloper’s structure window; secondly, in the Data Control Palette, expand the AppModuleDataControl, the EmpView1, and the Operations folder. Find the “Create” operation and drag it into the actions facet, using the structure window. In the pop-up window that appears when you release the mouse, select ADF Command Button. You can now use the property inspector to change the text of the command button to “New Employee.”

    Now, without going into a long explanation of why (read the docs if you must), we need to change the binding on the “New Employee” button from the “Create” action to “CreateInsert.” To do this, right-click in a blank area of the emplist.jspx page and choose “Go To Page Definition.” Using the structure window, expand the bindings folder and double-click on the “Create” binding. In the window that appears, change the action from “Create” to “CreateInsert:”


    Now, save and close the page binding. The final piece of work we need to do on this page is to set up some navigation cases so that we can navigate back and forth between the list and edit pages. I’m not going to walk through all of the steps here (a quick read of the docs should set you in the right direction). Create two navigation cases, one going from emplist.jspx to empedit.jspx called “edit” and one going from empedit.jspx to emplist.jspx called “returntolist.” Here’s what my faces-config.xml navigation diagram looked like after this step:


    Wow – this is more steps than I thought…. continued in part deux.


    DBA_HIST_FILEMETRIC_HISTORY

    Fairlie Rego - Sat, 2007-06-02 08:25
    There are so many new views in 10G that you begin to wonder if many of them are
    actually populated correctly. For example there are quite a few bugs with some of the DBA_HIST* views not being populated correctly. One such view DBA_HIST_TBSPC_SPACE_USAGE is notorious for incorrect values .But the view DBA_HIST_FILEMETRIC_HISTORY which supposedly stores alerts only for 7 days does not even seem to be getting populated.


    So on a 10.1.0.5 database

    SQL> select count(*) from dba_hist_filemetric_history;

    COUNT(*)
    ----------
    0

    If you have a look at the definition of the view from catawrvw.sql

    create or replace view DBA_HIST_FILEMETRIC_HISTORY
    (SNAP_ID, DBID, INSTANCE_NUMBER, FILEID, CREATIONTIME, BEGIN_TIME,
    END_TIME, INTSIZE, GROUP_ID, AVGREADTIME, AVGWRITETIME, PHYSICALREAD,
    PHYSICALWRITE, PHYBLKREAD, PHYBLKWRITE)
    as
    select fm.snap_id, fm.dbid, fm.instance_number,
    fileid, creationtime, begin_time,
    end_time, intsize, group_id, avgreadtime, avgwritetime,
    physicalread, physicalwrite, phyblkread, phyblkwrite
    from wrm$_snapshot sn, WRH$_FILEMETRIC_HISTORY fm
    where sn.snap_id = fm.snap_id
    and sn.dbid = fm.dbid
    and sn.instance_number = fm.instance_number
    and sn.status = 0


    SQL> select count(*) from WRH$_FILEMETRIC_HISTORY;

    COUNT(*)
    ----------
    0

    I have tried various ways to get this base table populated but
    am not sure when information is flushed to WRH$_FILEMETRIC_HISTORY.
    This behaviour is also consistent in 10.2.0.3

    Returning into clause and post statement triggers

    Jornica - Fri, 2007-06-01 17:04

    If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

    To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.

    SQL> CREATE TABLE t1(c1 VARCHAR2(10));

    Table created.

    SQL>
    SQL> CREATE TRIGGER t_ais_t1 AFTER INSERT ON t1
    2 BEGIN
    3 UPDATE t1
    4 SET c1 = 'TRIGGER';
    5 END;
    6 /

    Trigger created.

    Insert a row in t1 and return the new value of c1 into bind variable c1.

    SQL> VARIABLE c1 VARCHAR2(10)
    SQL>
    SQL> INSERT INTO t1(c1)VALUES('SQLPLUS') RETURNING c1 INTO :c1
    2 /

    1 row created.

    And inspect the contents of the table and the value of the bind variable.

    SQL>
    SQL> SELECT c1 "column",
    2 :c1 "bind"
    3 FROM t1
    4 /

    column bind
    ---------- ----------
    TRIGGER SQLPLUS

    1 row selected.

    As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

    Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.

    Access Migration Tutorial

    Donal Daly - Thu, 2007-05-31 13:33
    Are you considering migrating that Microsoft Access application to Oracle Application Express? Well read on...

    Hopefully you are aware of Oracle SQL Developer and that we have redeveloped the Migration Workbench and integrated it tightly with Oracle SQL Developer. An early adopter version of this is available now and will be production very soon. More of that in a subsequent post.

    With Oracle Application Express 3.0 we introduced the Application Migration Workshop to assist with migrating your Access Forms & Reports. When I talk about this solution, I get asked do we have a step by step guide or methodology for such migrations. So, we have produced a migration tutorial to address this and have published it on OTN.

    We have taken the Microsoft Access sample application, Northwind Traders and migrated it to Oracle Application Express. The tutorial covers this in step by step detail. Following this tutorial would be a useful exercise for any user that wishes to undertake migrating their applications from Microsoft Access to Oracle Application Express. We have called the converted application Southwind Wholesalers. :-)

    You can see it running on apex.oracle.com and we have also provided it as a packaged application so you can examine it in detail.

    Run system commands from Oracle with PL/SQL

    Hampus Linden - Wed, 2007-05-30 15:55
    I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
    He had full access to Oracle with sysdba/dba roles etc, but not SSH.
    I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

    One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
    Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
    I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
    The temporary spool file handling in my example is quite poor, but works. :)
    A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

    Example of use:
    oracle@htpc:~$ rsqlplus hlinden/password as sysdba

    SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007

    Copyright (c) 1982, 2005, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SQL> set serveroutput on
    SQL> @system_run

    Procedure created.

    SQL> exec system_run('ls -l /home/oracle/bin');
    total 12
    -rwxr-xr-x 1 oracle dba 797 Nov 5 2006 backup_controlfile.sh


    PL/SQL procedure successfully completed.

    SQL> exec system_run('uname -a');
    Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

    PL/SQL procedure successfully completed.

    SQL>
    And here is the procedure code:
    CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)
    IS
    script_file varchar2(40) := 'my-temp-script.sh';
    script_data varchar2(4000);
    MyFile utl_file.file_type;
    d varchar2(4000);
    dump_file varchar2(40) := '/tmp/my-temp-file.dat';
    dump_type utl_file.file_type;
    BEGIN
    -- Open file
    MyFile := utl_file.fopen('TMP',script_file,'w');
    -- Write data to file
    script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
    utl_file.put_line(MyFile, script_data, FALSE);
    -- Close file
    utl_file.fflush(MyFile);
    utl_file.fclose(MyFile);
    -- Purge old logs, no fun anyway
    dbms_scheduler.purge_log(JOB_NAME=>'TEST');
    -- Execute script
    -- The job is created as disabled as
    -- we execute it manually and will
    -- drop itself once executed.
    dbms_scheduler.create_job(
    job_name => 'TEST',
    job_type => 'EXECUTABLE',
    job_action => '/bin/bash',
    number_of_arguments => 1,
    start_date => SYSTIMESTAMP,
    enabled => FALSE);
    dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
    dbms_scheduler.enable('TEST');
    -- Wait for the job to be executed
    -- usually done within 1 second but
    -- I set it to 2 just in case.
    dbms_lock.sleep(2);
    -- Open the output file and
    -- print the result.
    dump_type := utl_file.fopen('TMP',dump_file,'r');
    loop
    begin
    utl_file.get_line(dump_type,d);
    dbms_output.put_line(d);
    exception
    when others then
    exit;
    end;
    end loop;
    utl_file.fclose(dump_type);
    -- Clean up our temp files
    utl_file.fremove('TMP', script_file);
    utl_file.fremove('TMP', dump_file);
    END;
    /

    Multirow Inserts

    Robert Vollman - Mon, 2007-05-28 12:36
    While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?INSERT INTO table (column1, column2)VALUES (value1, value2), (value1, value2);ERROR at line 1:ORA-00933: SQL command not properly endedUnlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com7

    Records

    Robert Baillie - Mon, 2007-05-28 12:02
    And to follow on from the last post... my current personal bests: I figure if I keep them here, at least I'll always know where they are! 5km Run23:44 (Battersea Park, 'Beat the Baton' 28/05/07) 10km Run53:23 (Hyde Park, 'Run London' 08/10/06) Half Marathon2:17:49 (Redcar, 'Tees Valley Half Marathon' 12/03/06) Rubik's cube57 seconds

    Records

    Rob Baillie - Mon, 2007-05-28 11:45
    And to follow on from the last post... my current personal bests:

    I figure if I keep them here, at least I'll always know where they are!
    5km Run23:44 (Battersea Park, 'Beat the Baton' 28/05/07)
    10km Run53:23 (Hyde Park, 'Run London' 08/10/06)
    Half Marathon2:17:49 (Redcar, 'Tees Valley Half Marathon' 12/03/06)
    Rubik's cube57 seconds

    Targets

    Robert Baillie - Mon, 2007-05-28 11:44
    You've gotta have targets. The more I try to motivate myself to do things, the more I realise that if I don't have a target it's incredibly difficult. When I realised this it came as a big surprise to me. I'm really not the sort of person to have a 5 year plan or career goals, but it seems that if I don't set myself an only just achievable goal I find it very difficult to motivate myself to do much. I keep myself fit so that I get the most out of playing football. But just having that in mind isn't enough to get me out and running. If I didn't set myself a target time for a 5km or 10km run and then book a place at a running event, then I'd just sit on my fat arse every night watching TV. OK, so I may be exaggerating my self deprecation, but you get the idea. I find that this affects me in many different aspects of my life. To motivate myself to run I set a target (public) 5km or 10km time (this year it's 22:30 and 50:00 respectively). To motivate myself to learn to do the...

    Targets

    Rob Baillie - Mon, 2007-05-28 11:12
    You've gotta have targets.

    The more I try to motivate myself to do things, the more I realise that if I don't have a target it's incredibly difficult.

    When I realised this it came as a big surprise to me. I'm really not the sort of person to have a 5 year plan or career goals, but it seems that if I don't set myself an only just achievable goal I find it very difficult to motivate myself to do much.

    I keep myself fit so that I get the most out of playing football. But just having that in mind isn't enough to get me out and running. If I didn't set myself a target time for a 5km or 10km run and then book a place at a running event, then I'd just sit on my fat arse every night watching TV. OK, so I may be exaggerating my self deprecation, but you get the idea.

    I find that this affects me in many different aspects of my life.

    To motivate myself to run I set a target (public) 5km or 10km time (this year it's 22:30 and 50:00 respectively).

    To motivate myself to learn to do the Rubik's cube, I set myself a target completion time (1 minute - yup, managed it).

    To motivate myself to save money I set a target amount to reach by a certain date (nope, not telling you how much).

    A friend of mine decided that he'd set himself the target of taking a photo a day for a year and posting it on his site. I may have to steal that idea next year... but until then you can find his here: www.ysr23.com/blog. It really is damn good.

    I do the job I do because I just flat out enjoy it. As soon as it becomes too much of a chore I'll move on. And I reckon I'm doing alright career wise in whatever way you choose to measure it. For me the only measure that truly counts is enjoyment, and in the main it's a damn fine job. Well, it is most of the time anyway ;-)

    Someone at work once said to me: You know, every now and again Tom Cruise probably gets up in the morning, probably on set, in his trailer and thinks to himself "Damn, gotta do some of that acting shit again today". OK, so he gets paid more in a minute that I do in a year, but you get the point.

    And the big thing that keeps my enjoying my job is that I'm still learning new things. I suppose I have a clear target in my career to always keep on learning and to surround myself in people who can teach me. It's probably one of the biggest reasons why I'm so pleased to be working with Extreme Programming. It makes it easy to fulfill that goal. And it works on a clear system of easy to understand targets.

    A release to the business has a target set of functionality.
    A single story has a clearly defined purpose.
    A unit test gives you a goal that must be met, and a clear way of determining the success or failure.

    Layers of targets.

    And if you're doing XP properly you get to celebrate when you meet those targets.

    A brief whoop when the unit test passes.
    A handful of jelly beans when the story's complete.
    A damn big meal and a piss up when a release hits the business.

    OK, so real life targets don't have quite the same level of celebration, but it's the same deal.

    Set yourself a clear target and you get clarity of purpose in aiming for it, and the celebration when you pass it.

    Cyrus IMAP file system tuning

    Hampus Linden - Mon, 2007-05-28 04:59
    Been busy, not enough blogging, bla bla bla. I know.
    Just a lot of stuff going on at work at the moment, mergers and integrations.

    We've had some problems with one of our IMAP servers at work running Postfix and Cyrus IMAPd. A for the job quite well speced machine, dual Xeons and 3x146Gb disk in RAID5 (4x146 in RAID10 would have been nicer). Anyway, the machine has got 50 or so IMAP users and perhaps 100Gb spool data on a ReiserFS partition.
    The machines' avg. load has peaked at over 6.00 with about 75% in iowait on a bad day. I suspect that modern fancy e-mail search tools are to blame for the problems, applications building search indexes and such (Apple Mail anyone?).
    Monitoring of the server showed quite a lot of inode update activity, even though there isn't *that* much new email coming in.
    Must be our old (not so) dear friend atime that's making a little mess, I've used the noatimea and nodiratime mount options in the past with great success. Seen performance improvements of a couple of percent.
    The mount-options noatime and nodiratime simply disables the feature to update the access timestamp of a file (and directory). I.e. when someone clicks and reads an email in their mail application the inode atime timestamp is updated. When is this atime timestamp used? Never.
    Did a quick online remount of the spool fs with noatime and nodiratime.
    The result?
    Avg. load hasn't touch 1.00 since. Wow! I was expecting an improvement, but not that big.
    Great and easy way to improve performance on IMAP spools.
    mount -o remount,noatime,nodiratime /var/spool/imap
    And don't forget to update /etc/fstab with the same mount options.
    Cool.

    What Makes a Great Oracle Blog?

    Robert Vollman - Fri, 2007-05-25 13:27
    Along the side of my page, you'll see my favourite Oracle blogs listed. I carefully maintain this list of fellow enthusiasts whose opinions and insights I most especially want to follow among the seemingly hundreds of Oracle blogs that are out there. Studying them, I think you'll find that each of them share the same core qualities listed below.1. AccuracyAccuracy is an absolute must. Just Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com18

    Listener passwords: always for 9i, never for 10g

    Andrew Fraser - Thu, 2007-05-24 06:32

    This page has been moved to http://andrewfraserdba.com/?p=44


    Categories: DBA Blogs

    J2EE Container Managed Security: How to reference the current user

    Brenden Anstey - Wed, 2007-05-23 23:48

    When J2EE container managed security is used the User Principal can be referenced in a number of ways:


    Expression Language
           <af:outputText value="#{facesContext.externalContext.userPrincipal.name}"/>

    Managed / Backing Bean
           ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
    String userName = ectx.getUserPrincipal().getName();
    System.out.println("Current user: " + userName);

    ADF BC Application Module
           String userName = getUserPrincipalName();
    System.out.println("Current user: " + userName);


    A year already? So long incubator!

    Adam Winer - Wed, 2007-05-23 16:20
    How time flies... A year ago, we checked ADF Faces into the Apache incubator. Now, we're out of the incubator, we're named Trinidad, and we're officially part of the Apache MyFaces project. You can visit our site, and download nightly builds .

    A lot has happened in this past year - a few highlights:
    • A bunch of new committers were added from inside Oracle and, most importantly, from outside Oracle
    • All the ins-and-outs of running an Apache project were ably handled by Matthias Wessendorf
    • Skinning functionality has gotten a lot better, mostly courtesy of Jeanne Waldman
    • Portlet support from Scott O'Bryan
    • Client-side validation now looks much better (no more JS alerts), from Danny Robinson
    • Lots and lots of bugs (300+) were put to ground
    • New components - a spinbox and an outputDocument
    • JSF 1.2 support was implemented (on a branch); the MyFaces implementation of the 1.2 JSF API uses a Trinidad plugin to generate components and tags.
    • And, I got engaged! (The future Mrs. even lets me get away with working on Trinidad at home.)

    It all took awhile, and a lot of work, but we're all glad to have reached this point.

    Pages

    Subscribe to Oracle FAQ aggregator