Component Interface Meta data

Recently I was analyzing a number of Component Interface meta data and below are few simple SQL queries I formed and thought would be useful. However, all these queries are based on PSBCDEFN and PSBCITEM tables.

SQL Query to find search records in a CI:
 SELECT SEARCHRECNAME, ADDSRCHRECNAME
 FROM PSBCDEFN
 WHERE BCNAME = :1 -- Component Interface name

SQL Query to find out the records exposed by a CI:
 SELECT distinct recname
 FROM PSBCITEM
 WHERE BCNAME = :1 -- Component Interface name
This query also returns the search record.

SQL Query to find out the Component associated with a Component Interface:
 SELECT BCPGNAME,MARKET,MENUNAME
 FROM PSBCDEFN
 WHERE BCNAME = :1 -- Component Interface name

SQL 'LIKE' with 'IN' clause

SELECT Tblb.RECNAME, Tbla.SQLID, Tbla.SQLTYPE, Tbla.SQLTEXT FROM PSSQLTEXTDEFN Tbla
INNER JOIN PS_TEMP_ABC Tblb ON
Tbla.SQLTEXT Like '%'+Tblb.RECNAME+'%'

Avoid using the SqlExec statements in the PeopleCode

Some of the issues with SQL Exec are: 

1. It can select only single row of data. 

2. It surpass the component processor and interacts directly with Database server. 

3. It has to be used in only limited number of events and that too you can cause data consistency problems if not timed properly. 

 #1 is precisely the reason why you'll use SQLExec if your requirements demand it. SQLExec outputs only the first row to its output variables. Any subsequent rows are discarded. This means if you only want to fetch a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched.

SQL objects for insert/update/delete for all of the above and they are well documented within PeopleBooks. The record object has methods called Update(), Delete(), Insert(). It also has methods called SelectByKey() and SelectByKeyEffdt() which can be used for selecting into the record object.

UNION vs UNION ALL

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
-UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

-UNION ALL does not remove duplicates, and it therefore faster than UNION.

In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.

Oracle SQL - Working with long columns (long datatype)

The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions, yet they still exist in the data dictionary and legacy systems.The Oracle documentation describes the LONG datatype's main restrictions as follows:

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement
• The column list of a CREATE CLUSTER statement
• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
• SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses
• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
• SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements
• SELECT lists in subqueries in INSERT statements

Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above.
--------------------

Issue simulation:
SELECT * FROM MYTABLE WHERE UPPER(MY_LONG_COLUMN) LIKE UPPER('%my_search_word%');

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Advice:
1) Convert the column to a CLOB

SQL> alter table MYTABLE modify (MY_LONG_COLUMN CLOB) ;

2) Write some PL/SQL to do your searching (limited upto 32K)

SQL> declare
    cursor text is select text from all_views where rownum<100;
    v varchar2(4000);
begin
    for c in text loop
        v:=c.text;
        if ( v like '%ZZ%') then
            dbms_output.put_line( 'text is '||substr(v,1,50));
        end if;
    end loop;
end;

How to identify bundles that have been applied?

Bundles Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%BUNDLE%'
ORDER BY 2 DESC;

Tax Update Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%TAX UPDATE%'
ORDER BY 2 DESC;

SQL to get a process historical run status

select
    PQ.SERVERNAMERUN,  
    PQ.PRCSINSTANCE,
    PQ.PRCSTYPE,
    PQ.PRCSJOBNAME,
    PQ.PRCSNAME,
    PD.DESCR,
    PQ.OPRID,
    PQ.RUNCNTLID,
    (select  XLATSHORTNAME
        from    PSXLATITEM
        where   FIELDNAME = 'RUNSTATUS'
        and     FIELDVALUE = PQ.RUNSTATUS
    ) as RUNSTATUS,
    PQ.RUNDTTM,
    PQ.RQSTDTTM,
    PQ.ENDDTTM,
    PQ.BEGINDTTM
from
    PSPRCSRQST PQ inner join PS_PRCSDEFN PD
    on PQ.PRCSNAME = PD.PRCSNAME
where
    PQ.PRCSNAME = 'SYSAUDIT'
order by PRCSINSTANCE desc;

SQL: List of objects in a project

