aufsummieren===
Mit **"SUM(<Spalte>) OVER (ORDER BY TRUNC(<Treibendes Datum>) ROWS BETWEEN UNBOUNDED ... isplaydate
-- BL planned labor units summed by date
, sum(actsp.baselineplannedlaborunits) a...
-- BL planned labor units - running totals by date
, sum(sum(actsp.baselineplannedlaborunits)) over (order by trunc(actsp.startdate) rows between unbounded pre
n===
==Associative Arrays (vor Oracle 10g "index-by tables")==
Typ definieren:
<code plsql>
TYPE myPlsqltab is TABLE OF varchar2(2000) index by binary_integer;
</code>
Variable deklarieren:
<c... numeric overflow" zeigt uns dann an das der INDEX BY Type (pls_integer, binary_integer) auch mal zu kl... p_lookup_tab is table of emp_lookup.id%type INDEX BY varchar2(16);
v_emp_lookuptab t_emp_lookup_t
, listagg(ename,':') within group (order by ename) as emp_list
from emp group by deptno
/
DEPTNO EMP_LIST
---------- ------... , listagg(ename,':') within group (order by ename) on overflow truncate '...' with count as emp_list
from emp group by deptno
/
DEPTNO EMP_LIST
----------
aufzubauen:
<code sql>
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 e... id)
)
start with parent is null
connect by prior id = parent
order siblings by name
</code>
Der eigentliche Thesaurus wird dann über PL/SQL a... c_thes
is
select *
from thesaurus
order by id;
cursor c_pharse (p_ths_id number)
is
sel
unktion kann in der SELECT-Listen, WHERE-, GROUP BY- und HAVING-Klausel verwendet werden ( Scalar SQL... se of SQL macro: %s"
</code>
Kann nicht im Group by verwendet werden!
<code bash>
SQL Error: ORA-646... >
create table Kunden(
SK number(11) GENERATED BY DEFAULT ON NULL AS IDENTITY
, institut varchar...
end as InteralKDNr
from Kunden k
group by Kundenname
,InteralKDNr; -- < -- 23c Feat
--------------
Predicate Information (identified by operation id):
----------------------------------... 2 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 | 1 (0)|... --------------
Predicate Information (identified by operation id):
----------------------------------... NLSSORT =====
Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwung
where d.deptno = e.deptno
group by d.dname
)
pivot (
MAX (percount)
... WHERE d.deptno = e.deptno
9 GROUP BY d.dname,d.loc
10 )
11 pivot (
12 ... where d.deptno = e.deptno
group by d.dname
)
pivot XML (
MAX (percoun... where d.deptno = e.deptno
group by d.dname
)
pivot XML (
MAX (percoun
Generatoren =====
==== Dual Tabelle mit Connect By ====
Vorlaufender Zähler:
<code bash>
-- vor 10g
select level-1
from dual
connect by level < 4
/
Level
-------
0
1
2
--ab 10g
SELEC... CT 1 as col
FROM dual
CONNECT BY LEVEL <= 7
)
X
---
1
2
...
7
</cod... ect count(*) from (select level from dual connect by level < 100000000);
uppiern nach kundennummer
PARTITION BY KDNR order by datum
-- Was messen wir
MEASURES first(STRT.datum) ... t(BETRAG) >= 1
) KTO
order by 2 desc
;
</code>
Wie erstell... RIC_CHARACTERS=,.') as betrag
from konten
order by datum desc
/
spool off
set markup CSV OFF
set t
code sql>
alter user APEX_PUBLIC_USER IDENTIFIED by apex1234;
alter user APEX_PUBLIC_USER account unlock;
alter user APEX_LISTENER IDENTIFIED by apex1234;
alter user APEX_LISTENER account unlock... =Problem mit Theme roller generated CSS not found by ORDS ===
Eine neues Theme lässt sich zwar mit de... be procedures that happen to be granted to public by accident or intentionally.
<entry key="apex.sec
=====Oracle PL/SQL - mit "accessible by" Zugriffe in 12c auf PL/SQL einschränken=====
**Aufgabe:** E... ate or replace procedure execute_proc
accessible by (gpi.call_proc)
is
begin
dbms_output.put_line('-... chten!
Aus der Doku:
<code bash>
The ACCESSIBLE BY clause allows access only when the call is direct... base/oracle/oracle-database/12.2/lnpls/ACCESSIBLE-BY-clause.html#GUID-9720619C-9862-4123-96E7-3E85F240
, rowid
, ROW_NUMBER( ) over( partition by id order by id ) rang
from t
/
ID ROWID RANG
----- ------------------ ----... , ROW_NUMBER() over( partition by id order by id) rang
from t )
where rang > 1 )
/
56033
cnt
, object_type
from t
group by object_type
)
group by cnt
, object_type
order by cnt desc
/
CNT OBJECT_TYPE PROZENT
-------... werden mit der Option => **" ... OVER( PARTITION BY <ergebniss_spalte> )**.
Mehr Beispiele: [[prog
from APEX_050000.APEX_APPLICATION_PAGES
group by application_id,application_name order by 1
/
</code>
----
==== Die PL/SQL Package hinter Apex ====
... n_page_items
where application_id = :APP_ID
order by page_id,page_name
</code>
Z.B. in einer Modalen... KSPACE_SESSIONS
where workspace_name='GPI'
group by null,user_name
</code>
----
==== Charts in Ap