SQL

ORA-01775 - Looping chain of synonyms

I Never had this error before while trying to SELECT from a table, but there is always fist time for everything I guess. So, after few minutes of troubleshooting I found out that there is a synonym for the table on DBA_SYNONYMS table but the actual table object doesn't exist on DBA_OBJECTS table.

How did this happen? Well, my guess is someone dropped the table but never did the same for the synonym. So, the synonym ends up referencing itself when ever you select from that table and thus the “Looping chain of synonyms".

User's Own Session Details

An SQL to find user's session's details from within his/her session.

SELECT * FROM v$session
WHERE sid = (SELECT sid FROM v$mystat
             WHERE rownum=1);

You can also use the SQL below if you are running on 10g or higher:
SELECT * FROM v$session
WHERE sid = sys_context('userenv','sid');

Who am I?

Here is a quick SQL to help you identify what database are you logged into and with what user Id.

SELECT sys_context('USERENV',   'SESSION_USER') SESSION_USER,
  sys_context('USERENV',   'DB_NAME') db_name
FROM dual;

SESSION_USER    DB_NAME
dba123456       developdb

SQL & PL/SQL

This is a handbook to organize SQL snippets/sample code. Snippets included in this handbook range in difficulty from simple short SQLs to fairly complex ones.

I encourage you to look at current published snippets and try to enhance them by using the comment form at the end of each post. There is always a different/better way of doing things!

SQL to Generate a Sequence Number

Generating a sequence number isn't that hard. All what you need to type is the following:

CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.

SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?

SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;

Sequence created.

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         1

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         4

SQL DECODE to Order Months

The SQL below will order months from Jan to Dec (01 to 12).

SELECT short_month AS short,
  long_month AS long
FROM month_tbl
ORDER BY decode(UPPER(short_month),
  'JAN',   '01',  
  'FEB',   '02',  
  'MAR',   '03',  
  'APR',   '04',  
  'MAY',   '05',  
  'JUN',   '06',  
  'JUL',   '07',  
  'AUG',   '08',  
  'SEP',   '09',  
  'OCT',   '10',  
  'NOV',   '11',  
  'DEC',   '12');

short       long
JAN     JANUARY
FEB     FEBRUARY
MAR     MARCH
APR     APRIL
MAY     MAY
JUN     JUNE
JUL     JULY
AUG     AUGUST
SEP     SEPTEMBER
OCT     OCTOBER
NOV     NOVEMBER

SQL to Show Database Information

Here is a quick SQL snippet to show some Database information. I'm only pulling Database name, date created, database owner Id and database long name.

SELECT A.NAME,
  B.DBNAME,
  A.CREATED,
  B.OWNERID,
  C.LONGNAME
FROM V$DATABASE A,
  PS.PSDBOWNER B,
  PSOPTIONS C;

Get all Components Under a Certain Portal Folder

Use this SQL snippet to return all components plus sub-folders (if any) under a specific portal folder.

For example, lets say you have a folder called "Workforce Admin". Under Job Information, you have a sub-folder called "Job Information" and few components.

Run the SQL against the Workforce Admin folder and you should get a row for the sub-folder, and a row for each of the components.

-- Get all sub-folders and components under a certain folder name --
SELECT PORTAL_NAME,
   PORTAL_PRNTOBJNAME AS FOLDER,
   PORTAL_OBJNAME AS CONTENT_REFRENCE,

Component, Content Reference, and Menu

Ok, so we all know that a component needs to be attached to a menu before you are able to register it to the portal structure. Once a component is registered using Application Designer, then a content reference is created and your component will be available under the folder and path you specified during the registration process.

Oracle Group By Grouping Sets

In one of my previous ROLLUP posts I showed you how to generate summary information. However, the output of ROLLUP includes the rows produced by the regular GROUP BY operation along with the summary rows.

So, what if you are only interested in totals. Well, Oracle does provide an extension to the GROUP BY clause called GROUPING SETS that you can use to generate summary information at the level you choose without including all the rows produced by the regular GROUP BY operation.

Lets take a look at an example:

SELECT ord.year,
   TO_CHAR(TO_DATE(ord.month, 'MM'), 'Month') month,
   reg.region,
  SUM(ord.total_sales)
FROM orders ord, region reg
WHERE ord.region_id = reg.region_id
AND ord.month BETWEEN 1 AND 3
GROUP BY GROUPING SETS (ord.year, ord.month, reg.region);