Use: APEX_JSON.write('P_INVOICE_TRACKING', '', true);
Result: "P_INVOICE_TRACKING":null
Use: APEX_JSON.write_raw('P_INVOICE_NUMBER', '"'||r_header.sppd_code||'"');
Result: "P_INVOICE_NUMBER":"0134/XYZ/084/RS/2021"
Select * from APEX_MAIL_QUEUE;
create or replace procedure set_apex_context (
p_app_id in number
) as
begin
FOR c1 IN (
SELECT workspace_id
FROM apex_applications
WHERE application_id = p_app_id
)
LOOP
apex_util.set_security_group_id(p_security_group_id => c1.workspace_id);
END LOOP;
end set_apex_context;
execute set_apex_context (140); --i40 is app id Select * from APEX_MAIL_QUEUE;
ALTER TABLESPACE tablespace_name OFFLINE;
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'fully qualified path to original data file name' TO 'new or original fully qualified path to new or original data file name';
ALTER TABLESPACE tablespace_name ONLINE;
recover datafile 'new fully qualified path to original data file name'
function(){
openModal('addDept');
gBackground.off('click');
}
http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.xchange where pair in ("USDIDR")&env=store://datatables.org/alltableswithkeys
create or replace function get_yahoo_rate
return number
is
/******************************************************************************
NAME: get_yahoo_rate
PURPOSE: get latest USD-IDR rate from Yahoo finance exchange
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/10/2014 Lutfi Hedir 1. Created this function.
******************************************************************************/
l_xml xmltype;
l_yahoo_uri constant varchar2(500) := 'http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22USDIDR%22)&env=store://datatables.org/alltableswithkeys';
l_rate varchar2(500);
l_rate_n number(10);
begin
-- get xml data
l_xml := xmltype(utl_http.request(l_yahoo_uri));
-- extract rate value from xml data
select extractvalue(l_xml, '/query/results/rate/Rate/text()') into l_rate
from dual;
-- convert to number
l_rate_n := to_number(REGEXP_SUBSTR(l_rate,'[0-9]+'));
return l_rate_n;
end get_yahoo_rate;
/
create or replace PACKAGE BODY web_utils IS
-- retrieves image from blob in product info
PROCEDURE display_bu_logo (p_id number, p_product_name varchar2)
IS
l_mime varchar2 (255);
l_length number;
l_file_name varchar2 (2000);
lob_loc blob;
begin
if p_id is not null then
select mimetype
, filename
, nvl(product_image, empty_blob()) blob_content
, dbms_lob.getlength(product_image)
into l_mime
, l_file_name
, lob_loc
, l_length
from demo_product_info
where product_id = p_id;
elsif p_product_name is not null then
select mimetype
, filename
, nvl(product_image, empty_blob()) blob_content
, dbms_lob.getlength(product_image)
into l_mime
, l_file_name
, lob_loc
, l_length
from demo_product_info
where lower(product_name) = lower(p_product_name);
end if;
owa_util.mime_header (nvl (l_mime, 'application/octet'), false);
-- set the size so the browser knows how much to download
htp.p ('Content-length: ' || l_length);
-- the filename will be used by the browser if the users does a save as
htp.p ( 'Content-Disposition: attachment; filename="'
|| replace (replace (substr (l_file_name,
instr (l_file_name, '/') + 1
),
chr (10),
null
),
chr (13),
null
)
|| '"'
);
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file (lob_loc);
END display_bu_logo;
END web_utils;
-- htp.p ( 'Content-Disposition: attachment; filename="' -- || replace (replace (substr (l_file_name, -- instr (l_file_name, '/') + 1 -- ), -- chr (10), -- null -- ), -- chr (13), -- null -- ) -- || '"' -- );
http://localhost:8888/ords/hr.web_utils.display_bu_logo?p_product_name=Blouse&p_id=
http://localhost:8888/ords/!hr.web_utils.display_bu_logo?p_product_name=Blouse&p_id=
insert into mytable@mylink
( ename
, nipeg
, start_date
, end_date)
select ename
, nipeg
, get_start(nipeg)
, get_end(nipeg)
from emp;
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names'
begin
for c_emp in ( select ename
, nipeg
, get_start(nipeg) start_date
, get_end(nipeg) end_date
from emp)
loop
insert into mytable@mylink
( ename
, nipeg
, start_date
, end_date)
values
( c_emp.ename
, c_emp.nipeg
, c_emp.start_date
, c_emp.end_date);
end loop;
end;
contoh dalam collection :
declare
type t_emp is table of mytable@mylink%rowtype
index by pls_integer;
c_emp t_emp;
begin
select ename
, nipeg
, get_start(nipeg) start_date
, get_end(nipeg) end_date
bulk collect
into c_emp;
for i in c_emp.first .. c_emp.last
loop
insert into mytable@mylink
( ename
, nipeg
, start_date
, end_date)
values (c_emp(i).ename
, c_emp(i).nipeg
, c_emp(i).start_date
, c_emp(i).end_date);
end loop;
end;
| Operator | Description |
|---|---|
| EQ | Equals (this is the default) |
| NEQ | Not equals |
| LT | Less than |
| LTE | Less than or equal to |
| GT | Greater than |
| GTE | Greater than or equal to |
| LIKE | SQL 'like' operator |
| N | is null |
| NN | is not null |
| C | Contains |
| NC | not contains |
| IN | SQL 'in' Operator |
| NIN | SQL 'not in' Operator |
--- create new ACL for HR
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'hr_acl_file.xml',
description => 'A test of the ACL functionality',
principal => 'HR',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
-- assign ACL to networks chart.googleapis.com
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'hr_acl_file.xml',
host => '*.googleapis.com',
lower_port => 80,
upper_port => 9999);
COMMIT;
END;
/
declare
v_encode_url varchar2 (1000);
begin
v_encode_url := 'https://chart.googleapis.com/chart?chs='||:P2_SIZE||'&cht=qr&chl='||utl_url.escape(:P2_QRCODE);
update emp
set EMP_PHOTO = return_binary_from_url (v_encode_url)
, ATTACH_MIMETYPE = 'image/png'
, ATTACH_FILENAME = :P2_EMPNO||'_QR_CODE.png'
, ATTACH_LAST_UPDATE = sysdate
where empno = :P2_EMPNO;
end;
select utl_http.request('https://www.google.com'
, null
, 'file:/mywallets/google.com'
, 'your wallet password')
from dual;
select utl_http.request('https://www.google.com'
, 'username:pwdproxy@yourproxyhost:yourproxyport'
, 'file:/mywallets/google.com'
, 'your wallet password')
from dual;
select null "icon" ,deptno, DNAME ,case when deptno = :PXX_DEPT_HL then 'HIGHLIGHT' else null end HIGHLIGHT from dept
select EMP.EMPNO as EMPNO,
EMP.ENAME as ENAME,
EMP.JOB as JOB,
EMP.MGR as MGR,
EMP.HIREDATE as HIREDATE,
EMP.SAL as SAL,
EMP.COMM as COMM,
EMP.DEPTNO as DEPTNO ,case when deptno = :PXX_DEPT_HL then 'HIGHLIGHT' else null end HIGHLIGHT
from EMP EMP
javascript:$s('PXX_DEPT_HL','#DEPTNO#');
<span id="#HIGHLIGHT#DNAME#R#">#DNAME#</span>
$("[id^=HIGHLIGHT]").closest('tr').children('td').css("background-color", "#FD4");
dan Refresh region report interactive.
create or replace
function get_report_headers
(
p_table in varchar2 default 'EMP'
, p_separator in varchar2 default ','
, p_type in varchar2 default 'HEADER'
, p_num in number default 1
) return varchar2 as
v_temp_arr apex_application_global.vc_arr2;
v_list varchar2(32767);
begin
if p_type = 'HEADER' then
SELECT column_name bulk collect
into v_temp_arr
FROM USER_TAB_COLUMNS
WHERE owner = 'HR'
and table_name = p_table
and owner = 'HR'
and data_type <> 'BLOB'
order by column_id;
v_list := apex_util.table_to_string(v_temp_arr,p_separator);
return v_list;
else
if p_num > 10 then
v_list := null;
else
begin
SELECT initcap(REPLACE(column_name,'_',' '))
into v_list
FROM USER_TAB_COLUMNS
WHERE owner = 'HR'
and table_name = p_table
and owner = 'HR'
and column_id = p_num;
exception when others then v_list := null;
end;
end if;
return v_list;
end if;
end get_report_headers;
declare
v_query VARCHAR2(32767):=null;
v_collection varchar2(100) := nvl(:P20_COLL_NAME,'DYNAMIC_'||:SESSION_ID);
begin
v_query := 'select '||
get_report_headers(p_table => :P20_TABLE_NAME) ||
' from HR.' || nvl(:P20_TABLE_NAME,'EMP');
IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => v_collection) THEN
APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v_collection);
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
,p_query => v_query );
else
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
,p_query => v_query );
end if;
end;
:P20_C01 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 1); :P20_C02 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 2); :P20_C03 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 3); :P20_C04 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 4); :P20_C05 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 5); :P20_C06 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 6); :P20_C07 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 7); :P20_C08 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 8); :P20_C09 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 9); :P20_C10 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 10);
select * from APEX_collections where collection_name = :P20_COLL_NAME;
SELECT ENAME
, EMPNO
FROM EMP
ORDER by 1
Lalu pada bagian render before header buatlah process : GET_SELECTED:declare l_emp_array wwv_flow_global.vc_arr2; l_list varchar2(32767); begin -- get all selected emp into array select empno bulk collect into l_emp_array from emp_assign; -- convert array to colon seperated string l_list := apex_util.table_to_string(l_emp_array,':'); :PXX_SELECTED_EMPLOYEES := l_list; end;
DECLARE
v_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
--delete existing assignment
delete emp_assign;
-- Convert the colon separated string into an array
v_selected := APEX_UTIL.STRING_TO_TABLE(:PXX_SELECTED_EMPLOYEES);
-- Loop over array and insert into table
FOR i IN 1..v_selected.count LOOP
insert into emp_assign
(empno)
values
(v_selected(i));
END LOOP;
END;
$v("PXX_SELECTED_EMPLOYEES")
.
DECLARE
l_body CLOB;
l_body_html CLOB;
BEGIN
l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
l_body_html := '<html>
<head>
<style type="text/css">
body{font-family: Arial, Helvetica, sans-serif;
font-size:10pt;
margin:30px;
background-color:#ffffff;}
span.sig{font-style:italic;
font-weight:bold;
color:#811919;}
</style>
</head>
<body>'||utl_tcp.crlf;
l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p>'||utl_tcp.crlf;
l_body_html := l_body_html ||' Sincerely,<br />'||utl_tcp.crlf;
l_body_html := l_body_html ||' <span class="sig">The APEX Dev Team</span><br/>'||utl_tcp.crlf;
l_body_html := l_body_html ||'</body></html>';
apex_mail.send( p_to => 'some_user@somewhere.com', -- change to your email address
p_from => 'some_sender@somewhere.com', -- change to a real senders email address
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'APEX_MAIL Package - HTML formatted message');
END;
/
Beberapa error yang muncul seperti :
Hal ini terjadi jika type yang tidak sama anda berikan pada value p_body dan p_body_html pastikan jika memakai varchar2 semua atau clob semua
Hal ini terjadi kita memakai clob dengan data memakai concate '||'yang melebihi kapasitas varchar2(32767)
Solusinya kita dapat mengganti dengan tipe data CLOB dengan memakai DBMS_LOB.APPEND, pada contoh diatas dapat dimodifikasi dengan cara
DECLARE
l_body CLOB;
l_body_html CLOB;
BEGIN
l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
dbms_lob.createtemporary (l_body_html,true);
DBMS_LOB.append (l_body_html,'<html>
<head>
<style type="text/css">
body{font-family: Arial, Helvetica, sans-serif;
font-size:10pt;
margin:30px;
background-color:#ffffff;}
span.sig{font-style:italic;
font-weight:bold;
color:#811919;}
</style>
</head>
<body>');
DBMS_LOB.append (l_body_html,'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p>');
DBMS_LOB.append (l_body_html,' Sincerely,<br />');
DBMS_LOB.append (l_body_html,' <span class="sig">The APEX Dev Team</span><br/>');
DBMS_LOB.append (l_body_html,'</body></html>');
apex_mail.send(p_to => 'some_user@somewhere.com', -- change to your email address
p_from => 'some_sender@somewhere.com', -- change to a real senders email address
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'APEX_MAIL Package - HTML formatted message');
dbms_lob.freetemporary (l_body_html);
END;
/
<iframe src="f?p=&APP_ID.:29:&SESSION."
style="width:500px;height:500px"
name="details"
scrolling="no" marginwidth="0" marginheight="0"
frameborder="0" vspace="0" hspace="0" >
</iframe>
<iframe src="f?p=&APP_ID.:30:&SESSION."
style="width:500px;height:500px"
name="details1"
scrolling="no" marginwidth="0" marginheight="0"
frameborder="0" vspace="0" hspace="0" >
</iframe>
select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ORDER_TIMESTAMP from DEMO_ORDERS where customer_id=:P29_CUSTOMER_ID
top.frames['details'].location.href = 'f?p=&APP_ID.:29:&SESSION.::&DEBUG.::P29_CUSTOMER_ID:&P27_CUSTOMER_ID.';top.frames['details1'].location.href = 'f?p=&APP_ID.:30:&SESSION.::&DEBUG.::P30_CUSTOMER_ID:&P27_CUSTOMER_ID.';
begin
--set user menjadi nama tertentu cara 1
apex_custom_auth.set_user('lutfi.hedir');
--set user menjadi nama tertentu cara 2
wwv_flow.g_user := 'lutfi.hedir';
end;
create or replace procedure my_post_login is
begin
apex_custom_auth.set_user('lutfi.hedir');
end my_post_login;
begin
wwv_flow_api.create_or_remove_file(
p_location => 'APPLICATION',
p_name => 'xxx.jpg',
p_mode => 'REMOVE',
p_type => 'IMAGE');
end;
/
declare
v_id number;
v_ws varchar2(100):='TEST';
begin
--- cari workspace ID
select workspace_id into v_id
from apex_workspaces
where WORKSPACE=upper(v_ws);
---set security supaya bisa di exsekusi diluar apex
wwv_flow_api.set_security_group_id(v_id);
---cursor
FOR x IN (select *
from APEX_WORKSPACE_FILES
where MIME_TYPE like 'image/%') LOOP
wwv_flow_api.create_or_remove_file(
p_location => 'WORKSPACE',
p_name => x.FILE_NAME,
p_mode => 'REMOVE',
p_type => 'IMAGE');
END LOOP;
commit;
end;
/