Category: SQL

Creating a Readonly Database User

Here’s a common task: You need to create a database user with read only access.  You have some power users who know how to use SQL, and they are allowed to see all of the data in the system.  You can use these commands to create such a user (in Oracle):

[sourcecode language=”sql”]
create user PSRO identified by PSRO;
grant create session, select any table, select any dictionary to PSRO;
[/sourcecode]

Note: You can probably find this all over the web, but I wanted to capture it on my blog so that I could find the syntax easier.

Resources

Querying the Component

Here are some queries to find things out about the component.

This one is a simple list of all the fields in the component (doesn’t take into account subpages, etc):

SELECT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,
           CPG.PNLNAME, CPG.ITEMLABEL,
           PG.DESCR,
           PGF.FIELDTYPE, PGF.LBLTEXT, PGF.RECNAME, PGF.FIELDNAME
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME;

This one shows just the records involved:

SELECT DISTINCT CP.PNLGRPNAME, CP.MARKET, CP.DESCR,  PGF.RECNAME, R.RECDESCR, R.RECTYPE
FROM PSPNLGRPDEFN CP,
         PSPNLGROUP CPG,
         PSPNLDEFN PG,
         PSPNLFIELD PGF,
         PSRECDEFN R
WHERE CP.PNLGRPNAME = 'JOB_DATA'
     AND CP.MARKET = 'GBL'
    AND CP.PNLGRPNAME = CPG.PNLGRPNAME
    AND CP.MARKET = CP.MARKET
    AND PG.PNLNAME = CPG.PNLNAME
    AND PGF.PNLNAME = PG.PNLNAME
    AND PGF.RECNAME = R.RECNAME;

I hope this helps.

Any Interest in Program?

I have been tinkering around with a little Java application I started for running queries against a PeopleSoft database.  I was just curious if there was any interest in such an application.  Here is a screenshot of what I have so far:screenshot

So far the program connects to SQL Server databases and Apache Databases.  It runs queries and loads the data into the JTable at the bottom.

I have a SQL “preprocessor” that evaluates the SQL right before it passes it to the database.  Currently, the only thing it does is to pull the semi-colon off of the end.  But, I plan to have it resolve meta-sql such as %Table() and %CurrentDateIn, etc.

Also, I would like to build SQL tools in that understand the SQL syntax.  I would like tools that automatically generate effective date criteria.  Another feature would be nice to have where it would join a table into a SQL statement.

Looking for Orphan Rows

Here is some SQL that you might find helpful:

SELECT ‘SELECT ”’ + C.RECNAME + ”’ AS RECNAME, OPRID FROM ‘ + D.TABLENAME +
‘ A WHERE NOT EXISTS (SELECT ”X” FROM PSOPRDEFN B WHERE A.OPRID = B.OPRID) UNION ‘
FROM PSRECFIELDALL C,
(SELECT RECNAME, SQLTABLENAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME <> ‘ ‘
AND RECTYPE = 0
UNION
SELECT RECNAME, ‘PS_’ + RECNAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME = ‘ ‘
AND RECTYPE = 0
) D
WHERE C.RECNAME = D.RECNAME
AND C.FIELDNAME = ‘OPRID’

This Query writes a huge SQL Statement that looks for rows in any table that has an OPRID that does not exist in the PSOPRDEFN table.

Here is how to use it:

  • Run the SQL Statement
  • Copy all of the rows as a new query
  • Remove the last Union from the last line
  • You might want to add “ORDER BY 1” at the end to get it in record order
  • Execute the newly generated query

Note, this pattern can be replicated for other fields or situations too.  It is not just limited to use with OPRID.

Query Tool that includes Meta-SQL

Once I learned about SQL Developer, I switched from using Toad as my Query Tool.   If I were to improve one thing though, it would be that the query tool understand Meta-SQL.  Now, if I copy SQL from PeopleSoft, say from an Application Engine, I have to remove all of the Meta-SQL tags that PeopleTools understands before I can run it.  Then, I have to replace them before copying back to PeopleTools.

I have thought about trying to write an extension for SQL Developer.  Does anyone know of any documentation about writing extensions or plug-ins for SQL Developer?  This might be a place to start.  Another route I have thought about is an open source tool called Squirrel SQL hosted on Source Forge.  It looks like it has a way to write plug-ins or extensions, but I haven’t been able to get it to connect to any of my databases yet.  To be fair, I haven’t given it a thorough run-through yet.

If you have any ideas or information, please let me know.

Oracle Tip: Dropping a Database

Here is how to drop a database in Oracle.

Make sure that have ORACLE_SID set first.

Log into sqlplus with sysdba privileges:

sqlplus /nolog
connect / as sysdba

Run the following commands in sqlplus:

shutdown immediate
startup restrict mount exclusive

Before you complete the drop, verify that you are in the correct database — the one that you want to drop (thanks to Dan Norris’ comment):

select * from v$database;

Now you can drop the database.

drop database;

PeopleTools Reference: Meta-SQL %SelectAll

Generates a select statement that selects all the fields from a chosen record.

Typically, when you use the %SelectAll, you would use a record object to store the results. That way you don’t have to know how many fields your result will return.

Examples

%SelectAll(JOB)

Local Record &myRecord;
&myRecord = CreateRecord(RECORD.MY_RECORD);
SQLExec(“%SelectAll(:1) WHERE MY_KEY = :2”, &myRecord, “My Key”, &myRecord);

Local Record &myInstallation;
&myRecord = CreateRecord(RECORD.INSTALLATION);
SQLExec(“%SelectAll(:1)”, &RECORD.INSTALLATION, &myInstallation);

PeopleTools Reference: Meta-SQL %Table

%Table

Replaces with the actual table name of the given record.

Generally, this just means adding a “PS_” to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.

Two Uses:

  • Access a table with a reference or record object rather than embedding a table name in the SQL; PeopleTools will not index the table/record in a string literal.
  • Reference the Temporary table in an App Engine; %Table is the only way to reference the table because the table name is assign dynamically at run time.

Examples:

SELECT * FROM %Table(JOB) WHERE EMPLID = :1

SqlExec(“SELECT NAME FROM %Table(:1) WHERE EMPLID = :2”, Record.NAMES, &emplid, &name);

&sql = CreateSql(“SELECT * FROM %Table(:1) WHERE EMPLID = :2”, &MyRecord, &emplid);

INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE