<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report1" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="0a6876d5-cc17-47a9-b072-2f4be3000e61">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<queryString>
<![CDATA[select team as "Verein",
nvl(sum("11/12"),0) as "11/12", nvl(sum("10/11"),0) as "10/11",
nvl(sum("09/10"),0) as "09/10", nvl(sum("08/09"),0) as "08/09",
to_char(sysdate,'dd.mm.yyyy') as cur_date,
v('P10_USERNAME') as app_user
from (
SELECT team,
DECODE (jahr, '11/12', punkte, NULL) as "11/12",
DECODE (jahr, '10/11', punkte, NULL) as "10/11",
DECODE (jahr, '09/10', punkte, NULL) as "09/10",
DECODE (jahr, '08/09', punkte, NULL) as "08/09"
FROM (SELECT jahr, team, punkte FROM tbl_bundesliga)
)
group by team
order by team asc]]>
</queryString>
<field name="Verein" class="java.lang.String"/>
<field name="11/12" class="java.math.BigDecimal"/>
<field name="10/11" class="java.math.BigDecimal"/>
<field name="09/10" class="java.math.BigDecimal"/>
<field name="08/09" class="java.math.BigDecimal"/>
<field name="CUR_DATE" class="java.lang.String"/>
<field name="APP_USER" class="java.lang.String"/>
<variable name="PRINT1" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<pageHeader>
<band height="62">
<staticText>
<reportElement uuid="39c15a6e-237e-49f7-bb5b-343a8c60dd98" x="0" y="13" width="555" height="37"/>
<textElement textAlignment="Center">
<font size="22" isBold="true" isUnderline="true"/>
</textElement>
<text><![CDATA[Bundesliga Historie 08/09 - 11/12]]></text>
</staticText>
</band>
</pageHeader>
<columnHeader>
<band height="21" splitType="Stretch">
<line>
<reportElement uuid="08af8ace-e78a-4a9e-afdc-6b74abeef679" x="108" y="0" width="1" height="20"/>
</line>
<line>
<reportElement uuid="5a5b9e4f-672f-4f1c-be01-576545ed5efb" x="1" y="20" width="554" height="1"/>
</line>
<staticText>
<reportElement uuid="215c203d-8de5-42c4-a64f-c5af97e973d4" x="0" y="0" width="111" height="20"/>
<textElement/>
<text><![CDATA[Verein]]></text>
</staticText>
<staticText>
<reportElement uuid="6ee6eec2-5139-44fc-856d-94cbd0be7e9a" x="111" y="0" width="111" height="20"/>
<textElement/>
<text><![CDATA[11/12]]></text>
</staticText>
<staticText>
<reportElement uuid="0b89f4f7-0cd0-4326-9b10-569d3386860b" x="222" y="0" width="111" height="20"/>
<textElement/>
<text><![CDATA[10/11]]></text>
</staticText>
<staticText>
<reportElement uuid="4b5b3f24-ad6c-4e1a-befc-0aa2126e29c5" x="333" y="0" width="111" height="20"/>
<textElement/>
<text><![CDATA[09/10]]></text>
</staticText>
<staticText>
<reportElement uuid="895f102a-e381-4348-899d-3141a0b28123" x="444" y="0" width="111" height="20"/>
<textElement/>
<text><![CDATA[08/09]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="20" splitType="Stretch">
<line>
<reportElement uuid="aa17a7d8-4339-401c-a143-39b6f289299d" x="108" y="0" width="1" height="20"/>
</line>
<textField>
<reportElement uuid="125ed9f3-0ee7-4d7a-895a-4bc917fdfdaf" x="0" y="0" width="111" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{Verein}]]></textFieldExpression>
</textField>
<textField>
<reportElement uuid="961d5a0f-c46e-4879-abf7-33dcd67d281f" x="111" y="0" width="111" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{11/12}]]></textFieldExpression>
</textField>
<textField>
<reportElement uuid="4a923094-0df9-482a-94d7-73f0f1638c6c" x="222" y="0" width="111" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{10/11}]]></textFieldExpression>
</textField>
<textField>
<reportElement uuid="aeec58bc-280a-492a-b6e4-7d42b5d7cd94" x="333" y="0" width="111" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{09/10}]]></textFieldExpression>
</textField>
<textField>
<reportElement uuid="8b66b028-f274-413f-a1e6-be0900b40a17" x="444" y="0" width="111" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{08/09}]]></textFieldExpression>
</textField>
</band>
</detail>
<pageFooter>
<band height="31">
<staticText>
<reportElement uuid="a8347c0f-3c4d-42f3-99f7-766255410b0b" x="0" y="0" width="40" height="20"/>
<textElement/>
<text><![CDATA[Nutzer:]]></text>
</staticText>
<textField>
<reportElement uuid="9a8b7e7a-7482-48ff-8e70-f142b8291437" x="40" y="0" width="311" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{APP_USER}]]></textFieldExpression>
</textField>
<textField>
<reportElement uuid="b6a7c7cb-e435-4341-8b6f-976c3bbad57b" x="444" y="0" width="111" height="20"/>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA["Datum: " + $F{CUR_DATE}]]></textFieldExpression>
</textField>
</band>
</pageFooter>
</jasperReport>
$(".apexir_WORKSHEET_DATA").find("[headers='COLUMN_NAME']").hide();
$('#COLUMN_NAME').hide();
Why would you need this?
In case the end user wants to search for a text and doesn't need the corresponding column to be displayed.
/* Property name */
net.sf.jasperreports.export.pdf.javascript
/* Property value */
this.print({bUI: true,bSilent: true,bShrinkToFit: false});
<script>
function refreshPDF(){
var iframe = document.getElementById('region_iframe_pdf');
iframe.src = 'f?p=&APP_ID.:2:&SESSION.:::2::';
};
</script>
<!-- iFrame element which leads to page 2 (including our Jasper Reports Call as BeforeHeader Process) -->
<iframe id="region_iframe_pdf" src="#" style="border:0px #FFFFFF none;" name="iFramePDF" scrolling="no" frameborder="0" marginheight="0px" marginwidth="0px" height="1px" width="1px"></iframe>
Now we only need to call the javascript function to refresh our page 2 (which includes our PDF generation):
javscript:refreshPDF()This could be added as button href link or as a dynamic action.
APEX_UTIL.HTML_PCT_GRAPH_MASK (
p_number IN NUMBER DEFAULT NULL,
p_size IN NUMBER DEFAULT 100,
p_background IN VARCHAR2 DEFAULT NULL,
p_bar_background IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
select
case when FINISHED_IN_PERCENT <= 30
then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','BF381A')
when FINISHED_IN_PERCENT >= 31 and
FINISHED_IN_PERCENT <= 70
then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','EFD13B')
when FINISHED_IN_PERCENT >= 71
then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','7DC24B')
end as chart,
ID
from ORDERS
select apex_view_name, comments
from apex_dictionary
where column_id = 0
order by 1;
select LPAD (' ', (LEVEL - 1) * 2) || apex_view_name s, comments
from (
select 'ROOT' as apex_view_name, null as comments, null as parent_view
from dual
UNION
select apex_view_name, comments, nvl(parent_view,'ROOT') as parent_view
from apex_dictionary
where column_id = 0
)
connect by prior apex_view_name = parent_view
start with parent_view is null
order SIBLINGS by apex_view_name DESC;
Current APEX views in version 4.2| APEX_VIEWS | COMMENTS |
| *ROOT | |
| **APEX_WORKSPACES | Available Application Express (APEX) workspaces |
| ***APEX_WS_APPLICATIONS | |
| ****APEX_WS_DATA_GRID | Websheet Data Grid definition |
| *****APEX_WS_DATA_GRID_COL | Report column definitions for Websheet Data Grid columns |
| ****APEX_WS_APP_PAGES | |
| ***APEX_WORKSPACE_UI_TYPES | The UI Types identify the available user interface types of a workspace |
| ***APEX_WORKSPACE_SQL_SCRIPTS | Identifies SQL Scripts used to execute SQL and PL/SQL commands |
| ***APEX_WORKSPACE_SESSIONS | Application Express (APEX) sessions by workspace and APEX user |
| ***APEX_WORKSPACE_SCHEMAS | Database Schemas mapped to APEX workspaces |
| ***APEX_WORKSPACE_GROUP_USERS | Application Express (APEX) users and groups relationship |
| ***APEX_WORKSPACE_GROUPS | Application Express (APEX) users and groups relationship |
| ***APEX_WORKSPACE_FILES | Identifies uploaded files belonging to the workspace in the modplsql or EPG documents table |
| ***APEX_WORKSPACE_DEVELOPERS | Application Express (APEX) developers, APEX users with privilege to develop applications |
| ***APEX_WORKSPACE_CLICKS | Clicks in Application Express that are tracked by using APEX_UTIL.COUNT_CLICKS |
| ***APEX_WORKSPACE_APEX_USERS | Application Express (APEX) users |
| ***APEX_WORKSPACE_ACTIVITY_LOG | Page view activity log detail. One row is logged for each page view for application with logging enabled. |
| ****APEX_WORKSPACE_LOG_SUMMARY_USR | Page view activity log summarized by user for the last two weeks |
| ****APEX_WORKSPACE_LOG_SUMMARY | Page view activity log summarized by application for the last 14 days |
| ****APEX_WORKSPACE_LOG_ARCHIVE | Page view activity is a daily summary of workspace acitivity that is retained until physically purged |
| ***APEX_WORKSPACE_ACCESS_LOG | One row is logged for each login attempt. |
| ***APEX_TEAM_TODOS | Items that need to get done - i.e. to dos. |
| ***APEX_TEAM_MILESTONES | Identifies bugs, also known as software defects. |
| ***APEX_TEAM_FEEDBACK | Identifies user feedback. |
| ****APEX_TEAM_FEEDBACK_FOLLOWUP | Identifies user feedback followup. |
| ***APEX_TEAM_FEATURES | Items that need to get done - i.e. to dos. |
| ***APEX_TEAM_BUGS | Identifies bugs, also known as software defects. |
| ***APEX_APPLICATIONS | Applications defined in the current workspace or database user. |
| ****APEX_APPL_USER_INTERFACES | |
| ****APEX_APPL_PLUGIN_SETTINGS | Stores the values of custom attribute of scope "application" of a plug-in. |
| ****APEX_APPL_PLUGINS | Stores the meta data for the plug-ins of an application. |
| *****APEX_APPL_PLUGIN_FILES | Stores the files like CSS, images, javascript files, ... of a plug-in. |
| *****APEX_APPL_PLUGIN_EVENTS | Stores which events can be triggered by this plug-in. This events are used for binding dynamic actions. |
| *****APEX_APPL_PLUGIN_ATTRIBUTES | Stores the meta data for the dynamic attributes of a plug-in. |
| ******APEX_APPL_PLUGIN_ATTR_VALUES | Stores the possible values of a plug-in attribute if it's of type selectlist. |
| ****APEX_APPL_LOAD_TABLE_RULES | Identifies a collection of transformation rules that are to be used on the load tables. |
| ****APEX_APPL_LOAD_TABLE_LOOKUPS | Identifies a the collection of key lookups of the data loading tables |
| ****APEX_APPL_LOAD_TABLES | Identifies a named collection of Application Data loding tables which are used to store the metadata information of the data loading tables |
| ****APEX_APPLICATION_WEB_SERVICES | Web Services referenceable from this Application |
| ****APEX_APPLICATION_TREES | Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree |
| ****APEX_APPLICATION_TRANS_REPOS | Repository of translation strings. These are populated from the translation seeding process. |
| ****APEX_APPLICATION_TRANS_MAP | Application Groups defined per workspace. Applications can be associated with an application group. |
| ****APEX_APPLICATION_TRANS_DYNAMIC | Application dynamic translations. These are created in the Translation section of Shared Components, and referenced at runtime via the function APEX_LANG.LANG. |
| ****APEX_APPLICATION_TRANSLATIONS | Identifies message primary language text and translated text |
| ****APEX_APPLICATION_THEMES | Identifies a named collection of Templates |
| *****APEX_APPL_THEME_DISPLAY_POINTS | The Theme Display Points identify the available display points in page and region templates |
| *****APEX_APPLICATION_THEME_STYLES | The Theme Style identifies the CSS file URLs which should be used for a theme |
| *****APEX_APPLICATION_TEMP_REPORT | Identifies the HTML template markup used to render a Report Headings and Rows |
| *****APEX_APPLICATION_TEMP_REGION | Identifies a regions HTML template display attributes |
| *****APEX_APPLICATION_TEMP_POPUPLOV | Identifies the HTML template markup and some functionality of all Popup List of Values controls for this application |
| *****APEX_APPLICATION_TEMP_PAGE | The Page Template which identifies the HTML used to organized and render a page content |
| *****APEX_APPLICATION_TEMP_LIST | Identifies HTML template markup used to render a List with List Elements |
| *****APEX_APPLICATION_TEMP_LABEL | Identifies a Page Item Label HTML template display attributes |
| *****APEX_APPLICATION_TEMP_CALENDAR | Identifies the HTML template markup used to display a Calendar |
| *****APEX_APPLICATION_TEMP_BUTTON | Identifies the HTML template markup used to display a Button |
| *****APEX_APPLICATION_TEMP_BC | Identifies the HTML template markup used to render a Breadcrumb |
| *****APEX_APPLICATION_TEMPLATES | Identifies reference counts for templates of all types |
| ****APEX_APPLICATION_TABS | Identifies a set of tabs collected into tab sets which are associated with a Standard Tab Entry |
| ****APEX_APPLICATION_SUPP_OBJECTS | Identifies the Supporting Object installation messages |
| *****APEX_APPLICATION_SUPP_OBJ_SCR | Identifies the Supporting Object installation SQL Scripts |
| *****APEX_APPLICATION_SUPP_OBJ_CHCK | Identifies the Supporting Object pre-installation checks to ensure the database is compatible with the objects to be installed |
| *****APEX_APPLICATION_SUPP_OBJ_BOPT | Identifies the Application Build Options that will be exposed to the Supporting Object installation |
| ****APEX_APPLICATION_SUBSTITUTIONS | Application level definitions of substitution strings. |
| ****APEX_APPLICATION_SHORTCUTS | Identifies Application Shortcuts which can be referenced "MY_SHORTCUT" syntax |
| ****APEX_APPLICATION_PROCESSES | Identifies Application Processes which can run for every page, on login or upon demand |
| ****APEX_APPLICATION_PARENT_TABS | Identifies a collection of tabs called a Tab Set. Each tab is part of a tab set and can be current for one or more pages. Each tab can also have a corresponding Parent Tab if two levels of Tabs are defined. |
| ****APEX_APPLICATION_PAGES | A Page definition is the basic building block of page. Page components including regions, items, buttons, computations, branches, validations, and processes further define the definition of a page. |
| *****APEX_APPLICATION_PAGE_VAL | Identifies Validations associated with an Application Page |
| *****APEX_APPLICATION_PAGE_REGIONS | Identifies a content container associated with a Page and displayed within a position defined by the Page Template |
| ******APEX_APPLICATION_PAGE_TREES | Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree |
| ******APEX_APPLICATION_PAGE_RPT | Printing attributes for regions that are reports |
| *******APEX_APPLICATION_PAGE_RPT_COLS | Report column definitions used for report regions |
| ******APEX_APPLICATION_PAGE_ITEMS | Identifies Page Items which are used to render HTML form content. Items automatically maintain session state which can be accessed using bind variables or substitution stings. |
| ******APEX_APPLICATION_PAGE_FLASH_CH | Identifies a Flash chart associated with a Page and Region |
| *******APEX_APPLICATION_PAGE_FLASH_S | Identifies the Flash chart series which comprise a Flash chart |
| ******APEX_APPLICATION_PAGE_FLASH5 | Identifies a Flash chart 5 chart associated with a Page and Region |
| *******APEX_APPLICATION_PAGE_FLASH5_S | Identifies the Flash chart series which comprise a Flash chart |
| ******APEX_APPLICATION_PAGE_BUTTONS | Identifies buttons associated with a Page and Region |
| *****APEX_APPLICATION_PAGE_PROC | Identifies SQL or PL/SQL processing associated with a page |
| *****APEX_APPLICATION_PAGE_MAP | Identifies the full breadcrumb path for each page with a breadcrumb entry |
| *****APEX_APPLICATION_PAGE_GROUPS | Identifies page groups |
| *****APEX_APPLICATION_PAGE_DB_ITEMS | Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view. |
| *****APEX_APPLICATION_PAGE_DA | Identifies Dynamic Actions associated with a Page |
| ******APEX_APPLICATION_PAGE_DA_ACTS | Identifies the Actions of a Dynamic Action associated with a Page |
| *****APEX_APPLICATION_PAGE_COMP | Identifies the computation of Item Session State |
| *****APEX_APPLICATION_PAGE_BRANCHES | Identifies branch processing associated with a page. A branch is a directive to navigate to a page or URL which is run at the conclusion of page accept processing. |
| ****APEX_APPLICATION_NAV_BAR | Identifies navigation bar entries displayed on pages that use a Page Template that include a #NAVIGATION_BAR# substitution string |
| ****APEX_APPLICATION_LOVS | Identifies a shared list of values that can be referenced by a Page Item or Report Column |
| *****APEX_APPLICATION_LOV_ENTRIES | Identifies the List of Values Entries which comprise a shared List of Values |
| ****APEX_APPLICATION_LISTS | Identifies a named collection of Application List Entries which can be included on any page using a region of type List. Display attributes are controlled using a List Template. |
| *****APEX_APPLICATION_LIST_ENTRIES | Identifies the List Entries which define a List. List Entries can be hierarchical or flat. |
| ****APEX_APPLICATION_ITEMS | Identifies Application Items used to maintain session state that are not associated with a page |
| ****APEX_APPLICATION_GROUPS | Application Groups defined per workspace. Applications can be associated with an application group. |
| ****APEX_APPLICATION_COMPUTATIONS | Identifies Application Computations which can run for every page or on login |
| ****APEX_APPLICATION_CACHING | Applications defined in the current workspace or database user. |
| ****APEX_APPLICATION_BUILD_OPTIONS | Identifies Build Options available to an application |
| ****APEX_APPLICATION_BREADCRUMBS | Identifies the definition of a collection of Breadcrumb Entries which are used to identify a page Hierarchy |
| ****APEX_APPLICATION_BC_ENTRIES | Identifies Breadcrumb Entries which map to a Page and identify a pages parent |
| ****APEX_APPLICATION_AUTHORIZATION | Identifies Authorization Schemes which can be applied at the application, page or component level |
| ****APEX_APPLICATION_AUTH | Identifies the available Authentication Schemes defined for an Application |
| ****APEX_APPLICATION_ALL_AUTH | All authorization schemes for all components by Application |
| **APEX_UI_DEFAULTS_TABLES | The User Interface Defaults for the tables within this schema. Used by the wizards when generating applications. |
| **APEX_UI_DEFAULTS_LOV_DATA | If you create a form, report, or tabular form that includes this column and if the appropriate Display As Type is set to use a list of values (Radio Group or Select List) then a Named List of Values will be created within the application and will be referenced by the resulting item or report column. |
| **APEX_UI_DEFAULTS_GROUPS | The User Interface Defaults for the groups within the tables in this schema. Used by the wizards when generating applications. |
| **APEX_UI_DEFAULTS_COLUMNS | The User Interface Defaults for the columns within this schema. |
| **APEX_UI_DEFAULTS_ATTR_DICT | The Attribute Dictionary is specific to a workspace. It is part of User Interface Defaults and can be used in report and form creation. |
| **APEX_THEMES | List of APEX built-in, public and workspace themes |
-- Validation of Type: Function Returning Error Text
DECLARE
l_facility_ids varchar2(32000);
l_facility_bez varchar2(200);
l_error VARCHAR2 (4000);
BEGIN
FOR i IN 1 .. apex_application.g_f07.COUNT -- select list with facilities
LOOP
IF instr(':'||l_facility_ids||':',':'||apex_application.g_f07(i)||':')>0
THEN
select description into l_facility_bez from facilities where id = apex_application.g_f07(i);
l_error :=
l_error
|| '</br>'
|| 'Row '
|| i
|| ': facility"' || l_facility_bez ||'" is already selected.';
END IF;
l_facility_ids := l_facility_ids ||':'|| apex_application.g_f07(i);
END LOOP;
RETURN LTRIM (l_error, '</br>');
END;
Is there an workaround for question two available?
-- All tables inside my user select table_name, comments from user_tab_comments where table_name = :MY_TABLE; -- All tables for all users: select table_name, comments from all_tab_comments where owner = :MY_USER and table_name = :MY_TABLE;To select the column comments use this select: (idea comes from bdelmee)
select TABLE_NAME,
K.COLUMN_ID, COLUMN_NAME,
K.NULLABLE, K.DATA_TYPE ||
case when K.DATA_SCALE is not null then '(' || K.DATA_PRECISION || ',' || K.DATA_SCALE || ')'
when K.DATA_PRECISION is not null then '(' || K.DATA_PRECISION || ')'
when K.DATA_LENGTH is not null and K.DATA_TYPE like '%CHAR%' then '(' || K.DATA_LENGTH || ')'
end DATA_TYPE,
C.COMMENTS
from user_col_comments C join user_tab_cols K
using(TABLE_NAME,COLUMN_NAME)
where table_name = :MY_TABLE
order by TABLE_NAME, K.COLUMN_ID;
Now we join both selects together and get all information we need:
select
decode(column_id,0,TABLE_NAME,null) as TABLE_NAME,
decode(column_id,0,null,column_id) as COLUMN_ID,
COLUMN_NAME, NULLABLE, DATA_TYPE, COMMENTS
from (
select cc.TABLE_NAME,
tc.COLUMN_ID, tc.COLUMN_NAME,
tc.NULLABLE, tc.DATA_TYPE ||
case when tc.DATA_SCALE is not null then '(' || tc.DATA_PRECISION || ',' || tc.DATA_SCALE || ')'
when tc.DATA_PRECISION is not null then '(' || tc.DATA_PRECISION || ')'
when tc.DATA_LENGTH is not null and tc.DATA_TYPE like '%CHAR%' then '(' || tc.DATA_LENGTH || ')'
end DATA_TYPE,
cc.COMMENTS
from user_col_comments cc
INNER JOIN user_tab_cols tc ON (cc.TABLE_NAME = tc.TABLE_NAME and cc.TABLE_NAME = tc.TABLE_NAME and cc.COLUMN_NAME = tc.COLUMN_NAME)
UNION
select tab.table_name as TABLE_NAME,
0 as COLUMN_ID, '' as COLUMN_NAME,
'' as NULLABLE, '' as DATA_TYPE,
tab.comments as COMMENTS
from user_tab_comments tab
)
where table_name = UPPER(:MY_TABLE)
order by table_name, column_id ;
That's it.
declare
v_text varchar2(16000) := :P1_TEXT;
v_text_cnt number;
v_text_err varchar2(1000);
begin
select count(*) into v_text_cnt from dual
where
REGEXP_LIKE (v_text,'^[[:alpha:] .,-:;!?[:digit:]]+$');
/* List of allowed characters */
if v_text_cnt = 0 then
select regexp_replace (v_text,'[[:alpha:] .,-:;!?[:digit:]]+'
) invalid_characters into v_text_err
from dual;
return 'Error. The following characters are not allowed: ' || v_text_err;
end if;
end;
/* 1. Standard sub query */ UPDATE tbl_employees x SET department = ( SELECT y.name FROM tbl_department y WHERE y.dept_id = x.dept_id); /* 2. Sub query with exist clause */ UPDATE tbl_employees x SET department = ( SELECT y.name FROM tbl_department WHERE y.dept_id = x.dept_id) WHERE EXISTS ( SELECT y.name FROM tbl_department y WHERE y.dept_id = x.dept_id); /* 3. Updatable view (needs foreign key connection) */ UPDATE ( SELECT x.department AS old_val, y.name AS new_val FROM tbl_employees x, tbl_department y WHERE y.dept_id = x.dept_id) SET old_val = new_val; /* 4. Update trough merge statement */ MERGE INTO tbl_employees x USING (SELECT name, dept_id FROM tbl_department y ) y ON (y.dept_id = x.dept_id) WHEN matched THEN UPDATE SET x.department = y.name;
<script>
$('#labelcloud_plugin_MYCLOUD').find('span:odd').css('font-weight','bold');
$('#labelcloud_plugin_MYCLOUD').find('span').css('padding','2px');
</script>
begin
owa_util.mime_header('text/html', FALSE);
owa_cookie.send(
name=>'APEX_AT_WORK_COOKIE',
value=>'1',
expires => sysdate + 30);
apex_application.g_unrecoverable_error := true;
owa_util.redirect_url('f?p=&APP_ID.:12:&SESSION.');
exception when others then null;
end;
Update code from Patrick (for more information check the comments):
begin
owa_util.mime_header('text/html', FALSE);
owa_cookie.send(
name => 'APEX_AT_WORK_COOKIE',
value => '1',
expires => sysdate + 30 );
apex_util.redirect_url (
p_url => 'f?p=&APP_ID.:1:' || :SESSION,
p_reset_htp_buffer => false );
end;
The condition checks for the cookie and reads the value. If it is NULL then the region will be displayed.
-- Type: PL/SQL Function Body Returning a Boolean
DECLARE
cookie OWA_COOKIE.cookie;
BEGIN
cookie := OWA_COOKIE.get('APEX_AT_WORK_COOKIE');
if cookie.vals.First IS NULL then
return true;
else
return false;
end if;
END;
Documentation:
v_text = $('#P1_MY_ITEM').val();
v_regex = /^[a-zA-Z0-9_ .,-:;!?&\(\)"\t\r\n]+$/;
/* only number: /^[0-9]+$/; */
/* only character: /^[a-zA-Z]+$/; */
if(!v_regex.test(v_text)){
alert('ERROR');
}else{
alert('OK');
}
Important links to the topic:
select
owa_util.get_cgi_env('REMOTE_ADDR')
from dual;
select
sys_context('userenv','ip_address')
from dual;
Das Ganze funktioniert super solange Sie keinen vorgeschalteten Proxy Server verwenden! Aber auch dafür gibt es einen Workaround. Stellen Sie folgende Einstellung in Ihrer http.conf ein:
RewriteCond %{REMOTE_ADDR} (.*)
RewriteRule .* - [E=REMOTEA:%1]
RequestHeader set X-Oracle-Cache-User "%{REMOTEA}e"
# Außerdem müssen folgende Einstellung mit gesetzt werden:
# 1. Konfiguration der RewriteRule muss in einem VirtualHost hinterlegt sein:
<VirtualHost 192.168.0.1:80>
# 2. Rewrite muss aktiviert werden
RewriteEngine on
# 3. ModHeader muss mit geladen werden
LoadModule headers_module modules/mod_headers.so
Die Lösung wurde von Dietmar Aust im Oracle APEX Forum auch noch tief greifender beschrieben:
https://forums.oracle.com/forums/thread.jspa?messageID=1262928�
select
owa_util.get_cgi_env('HTTP_X_ORACLE_CACHE_USER')
from dual;
Info: Es kann vorkommen das andere Einstellungen verhindern das die Variable richtig durchgeroutet wird.