Ask Question
Help Others
Home

Blogs

Oracle Minus Operator

Mon, 07/14/2008 - 5:05pm — Zooz

I find the MINUS operator very handy and use it a lot to compare tables and find out missing things.

For example suppose you want to compare Table1 with Table2.

If the two tables have the same layouts (same column names and data content), you can simply do this

SELECT * FROM <Table 1>
MINUS
SELECT * FROM <Table 2>

This query will compare each record in Table 1 to a record in table 2.
The result returned will be records in table 1 that are not in table 2.
you can reverse the MINUS order to get records in table 2 that are not in table 1

Grant and Revoke privileges

Fri, 07/11/2008 - 6:40pm — Zooz

Typically in a database you have many users.

The owner shcema that owns the objects, usually decides the privileges given to other users on the objects it owns.

Lets call the schema owner "OWNER" and suppose we have another user in the database called "READER".

For the user "READER" to be able to view the data in a table owned by "OWNER", the owner has to grant the reader select priviliges.

GRANT SELECT ON <table name> TO READER;

Hide a Subpage Using PeopleCode

Sat, 07/05/2008 - 11:43am — Lepa

There was a question asked on the forum on how to hid a subpage. There is no Peoplecode function to hide a subpage directly, BUT one way around that is putting the subpage in a group box and hid the group box instead. Hiding the group box will end up hiding your sub page.

Make sure to follow the following post, it will walk you through how to hide a group box on a page.

Create a Database Link

Thu, 07/03/2008 - 12:26pm — Zooz

In many cases you might need to compare data between tables across different databases. For example your test instance and production instance.

You can easily create a database link between the 2 instances and be able to access the production data from the test database.

CREATE DATABASE LINK <Link Name>
 CONNECT TO <Schema Name>
 IDENTIFIED BY <PWD>
 USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <Host Name>)(PORT = <port#>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <Database Name>)
    )
  )';

Get Table Sizes in your Schema

Thu, 07/03/2008 - 12:06pm — Zooz

If you want to see how much space your tables are taking in the database, log in to the shcema and execute this SQL.

SELECT   segment_name table_name, MAX (tablespace_name) tablespace_name,
         SUM (BYTES) / (1024 * 1024) table_size_mb
    FROM user_extents
   WHERE segment_name IN (SELECT tname
                            FROM tab
                           WHERE tabtype = 'TABLE')
GROUP BY segment_name
ORDER BY tablespace_name

The Above query tells you the table name, the table space it is residing in and the size in MB.

Thanks,

Zooz

Enable Row Movement Between Table Partitions

Thu, 07/03/2008 - 11:55am — Zooz

If you have a table partitoned on a certain column and on one of your loads you try to update that column on already existing records. You might run into this oracle error

Oracle error 14402 : ORA-14402: updating partition KEY COLUMN would cause a partition CHANGE

The above error was caused because when you updated the record, it now belongs to a different partition but since row movement between partitions is not enabled on the table, you got that error.

Delete PeopleSoft Query From the Database

Wed, 07/02/2008 - 7:33pm — Lepa

There could be different reasons why a PeopleSoft developer would like to delete a query from the database. Upgrade clean up would probably be the most common one. Here is a function you can use to get you started.

Function DeleteQuery(&sQueryName As string)
   SQLExec("DELETE FROM PSQRYDEFN WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYSELECT WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYRECORD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELD WHERE QRYNAME=:1", &sQueryName);

Check Box Select/Deselect All on Grid

Tue, 07/01/2008 - 7:01pm — Lepa

The below function is to be used on a grid with multiple check boxes. Place the code behind a FieldChange event and users will have the option to Select or Deselect grid rows all at once.

Function selectAllRows(&rs As Rowset)
   Local number &i;
   Local Row &row;
   
   For &i = 1 To &rs.ActiveRowCount
      &row = &rs.GetRow(&i);
      /* Make sure we only select visible rows. */
      If &row.Visible = True Then
         &row.Selected = True;
      End-If;
   End-For;
end-function;

/*main line*/
Local Rowset &rs;

Common Terminology Used in Web Services

Mon, 06/23/2008 - 11:47pm — Lepa

Before start using PeopleSoft Integration Broker for sending and receiving services, you need to be familiar with the terminology used for Web services. In this post, I will try to list and describe common terms used for Web services.

Extensible Markup Language (XML): XML is a text-based format that provides a mechanism to describe document structures using markup tags. It allows developers to create their own customized tags, enabling the definition, transmission, validation, and interpretation of data between applications and between organizations.

Unlock PeopleSoft Objects All at Once (Change Control Locking)

Sun, 06/22/2008 - 4:48pm — Lepa

Change control is used in Application Designer to lock definitions and track history changes for each object. Change control can be activated through Application Designer using Tools > Change Control > Administrator.

After few good size projects, you can easily have hundreds if not thousands of locked objects. Attempting to unlock the objects one by one is very time consuming, so here is a way to speed up the process.

Locked objects are stored on PSCHGCTLLOCK table. Here is how PeopleSoft describes the table: