When working in one database you can make reference to data in another database by creating a database link.

To create an Oracle database link, the quickest way is to just create a public database link using a user id in the other database that has permissions to reference the data you need. {LINKNAME} = the name for the link you want to create, {LINKUSER} = the user on the remote database that has access, and {LINKUSERPASSWORD} is that users password.  {TNSNAMES-DB-ENTRY} is the database entry name from the TNSNAMES.ora file.

create public database link {LINKNAME} connect to {LINKUSER} identified by {LINKUSERPASSWORD using ‘{TNSNAMES-DB-ENTRY}’;

To Reference the data from the linked database, simply add the link identifier to the table you are selecting – for example:

select * from remotetable@{LINKNAME};

To Remove the database link:

drop public database link {LINKNAME};

In PeopleSoft you can use datamover to add SQL security.  The reason for this is even though you log into PeopleSoft as a specific user once the user is verified it actually uses the user that is associated with the psaccessprfl access profile.  This means that in oracle typically the user sysadm is the user behind the access profile, and since the sysadm user is typically built using the PSADMIN role profile, you have priviledges to run a lot of commands from datamover.

A good example of this is when you build a table in PeopleSoft but you can’t see it in SQL because the user account you have in Oracle does not access to the new table.  So in order to grant access to this you can do a couple of different statements.  The simplest is to grant select access on the new table to a specific SQL user account:

grant select on new_table to sql_user;

Often times there are roles setup that are assigned to a user, if you do a:

select * from dba_roles;

You can see all the roles that are on the system.  Typically there is a role that will be setup that will allow “select” access and another role that is setup to allow “select, update, delete, insert”, sometimes referred to as SUDI access.  So lets say for example there is a role called SUDI_ALL_TABS, you can issue the statement in datamover:

grant select, update, delete, insert on new_table to SUDI_ALL_TABS;

Note: The new_table needs to have the ps_ qualifier on it, as this is not a typical function of datamover and therefore needs the fully qualified table name.

Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);

When you are at that client site that insists that you must use your own account to interact with PeopleSoft but they don’t set your schema, just change your default schema using the command:

alter session set current_schema=sysadm;

Where sysadm is the default PeopleSoft schema in Oracle, however, PeopleSoft does not have any issues with the schema owner being different then sysadm.  A couple of ways to find the schema, is to look at the table:  psdbowner (ownerid) or psstatus (ownerid). By default the psdbowner table is stored in the PS schema.

When doing SQL in Oracle where you want to search for something containing a string, you can use the like command.

 Select * from table1 where field1 like ‘somevalue’;

This will return rows with field1 = somevalue, not exactly useful.  But you can use the wildcard ‘%’ so

 Select * from table1 where field1 like ‘%somevalue%’;

This will return rows with field1 containing the string ‘somevalue’ in any form.  There is another wildcard ‘_’ which will match any character in that position, so

Select * from tabe1 where field1 like ‘_omevalue’;

This will return rows with field1 = aomevalue, bomevalue, comevalue, domevalue, …., zomevalue

If you want to use the the literal of % and _ then you will have to put an escape character in front of it, and tell Oracle what the escape character is:

Select * from table1 where field1 like ‘lookfor\%’ escape ‘\’

This will return rows where field1 = ‘lookfor%’