FreeQL – Free Oracle SQL Select Statements

AshokOfferingsLeave a Comment

M&S Consulting has begun a revolutionary service where we will write Oracle SQL statements for FREE. We have named this service FreeQLTM.

ms_freeql_logov1

Who can use FreeQL?

Anybody can request an Oracle SQL statement to be written, as complex as you desire. You must provide a valid, non-free email address that we will use to communicate with you. That means that we will not entertain requests from gmail, hotmail, yahoo, etc.

What’s the catch?

With years in the business writing high performing, very practical SQL statements for Oracle customers (many in the business intelligence space), we want to share our knowledge and get the word out about our skills, ability, and friendly business style.

You are welcome to sign up for our newsletter or make a small payment if you desire, though that is not a requirement.

I’m in. Now what?

Simply populate the FreeQL form and we will respond to you with SQL. Everyone wants something without spending money…and we’re here to give it to you!

Oracle SUBSTR Function – SQL Syntax Examples

AshokTechnical TipsLeave a Comment

The Oracle SUBSTR SQL Function is very common, and many times misused.

Below shows the SUBSTR function along with the arguments it takes:

1

substr(string, POSITION)
1

substr(string, POSITION, substring_length)

The Oracle SUBSTR function returns a portion of string, beginning at character position, substring_length characters long.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

Supplier Portal – Oracle EBS and Middleware

AshokCase StudiesLeave a Comment

The Challenge

ABC, a publicly traded services and manufacturing organization, has leveraged Oracle E-Business Suite (EBS) to manage a variety of back-office functions, including their suppliers. Suppliers have been provided data from EBS manually by internal teams — the data sent has varied in both content and as well as format.

Internal teams spend valuable time generating reports manually for external supplier organizations and suppliers are left with the tedious process of generating their own reports. All the while, both groups may be relying on measures out of line with ABC’s priorities.

The Solution

We took a systematic approach to the issue, addressing:

  1. EBS/Data
  2. Discoverer/Reporting Logic
  3. Portal/UI

The Results

Finally, by setting up proper SSO and hooks from Portal to Discoverer and integrating end-to-end all the way down into the database, users in Portal (regardless of whether they are internal or external users) see only what their user account can see. Additionally, by leveraging the latest Portal 10.1.4 features, we were able to provide ABC with a portal that had a completely customized look-and-feel matching their external website exactly.

Within days, the entire solution was architected, designed, and developed. We were all excited about the quick progress and all of ABC’s expectations were exceeded.

alter table, add column – Oracle Example Syntax

AshokTechnical TipsLeave a Comment

Following is simple example syntax for adding a column to an Oracle table using “alter table” SQL: 1 2 ALTER TABLE [my_table_name] ADD [my_column_name] [my_data_type];ALTER TABLE [my_table_name] ADD [my_column_name] [my_data_type]; A simple example would be: 1 2 ALTER TABLE employee ADD tps_reports_completed NUMBER;ALTER TABLE employee ADD tps_reports_completed number;

Selected for Oracle iSupplier Portal Integrated Solution

AshokNews and UpdatesLeave a Comment

Bringing together many components of the Oracle Fusion Middleware Suite, as well as Oracle Applications (via E-Business Suite), M&S is uniquely positioned to delivery immense value in a short amount of time. M&S is designing and implementing a security, integration, and user presentation architecture with Oracle’s EBS, Discoverer, Portal, and Identity Management.

Conditionally Purge BPEL Instances on 10.1.3.x

AshokOfferings, Technical Tips1 Comment

Marc Kelderman wrote a helpful article on this topic: http://orasoa.blogspot.com/2007/03/delete-bpel-instances.html One of the users there notes: Note there is a patch to 10.1.3.3 to purge old data (6372741). Its a fairly dumb one that deletes everything older than a given date. Trying to do something more elaborate we have noticed that if you use the script in this article as a basis then rows in xml_document table are not removed. We have written code to conditionally purge BPEL instances in a sophisticated manner, taking into account custom logic, scheduling, and performance for multiple customers in SOA/BPEL versions 10.1.2 and 10.1.3.x. … Read More

Oracle BAM – SQL to List Users and Roles

AshokTechnical TipsLeave a Comment

I don’t really like what Oracle did with BAM user/roles and authentication. I believe they took Microsoft (SQL Server) tables and just slapped them into Oracle (now requiring that we put quotes around tables and columns). I assume this process will change in the upcoming releases, but here is a quick SQL statement you can use to get a look at your users and their roles as BAM understands them: 1 2 3 4 SELECT iu."SysIterName" username, iur."RoleName" userrole FROM orabam."SysIterUser" iu, orabam."SysIterRoleUserMapping" iur WHERE iur."UserID" = iu."SysIterID" ORDER BY iu."SysIterName"SELECT iu."SysIterName" username, iur."RoleName" userrole FROM orabam."SysIterUser" iu, orabam."SysIterRoleUserMapping" iur … Read More