Skip navigation.

XTended Oracle SQL

Syndicate content
XTended Oracle SQL
Updated: 5 hours 57 min ago

SQL*Plus tips #7: How to find the current script directory

Fri, 2015-06-26 13:06

You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t to find @@filename.

So we can simply get this path from the error text:

rem Simple example how to get path (@@) of the current script.
rem This script will set "cur_path" variable, so we can use &cur_path later.
 
set termout off
spool _cur_path.remove
@@notfound
spool off;
 
var cur_path varchar2(100);
declare 
  v varchar2(100);
  m varchar2(100):='SP2-0310: unable to open file "';
begin v :=rtrim(ltrim( 
                        q'[
                            @_cur_path.remove
                        ]',' '||chr(10)),' '||chr(10));
  v:=substr(v,instr(v,m)+length(m));
  v:=substr(v,1,instr(v,'notfound.')-1);
  :cur_path:=v;
end;
/
set scan off;
ho (rm _cur_path.remove 2>&1  | echo .)
ho (del _cur_path.remove 2>&1 | echo .)
col cur_path new_val cur_path noprint;
select :cur_path cur_path from dual;
set scan on;
set termout on;
 
prompt Current path: &cur_path

I used here the reading file content into variable, that I already showed in the “SQL*Plus tips. #1″.
UPDATE: I’ve replaced this script with a cross platform version.

Also I did it with SED and rtrim+ltrim, because 1) I have sed even on windows; and 2) I’m too lazy to write big PL/SQL script that will support 9i-12c, i.e. without regexp_substr/regexp_replace, etc.
But of course you can rewrite it without depending on sed, if you use windows without cygwin.

PS. Note that “host pwd” returns only directory where SQL*Plus was started, but not executed script directory.

Download latest version

Categories: Development

Little quiz: Ordering/Grouping – Guess the output

Tue, 2015-04-28 06:20

How many times have you guessed the right answer? :)
1

select * from dual order by -1;
select * from dual order by 0;

[collapse] 2

select *                   from dual                                     order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;

[collapse] 3

select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0+0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 3+7 desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(3.1+0f) desc;

[collapse] 4

select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 1.9;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.5;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.7 desc;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by -2.7 desc;

[collapse]

Categories: Development

12c: Little test of “TABLE ACCESS INMEMORY FULL” with count stopkey

Mon, 2015-03-16 17:02

The table has 9M rows:

SQL> with function f return int is
  2       begin
  3          for r in (select value from v$mystat natural join v$statname where name like 'IM scan rows') loop
  4             dbms_output.put_line(r.value);
  5             return r.value;
  6          end loop;
  7       end;
  8  select f() from t_inmemory where rownum<=1
  9  ;
 10  /

       F()
----------
         0

1 row selected.

SQL> /

       F()
----------
    491436

1 row selected.

SQL> /

       F()
----------
    982872

1 row selected.
DDL and Plan
create table t_inmemory inmemory
as 
with gen as (select 0 id from dual connect by level<=3e3)
select 0 n from gen,gen;

SQL_ID  cpgrrfv9h6m52, child number 0
-------------------------------------
with function f return int is      begin         for r in (select value
from v$mystat natural join v$statname where name like 'IM scan rows')
loop            dbms_output.put_line(r.value);            return
r.value;         end loop;      end; select f() from t_inmemory where
rownum<=1

Plan hash value: 3697881339

----------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY              |            |       |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY |     1 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)

[collapse]

Categories: Development