API tools faq
paste
Advertisement
Guest User

DDL

a guest
May 15th, 2017
159
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 59.27 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE "APEX$_ACL"
  2. ( "ID" NUMBER NOT NULL ENABLE,
  3. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  4. "USERNAME" VARCHAR2(255) NOT NULL ENABLE,
  5. "PRIV" VARCHAR2(1) NOT NULL ENABLE,
  6. "CREATED_ON" DATE NOT NULL ENABLE,
  7. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  8. "UPDATED_ON" DATE,
  9. "UPDATED_BY" VARCHAR2(255),
  10. CONSTRAINT "APEX$_ACL_PRIV_CK" CHECK (priv in ('R','C','A')) ENABLE,
  11. CONSTRAINT "APEX$_ACL_PK" PRIMARY KEY ("ID") ENABLE
  12. ) ;CREATE TABLE "APEX$_WS_ROWS"
  13. ( "ID" NUMBER NOT NULL ENABLE,
  14. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  15. "DATA_GRID_ID" NUMBER NOT NULL ENABLE,
  16. "UNIQUE_VALUE" VARCHAR2(255),
  17. "TAGS" VARCHAR2(4000),
  18. "PARENT_ROW_ID" NUMBER,
  19. "OWNER" VARCHAR2(255),
  20. "GEOCODE" VARCHAR2(512),
  21. "LOAD_ORDER" NUMBER,
  22. "CHANGE_COUNT" NUMBER,
  23. "CREATED_ON" DATE NOT NULL ENABLE,
  24. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  25. "UPDATED_ON" DATE,
  26. "UPDATED_BY" VARCHAR2(255),
  27. "C001" VARCHAR2(4000),
  28. "C002" VARCHAR2(4000),
  29. "C003" VARCHAR2(4000),
  30. "C004" VARCHAR2(4000),
  31. "C005" VARCHAR2(4000),
  32. "C006" VARCHAR2(4000),
  33. "C007" VARCHAR2(4000),
  34. "C008" VARCHAR2(4000),
  35. "C009" VARCHAR2(4000),
  36. "C010" VARCHAR2(4000),
  37. "C011" VARCHAR2(4000),
  38. "C012" VARCHAR2(4000),
  39. "C013" VARCHAR2(4000),
  40. "C014" VARCHAR2(4000),
  41. "C015" VARCHAR2(4000),
  42. "C016" VARCHAR2(4000),
  43. "C017" VARCHAR2(4000),
  44. "C018" VARCHAR2(4000),
  45. "C019" VARCHAR2(4000),
  46. "C020" VARCHAR2(4000),
  47. "C021" VARCHAR2(4000),
  48. "C022" VARCHAR2(4000),
  49. "C023" VARCHAR2(4000),
  50. "C024" VARCHAR2(4000),
  51. "C025" VARCHAR2(4000),
  52. "C026" VARCHAR2(4000),
  53. "C027" VARCHAR2(4000),
  54. "C028" VARCHAR2(4000),
  55. "C029" VARCHAR2(4000),
  56. "C030" VARCHAR2(4000),
  57. "C031" VARCHAR2(4000),
  58. "C032" VARCHAR2(4000),
  59. "C033" VARCHAR2(4000),
  60. "C034" VARCHAR2(4000),
  61. "C035" VARCHAR2(4000),
  62. "C036" VARCHAR2(4000),
  63. "C037" VARCHAR2(4000),
  64. "C038" VARCHAR2(4000),
  65. "C039" VARCHAR2(4000),
  66. "C040" VARCHAR2(4000),
  67. "C041" VARCHAR2(4000),
  68. "C042" VARCHAR2(4000),
  69. "C043" VARCHAR2(4000),
  70. "C044" VARCHAR2(4000),
  71. "C045" VARCHAR2(4000),
  72. "C046" VARCHAR2(4000),
  73. "C047" VARCHAR2(4000),
  74. "C048" VARCHAR2(4000),
  75. "C049" VARCHAR2(4000),
  76. "C050" VARCHAR2(4000),
  77. "N001" NUMBER,
  78. "N002" NUMBER,
  79. "N003" NUMBER,
  80. "N004" NUMBER,
  81. "N005" NUMBER,
  82. "N006" NUMBER,
  83. "N007" NUMBER,
  84. "N008" NUMBER,
  85. "N009" NUMBER,
  86. "N010" NUMBER,
  87. "N011" NUMBER,
  88. "N012" NUMBER,
  89. "N013" NUMBER,
  90. "N014" NUMBER,
  91. "N015" NUMBER,
  92. "N016" NUMBER,
  93. "N017" NUMBER,
  94. "N018" NUMBER,
  95. "N019" NUMBER,
  96. "N020" NUMBER,
  97. "N021" NUMBER,
  98. "N022" NUMBER,
  99. "N023" NUMBER,
  100. "N024" NUMBER,
  101. "N025" NUMBER,
  102. "N026" NUMBER,
  103. "N027" NUMBER,
  104. "N028" NUMBER,
  105. "N029" NUMBER,
  106. "N030" NUMBER,
  107. "N031" NUMBER,
  108. "N032" NUMBER,
  109. "N033" NUMBER,
  110. "N034" NUMBER,
  111. "N035" NUMBER,
  112. "N036" NUMBER,
  113. "N037" NUMBER,
  114. "N038" NUMBER,
  115. "N039" NUMBER,
  116. "N040" NUMBER,
  117. "N041" NUMBER,
  118. "N042" NUMBER,
  119. "N043" NUMBER,
  120. "N044" NUMBER,
  121. "N045" NUMBER,
  122. "N046" NUMBER,
  123. "N047" NUMBER,
  124. "N048" NUMBER,
  125. "N049" NUMBER,
  126. "N050" NUMBER,
  127. "D001" DATE,
  128. "D002" DATE,
  129. "D003" DATE,
  130. "D004" DATE,
  131. "D005" DATE,
  132. "D006" DATE,
  133. "D007" DATE,
  134. "D008" DATE,
  135. "D009" DATE,
  136. "D010" DATE,
  137. "D011" DATE,
  138. "D012" DATE,
  139. "D013" DATE,
  140. "D014" DATE,
  141. "D015" DATE,
  142. "D016" DATE,
  143. "D017" DATE,
  144. "D018" DATE,
  145. "D019" DATE,
  146. "D020" DATE,
  147. "D021" DATE,
  148. "D022" DATE,
  149. "D023" DATE,
  150. "D024" DATE,
  151. "D025" DATE,
  152. "D026" DATE,
  153. "D027" DATE,
  154. "D028" DATE,
  155. "D029" DATE,
  156. "D030" DATE,
  157. "D031" DATE,
  158. "D032" DATE,
  159. "D033" DATE,
  160. "D034" DATE,
  161. "D035" DATE,
  162. "D036" DATE,
  163. "D037" DATE,
  164. "D038" DATE,
  165. "D039" DATE,
  166. "D040" DATE,
  167. "D041" DATE,
  168. "D042" DATE,
  169. "D043" DATE,
  170. "D044" DATE,
  171. "D045" DATE,
  172. "D046" DATE,
  173. "D047" DATE,
  174. "D048" DATE,
  175. "D049" DATE,
  176. "D050" DATE,
  177. "CLOB001" CLOB,
  178. "SEARCH_CLOB" CLOB,
  179. CONSTRAINT "APEX$_WS_ROWS_PK" PRIMARY KEY ("ID") ENABLE
  180. ) ;CREATE TABLE "APEX$_WS_FILES"
  181. ( "ID" NUMBER,
  182. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  183. "DATA_GRID_ID" NUMBER,
  184. "ROW_ID" NUMBER,
  185. "WEBPAGE_ID" NUMBER,
  186. "COMPONENT_LEVEL" VARCHAR2(30),
  187. "NAME" VARCHAR2(255) NOT NULL ENABLE,
  188. "IMAGE_ALIAS" VARCHAR2(255),
  189. "IMAGE_ATTRIBUTES" VARCHAR2(255),
  190. "CONTENT" BLOB,
  191. "CONTENT_LAST_UPDATE" DATE,
  192. "MIME_TYPE" VARCHAR2(255) NOT NULL ENABLE,
  193. "CONTENT_CHARSET" VARCHAR2(255),
  194. "CONTENT_FILENAME" VARCHAR2(500),
  195. "DESCRIPTION" VARCHAR2(4000),
  196. "CREATED_ON" DATE NOT NULL ENABLE,
  197. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  198. "UPDATED_ON" DATE,
  199. "UPDATED_BY" VARCHAR2(255),
  200. CONSTRAINT "APEX$_WS_FILES_CL_CK" CHECK (component_level in ('WEBSHEET','ROW','WORKSPACE','WEBPAGE')) ENABLE,
  201. CONSTRAINT "APEX$_WS_FILES_PK" PRIMARY KEY ("ID") ENABLE
  202. ) ;CREATE TABLE "APEX$_WS_HISTORY"
  203. ( "ROW_ID" NUMBER NOT NULL ENABLE,
  204. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  205. "DATA_GRID_ID" NUMBER NOT NULL ENABLE,
  206. "COLUMN_NAME" VARCHAR2(255),
  207. "OLD_VALUE" VARCHAR2(4000),
  208. "NEW_VALUE" VARCHAR2(4000),
  209. "APPLICATION_USER_ID" VARCHAR2(255),
  210. "CHANGE_DATE" DATE
  211. ) ;CREATE TABLE "APEX$_WS_LINKS"
  212. ( "ID" NUMBER,
  213. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  214. "DATA_GRID_ID" NUMBER,
  215. "ROW_ID" NUMBER,
  216. "WEBPAGE_ID" NUMBER,
  217. "COMPONENT_LEVEL" VARCHAR2(30),
  218. "TAGS" VARCHAR2(4000),
  219. "SHOW_ON_HOMEPAGE" VARCHAR2(1),
  220. "LINK_NAME" VARCHAR2(255) NOT NULL ENABLE,
  221. "URL" VARCHAR2(4000) NOT NULL ENABLE,
  222. "LINK_DESCRIPTION" VARCHAR2(4000),
  223. "DISPLAY_SEQUENCE" NUMBER,
  224. "CREATED_ON" DATE NOT NULL ENABLE,
  225. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  226. "UPDATED_ON" DATE,
  227. "UPDATED_BY" VARCHAR2(255),
  228. CONSTRAINT "APEX$_WS_LINKS_CL_CK" CHECK (component_level in ('WEBSHEET','ROW','WORKSPACE','WEBPAGE')) ENABLE,
  229. CONSTRAINT "APEX$_WS_LINKS_SH_CK" CHECK (show_on_homepage in ('Y','N')) ENABLE,
  230. CONSTRAINT "APEX$_WS_LINKS_PK" PRIMARY KEY ("ID") ENABLE
  231. ) ;CREATE TABLE "APEX$_WS_NOTES"
  232. ( "ID" NUMBER,
  233. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  234. "DATA_GRID_ID" NUMBER,
  235. "ROW_ID" NUMBER,
  236. "WEBPAGE_ID" NUMBER,
  237. "COMPONENT_LEVEL" VARCHAR2(30),
  238. "CONTENT" CLOB,
  239. "CREATED_ON" DATE NOT NULL ENABLE,
  240. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  241. "UPDATED_ON" DATE,
  242. "UPDATED_BY" VARCHAR2(255),
  243. CONSTRAINT "APEX$_WS_NOTES_CL_CK" CHECK (component_level in ('WEBSHEET','ROW','WORKSPACE','WEBPAGE')) ENABLE,
  244. CONSTRAINT "APEX$_WS_NOTES_PK" PRIMARY KEY ("ID") ENABLE
  245. ) ;CREATE TABLE "APEX$_WS_TAGS"
  246. ( "ID" NUMBER,
  247. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  248. "DATA_GRID_ID" NUMBER,
  249. "ROW_ID" NUMBER,
  250. "WEBPAGE_ID" NUMBER,
  251. "COMPONENT_LEVEL" VARCHAR2(30),
  252. "TAG" VARCHAR2(4000),
  253. "CREATED_ON" DATE NOT NULL ENABLE,
  254. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  255. "UPDATED_ON" DATE,
  256. "UPDATED_BY" VARCHAR2(255),
  257. CONSTRAINT "APEX$_WS_TAGS_CL_CK" CHECK (component_level in ('WEBSHEET','ROW','WORKSPACE','WEBPAGE')) ENABLE,
  258. CONSTRAINT "APEX$_WS_TAGS_PK" PRIMARY KEY ("ID") ENABLE
  259. ) ;CREATE TABLE "APEX$_WS_WEBPG_SECTIONS"
  260. ( "ID" NUMBER NOT NULL ENABLE,
  261. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  262. "WEBPAGE_ID" NUMBER,
  263. "DISPLAY_SEQUENCE" NUMBER,
  264. "SECTION_TYPE" VARCHAR2(30) NOT NULL ENABLE,
  265. "TITLE" VARCHAR2(4000),
  266. "CONTENT" CLOB,
  267. "CONTENT_UPPER" CLOB,
  268. "NAV_START_WEBPAGE_ID" NUMBER,
  269. "NAV_MAX_LEVEL" NUMBER,
  270. "NAV_INCLUDE_LINK" VARCHAR2(1),
  271. "DATA_GRID_ID" NUMBER,
  272. "REPORT_ID" NUMBER,
  273. "DATA_SECTION_STYLE" NUMBER,
  274. "CHART_TYPE" VARCHAR2(255),
  275. "CHART_3D" VARCHAR2(1),
  276. "CHART_LABEL" VARCHAR2(255),
  277. "LABEL_AXIS_TITLE" VARCHAR2(255),
  278. "CHART_VALUE" VARCHAR2(255),
  279. "VALUE_AXIS_TITLE" VARCHAR2(255),
  280. "CHART_AGGREGATE" VARCHAR2(255),
  281. "CHART_SORTING" VARCHAR2(255),
  282. "CREATED_ON" DATE NOT NULL ENABLE,
  283. "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
  284. "UPDATED_ON" DATE,
  285. "UPDATED_BY" VARCHAR2(255),
  286. CONSTRAINT "APEX$_WS_WEBPG_SECTION_TYPE_CK" CHECK (section_type in ('TEXT','DATA','CHART','NAV_PAGE','NAV_SECTION')) ENABLE,
  287. CONSTRAINT "APEX$_WS_WEBPG_SECTION_LINK_CK" CHECK (nav_include_link in ('Y','N')) ENABLE,
  288. CONSTRAINT "APEX$_WS_WEBPG_SECTION_3D_CK" CHECK (chart_3d in ('Y','N')) ENABLE,
  289. CONSTRAINT "APEX$_WS_WEBPG_SECTIONS_PK" PRIMARY KEY ("ID") ENABLE
  290. ) ;CREATE TABLE "APEX$_WS_WEBPG_SECTION_HISTORY"
  291. ( "SECTION_ID" NUMBER NOT NULL ENABLE,
  292. "WS_APP_ID" NUMBER NOT NULL ENABLE,
  293. "WEBPAGE_ID" NUMBER NOT NULL ENABLE,
  294. "OLD_DISPLAY_SEQUENCE" NUMBER,
  295. "NEW_DISPLAY_SEQUENCE" NUMBER,
  296. "OLD_TITLE" VARCHAR2(4000),
  297. "NEW_TITLE" VARCHAR2(4000),
  298. "OLD_CONTENT" CLOB,
  299. "NEW_CONTENT" CLOB,
  300. "APPLICATION_USER_ID" VARCHAR2(255) NOT NULL ENABLE,
  301. "CHANGE_DATE" DATE NOT NULL ENABLE
  302. ) ;CREATE TABLE "EMP"
  303. ( "EMPNO" NUMBER(4,0),
  304. "ENAME" VARCHAR2(12) NOT NULL ENABLE,
  305. "JOB" VARCHAR2(12) NOT NULL ENABLE,
  306. "MGR" NUMBER(4,0),
  307. "HIREDATE" DATE DEFAULT SYSDATE,
  308. "SAL" NUMBER(8,2),
  309. "COMM" NUMBER(8,2),
  310. "DEPNO" NUMBER(2,0),
  311. CONSTRAINT "VAL_SAL" CHECK ( Sal BETWEEN 500 AND 6000) ENABLE,
  312. CONSTRAINT "EMP_KEY" PRIMARY KEY ("EMPNO") ENABLE
  313. ) ;CREATE TABLE "PROJECTS"
  314. ( "PROJID" NUMBER(4,0),
  315. "P_DESC" VARCHAR2(25),
  316. "P_START_DATE" DATE,
  317. "P_END_DATE" DATE,
  318. "BUDGET_AMOUNT" NUMBER(7,2),
  319. "MAX_NO_STAFF" NUMBER(2,0),
  320. "COMMENTS" LONG,
  321. CONSTRAINT "PROJ_KEY" PRIMARY KEY ("PROJID") ENABLE
  322. ) ;CREATE TABLE "ASSIGNMENTS"
  323. ( "PROJID" NUMBER(4,0) NOT NULL ENABLE,
  324. "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
  325. "A_START_DATE" DATE,
  326. "A_END_DATE" DATE,
  327. "BILL_RATE" NUMBER(4,2) NOT NULL ENABLE,
  328. "ASSIGN_TYPE" VARCHAR2(2),
  329. "HOURS" NUMBER(3,0),
  330. CONSTRAINT "DATA_CONS" CHECK (A_end_date >= A_start_date) ENABLE,
  331. CONSTRAINT "UNQ_KEY" UNIQUE ("PROJID", "EMPNO") ENABLE
  332. ) ;CREATE TABLE "DEMO_CUSTOMERS"
  333. ( "CUSTOMER_ID" NUMBER NOT NULL ENABLE,
  334. "CUST_FIRST_NAME" VARCHAR2(20) NOT NULL ENABLE,
  335. "CUST_LAST_NAME" VARCHAR2(20) NOT NULL ENABLE,
  336. "CUST_STREET_ADDRESS1" VARCHAR2(60),
  337. "CUST_STREET_ADDRESS2" VARCHAR2(60),
  338. "CUST_CITY" VARCHAR2(30),
  339. "CUST_STATE" VARCHAR2(2),
  340. "CUST_POSTAL_CODE" VARCHAR2(10),
  341. "PHONE_NUMBER1" VARCHAR2(25),
  342. "PHONE_NUMBER2" VARCHAR2(25),
  343. "CREDIT_LIMIT" NUMBER(9,2),
  344. "CUST_EMAIL" VARCHAR2(30),
  345. CONSTRAINT "DEMO_CUST_CREDIT_LIMIT_MAX" CHECK (credit_limit <= 5000) ENABLE,
  346. CONSTRAINT "DEMO_CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID") ENABLE
  347. ) ;CREATE TABLE "DEMO_USERS"
  348. ( "USER_ID" NUMBER,
  349. "USER_NAME" VARCHAR2(100),
  350. "PASSWORD" VARCHAR2(4000),
  351. "CREATED_ON" DATE,
  352. "QUOTA" NUMBER,
  353. "PRODUCTS" CHAR(1),
  354. "EXPIRES_ON" DATE,
  355. "ADMIN_USER" CHAR(1),
  356. CONSTRAINT "DEMO_USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
  357. ) ;CREATE TABLE "DEMO_ORDERS"
  358. ( "ORDER_ID" NUMBER NOT NULL ENABLE,
  359. "CUSTOMER_ID" NUMBER NOT NULL ENABLE,
  360. "ORDER_TOTAL" NUMBER(8,2),
  361. "ORDER_TIMESTAMP" DATE,
  362. "USER_ID" NUMBER,
  363. CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,
  364. CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID") ENABLE
  365. ) ;CREATE TABLE "DEMO_PRODUCT_INFO"
  366. ( "PRODUCT_ID" NUMBER NOT NULL ENABLE,
  367. "PRODUCT_NAME" VARCHAR2(50),
  368. "PRODUCT_DESCRIPTION" VARCHAR2(2000),
  369. "CATEGORY" VARCHAR2(30),
  370. "PRODUCT_AVAIL" VARCHAR2(1),
  371. "LIST_PRICE" NUMBER(8,2),
  372. "PRODUCT_IMAGE" BLOB,
  373. "MIMETYPE" VARCHAR2(255),
  374. "FILENAME" VARCHAR2(400),
  375. "IMAGE_LAST_UPDATE" DATE,
  376. CONSTRAINT "DEMO_PRODUCT_INFO_PK" PRIMARY KEY ("PRODUCT_ID") ENABLE
  377. ) ;CREATE TABLE "DEMO_ORDER_ITEMS"
  378. ( "ORDER_ITEM_ID" NUMBER(3,0) NOT NULL ENABLE,
  379. "ORDER_ID" NUMBER NOT NULL ENABLE,
  380. "PRODUCT_ID" NUMBER NOT NULL ENABLE,
  381. "UNIT_PRICE" NUMBER(8,2) NOT NULL ENABLE,
  382. "QUANTITY" NUMBER(8,0) NOT NULL ENABLE,
  383. CONSTRAINT "DEMO_ORDER_ITEMS_PK" PRIMARY KEY ("ORDER_ITEM_ID") ENABLE
  384. ) ;CREATE TABLE "DEMO_STATES"
  385. ( "ST" VARCHAR2(30),
  386. "STATE_NAME" VARCHAR2(30)
  387. ) ;CREATE TABLE "DEPT"
  388. ( "DEPNO" NUMBER(2,0),
  389. "DNAME" VARCHAR2(12) NOT NULL ENABLE,
  390. "LOC" VARCHAR2(12) NOT NULL ENABLE,
  391. CONSTRAINT "DEPNO_KEY" PRIMARY KEY ("DEPNO") ENABLE
  392. ) ;CREATE TABLE "DEPT30"
  393. ( "EMPNO" NUMBER(4,0),
  394. "ENAME" VARCHAR2(12) NOT NULL ENABLE,
  395. "JOB" VARCHAR2(12) NOT NULL ENABLE,
  396. "SAL" NUMBER(8,2)
  397. ) ;CREATE TABLE "SALGRADE"
  398. ( "GRADE" NUMBER(2,0),
  399. "LOSAL" NUMBER(8,0) NOT NULL ENABLE,
  400. "HISAL" NUMBER(8,0) NOT NULL ENABLE,
  401. CONSTRAINT "GRADE_KEY" PRIMARY KEY ("GRADE") ENABLE
  402. ) ;ALTER TABLE "APEX$_WS_FILES" ADD CONSTRAINT "APEX$_WS_FILES_FK" FOREIGN KEY ("ROW_ID")
  403. REFERENCES "APEX$_WS_ROWS" ("ID") ON DELETE CASCADE ENABLE;ALTER TABLE "APEX$_WS_LINKS" ADD CONSTRAINT "APEX$_WS_LINKS_FK" FOREIGN KEY ("ROW_ID")
  404. REFERENCES "APEX$_WS_ROWS" ("ID") ON DELETE CASCADE ENABLE;ALTER TABLE "APEX$_WS_NOTES" ADD CONSTRAINT "APEX$_WS_NOTES_FK" FOREIGN KEY ("ROW_ID")
  405. REFERENCES "APEX$_WS_ROWS" ("ID") ON DELETE CASCADE ENABLE;ALTER TABLE "APEX$_WS_TAGS" ADD CONSTRAINT "APEX$_WS_TAGS_FK" FOREIGN KEY ("ROW_ID")
  406. REFERENCES "APEX$_WS_ROWS" ("ID") ON DELETE CASCADE ENABLE;ALTER TABLE "DEMO_ORDERS" ADD CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
  407. REFERENCES "DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE;ALTER TABLE "DEMO_ORDERS" ADD CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID")
  408. REFERENCES "DEMO_USERS" ("USER_ID") ENABLE;ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_FK" FOREIGN KEY ("ORDER_ID")
  409. REFERENCES "DEMO_ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE;ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_PRODUCT_ID_FK" FOREIGN KEY ("PRODUCT_ID")
  410. REFERENCES "DEMO_PRODUCT_INFO" ("PRODUCT_ID") ON DELETE CASCADE ENABLE;ALTER TABLE "EMP" ADD CONSTRAINT "DEPNO_FR_KEY" FOREIGN KEY ("DEPNO")
  411. REFERENCES "DEPT" ("DEPNO") ENABLE;ALTER TABLE "EMP" ADD CONSTRAINT "EMP_MGR" FOREIGN KEY ("MGR")
  412. REFERENCES "EMP" ("EMPNO") ENABLE;ALTER TABLE "ASSIGNMENTS" ADD FOREIGN KEY ("PROJID")
  413. REFERENCES "PROJECTS" ("PROJID") ENABLE;ALTER TABLE "ASSIGNMENTS" ADD FOREIGN KEY ("EMPNO")
  414. REFERENCES "EMP" ("EMPNO") ENABLE;
  415. CREATE OR REPLACE FUNCTION "AREA_QUADRADO" (lado1 in number, lado2 in number) return number
  416. IS
  417. area number;
  418. l1 number;
  419. l2 number;
  420. BEGIN
  421. l1:= lado1;
  422. l2:= lado2;
  423. area :=POWER((l1*l2),2);
  424. return(area);
  425. END;
  426. /
  427. /
  428. CREATE OR REPLACE FUNCTION "CUSTOM_AUTH" (p_username in VARCHAR2, p_password in VARCHAR2)
  429. return BOOLEAN
  430. is
  431. l_password varchar2(4000);
  432. l_stored_password varchar2(4000);
  433. l_expires_on date;
  434. l_count number;
  435. begin
  436. -- First, check to see if the user is in the user table
  437. select count(*) into l_count from demo_users where user_name = p_username;
  438. if l_count > 0 then
  439. -- First, we fetch the stored hashed password & expire date
  440. select password, expires_on into l_stored_password, l_expires_on
  441. from demo_users where user_name = p_username;
  442.  
  443. -- Next, we check to see if the user's account is expired
  444. -- If it is, return FALSE
  445. if l_expires_on > sysdate or l_expires_on is null then
  446.  
  447. -- If the account is not expired, we have to apply the custom hash
  448. -- function to the password
  449. l_password := custom_hash(p_username, p_password);
  450.  
  451. -- Finally, we compare them to see if they are the same and return
  452. -- either TRUE or FALSE
  453. if l_password = l_stored_password then
  454. return true;
  455. else
  456. return false;
  457. end if;
  458. else
  459. return false;
  460. end if;
  461. else
  462. -- The username provided is not in the DEMO_USERS table
  463. return false;
  464. end if;
  465. end;
  466. /
  467. /
  468. CREATE OR REPLACE FUNCTION "CUSTOM_HASH" (p_username in varchar2, p_password in varchar2)
  469. return varchar2
  470. is
  471. l_password varchar2(4000);
  472. l_salt varchar2(4000) := 'JQ0G2TRSJHYMTPRY7M9CBJSRXUHW6F';
  473. begin
  474.  
  475. -- This function should be wrapped, as the hash algorhythm is exposed here.
  476. -- You can change the value of l_salt or the method of which to call the
  477. -- DBMS_OBFUSCATOIN toolkit, but you much reset all of your passwords
  478. -- if you choose to do this.
  479.  
  480. l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
  481. (input_string => p_password || substr(l_salt,10,13) || p_username ||
  482. substr(l_salt, 4,10)));
  483. return l_password;
  484. end;
  485. /
  486. /
  487. CREATE OR REPLACE FUNCTION "DATE_DIFF" (max_date STRING, min_date STRING) RETURN INTEGER
  488. IS BEGIN RETURN TO_DATE(max_date) - TO_DATE(min_date);
  489. EXCEPTION WHEN OTHERS THEN RETURN NULL; END date_diff;
  490. /
  491. /
  492. CREATE OR REPLACE FUNCTION "EXERC1" (empid NUMBER) RETURN NUMBER IS
  493. minsal emp.sal%TYPE;
  494. maxsal emp.sal%TYPE;
  495. jobid emp.job%TYPE;
  496. salario emp.sal%TYPE;
  497.  
  498. BEGIN
  499. SELECT job, sal INTO jobid, salario FROM emp
  500. WHERE empno = empid;
  501.  
  502. SELECT MIN(sal), MAX(sal) INTO minsal, maxsal FROM emp
  503. WHERE job = jobid;
  504. RETURN ((salario - minsal) / (maxsal-minsal)*100);
  505. END;
  506. /
  507. /
  508. CREATE OR REPLACE FUNCTION "NOME_EMPREGADO" ( numero in number) return varchar2 is
  509. emp_name VARCHAR2(20);
  510. num Number;
  511. BEGIN
  512. num:= numero;
  513. SELECT ename INTO emp_name
  514. FROM emp WHERE empno = num;
  515. return(emp_name);
  516. END;
  517. /
  518. /
  519. CREATE UNIQUE INDEX "DEMO_USERS_PK" ON "DEMO_USERS" ("USER_ID")
  520. ;CREATE UNIQUE INDEX "DEMO_CUSTOMERS_PK" ON "DEMO_CUSTOMERS" ("CUSTOMER_ID")
  521. ;CREATE INDEX "DEMO_CUST_NAME_IX" ON "DEMO_CUSTOMERS" ("CUST_LAST_NAME", "CUST_FIRST_NAME")
  522. ;CREATE UNIQUE INDEX "DEMO_ORDER_PK" ON "DEMO_ORDERS" ("ORDER_ID")
  523. ;CREATE INDEX "DEMO_ORD_CUSTOMER_IX" ON "DEMO_ORDERS" ("CUSTOMER_ID")
  524. ;CREATE UNIQUE INDEX "SYS_IL0000020461C00007$$" ON "DEMO_PRODUCT_INFO" (
  525. ;CREATE UNIQUE INDEX "DEMO_PRODUCT_INFO_PK" ON "DEMO_PRODUCT_INFO" ("PRODUCT_ID")
  526. ;CREATE UNIQUE INDEX "DEMO_ORDER_ITEMS_PK" ON "DEMO_ORDER_ITEMS" ("ORDER_ITEM_ID")
  527. ;CREATE UNIQUE INDEX "APEX$_ACL_PK" ON "APEX$_ACL" ("ID")
  528. ;CREATE UNIQUE INDEX "APEX$_WS_WEBPG_SECTIONS_PK" ON "APEX$_WS_WEBPG_SECTIONS" ("ID")
  529. ;CREATE UNIQUE INDEX "SYS_IL0000020476C00008$$" ON "APEX$_WS_WEBPG_SECTIONS" (
  530. ;CREATE UNIQUE INDEX "SYS_IL0000020476C00007$$" ON "APEX$_WS_WEBPG_SECTIONS" (
  531. ;CREATE UNIQUE INDEX "APEX$_WS_ROWS_PK" ON "APEX$_WS_ROWS" ("ID")
  532. ;CREATE UNIQUE INDEX "SYS_IL0000020482C00166$$" ON "APEX$_WS_ROWS" (
  533. ;CREATE UNIQUE INDEX "SYS_IL0000020482C00165$$" ON "APEX$_WS_ROWS" (
  534. ;CREATE UNIQUE INDEX "APEX$_WS_NOTES_PK" ON "APEX$_WS_NOTES" ("ID")
  535. ;CREATE UNIQUE INDEX "SYS_IL0000020489C00007$$" ON "APEX$_WS_NOTES" (
  536. ;CREATE UNIQUE INDEX "APEX$_WS_LINKS_PK" ON "APEX$_WS_LINKS" ("ID")
  537. ;CREATE UNIQUE INDEX "APEX$_WS_TAGS_PK" ON "APEX$_WS_TAGS" ("ID")
  538. ;CREATE UNIQUE INDEX "APEX$_WS_FILES_PK" ON "APEX$_WS_FILES" ("ID")
  539. ;CREATE UNIQUE INDEX "SYS_IL0000020497C00010$$" ON "APEX$_WS_FILES" (
  540. ;CREATE UNIQUE INDEX "SYS_IL0000020501C00009$$" ON "APEX$_WS_WEBPG_SECTION_HISTORY" (
  541. ;CREATE UNIQUE INDEX "SYS_IL0000020501C00008$$" ON "APEX$_WS_WEBPG_SECTION_HISTORY" (
  542. ;CREATE INDEX "APEX$_ACL_IDX1" ON "APEX$_ACL" ("WS_APP_ID")
  543. ;CREATE INDEX "APEX$_WS_ROWS_IDX" ON "APEX$_WS_ROWS" ("WS_APP_ID", "DATA_GRID_ID")
  544. ;CREATE INDEX "APEX$_WS_HISTORY_IDX" ON "APEX$_WS_HISTORY" ("WS_APP_ID", "DATA_GRID_ID", "ROW_ID")
  545. ;CREATE INDEX "APEX$_WS_NOTES_IDX1" ON "APEX$_WS_NOTES" ("WS_APP_ID", "DATA_GRID_ID", "ROW_ID")
  546. ;CREATE INDEX "APEX$_WS_NOTES_IDX2" ON "APEX$_WS_NOTES" ("WS_APP_ID", "WEBPAGE_ID")
  547. ;CREATE INDEX "APEX$_WS_LINKS_IDX1" ON "APEX$_WS_LINKS" ("WS_APP_ID", "DATA_GRID_ID", "ROW_ID")
  548. ;CREATE INDEX "APEX$_WS_LINKS_IDX2" ON "APEX$_WS_LINKS" ("WS_APP_ID", "WEBPAGE_ID")
  549. ;CREATE INDEX "APEX$_WS_TAGS_IDX1" ON "APEX$_WS_TAGS" ("WS_APP_ID", "DATA_GRID_ID", "ROW_ID")
  550. ;CREATE INDEX "APEX$_WS_TAGS_IDX2" ON "APEX$_WS_TAGS" ("WS_APP_ID", "WEBPAGE_ID")
  551. ;CREATE INDEX "APEX$_WS_FILES_IDX1" ON "APEX$_WS_FILES" ("WS_APP_ID", "DATA_GRID_ID", "ROW_ID")
  552. ;CREATE INDEX "APEX$_WS_FILES_IDX2" ON "APEX$_WS_FILES" ("WS_APP_ID", "WEBPAGE_ID")
  553. ;CREATE INDEX "APEX$_WS_WEBPG_SECHIST_IDX1" ON "APEX$_WS_WEBPG_SECTION_HISTORY" ("WS_APP_ID", "WEBPAGE_ID", "SECTION_ID")
  554. ;CREATE UNIQUE INDEX "PROJ_KEY" ON "PROJECTS" ("PROJID")
  555. ;CREATE UNIQUE INDEX "GRADE_KEY" ON "SALGRADE" ("GRADE")
  556. ;CREATE UNIQUE INDEX "UNQ_KEY" ON "ASSIGNMENTS" ("PROJID", "EMPNO")
  557. ;CREATE UNIQUE INDEX "DEPNO_KEY" ON "DEPT" ("DEPNO")
  558. ;CREATE UNIQUE INDEX "EMP_KEY" ON "EMP" ("EMPNO")
  559. ;
  560. Rem No package found to generate DDL.
  561. CREATE OR REPLACE PROCEDURE "TRAB_1" (x1 IN NUMBER, x2 IN NUMBER) As
  562. salario_mensal NUMBER(6);
  563. dias_trabalho NUMBER(2);
  564. diaria NUMBER(6,2);
  565. BEGIN
  566. salario_mensal:= x1;
  567. dias_trabalho:= x2;
  568. diaria:= salario_mensal/dias_trabalho;
  569. htp.p ('O Valor da diaria é: ' || To_char(diaria));
  570. EXCEPTION
  571. WHEN ZERO_DIVIDE THEN
  572. diaria:=0;
  573. END;
  574. /
  575. /
  576. CREATE OR REPLACE PROCEDURE "TRAB_4" (NUM In NUMBER) AS
  577. Taxa CONSTANT NUMBER(2,2) := 0.05;
  578. Bonus NUMBER(8,2);
  579. Emp_id NUMBER(4);
  580. BEGIN
  581. Emp_id := NUM;
  582. SELECT EMP.Sal*Taxa INTO Bonus
  583. FROM EMP
  584. WHERE Empno = Emp_id;
  585. htp.p ('O Empregado é: ' ||TO_CHAR( Emp_id) || 'O Bonus é: ' || TO_CHAR(Bonus));
  586. END;
  587. /
  588. /
  589. CREATE SEQUENCE "DEMO_CUST_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ; CREATE SEQUENCE "DEMO_ORDER_ITEMS_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE ; CREATE SEQUENCE "DEMO_ORD_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 11 CACHE 20 NOORDER NOCYCLE ; CREATE SEQUENCE "DEMO_PROD_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ; CREATE SEQUENCE "DEMO_USERS_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;
  590. Rem No synonym found to generate DDL.
  591. CREATE OR REPLACE TRIGGER "APEX$_ACL_T1"
  592. before insert or update on "APEX$_ACL"
  593. for each row
  594. begin
  595. --
  596. -- maintain pk and timestamps
  597. --
  598. :new.username := upper(:new.username);
  599. if inserting and :new.id is null then
  600. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  601. end if;
  602. if inserting then
  603. :new.created_on := sysdate;
  604. :new.created_by := nvl(v('APP_USER'),user);
  605. :new.updated_on := sysdate;
  606. :new.updated_by := nvl(v('APP_USER'),user);
  607. elsif updating then
  608. :new.updated_on := sysdate;
  609. :new.updated_by := nvl(v('APP_USER'),user);
  610. end if;
  611. end;
  612. /
  613. ALTER TRIGGER "APEX$_ACL_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_FILES_T1"
  614. before insert or update on "APEX$_WS_FILES"
  615. for each row
  616. begin
  617. --
  618. -- maintain pk and timestamps
  619. --
  620. if inserting and :new.id is null then
  621. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  622. end if;
  623. if inserting and :new.image_alias is null then
  624. :new.image_alias := :new.name;
  625. end if;
  626. if inserting then
  627. :new.created_on := sysdate;
  628. :new.created_by := nvl(v('APP_USER'),user);
  629. :new.updated_on := sysdate;
  630. :new.updated_by := nvl(v('APP_USER'),user);
  631. :new.content_last_update := sysdate;
  632. elsif updating then
  633. :new.updated_on := sysdate;
  634. :new.updated_by := nvl(v('APP_USER'),user);
  635. if nvl(length(:new.content),0) <> nvl(length(:old.content),0) then
  636. :new.content_last_update := sysdate;
  637. end if;
  638. end if;
  639. end;
  640. /
  641. ALTER TRIGGER "APEX$_WS_FILES_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_LINKS_T1"
  642. before insert or update on "APEX$_WS_LINKS"
  643. for each row
  644. begin
  645. --
  646. -- maintain pk and timestamps
  647. --
  648. if inserting and :new.id is null then
  649. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  650. end if;
  651. if inserting then
  652. :new.created_on := sysdate;
  653. :new.created_by := nvl(v('APP_USER'),user);
  654. :new.updated_on := sysdate;
  655. :new.updated_by := nvl(v('APP_USER'),user);
  656. elsif updating then
  657. :new.updated_on := sysdate;
  658. :new.updated_by := nvl(v('APP_USER'),user);
  659. end if;
  660. end;
  661. /
  662. ALTER TRIGGER "APEX$_WS_LINKS_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_NOTES_T1"
  663. before insert or update on "APEX$_WS_NOTES"
  664. for each row
  665. begin
  666. --
  667. -- maintain pk and timestamps
  668. --
  669. if inserting and :new.id is null then
  670. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  671. end if;
  672. if inserting then
  673. :new.created_on := sysdate;
  674. :new.created_by := nvl(v('APP_USER'),user);
  675. :new.updated_on := sysdate;
  676. :new.updated_by := nvl(v('APP_USER'),user);
  677. elsif updating then
  678. :new.updated_on := sysdate;
  679. :new.updated_by := nvl(v('APP_USER'),user);
  680. end if;
  681. end;
  682. /
  683. ALTER TRIGGER "APEX$_WS_NOTES_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_ROWS_T1"
  684. before insert or update on "APEX$_WS_ROWS"
  685. for each row
  686. declare
  687. l_tag_list apex_application_global.vc_arr2;
  688. type col_arr is table of varchar2(32767) index by varchar2(255);
  689. la_col_label col_arr;
  690. procedure datagrid_logging(
  691. p_row_id in number,
  692. p_ws_app_id in number,
  693. p_data_grid_id in number,
  694. p_col_name in varchar2,
  695. p_type in varchar2,
  696. p_old_c in varchar2 default null,
  697. p_new_c in varchar2 default null,
  698. p_old_d in date default null,
  699. p_new_d in date default null,
  700. p_old_n in number default null,
  701. p_new_n in number default null)
  702. is
  703. begin
  704. case p_type
  705. when 'C' then
  706. if (p_old_c is null and p_new_c is not null) or (p_old_c is not null and p_new_c is null) or (p_old_c != p_new_c) then
  707. insert into apex$_ws_history (row_id, ws_app_id, data_grid_id, column_name, old_value, new_value, change_date, application_user_id)
  708. values (p_row_id, p_ws_app_id, p_data_grid_id, p_col_name, p_old_c, p_new_c, sysdate, v('APP_USER'));
  709. end if;
  710. when 'D' then
  711. if (p_old_d is null and p_new_d is not null) or (p_old_d is not null and p_new_d is null) or (p_old_d != p_new_d) then
  712. insert into apex$_ws_history (row_id, ws_app_id, data_grid_id, column_name, old_value, new_value, change_date, application_user_id)
  713. values (p_row_id, p_ws_app_id, p_data_grid_id, p_col_name, p_old_d, p_new_d, sysdate, v('APP_USER'));
  714. end if;
  715. when 'N' then
  716. if (p_old_n is null and p_new_n is not null) or (p_old_n is not null and p_new_n is null) or (p_old_n != p_new_n) then
  717. insert into apex$_ws_history (row_id, ws_app_id, data_grid_id, column_name, old_value, new_value, change_date, application_user_id)
  718. values (p_row_id, p_ws_app_id, p_data_grid_id, p_col_name, p_old_n, p_new_n, sysdate, v('APP_USER'));
  719. end if;
  720. end case;
  721. end datagrid_logging;
  722. procedure wa( p_c in out nocopy clob, p_buf in varchar2 )
  723. is
  724. l_lf varchar2(2) := unistr('\000a');
  725. begin
  726. if p_buf is not null then dbms_lob.writeappend( p_c, length(p_buf||l_lf), upper(p_buf)||l_lf); end if;
  727. end wa;
  728. begin
  729. --
  730. -- maintain pk and timestamps
  731. --
  732. if inserting and :new.id is null then
  733. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  734. end if;
  735. if inserting then
  736. :new.created_on := sysdate;
  737. :new.created_by := nvl(v('APP_USER'),user);
  738. :new.updated_on := sysdate;
  739. :new.updated_by := nvl(v('APP_USER'),user);
  740. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.load_order from dual;
  741. :new.change_count := 0;
  742. elsif updating then
  743. :new.updated_on := sysdate;
  744. :new.updated_by := nvl(v('APP_USER'),user);
  745. :new.change_count := :old.change_count + 1;
  746. end if;
  747. --
  748. -- inserting remove chr 13
  749. --
  750. if instr(:new.c001,chr(13)) > 0 then :new.c001 := replace(:new.c001,chr(13),null); end if;
  751. if instr(:new.c002,chr(13)) > 0 then :new.c002 := replace(:new.c002,chr(13),null); end if;
  752. if instr(:new.c003,chr(13)) > 0 then :new.c003 := replace(:new.c003,chr(13),null); end if;
  753. if instr(:new.c004,chr(13)) > 0 then :new.c004 := replace(:new.c004,chr(13),null); end if;
  754. if instr(:new.c005,chr(13)) > 0 then :new.c005 := replace(:new.c005,chr(13),null); end if;
  755. if instr(:new.c006,chr(13)) > 0 then :new.c006 := replace(:new.c006,chr(13),null); end if;
  756. if instr(:new.c007,chr(13)) > 0 then :new.c007 := replace(:new.c007,chr(13),null); end if;
  757. if instr(:new.c008,chr(13)) > 0 then :new.c008 := replace(:new.c008,chr(13),null); end if;
  758. if instr(:new.c009,chr(13)) > 0 then :new.c009 := replace(:new.c009,chr(13),null); end if;
  759. if instr(:new.c010,chr(13)) > 0 then :new.c010 := replace(:new.c010,chr(13),null); end if;
  760. if instr(:new.c011,chr(13)) > 0 then :new.c011 := replace(:new.c011,chr(13),null); end if;
  761. if instr(:new.c012,chr(13)) > 0 then :new.c012 := replace(:new.c012,chr(13),null); end if;
  762. if instr(:new.c013,chr(13)) > 0 then :new.c013 := replace(:new.c013,chr(13),null); end if;
  763. if instr(:new.c014,chr(13)) > 0 then :new.c014 := replace(:new.c014,chr(13),null); end if;
  764. if instr(:new.c015,chr(13)) > 0 then :new.c015 := replace(:new.c015,chr(13),null); end if;
  765. if instr(:new.c016,chr(13)) > 0 then :new.c016 := replace(:new.c016,chr(13),null); end if;
  766. if instr(:new.c017,chr(13)) > 0 then :new.c017 := replace(:new.c017,chr(13),null); end if;
  767. if instr(:new.c018,chr(13)) > 0 then :new.c018 := replace(:new.c018,chr(13),null); end if;
  768. if instr(:new.c019,chr(13)) > 0 then :new.c019 := replace(:new.c019,chr(13),null); end if;
  769. if instr(:new.c020,chr(13)) > 0 then :new.c020 := replace(:new.c010,chr(23),null); end if;
  770. if instr(:new.c021,chr(13)) > 0 then :new.c021 := replace(:new.c001,chr(23),null); end if;
  771. if instr(:new.c022,chr(13)) > 0 then :new.c022 := replace(:new.c002,chr(23),null); end if;
  772. if instr(:new.c023,chr(13)) > 0 then :new.c023 := replace(:new.c003,chr(23),null); end if;
  773. if instr(:new.c024,chr(13)) > 0 then :new.c024 := replace(:new.c004,chr(23),null); end if;
  774. if instr(:new.c025,chr(13)) > 0 then :new.c025 := replace(:new.c005,chr(23),null); end if;
  775. if instr(:new.c026,chr(13)) > 0 then :new.c026 := replace(:new.c006,chr(23),null); end if;
  776. if instr(:new.c027,chr(13)) > 0 then :new.c027 := replace(:new.c007,chr(23),null); end if;
  777. if instr(:new.c028,chr(13)) > 0 then :new.c028 := replace(:new.c008,chr(23),null); end if;
  778. if instr(:new.c029,chr(13)) > 0 then :new.c029 := replace(:new.c009,chr(23),null); end if;
  779. if instr(:new.c030,chr(13)) > 0 then :new.c030 := replace(:new.c030,chr(13),null); end if;
  780. if instr(:new.c031,chr(13)) > 0 then :new.c031 := replace(:new.c031,chr(13),null); end if;
  781. if instr(:new.c032,chr(13)) > 0 then :new.c032 := replace(:new.c032,chr(13),null); end if;
  782. if instr(:new.c033,chr(13)) > 0 then :new.c033 := replace(:new.c033,chr(13),null); end if;
  783. if instr(:new.c034,chr(13)) > 0 then :new.c034 := replace(:new.c034,chr(13),null); end if;
  784. if instr(:new.c035,chr(13)) > 0 then :new.c035 := replace(:new.c035,chr(13),null); end if;
  785. if instr(:new.c036,chr(13)) > 0 then :new.c036 := replace(:new.c036,chr(13),null); end if;
  786. if instr(:new.c037,chr(13)) > 0 then :new.c037 := replace(:new.c037,chr(13),null); end if;
  787. if instr(:new.c038,chr(13)) > 0 then :new.c038 := replace(:new.c038,chr(13),null); end if;
  788. if instr(:new.c039,chr(13)) > 0 then :new.c039 := replace(:new.c039,chr(13),null); end if;
  789. if instr(:new.c040,chr(13)) > 0 then :new.c040 := replace(:new.c040,chr(13),null); end if;
  790. if instr(:new.c041,chr(13)) > 0 then :new.c041 := replace(:new.c041,chr(13),null); end if;
  791. if instr(:new.c042,chr(13)) > 0 then :new.c042 := replace(:new.c042,chr(13),null); end if;
  792. if instr(:new.c043,chr(13)) > 0 then :new.c043 := replace(:new.c043,chr(13),null); end if;
  793. if instr(:new.c044,chr(13)) > 0 then :new.c044 := replace(:new.c044,chr(13),null); end if;
  794. if instr(:new.c045,chr(13)) > 0 then :new.c045 := replace(:new.c045,chr(13),null); end if;
  795. if instr(:new.c046,chr(13)) > 0 then :new.c046 := replace(:new.c046,chr(13),null); end if;
  796. if instr(:new.c047,chr(13)) > 0 then :new.c047 := replace(:new.c047,chr(13),null); end if;
  797. if instr(:new.c048,chr(13)) > 0 then :new.c048 := replace(:new.c048,chr(13),null); end if;
  798. if instr(:new.c049,chr(13)) > 0 then :new.c049 := replace(:new.c049,chr(13),null); end if;
  799. if instr(:new.c050,chr(13)) > 0 then :new.c050 := replace(:new.c050,chr(13),null); end if;
  800. if :new.search_clob is null then
  801. dbms_lob.createtemporary( :new.search_clob, false, dbms_lob.session );
  802. else
  803. dbms_lob.trim( :new.search_clob, 0 );
  804. end if;
  805. wa(:new.search_clob,:new.c001);wa(:new.search_clob,:new.c002);wa(:new.search_clob,:new.c003);
  806. wa(:new.search_clob,:new.c004);wa(:new.search_clob,:new.c005);wa(:new.search_clob,:new.c006);
  807. wa(:new.search_clob,:new.c007);wa(:new.search_clob,:new.c008);wa(:new.search_clob,:new.c009);
  808. wa(:new.search_clob,:new.c010);wa(:new.search_clob,:new.c011);wa(:new.search_clob,:new.c012);
  809. wa(:new.search_clob,:new.c013);wa(:new.search_clob,:new.c014);wa(:new.search_clob,:new.c015);
  810. wa(:new.search_clob,:new.c016);wa(:new.search_clob,:new.c017);wa(:new.search_clob,:new.c018);
  811. wa(:new.search_clob,:new.c019);wa(:new.search_clob,:new.c020);wa(:new.search_clob,:new.c021);
  812. wa(:new.search_clob,:new.c022);wa(:new.search_clob,:new.c023);wa(:new.search_clob,:new.c024);
  813. wa(:new.search_clob,:new.c025);wa(:new.search_clob,:new.c026);wa(:new.search_clob,:new.c027);
  814. wa(:new.search_clob,:new.c028);wa(:new.search_clob,:new.c029);wa(:new.search_clob,:new.c030);
  815. wa(:new.search_clob,:new.c031);wa(:new.search_clob,:new.c032);wa(:new.search_clob,:new.c033);
  816. wa(:new.search_clob,:new.c034);wa(:new.search_clob,:new.c035);wa(:new.search_clob,:new.c036);
  817. wa(:new.search_clob,:new.c037);wa(:new.search_clob,:new.c038);wa(:new.search_clob,:new.c039);
  818. wa(:new.search_clob,:new.c040);wa(:new.search_clob,:new.c041);wa(:new.search_clob,:new.c042);
  819. wa(:new.search_clob,:new.c043);wa(:new.search_clob,:new.c044);wa(:new.search_clob,:new.c045);
  820. wa(:new.search_clob,:new.c046);wa(:new.search_clob,:new.c047);wa(:new.search_clob,:new.c048);
  821. wa(:new.search_clob,:new.c049);wa(:new.search_clob,:new.c050);
  822. --
  823. -- history
  824. --
  825. if updating then
  826. -- initialize column label array
  827. for i in 1..50
  828. loop
  829. la_col_label('C'||to_char(i,'FM009')) := null;
  830. la_col_label('N'||to_char(i,'FM009')) := null;
  831. la_col_label('D'||to_char(i,'FM009')) := null;
  832. end loop;
  833. -- get column label array
  834. for c1 in (select column_alias, report_label
  835. from apex_ws_data_grid_col
  836. where data_grid_id = :new.data_grid_id)
  837. loop
  838. la_col_label(c1.column_alias) := c1.report_label;
  839. end loop;
  840. -- strings
  841. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C001'),'C',:old.c001,:new.c001);
  842. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C002'),'C',:old.c002,:new.c002);
  843. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C003'),'C',:old.c003,:new.c003);
  844. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C004'),'C',:old.c004,:new.c004);
  845. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C005'),'C',:old.c005,:new.c005);
  846. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C006'),'C',:old.c006,:new.c006);
  847. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C007'),'C',:old.c007,:new.c007);
  848. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C008'),'C',:old.c008,:new.c008);
  849. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C009'),'C',:old.c009,:new.c009);
  850. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C010'),'C',:old.c010,:new.c010);
  851. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C011'),'C',:old.c011,:new.c011);
  852. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C012'),'C',:old.c012,:new.c012);
  853. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C013'),'C',:old.c013,:new.c013);
  854. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C014'),'C',:old.c014,:new.c014);
  855. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C015'),'C',:old.c015,:new.c015);
  856. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C016'),'C',:old.c016,:new.c016);
  857. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C017'),'C',:old.c017,:new.c017);
  858. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C018'),'C',:old.c018,:new.c018);
  859. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C019'),'C',:old.c019,:new.c019);
  860. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C020'),'C',:old.c020,:new.c020);
  861. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C021'),'C',:old.c021,:new.c021);
  862. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C022'),'C',:old.c022,:new.c022);
  863. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C023'),'C',:old.c023,:new.c023);
  864. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C024'),'C',:old.c024,:new.c024);
  865. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C025'),'C',:old.c025,:new.c025);
  866. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C026'),'C',:old.c026,:new.c026);
  867. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C027'),'C',:old.c027,:new.c027);
  868. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C028'),'C',:old.c028,:new.c028);
  869. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C029'),'C',:old.c029,:new.c029);
  870. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C030'),'C',:old.c030,:new.c030);
  871. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C031'),'C',:old.c031,:new.c031);
  872. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C032'),'C',:old.c032,:new.c032);
  873. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C033'),'C',:old.c033,:new.c033);
  874. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C034'),'C',:old.c034,:new.c034);
  875. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C035'),'C',:old.c035,:new.c035);
  876. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C036'),'C',:old.c036,:new.c036);
  877. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C037'),'C',:old.c037,:new.c037);
  878. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C038'),'C',:old.c038,:new.c038);
  879. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C039'),'C',:old.c039,:new.c039);
  880. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C040'),'C',:old.c040,:new.c040);
  881. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C041'),'C',:old.c041,:new.c041);
  882. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C042'),'C',:old.c042,:new.c042);
  883. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C043'),'C',:old.c043,:new.c043);
  884. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C044'),'C',:old.c044,:new.c044);
  885. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C045'),'C',:old.c045,:new.c045);
  886. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C046'),'C',:old.c046,:new.c046);
  887. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C047'),'C',:old.c047,:new.c047);
  888. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C048'),'C',:old.c048,:new.c048);
  889. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C049'),'C',:old.c049,:new.c049);
  890. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('C050'),'C',:old.c050,:new.c050);
  891. -- numbers
  892. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N001'),'N',null,null,null,null,:old.n001,:new.n001);
  893. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N002'),'N',null,null,null,null,:old.n002,:new.n002);
  894. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N003'),'N',null,null,null,null,:old.n003,:new.n003);
  895. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N004'),'N',null,null,null,null,:old.n004,:new.n004);
  896. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N005'),'N',null,null,null,null,:old.n005,:new.n005);
  897. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N006'),'N',null,null,null,null,:old.n006,:new.n006);
  898. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N007'),'N',null,null,null,null,:old.n007,:new.n007);
  899. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N008'),'N',null,null,null,null,:old.n008,:new.n008);
  900. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N009'),'N',null,null,null,null,:old.n009,:new.n009);
  901. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N010'),'N',null,null,null,null,:old.n010,:new.n010);
  902. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N011'),'N',null,null,null,null,:old.n011,:new.n011);
  903. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N012'),'N',null,null,null,null,:old.n012,:new.n012);
  904. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N013'),'N',null,null,null,null,:old.n013,:new.n013);
  905. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N014'),'N',null,null,null,null,:old.n014,:new.n014);
  906. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N015'),'N',null,null,null,null,:old.n015,:new.n015);
  907. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N016'),'N',null,null,null,null,:old.n016,:new.n016);
  908. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N017'),'N',null,null,null,null,:old.n017,:new.n017);
  909. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N018'),'N',null,null,null,null,:old.n018,:new.n018);
  910. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N019'),'N',null,null,null,null,:old.n019,:new.n019);
  911. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N020'),'N',null,null,null,null,:old.n020,:new.n020);
  912. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N021'),'N',null,null,null,null,:old.n021,:new.n021);
  913. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N022'),'N',null,null,null,null,:old.n022,:new.n022);
  914. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N023'),'N',null,null,null,null,:old.n023,:new.n023);
  915. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N024'),'N',null,null,null,null,:old.n024,:new.n024);
  916. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N025'),'N',null,null,null,null,:old.n025,:new.n025);
  917. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N026'),'N',null,null,null,null,:old.n026,:new.n026);
  918. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N027'),'N',null,null,null,null,:old.n027,:new.n027);
  919. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N028'),'N',null,null,null,null,:old.n028,:new.n028);
  920. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N029'),'N',null,null,null,null,:old.n029,:new.n029);
  921. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N030'),'N',null,null,null,null,:old.n030,:new.n030);
  922. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N031'),'N',null,null,null,null,:old.n031,:new.n031);
  923. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N032'),'N',null,null,null,null,:old.n032,:new.n032);
  924. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N033'),'N',null,null,null,null,:old.n033,:new.n033);
  925. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N034'),'N',null,null,null,null,:old.n034,:new.n034);
  926. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N035'),'N',null,null,null,null,:old.n035,:new.n035);
  927. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N036'),'N',null,null,null,null,:old.n036,:new.n036);
  928. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N037'),'N',null,null,null,null,:old.n037,:new.n037);
  929. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N038'),'N',null,null,null,null,:old.n038,:new.n038);
  930. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N039'),'N',null,null,null,null,:old.n039,:new.n039);
  931. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N040'),'N',null,null,null,null,:old.n040,:new.n040);
  932. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N041'),'N',null,null,null,null,:old.n041,:new.n041);
  933. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N042'),'N',null,null,null,null,:old.n042,:new.n042);
  934. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N043'),'N',null,null,null,null,:old.n043,:new.n043);
  935. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N044'),'N',null,null,null,null,:old.n044,:new.n044);
  936. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N045'),'N',null,null,null,null,:old.n045,:new.n045);
  937. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N046'),'N',null,null,null,null,:old.n046,:new.n046);
  938. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N047'),'N',null,null,null,null,:old.n047,:new.n047);
  939. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N048'),'N',null,null,null,null,:old.n048,:new.n048);
  940. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N049'),'N',null,null,null,null,:old.n049,:new.n049);
  941. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('N050'),'N',null,null,null,null,:old.n050,:new.n050);
  942. -- dates
  943. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D001'),'D',null,null,:old.d001,:new.d001);
  944. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D002'),'D',null,null,:old.d002,:new.d002);
  945. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D003'),'D',null,null,:old.d003,:new.d003);
  946. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D004'),'D',null,null,:old.d004,:new.d004);
  947. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D005'),'D',null,null,:old.d005,:new.d005);
  948. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D006'),'D',null,null,:old.d006,:new.d006);
  949. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D007'),'D',null,null,:old.d007,:new.d007);
  950. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D008'),'D',null,null,:old.d008,:new.d008);
  951. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D009'),'D',null,null,:old.d009,:new.d009);
  952. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D010'),'D',null,null,:old.d010,:new.d010);
  953. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D011'),'D',null,null,:old.d011,:new.d011);
  954. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D012'),'D',null,null,:old.d012,:new.d012);
  955. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D013'),'D',null,null,:old.d013,:new.d013);
  956. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D014'),'D',null,null,:old.d014,:new.d014);
  957. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D015'),'D',null,null,:old.d015,:new.d015);
  958. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D016'),'D',null,null,:old.d016,:new.d016);
  959. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D017'),'D',null,null,:old.d017,:new.d017);
  960. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D018'),'D',null,null,:old.d018,:new.d018);
  961. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D019'),'D',null,null,:old.d019,:new.d019);
  962. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D020'),'D',null,null,:old.d020,:new.d020);
  963. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D021'),'D',null,null,:old.d021,:new.d021);
  964. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D022'),'D',null,null,:old.d022,:new.d022);
  965. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D023'),'D',null,null,:old.d023,:new.d023);
  966. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D024'),'D',null,null,:old.d024,:new.d024);
  967. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D025'),'D',null,null,:old.d025,:new.d025);
  968. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D026'),'D',null,null,:old.d026,:new.d026);
  969. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D027'),'D',null,null,:old.d027,:new.d027);
  970. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D028'),'D',null,null,:old.d028,:new.d028);
  971. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D029'),'D',null,null,:old.d029,:new.d029);
  972. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D030'),'D',null,null,:old.d030,:new.d030);
  973. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D031'),'D',null,null,:old.d031,:new.d031);
  974. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D032'),'D',null,null,:old.d032,:new.d032);
  975. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D033'),'D',null,null,:old.d033,:new.d033);
  976. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D034'),'D',null,null,:old.d034,:new.d034);
  977. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D035'),'D',null,null,:old.d035,:new.d035);
  978. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D036'),'D',null,null,:old.d036,:new.d036);
  979. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D037'),'D',null,null,:old.d037,:new.d037);
  980. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D038'),'D',null,null,:old.d038,:new.d038);
  981. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D039'),'D',null,null,:old.d039,:new.d039);
  982. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D040'),'D',null,null,:old.d040,:new.d040);
  983. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D041'),'D',null,null,:old.d041,:new.d041);
  984. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D042'),'D',null,null,:old.d042,:new.d042);
  985. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D043'),'D',null,null,:old.d043,:new.d043);
  986. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D044'),'D',null,null,:old.d044,:new.d044);
  987. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D045'),'D',null,null,:old.d045,:new.d045);
  988. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D046'),'D',null,null,:old.d046,:new.d046);
  989. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D047'),'D',null,null,:old.d047,:new.d047);
  990. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D048'),'D',null,null,:old.d048,:new.d048);
  991. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D049'),'D',null,null,:old.d049,:new.d049);
  992. datagrid_logging(:new.id,:new.ws_app_id,:new.data_grid_id,la_col_label('D050'),'D',null,null,:old.d050,:new.d050);
  993. end if;
  994. --
  995. -- set owner
  996. --
  997. if :new.owner is null then
  998. :new.owner := :new.created_by;
  999. end if;
  1000. end;
  1001.  
  1002. /
  1003. ALTER TRIGGER "APEX$_WS_ROWS_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_TAGS_T1"
  1004. before insert or update on "APEX$_WS_TAGS"
  1005. for each row
  1006. begin
  1007. --
  1008. -- maintain pk and timestamps
  1009. --
  1010. if inserting and :new.id is null then
  1011. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  1012. end if;
  1013. if inserting then
  1014. :new.created_on := sysdate;
  1015. :new.created_by := nvl(v('APP_USER'),user);
  1016. :new.updated_on := sysdate;
  1017. :new.updated_by := nvl(v('APP_USER'),user);
  1018. elsif updating then
  1019. :new.updated_on := sysdate;
  1020. :new.updated_by := nvl(v('APP_USER'),user);
  1021. end if;
  1022. end;
  1023. /
  1024. ALTER TRIGGER "APEX$_WS_TAGS_T1" ENABLE;CREATE OR REPLACE TRIGGER "APEX$_WS_WEBPG_SECTIONS_T1"
  1025. before insert or update on "APEX$_WS_WEBPG_SECTIONS"
  1026. for each row
  1027. declare
  1028. l_sequence_changed varchar2(1) := 'N';
  1029. l_title_changed varchar2(1) := 'N';
  1030. l_content_changed varchar2(1) := 'N';
  1031. procedure clob_upper( p_content in clob, p_content_upper in out nocopy clob)
  1032. is
  1033. l_buf varchar2(32767);
  1034. l_off number;
  1035. l_amt number;
  1036. begin
  1037. if p_content is not null then
  1038. l_amt := 8000;
  1039. l_off := 1;
  1040. dbms_lob.trim( p_content_upper, 0);
  1041. begin
  1042. loop
  1043. dbms_lob.read( p_content, l_amt, l_off, l_buf );
  1044. l_buf := upper( l_buf );
  1045. dbms_lob.writeappend( p_content_upper, length(l_buf), l_buf);
  1046. l_off := l_off + l_amt;
  1047. l_amt := 8000;
  1048. end loop;
  1049. exception
  1050. when no_data_found then null;
  1051. end;
  1052. end if;
  1053. end clob_upper;
  1054. begin
  1055. --
  1056. -- maintain pk and timestamps
  1057. --
  1058. if inserting and :new.id is null then
  1059. select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') into :new.id from dual;
  1060. end if;
  1061. if :new.section_type = 'NAV_PAGE' then
  1062. if :new.nav_include_link is null then
  1063. :new.nav_include_link := 'Y';
  1064. end if;
  1065. end if;
  1066. if inserting and :new.content is not null then
  1067. dbms_lob.createtemporary( :new.content_upper, false, dbms_lob.call );
  1068. clob_upper( :new.content, :new.content_upper );
  1069. elsif updating then
  1070. if :new.content_upper is null then
  1071. dbms_lob.createtemporary( :new.content_upper, false, dbms_lob.call );
  1072. end if;
  1073. clob_upper( :new.content, :new.content_upper );
  1074. end if;
  1075. if inserting then
  1076. :new.created_on := sysdate;
  1077. :new.created_by := nvl(v('APP_USER'),user);
  1078. :new.updated_on := sysdate;
  1079. :new.updated_by := nvl(v('APP_USER'),user);
  1080. elsif updating then
  1081. :new.updated_on := sysdate;
  1082. :new.updated_by := nvl(v('APP_USER'),user);
  1083. if nvl(:old.display_sequence,-999) != nvl(:new.display_sequence,-999) then
  1084. l_sequence_changed := 'Y';
  1085. end if;
  1086. if nvl(:old.title,'jKKwZk') != nvl(:new.title,'jKKwZk') then
  1087. l_title_changed := 'Y';
  1088. end if;
  1089. if dbms_lob.compare(:new.content,:old.content) != 0 or nvl(length(:new.content),0) != nvl(length(:old.content),0) then
  1090. l_content_changed := 'Y';
  1091. end if;
  1092. if l_sequence_changed = 'Y' or l_title_changed = 'Y' or l_content_changed = 'Y' then
  1093. insert into apex$_ws_webpg_section_history (section_id, ws_app_id, webpage_id, old_display_sequence, new_display_sequence,
  1094. old_title, new_title, old_content, new_content, change_date, application_user_id)
  1095. values (:new.id, :new.ws_app_id, :new.webpage_id,
  1096. decode(l_sequence_changed,'Y',:old.display_sequence,null), decode(l_sequence_changed,'Y',:new.display_sequence,null),
  1097. decode(l_title_changed,'Y',:old.title,null), decode(l_title_changed,'Y',:new.title,null),
  1098. decode(l_content_changed,'Y',:old.content,null), decode(l_content_changed,'Y',:new.content,null), sysdate, v('APP_USER'));
  1099. end if;
  1100. end if;
  1101. end;
  1102. /
  1103. ALTER TRIGGER "APEX$_WS_WEBPG_SECTIONS_T1" ENABLE;CREATE OR REPLACE TRIGGER "BI_DEMO_USERS"
  1104. BEFORE
  1105. insert on "DEMO_USERS"
  1106. for each row
  1107. begin
  1108. begin
  1109. for c1 in (
  1110. select DEMO_USERS_SEQ.nextval next_val
  1111. from dual
  1112. ) loop
  1113. :new.USER_ID := c1.next_val;
  1114. :new.admin_user := 'N';
  1115. :new.created_on := sysdate;
  1116. end loop;
  1117. end;
  1118. end;
  1119. /
  1120. ALTER TRIGGER "BI_DEMO_USERS" ENABLE;CREATE OR REPLACE TRIGGER "DEMO_ORDER_ITEMS_GET_PRICE"
  1121. before insert or update on demo_order_items for each row
  1122. declare
  1123. l_list_price number;
  1124. begin
  1125. -- First, we need to get the current list price of the order line item
  1126. select list_price into l_list_price from demo_product_info
  1127. where product_id = :new.product_id;
  1128.  
  1129. -- Once we have the correct price, we will update the order line with the correct price
  1130. :new.unit_price := l_list_price;
  1131.  
  1132. end;
  1133. /
  1134. ALTER TRIGGER "DEMO_ORDER_ITEMS_GET_PRICE" ENABLE;CREATE OR REPLACE TRIGGER "DEMO_USERS_T1"
  1135. BEFORE
  1136. insert or update on "DEMO_USERS"
  1137. for each row
  1138. begin
  1139. :NEW.user_name := upper(:NEW.user_name);
  1140. end;
  1141. /
  1142. ALTER TRIGGER "DEMO_USERS_T1" ENABLE;CREATE OR REPLACE TRIGGER "INSERT_DEMO_CUST"
  1143. BEFORE INSERT ON demo_customers
  1144. FOR EACH ROW
  1145. DECLARE
  1146. cust_id number;
  1147. BEGIN
  1148. SELECT demo_cust_seq.nextval
  1149. INTO cust_id
  1150. FROM dual;
  1151. :new.CUSTOMER_ID := cust_id;
  1152. END;
  1153. /
  1154. ALTER TRIGGER "INSERT_DEMO_CUST" ENABLE;CREATE OR REPLACE TRIGGER "INSERT_DEMO_ORDER_ITEMS"
  1155. BEFORE
  1156. insert on "DEMO_ORDER_ITEMS"
  1157. for each row
  1158. begin
  1159. declare
  1160. order_item_id number;
  1161. begin
  1162. select demo_order_items_seq.nextval into order_item_id from dual;
  1163. :new.ORDER_ITEM_ID := order_item_id;
  1164. end;
  1165. end;
  1166. /
  1167. ALTER TRIGGER "INSERT_DEMO_ORDER_ITEMS" ENABLE;CREATE OR REPLACE TRIGGER "INSERT_DEMO_PROD"
  1168. BEFORE INSERT ON demo_product_info
  1169. FOR EACH ROW
  1170. DECLARE
  1171. prod_id number;
  1172. BEGIN
  1173. SELECT demo_prod_seq.nextval
  1174. INTO prod_id
  1175. FROM dual;
  1176. :new.PRODUCT_ID := prod_id;
  1177. END;
  1178. /
  1179. ALTER TRIGGER "INSERT_DEMO_PROD" ENABLE;CREATE OR REPLACE TRIGGER "UPDATE_ORDER_TOTAL"
  1180. after insert or update or delete on demo_order_items
  1181. begin
  1182.  
  1183. -- Update the Order Total when any order item is changed
  1184.  
  1185. update demo_orders set order_total =
  1186. (select sum(unit_price*quantity) from demo_order_items
  1187. where demo_order_items.order_id = demo_orders.order_id);
  1188. end;
  1189. /
  1190. ALTER TRIGGER "UPDATE_ORDER_TOTAL" ENABLE;
  1191. Rem No view found to generate DDL.
  1192. Rem No database link found to generate DDL.
  1193. Rem No type found to generate DDL.
  1194. Rem No materialized view found to generate DDL.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!