Feed aggregator

Screencast of Oracle E-Business Suite setup management tool

Raimonds Simanovskis - Thu, 2008-07-24 16:00

URRA.pngWarning: this post will be unrelatead to Ruby :)

For many years our company is using tool for Oracle E-Business Suite setup management that we initially built for our own purposes but now also we are offering to other Oracle partners and customers.

As I was bored to do demonstrations of it over and over again I finally decided to create a screencast of this tool (it’s 22 MB large). And I think that my first experiment in screencasting is not too bad :)

Previously screencasting was quite hard stuff as there was not so many good tools for that. In this case I used ScreenFlow tool which was released this year (and is available just on Mac OS X Leopard) and I have to say that it is much more better and easier compared to any other tools that I have seen earlier. So I am strongly recommending it to others who are interested in screencasting.

Categories: Development

Reset the APEX internal password

Duncan Mein - Thu, 2008-07-24 09:25
I noticed in one of the comments of Dimitri Gielis articles that Jornica pointed out a script called apxXEpwd.sql

I ran this as the SYS user and sure enough, it allows you to reset the ADMIN password for the internal workspace.

This script can be found in the root apex directory that you download from OTN.

Very useful when you forget what that password is!

Oracle Locator Express

Jared Still - Wed, 2008-07-23 16:44
If you do much work with the Oracle database on Windows, and you have 1+N Oracle homes installed, you've probably lamented the fact that the Oracle Home Switcher is no longer included with Oracle.

I can't recall exactly what the tool was called or which version Oracle was at when it ceased to be a part of the installation. I do know that it doesn't work with 10g+.

A little tool called Oracle Locater Express fills this niche nicely, and it does work with 10g. Sorry, have not yet tried it with 11g.

"Oracle Locator Express"


I've used it for several months without issue, save one minor glitch.

Sometimes Oracle 10g Homes are not displayed properly in the list of Oracle Homes to choose from. Other than that, no complaints

Categories: DBA Blogs

How to get mail working with Apex packed with 11g ..

Pankaj Chandiramani - Tue, 2008-07-22 22:44

Hi , Recently i installed 11g db on one of my systems ,as it comes with APEX i thought to move my apex app(which were in 10g) to the same .........when i moved my apps , i got everything working but mail .......it gave error :ORA-24247: network access denied by access control list (ACL)

The issue is with ACL as by default, the ability to interact with network services is disabled in Oracle Database 11g. To enable i needed to run a procedure as shown below .

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give FLOWS_030000
-- the "connect" privilege if FLOWS_030000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, make sure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'FLOWS_030000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'FLOWS_030000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'FLOWS_030000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'FLOWS_030000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Finally got it working :)

Categories: DBA Blogs

More APEX sessions at Oracle OpenWorld 2008

Oracle Apex Notebook - Mon, 2008-07-21 11:35
According to Oracle OpenWorld Blog, the top 25 voted sessions at Oracle Mix will be presented at Oracle OpenWorld 2008. Among the 25 winners there is a good representation of APEX, meaning that APEX community is very active. The winners, APEX related are: Rank: 1 (113 votes) Security: Writing Custom Authentication Schemes for Oracle Application Express (Oracle APEX).  Submitted by: Raj Mattamal
Categories: Development

Identifying Descriptive Flexfield in the Form

RameshKumar Shanmugam - Sun, 2008-07-20 22:08
Descriptive Flexfield are used to gather additional information about the business entity beyond the information required by the Oracle application

Or we can also define the descriptive flexfield as the configurable fields which we can used to capture the additional information

Descriptive flexfield appears on a form as a field enclosed within Brackets

Many time I get the question from my functional users how they will be able to identify the descriptive flexfield name in the form.

There is an easy way to find this

Navigate the the Form where you want to find the Descriotive flexfield
The select the following Menu

Help > Examine

In the Block Select ‘$DESCRIPTIVE_FLEXFIELD$
In the field select the Form in which you are looking for example “ PERSON.DF’. The Value field will display the Descriptive flexfield that you are looking for.