SELECT OBJECTTYPE,
CASE OBJECTTYPE
  WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)
                           WHEN 0 THEN 'SQL Table in DB'
                           WHEN 1 THEN 'SQL View in DB'
                           WHEN 2 THEN 'Work Record'
                           WHEN 3 THEN 'Sub Record'
                           WHEN 5 THEN 'Dynamic View'
                           WHEN 6 THEN 'Query View'
                           WHEN 7 THEN 'Temporary Table'
                           ELSE 'Other Record or Deleted' END
  WHEN 1     THEN 'Index'
  WHEN 2     THEN 'Field'
  WHEN 3     THEN 'Field Format'
  WHEN 4     THEN 'Translate Value'
  WHEN 5     THEN 'Page'
  WHEN 6     THEN 'Menu'
  WHEN 7     THEN 'Component'
  WHEN 8     THEN 'Record PeopleCode'
  WHEN 9     THEN 'Menu PeopleCode'
  WHEN 10    THEN 'Query'
  WHEN 11    THEN 'Tree Structure'
  WHEN 12    THEN 'Tree'
  WHEN 13    THEN 'Access Group'
  WHEN 14    THEN 'Color'
  WHEN 15    THEN 'Style'
  WHEN 16    THEN 'Business Process Map'
  WHEN 17    THEN 'Business Process'
  WHEN 18    THEN 'Activity'
  WHEN 19    THEN 'Role'
  WHEN 20    THEN 'Process Definition'
  WHEN 21    THEN 'Process Server Definition'
  WHEN 22    THEN 'Process Type Definition'
  WHEN 23    THEN 'Process Job Definition'
  WHEN 24    THEN 'Process Recurrence Definition'
  WHEN 25    THEN 'Message Catalog'
  WHEN 26    THEN 'Dimension'
  WHEN 27    THEN 'Cube Definition'
  WHEN 28    THEN 'Cube Instance Definition'
  WHEN 29    THEN 'Business Interlink'
  WHEN 30    THEN CASE OBJECTVALUE2
                               WHEN '0' THEN 'SQL Object'
                               WHEN '1' THEN 'App Engine SQL'
                               WHEN '2' THEN 'Record View SQL'
                               WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT'
                               WHEN '6' THEN 'App Engine XML SQL'
                               ELSE 'SQL' END
  WHEN 31    THEN 'File Layout'
  WHEN 32    THEN 'Component Interface'
  WHEN 33    THEN 'Application Engine Program'
  WHEN 34    THEN 'Application Engine Section'
  WHEN 35    THEN 'Message Node'
  WHEN 36    THEN 'Message Channel'
  WHEN 37    THEN 'Message'
  WHEN 38    THEN 'Approval rule set'
  WHEN 39    THEN 'Message PeopleCode'
  WHEN 40    THEN 'Subscription PeopleCode'
  WHEN 41    THEN 'N/A'
  WHEN 42    THEN 'Component Interface PeopleCode'
  WHEN 43    THEN 'Application Engine PeopleCode'
  WHEN 44    THEN 'Page PeopleCode'
  WHEN 45    THEN 'Page Field PeopleCode'
  WHEN 46    THEN 'Component PeopleCode'
  WHEN 47    THEN 'Component Record PeopleCode'
  WHEN 48    THEN 'Component Record Field PeopleCode'
  WHEN 49    THEN 'Image'
  WHEN 50    THEN 'Style sheet'
  WHEN 51    THEN 'HTML'
  WHEN 52    THEN 'Not used'
  WHEN 53    THEN 'Permission List'
  WHEN 54    THEN 'Portal Registry Definitions'
  WHEN 55    THEN 'Portal Registry Structure'
  WHEN 56    THEN 'URL Definitions'
  WHEN 57    THEN 'Application Packages'
  WHEN 58    THEN 'Application Package PeopleCode'
  WHEN 59    THEN 'Portal Registry User Homepage'
  WHEN 60    THEN 'Problem Type'
  WHEN 61    THEN 'Archive Templates'
  WHEN 62    THEN 'XSLT'
  WHEN 63    THEN 'Portal Registry User Favorite'
  WHEN 64    THEN 'Mobile Page'
  WHEN 65    THEN 'Relationships'
  WHEN 66    THEN 'Component Interface Property PeopleCode'
  WHEN 67    THEN 'Optimization Models'
  WHEN 68    THEN 'File References'
  WHEN 69    THEN 'File Type Codes'
  WHEN 70    THEN 'Archive Object Definitions'
  WHEN 71    THEN 'Archive Templates (Type 2)'
  WHEN 72    THEN 'Diagnostic Plug In'
  WHEN 73    THEN 'Analytic Model'
  WHEN 79    THEN 'Service'
  WHEN 80    THEN 'Service Operation'
  WHEN 81    THEN 'Service Operation Handler'
  WHEN 82    THEN 'Service Operation Version'
  WHEN 83    THEN 'Service Operation Routing'
  WHEN 84    THEN 'Info Broker Queues'
  WHEN 85    THEN 'XLMP Template Definition'
  WHEN 86    THEN 'XLMP Report Definition'
  WHEN 87    THEN 'XMLP File Definition'
  WHEN 88    THEN 'XMPL Data Source Definition'
  ELSE 'Unknown ' || OBJECTTYPE END AS Object_Type,

CASE OBJECTTYPE
  WHEN 12    THEN OBJECTVALUE3
  WHEN 30    THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1 /* SQL Object */
                       WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12)
                       WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1 /* Record View SQL */
                       ELSE ' ' END
  WHEN 34    THEN TRIM(OBJECTVALUE1) || '.' || TRIM(OBJECTVALUE2)
  WHEN 62    THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12))
  ELSE OBJECTVALUE1 END AS NAME,
CASE
  WHEN OBJECTTYPE = 1    THEN 'Index: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 4    THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' || OBJECTVALUE3 || '; ' ||
                             NVL((SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' ||
                                          XLATLONGNAME || '; Status: ' || EFF_STATUS
                                  FROM PSXLATITEM
                                  WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2
                                     AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD')
                                  ), 'XLAT Deleted')
  WHEN OBJECTTYPE = 7    THEN 'Market: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 8    THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 9    THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4
  WHEN OBJECTTYPE = 12   THEN 'EFFDT: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 20   THEN 'Process Name: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(22, 40)   THEN OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 25   THEN 'Message: ' || OBJECTVALUE2 ||
                              ' (Message Set Descr: ' || OBJECTVALUE3 || ')'
  WHEN OBJECTTYPE = 30   THEN
                         CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* SQL Object */
                              WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' ||
                                           SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                                           SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                                           SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' ||
                                           DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL',
                                                  'D', 'Do Select', 'W', 'Do While',
                                                  'H', 'Do When', 'N', 'Do Until',
                                           SUBSTR(OBJECTVALUE1, 29, 1))
                               WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */
                               ELSE ' ' END
  WHEN OBJECTTYPE = 38   THEN 'EFFDT: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(39, 42, 44)   THEN OBJECTVALUE2
  WHEN OBJECTTYPE = 43   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN
                                     'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 8) || '; Step: ' ||
                                     OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) ||
                                     '; Database: ' || TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) ||
                                     '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10))
                              ELSE 'Section: ' || OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3
                                       || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) ||
                                       '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END
  WHEN OBJECTTYPE = 46   THEN 'Market: ' || OBJECTVALUE2 || '; Event: ' || OBJECTVALUE3
  WHEN OBJECTTYPE = 47   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Event: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 48   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18))
                                         || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 19, 16))
  WHEN OBJECTTYPE = 55   THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3
  WHEN OBJECTTYPE = 57   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN
                                  'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3)
                                   || ':' || TRIM(OBJECTVALUE4)
                              ELSE
                                 CASE WHEN TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN
                                           'Subclass: ' || TRIM(OBJECTVALUE2) || ':' ||
                                           TRIM(OBJECTVALUE3)
                                     ELSE
                                         CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN
                                                  'Subclass: ' ||  TRIM(OBJECTVALUE2)
                                              ELSE ' '
                                         END
                                 END
                         END
  WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN
                         CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN
                                   TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)  || '.'
                                   || TRIM(OBJECTVALUE4)
                              ELSE
                                  CASE WHEN TRIM(OBJECTVALUE3) IS NOT NULL THEN
                                            TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)
                                  ELSE
                                      CASE WHEN TRIM(OBJECTVALUE2) IS NOT NULL THEN
                                                TRIM(OBJECTVALUE2)
                                           ELSE ' '
                                      END
                                  END
                              END
  WHEN OBJECTTYPE = 59   THEN TRIM(OBJECTVALUE2)
  WHEN OBJECTTYPE = 62   THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                              SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                              SUBSTR(OBJECTVALUE1, 21, 8)
  ELSE ' ' END AS EXTENDED_OBJ_NAME,
CASE OBJECTTYPE
  WHEN 0    THEN NVL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ')
  WHEN 1    THEN NVL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1
                      AND INDEXID = I.OBJECTVALUE2), ' ')
  WHEN 3    THEN NVL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ')
  WHEN 5    THEN NVL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ')
  WHEN 6    THEN NVL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ')
  WHEN 7    THEN NVL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1
                      AND MARKET = I.OBJECTVALUE2), ' ')
  WHEN 20   THEN NVL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1
                      AND PRCSNAME = I.OBJECTVALUE2), ' ')
  WHEN 32   THEN NVL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ')
  WHEN 33   THEN NVL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ')
  ELSE ' ' END AS DESCR
