Knowledge Base Nr: 00092 commonsql.txt - http://www.swe-kaiser.de

Downloads:

oracle: pl/sql: häufig verwendete befehle und besonderheiten

  
- escape von '&':
  pl/sql:
    select * from mytable where cmd='&Datei' --FUNKTIONIERT NICHT!!!
    select * from mytable where cmd='&' || 'Datei' --FUNKTIONIERT
  c++:
    CString orgtext = "select * from mytable where cmd='&Datei'";
    text.Replace(L"&", L"&' || '");

- tabelle kopieren (backup):
create table mka_etidruck_090902 as select * from mka_etidruck;
insert into v_qhp (select * from v_qhp_backup);

- tabellenfeld hinzufügen:
alter table arbplatz add (dosierextern number(1));

- sequence anlegen:
CREATE SEQUENCE seq_pk
  MINVALUE 10000000
  MAXVALUE 999999999999999999999999999
  INCREMENT BY 1
  NOCYCLE
  NOORDER
  NOCACHE

- select in insert:
insert into tbl_templatetexts (sid, stext)
  select replace(sid,'T_', 'P_'), stext
  from tbl_templatetexts
  where sid like('T_%');

- select in update:
update tbl_npshcurve set iinletcondition=
  (select iinletcondition
  from tbl_npshcurve_bu
  where tbl_npshcurve.npshcurve_pk=tbl_npshcurve_bu.npshcurve_pk);

- BLOB handling
INSERT INTO "TBL_LOGOS" (IORDERID, SMENUTEXT, blogo) VALUES ('4', 'Sero', EMPTY_blob());

- export/import:
exp userid=hyddb/***@vertest file=c:\temp\expdat.dmp log=c:\temp\expdat.log direct=y owner=hyddb
imp userid=hyddb/***@vertest file=c:\temp\expdat.dmp log=c:\temp\impdat.log fromuser=hyddb touser=hyddb