Taggato: query

Oracle Database

Retrive sqldata through shell script

It could be useful get data from DB by a  bash script Linux. Using a shell Bash is possible to create the following query: dir_in=`sqlplus -s $PMS_CONN_STRING <<EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF select directory_path FROM all_directories WHERE directory_name = ‘GARBAGE_SFTP_AREA’; exit EOF Analyze this script. The query above, very simple, extracts a path from the system table all_directories where directory name is GARBAGE_SFTP_AREA. At the beginning, dir_in = indicate the variable where we’ll put the result (the value of directory_path field). With the symbol s `COMMAND`  bash execute the command between `  and...

Oracle Glossary

Oracle glossary: Subquery

Oracle subquery is a sql query within another sql query. Some example: select tab.name, tab.surname from (select name, surname from customer where surname like ‘S%’) tab select id from myusers where (name, surname) in (select name, surname from customer where surname like ‘S%’) tab select (select ‘1’ from dual) from table.

Oracle Database

Access problem NOT IN query using db link on db 9i:select id_bda

from anag_li where id_bda not in (select idanag from prova@bdlink); Access plan: ———————————————————————————— | Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Inst   |IN-OUT| ———————————————————————————— |   0 | SELECT STATEMENT     |             | 37490 |   585K|   978K|        |      | |*  1 |  FILTER              |             |       |       |       |        |      | |   2 |   TABLE ACCESS FULL  | ANAG_LI     | 37490 |   585K|  3646 |        |      | |   3 |   REMOTE             |             |   186K|  1819K|    26 | BDAPR~ | R->S | ———————————————————————————— the query as written will never use no index because it must take all lines except...

Oracle Database

Retrive sqldata through shell script

It could be useful get data from DB by a  bash script Linux. Using a shell Bash is possible to create the following query: dir_in=`sqlplus -s $PMS_CONN_STRING <<EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF select directory_path FROM all_directories WHERE directory_name = ‘GARBAGE_SFTP_AREA’; exit EOF Analyze this script. The query above, very simple, extracts a path from the system table all_directories where directory name is GARBAGE_SFTP_AREA. At the beginning, dir_in = indicate the variable where we’ll put the result (the value of directory_path field). With the symbol s `COMMAND`  bash execute the command between `  and...

Oracle Database

A subquery example

We happened recently to solve a problem of this type: we had a structure in which, for each category (shown in a table A) had a number of items of detail (in a table B) numeric values ​​(the latter given in a table C that implemented them a one to many relationship between A and B). We were required to get the maximum value for each category and detail which he was associated. The following example is inspired from the problem that we faced. For obvious reasons, the logic was applied to the sample tables are different from the real...