FROM PSPROJECTITEM I
WHERE PROJECTNAME = 'MY_PEOPLESOFT_PROJECT'
ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4

Update matching rows in one table with data from another table

Update matching rows in t1 with data from t2

update t1
set (c1, c2, c3) =
(select c1, c2, c3 from t2
 where t2.user_id = t1.user_id)
where exists
(select * from t2
 where t2.user_id = t1.user_id)

The "where exists" part it to prevent updating the t1 columns to null where no match exists.

Oracle SQL - Rows to Coulmn conversion

Pivoting techniques (Rows to Column conversion) is very possible in Oracle SQL. There are two methods used popularly for Pivoting in Oracle SQLO( From Oracle 8i) . They are:

1. Using Decode ( or CASE )

2. Using Sys_connect_by_path

1. Using Decode 

This method uses Decode and group by together. 

2. Using Sys_connect_by_path 

As Sys_connect_by_path is used along with hierarchical queries, we need to make any query in that format for doing the pivot . It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

Though Decode gives more flexibility, Sys_connect_by_path is recommended in case row values are unknown or not fixed.

SQL: Insert into a table from another table

insert into table1(COL1,COL2,COL3,COL4)
select col1,col2,col3,col4 from table2

insert into table FINAL (select * from STAGING)

updated on 26-Jul-2015:
If you get error like "ORA-12899: value too large for column emp_name (actual: 16, maximum: 15)" while inserting or updating data from one table to another, and if you have also tried LENGTH function, then there is something more to look into.

The usual reason for problems like this are non-ASCII characters that can be represented with one byte in the original database but require two (or more) bytes in the target database (due to different NLS settings).

Try below query to find out the reason:
SELECT * FROM staging WHERE lengthb(mycol) > 15

SQL: CREATE a table from another table

Syntax #1 - Copying all columns from another table

The syntax for CREATING a table by copying all columns from another table is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

For Example:

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The syntax for CREATING a table by copying selected columns from another table is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n FROM old_table);

For Example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
      FROM companies
      WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The syntax for CREATING a table by copying selected columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);

For Example:

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Frequently Asked Questions

---

Question: How can I create an SQL table from another table without copying any values from the old table?
Answer: To do this, the syntax is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table WHERE 1=2);

For Example:

CREATE TABLE suppliers
  AS (SELECT * FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

Index on View (Oracle)

A view is really just a stored SQL statement so if you want to create an index on a view, create the index on the base table instead. 

You can't even add constraints on view. Constraints such as Primary key and unique key do occupy spaces because they create a unique index in turn.  You can create a view with check option.

Note: Using DISTINCT, GROUP BY or UNION clause in a View implies that no join using an index can be done on this View. These keywords should be avoided.

Retrieve milliseconds from Oracle database and display it on a page.

You can use the following SQL to get milliseconds from Oracle 9i and higher and assign it to a Field of Type Time in PeopleSoft page. May Place this code in FieldDefault event.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_TIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties.

For Date Time Field, use the following SQL.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_DATETIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties. Select Display Century and Display Time Zone in Page Field Properties to display complete date and time.

Oracle SQL Developer problem – enter / backspace / delete button not working – quick fix

To resolve the problem you have to go to:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Finding navigation based on component name - II

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2, 

                                              l_portal_reftype VARCHAR2, 

                                              l_portal_objname VARCHAR2, 

                                              l_level          NUMBER, 

                                              l_type           VARCHAR2, 

l_count_max      INTEGER DEFAULT 10) 

RETURN VARCHAR2 

IS 

  pl_count              INTEGER := 0; 

  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname; 

  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0; 

  pl_portal_label       psprsmdefn.portal_label%TYPE := ' '; 

  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' '; 

  CURSOR cur_1 IS 

    SELECT portal_prntobjname, 

           portal_label, 

           portal_seq_num 

    FROM   psprsmdefn 

    WHERE  portal_name = l_portal_name 

           AND portal_reftype = l_portal_reftype 

           AND portal_objname = pl_portal_objname; 