Categories: APPS Blogs

TechNight All India Oracle User Group (AIOUG)

Virag Sharma - Sat, 2008-07-19 11:48

AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

Murali Vallath

Phani Arega






Categories: DBA Blogs

EJB 3 In Action available at Safari Online

Debu Panda - Fri, 2008-07-18 03:37
If you enjoy reading online then it's a good news for readers of EJB 3 in Action. It is now available online at Safari

Oracle OpenWorld 2008

Pawel Barut - Thu, 2008-07-17 15:44
Written by Paweł Barut
It's only 2 month left to Oracle OpenWorld 2008. Few news about this conference and my first time ever participation. Last week I've registered for OOW2008 as Blogger. Yesterday I've received confirmation email - I was accepted. I'm really looking forward to be in San Francisco and meet with all Oracle geeks, enthusiast and other bloggers.
I still need to apply for US Visa, and organize my trip. And of course choose sessions in which I would like to participate.

There is also one good news for people in Eastern Europe, Post Soviet Republics, Middle East & Asia. People from those countries can get discount on registration fee.
Attendees will need to enter EMKT as their priority code during "Step 2" of the registration process.

The countries from the EE, CIS & MEA regions which qualify for the discount are:

African Operations (except South Africa), Albania, Armenia, Azerbaijan, Belarus, Bosnia & Herzegovina, Bulgaria, Croatia, Czech Republic, Cyprus, Estonia, FYR Macedonia, Georgia, Hungary, Iraq, Iran, Jordan, Kazakhstan, Kyrgyzstan, Latvia, Lebanon, Lithuania, Malta, Moldova, Montenegro, Oman, Palestine, Poland, Romania, Russia, Serbia, Slovakia, Slovenia, Syria, Tajikistan, Turkey, Turkmenistan, Ukraine, Uzbekistan, and Yemen.

Do not wait. Register for Oracle OpenWorld 2008 !

Paweł

--
Related Articles on Paweł Barut blog:

Categories: Development

Dynamic Date Filtering in Interactive Reports

Anthony Rayner - Wed, 2008-07-16 06:26
Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Introduction...
Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...


But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

How...
Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).


This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.



Method 2

It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual
2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless
3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#
Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:


Of particular interest here are...
  • Clear Cache = '2,CIR'
    This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

  • Item Names = 'IRLT_REVIEW_DATE'
    This can be broken down as follows:
    • 'IR': This is used to set interactive report filters dynamically over the URL.

    • 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.

    • 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.


This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.


Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
(case 
when review_date < sysdate then 'Yes'
else
'No'
end) review_overdue
Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:


This could then be saved by the user as a 'Named Report' for future use.


Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.


Conclusion...
So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).


A couple of questions for the community...
  • Would you find it useful to be able to filter by SYSDATE?

  • Would you find it useful to be able to filter not just by a value, but by another column value?

  • What else would you like to see incorporated into Interactive Reports to make them even better?


Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

Categories: Development

System 9 Gadgets

Oracle EPM Smart Space - Tue, 2008-07-15 16:55

A while ago I created some System 9 gadgets for the Windows Vista sidebar.   The project went well but when Hyperion was bought by Oracle it kind of got lost and forgotten.  Well I found the source while going through some old files and I decided to dust them off.  I removed the old branding and made sure they worked with the newest version of System 9 (11.1). 

image

image 

There are 2 gadgets, one that lets you browse the System 9 repository and another that stores favorite reports that you set in the first.  The gadgets are great for the user that opens the same key reports on some sort of periodic basis.

I think these are pretty useful and some users may want them.   Next steps are to figure out how to post them and to see if I can quickly convert them to Google gadgets.  I will update the blog when I get them posted.

Categories: Development

Import APEX application via SQL Developer

Duncan Mein - Mon, 2008-07-14 13:43
I will be honest and admit that I have not been SQL Developers biggest fan since it's release a few years ago. Having always used 3rd party products such as TOAD and PL/SQL Developer, I found certain things a little irritating and not overwhelmingly obvious when forced to use SQL Developer.

Recently however, I found an absolute god send of a feature in SQL Developer and that was the ability to import / export APEX applications. This means that the Web GUI for such actions is no longer required.

Very simply, you connect to your parsing schema's database account via SQL Developer, right click on the Application Express tree directory and select Import Application. This opens a simple wizard and off you go.

One other cool feature of this is that you can open the details window and see exactly what your import is doing, something that is not possible when using the web GUI.

Download SQL Developer from: OTN

I Guess I was Wrong

Oracle EPM Smart Space - Fri, 2008-07-11 09:26

About a month or so back I did a post on Silverlight and that post lead to a comment on iPhone support.  At the time I completely punted on my response saying that the mobile market will have to narrow before you will see any sort of BI applications for the phone.  Well I am officially eating crow and admitting it.  Here is a press release from yesterday…

Oracle Announces Oracle® Business Applications for iPhone Available on Apple App Store

Ok, Ok I was wrong, and I admit it but the real news here is that this helps make my case about User Experience being a key driving point for future BI applications.  If users get this great experience on their phones they will also expect it on their desktop or in their browser.

Categories: Development

Something is VERY busy

Claudia Zeiler - Fri, 2008-07-11 01:20
I have been suspecting that something is causing excess connects/disconnects from our database. To see the number of connects I went in search of the listener.log. By default it is in $ORACLE_HOME/network/log. There was a listener.ora there , but according to it, there hasn't been ANY connections to our database in months!

This is a solaris box. I read that there is a 2G file limit on Solaris. (Maybe that means a limit on log files. Obviously, my data files are much larger than that.).

I issued the lsnrctl command:

lsnrctl >
set log_file listener2.log


Quickly it resumed recording connections, now into listener2.log. It recorded 2 of them EVERY second!!! OK. my suspicion was right. Now to track down exactly WHAT is connecting and disconnecting so much.

New job...

Anthony Rayner - Thu, 2008-07-10 07:59
Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.

Anthony.
Categories: Development

Custom ActiveRecord create, add and delete methods for legacy databases

Raimonds Simanovskis - Wed, 2008-07-09 16:00

In some Ruby on Rails projects I am putting ActiveRecord interface on top of existing legacy databases. It is quite easy to specify legacy table names and primary keys in ActiveRecord models so that ActiveRecord would generate correct SQL SELECT statements to read data from legacy databases.

But it is more difficult to insert, update and delete in legacy databases using ActiveRecord. When using Oracle legacy databases then quite often they have exposed PL/SQL APIs for writing to tables and you are typically not allowed to directly modify tables with INSERT, UPDATE and DELETE statements.

Therefore I created support for custom create, update and delete methods in the version 1.1.3 of ActiveRecord Oracle enhanced adapter which uses also my ruby-plsql gem.

Let’s look at the following example. Assume that we have the following table:

CREATE TABLE test_employees (
  employee_id   NUMBER(6,0),
  first_name    VARCHAR2(20),
  last_name     VARCHAR2(25),
  hire_date     DATE,
  salary        NUMBER(8,2),
  version       NUMBER(15,0),
  create_time   DATE,
  update_time   DATE
)

And we have the following PL/SQL API package that should be used to write to this table:

CREATE OR REPLACE PACKAGE test_employees_pkg IS
  PROCEDURE create_employee(
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER,
      p_employee_id   OUT NUMBER);
  PROCEDURE update_employee(
      p_employee_id   NUMBER,
      p_first_name    VARCHAR2,
      p_last_name     VARCHAR2,
      p_hire_date     DATE,
      p_salary        NUMBER);
  PROCEDURE delete_employee(
      p_employee_id   NUMBER);
END;

Then we define ActiveRecord model in the following way:

class TestEmployee < ActiveRecord::Base
  set_primary_key :employee_id
  # should return ID of new record
  set_create_method do
    plsql.test_employees_pkg.create_employee(
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary,
      :p_employee_id => nil
    )[:p_employee_id]
  end
  # return value is ignored
  set_update_method do
    plsql.test_employees_pkg.update_employee(
      :p_employee_id => id,
      :p_first_name => first_name,
      :p_last_name => last_name,
      :p_hire_date => hire_date,
      :p_salary => salary
    )
  end
  # return value is ignored
  set_delete_method do
    plsql.test_employees_pkg.delete_employee(
      :p_employee_id => id
    )
  end
end

And as a result we can use this model in the same way as other ActiveRecord models:

@employee = TestEmployee.create(
  :first_name => "First",
  :last_name => "Last",
  :hire_date => Date.today
)
@employee.reload
@employee.first_name = "Second"
@employee.save!
@employee.destroy

And all writing to the database will be done using defined API procedures.

Currently this functionality is embedded into Oracle enhanced adapter but if somebody needs it also for other databases this functionality could easily be extraced from the adapter.

To get the new release of Oracle enhanced adapter just do:

sudo gem install activerecord-oracle_enhanced-adapter

Source code of Oracle enhanced adapter is located at GitHub and you can submit bug reports and enhancement requests in Lighthouse.

Categories: Development

New Features in Approving Expense Reports in Oracle Internet Expense (iExpense) using AME

Krishanu Bose - Wed, 2008-07-09 07:17
Following features are new in R12 Oracle Internet Expense if you are using AME for Manager Expense report approval.
1. Parallel Approval
With Parallel Approvals, you will be able to route approvals in parallel when expense reports are charged to multiple cost centers, multiple projects, or multiple awards. This will streamline the approvals process and thus ensure users are reimbursed as quickly as possible.
2. FYI Notification
Now, you will be able to define rules in AME to send FYI notifications to managers and others who should be informed of expenses charged to their area of authority, but who do not need to approve the expense report.
3. Aggregation of Amount
You will be able to route expense approvals based on the aggregated amounts charged to cost centers or projects. The approval notification will show both the total amount to be approved, and how much of each expense was charged to the area of approval authority. This feature will ensure that proper approval authority is enforced when an expense report is allocated to many different cost centers, projects, or awards.

Performing a JOIN on groups of values using nested tables

Mike Moore - Tue, 2008-07-08 12:35
-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does
-- a group by, and the details of that group are collected into a nested table.
-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.

--DROP TABLE D CASCADE CONSTRAINTS;

CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO d (animal, grp) VALUES ('cat', 4);
INSERT INTO d (animal, grp) VALUES ('cat', 7);
INSERT INTO d (animal, grp) VALUES ('toad', 7);
INSERT INTO d (animal, grp) VALUES ('frog', 4);
INSERT INTO d (animal, grp) VALUES ('frog', 7);
COMMIT ;

-- DROP TABLE c CASCADE CONSTRAINTS;

CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;
INSERT INTO c (animal, grp) VALUES ('cat', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 66);
INSERT INTO c (animal, grp) VALUES ('cat', 33);
INSERT INTO c (animal, grp) VALUES ('cat', 44);
INSERT INTO c (animal, grp) VALUES ('cat', 66);
INSERT INTO c (animal, grp) VALUES ('frog', 44);
INSERT INTO c (animal, grp) VALUES ('toad', 44);
COMMIT ;

SELECT grp, animal FROM c ORDER BY grp, animal;
SELECT grp, animal FROM d ORDER BY grp, animal;

CREATE OR REPLACE TYPE string20_table IS TABLE OF VARCHAR2(20);

DECLARE
mystr VARCHAR2(80);
BEGIN
FOR cur1 IN
(SELECT *
FROM (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1
FROM c GROUP BY grp)
JOIN
(SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2
FROM d GROUP BY grp)
ON tab1 = tab2
)
LOOP
DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)
|| '='
|| TO_CHAR(cur1.grp2));
END LOOP;
END;
-- final output
-- 1=4
-- 44=7
-- 66=4

Pages

Subscribe to Oracle FAQ aggregator