BEGIN 

  WHILE pl_count <> l_level LOOP 

      pl_count := pl_count + 1; 

      EXIT WHEN pl_count > l_count_max; 

      OPEN cur_1; 

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num 

      ; 

      IF cur_1%found THEN 

        pl_portal_objname := pl_portal_prntobjname; 

      ELSE 

        pl_portal_label := ' '; 

        pl_portal_seq_num := 0; 

        EXIT WHEN cur_1%notfound; 

      END IF; 

      CLOSE cur_1; 

  END LOOP; 

  IF l_type = 'S' THEN 

    RETURN pl_portal_seq_num; 

  ELSE 

    RETURN pl_portal_label; 

  END IF; 

END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname, 

       a.portal_linkobjname, 

       a.portal_seq_num                              seq, 

       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L') 

             || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L') 

       || Decode(a.portal_label, ' ', '', 

                                 ' > ' 

                                 || a.portal_label)) navigation, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7, 

       a.portal_uri_seg1                             menuname, 

       a.portal_uri_seg2                             component, 

       a.portal_uri_seg3                             market, 

       a.portal_urltext 

FROM   psprsmdefn a 

WHERE  a.portal_name = 'EMPLOYEE' 

       AND a.portal_reftype = 'C' 

       AND a.portal_prntobjname <> ' ' 

--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME' 

ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S') 

          || 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')  

Finding navigation based on component name - I

1) Run the below SQL to get the content reference name for your component

SELECT PORTAL_NAME,

 PORTAL_OBJNAME AS CONTENT_REFERENCE,

 PORTAL_LABEL,

 PORTAL_URI_SEG1 AS MENU,

 PORTAL_URI_SEG2 AS COMPONENT,

 PORTAL_URI_SEG3 AS MARKET

FROM psprsmdefn

 WHERE PORTAL_NAME = 'EMPLOYEE'

   AND PORTAL_URI_SEG2 = :1;

-- Replace :1 with the component name you are looking for.

2) From the query above - copy the value in the CONTENT_REFERENCE field and replace the ":1" variable and you will have the path to your component.

WITH portal_registry AS

  (SELECT RTRIM(REVERSE(sys_connect_by_path(REVERSE(portal_label),    ' >> ')),    ' >> ') path,

     LEVEL lvl

   FROM psprsmdefn

   WHERE portal_name = 'EMPLOYEE' START WITH PORTAL_OBJNAME = :1 CONNECT BY PRIOR portal_prntobjname =portal_objname)

SELECT path

FROM portal_registry

WHERE lvl =

  (SELECT MAX(lvl)

   FROM portal_registry);

So, the 1st query is to get the content reference for a component name that you know and then using Jim's query to find the path!

==================

==================

==================

==================

==================

Another SQL:

SELECT DISTINCT
Page = PNL.PNLNAME, 
Component = P1.PORTAL_URI_SEG2, 
Record = FLD.RECNAME, 
Navigation = ISNULL(P6.PORTAL_LABEL, '') + ' > '   + ISNULL(P5.PORTAL_LABEL, '') 
+ ' > '   + ISNULL(P4.PORTAL_LABEL, '') + ' > '   + ISNULL(P3.PORTAL_LABEL, '') 
+ ' > ' + ISNULL(P2.PORTAL_LABEL, '')    + ' > ' + ISNULL(P1.PORTAL_LABEL, 'Nav not found') 
FROM PSPNLGROUP PNL LEFT JOIN PSPNLFIELD FLD ON PNL.PNLNAME = FLD.PNLNAME 
LEFT JOIN PSPRSMDEFN P1 ON PNL.PNLGRPNAME = P1.PORTAL_URI_SEG2 
LEFT JOIN PSPRSMDEFN P2 ON P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME AND (P1.PORTAL_NAME = P2.PORTAL_NAME OR P1.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P3 ON P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME AND (P2.PORTAL_NAME = P3.PORTAL_NAME OR P2.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P4 ON P3.PORTAL_PRNTOBJNAME = P4.PORTAL_OBJNAME AND (P3.PORTAL_NAME = P4.PORTAL_NAME OR P3.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P5 ON P4.PORTAL_PRNTOBJNAME = P5.PORTAL_OBJNAME AND (P4.PORTAL_NAME = P5.PORTAL_NAME OR P4.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P6 ON P5.PORTAL_PRNTOBJNAME = P6.PORTAL_OBJNAME AND (P5.PORTAL_NAME = P6.PORTAL_NAME OR P5.PORTAL_NAME IS NULL) 
WHERE 
--P1.PORTAL_URI_SEG2 = 'JOB_DATA'
FLD.RECNAME IN (


)