SQL & PL/SQL :: Procedure Containing Select Statement To Fill Page Items

Oct 24, 2011

I would like to fill items :P200_A and :P200_B and so on with the result of a SELECT which depends on the different values of many select lists.

E.G. :P200_list_alpha with the list of values
STATIC:less than 10;less,equal than 10;equal,above 10;above,indifferent;indiff

:P200_list_beta with the list of values
STATIC:active;active,passiv;passiv,excluded;excluded

How do I write the select statement ? I think it has to be executed in an anonymous PLSQL Procedure (after submit).

What is a convenient way to write the select statement ? I could imagine to use lots of IF , ELSIF, ELSE statements and in each branch (here 12 ) the whole/complet SELECT statement is written.

How to solve this problem in an elegant way ? In my opinion the CASE statement could be useful, but how to use it in the WHERE clause with this nested conditions ?

View 1 Replies


ADVERTISEMENT

Application Express :: On Button Click To Fill Out Form On Same Page As Report

Jun 27, 2012

I have a report that contains a name that I want to pass in to a field in a form on a different region on the same page. My button located on the report is has its own column called ADD_LINK. Here is the button.

<button id="apexir_btn_ADD" class="apexir-button" type="button" value="Add" onclick=""><span>Add</span></button>

Once that button is clicked, I have the form region fade in. The region is called Add Tag to Employee, which has P4_ROWID, P4_EMPLOYEE_ID, and P4_TAG_ID. Once the region is displayed, I want the #NAME# to show in the P4_EMPLOYEE_ID.

I did copy this form from another page, where I had it working before. It made more sense to move it to this page for interface purposes, so maybe that tidbit will work as well.

View 15 Replies View Related

SQL & PL/SQL :: Using Optional Parameters To Fill Update Statement?

Jul 17, 2011

I need to use funtion optional parameters to construct an update statement in the funtion body. I have two scenarios, either both parameters are not null or the 2nd is null. Do I have to use

IF
(param2 is null)
THEN
udpate using param1 only
ELSE
update using both param1 and param2
END IF;
or is there a shortcut to do this?

View 15 Replies View Related

SQL & PL/SQL :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related

SQL & PL/SQL :: How To Make Procedure From Select Statement

Feb 8, 2011

i do have below query in oracle db just joining two views,

select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",

[Code] ........


how can i make a procedure with below two steps,

1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step

so in the final expecting result is

execute myprocnamehere -- will result same as running above query

View 3 Replies View Related

Store Procedure In View With Select Statement

Aug 31, 2012

Need a trigger in view with select statement that means

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;

i wanted to use select statement instead of delete.How can i get that

View 1 Replies View Related

Forms :: Items On Tab Page Getting Invisible?

Oct 12, 2010

when i am selecting a record from a LOV which is associated with a button on a tab page of a tabbed canvas; all the items on the tab page getting invisible. But when i navigate to another tab page and then come back to the former tab page all items reappearing.

What could possibly be the reason for this and how to restrict items from disappearing?

View 4 Replies View Related

Application Express :: Bug While Copying Items On A Page?

Oct 4, 2012

When I create a new page item via copying from existing page item it fails with the below error code, if there is > or < character in the source of the existing item.Error Source contains <, > or " which are invalid characters. It works fine if I create new item using wizard and then have the same source. I use div tags in the source of my display only items.

View 3 Replies View Related

Application Express :: 4.2 - 200 Page Items Limit

Jan 8, 2013

I have a master-detail form which was getting close to the 100 items per page limit in Apex 4.0 which I then scaled back and got to work properly. I had thought that I could start adding more columns to the detail part of the form since 4.2 was supposed to have increased that limit. I am finding that this does not seem to be true at least in the case of this particular form. I tried increasing and started getting 404 errors when trying to save data--same symptom I had had with 4.0. I am back nearly to the same # of columns I started with--and nowhere near the 200 limit. Columns are month by month projection data that I had hoped to extend with the increased page item limit.

I missing something required to take advantage of larger page items limit? Do I need to use one of the new templates?

View 4 Replies View Related

Application Express :: How To Print Page With Report And Some Items

Oct 28, 2013

db11gxe , apex 4.0 , firefox 24 , I have a page with a report and two items , and i want to print the whole page (the report with the items) , can apex do so ?

View 1 Replies View Related

Application Express :: Clear Page Cache - All Except Specific Items?

Feb 21, 2013

APEX 4.2.1 On a page with dozens of editable page items, when branching back to the same page on-submit, is there a way to clear cache for all items except a few (key) items? The branch attributes (via the f?p= syntax) support clearing cache for the entire page and setting specific item/value pairs but no support for exclusions.

View 2 Replies View Related

Application Express :: Dynamically Hide Show Page Items

Dec 14, 2012

Im tring to dynamically hide/show page item. I searched and found something but it not working for me. Im using apex 4.1 and below described are work I did.

In my page (form) I have 5 fileds iitems.

- Customer No
- Edit
- First Name
- Last Name
- Address

I want to hide First Name, Last Name, Address based on the value in Edit. Let say if user selects "Edit First Name" then First Name must get visible while Last Name & Address hidden.

So I placed this line onChange="$f_Hide_On_Value_Item(this,'LASTNAME,'LN')" in page item EDIT> Label > HTML Table Cell Attributes

But it not working for me? Where am I go wrong?

View 2 Replies View Related

Application Express :: How To Fetch Tabular Form Column Value Into Page Items

Sep 7, 2012

I have created tabular form. I have set LOV for one column. Here my requirement is when i select the value from lov then it will fetch that value into page item.

I am using apex 4.1

View 12 Replies View Related

Application Express :: Page Items Values Don't Get Submitted In Add_member Collection 4.2

Jun 10, 2013

The values in the page item dont get submitted hence generate errors in my application , in apex 4.2 using collectionEg.

apex_collection.add_member(p_collection_name => 'ORDER',     p_c001 => :P56_product_id,     p_c002 => :P56_product_name,    p_c003 => :P56_list_price,    p_c004 => 1);  

Can there be a reason why ?

View 4 Replies View Related

Application Express :: How To Align Items In A Form Page With Respect To Regions

Oct 16, 2013

I have 20 item fields in same form. i need to align them in sequential manner. For example x  y   z  abcl  m  o p   q   r   s   xyzabclmopqrs  are the items to achieve this what i have to do...

View 2 Replies View Related

Application Express :: Default Value For Page Items Doesn't Get Set When Session Is Cleared?

Nov 15, 2012

I am facing this problem, not sure how to solve this. Basically, the default value of the "items" on the page don't get set when the session is cleared and I visit the page for the first time.

So when I check the session for the first time, all page items are null. But when I revisit the page, the page items gets set sometimes.

View 6 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

Forms :: How To Bind Hierarchy Tree Items To Text And Other Items

Jan 31, 2012

I have already done a hierarchy tree. Now I'd like to make it bind to text items. When i click a node, it should automatically be bound to text items.

My tree is about sections of students, now when i click a specific section to view its corresponding subjects it would be displayed on text items.

For example:

+Sections
+First Semester
+LM2
+LM3
+Second Semester
+LM4
+LM5

When i would click on LM2 node, its subjects should appear immediately.

View 1 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

Application Express :: Unable To See Items Of Detail Form In Master Detail Form In Page Details?

Oct 31, 2013

I created a master-detail form using wizard in oracle apex 4.2 Now I want to attach an LOV to one of the items of detail form, which is visible on the same window as of master form. To do so, I need to first find the item in detail page, details of which are not available in page definitions. I can see all the items of master form but none for detail form in "Page Rendering" section. 

View 0 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

Application Express :: How To Get Cascading With Select List Page Item

Feb 22, 2013

Technical description:

Oracle apex version 4.1.0
Oracle Db 11g

how to set Value to select list page item based on the return value of other select list page item(before header process i need to include or need to write a process)

Example:

Select country: [This is my first select list ]

Select State: [ This is my second select list, which i want to set in before header process based on the first select list]

View 1 Replies View Related

Application Express :: Select List Default Value On Page Load

Aug 5, 2013

Basically I have a report that as part of it's source sql takes in an element from a select list. 

E.G. SELECT * FROM table_name WHERE table_column = : P1_SELECTLIST 

This works fine when the user selects an option from the select list however the first time the page runs it always returns data not found. The options correctly appear in the select list as they should without any nulls although it seems the select list is still initializing as null. I have been told to try and add a computation before the header on the select list item, although I'm not sure if this would work/how to do this.?

View 4 Replies View Related

Performance Tuning :: Calling Stored Procedure In Jsp Page

Feb 16, 2011

We are calling a stored procedure in jsp page. The sql where bottleneck was assumed is giving better performance in sql plus (0.093 seconds) but when called from jsp its taking too much time.

Here is the plan for the sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 |
| 1 | SORT GROUP BY | | 1 | 219 |
| 2 | NESTED LOOPS | | 1 | 219 |
| 3 | NESTED LOOPS | | 1 | 197 |
| 4 | NESTED LOOPS | | 1 | 162 |
| 5 | MERGE JOIN | | 1 | 127 |
| 6 | TABLE ACCESS BY INDEX ROWID | policy_tbl | 1 | 78 |
| 7 | INDEX FULL SCAN | pk_pol_id | 1 | |
| 8 | SORT JOIN | | 1585 | 77665 |
| 9 | TABLE ACCESS BY INDEX ROWID| policy_p_tbl | 1585 | 77665 |
| 10 | INDEX RANGE SCAN | pp_param_1 | 1679 | |
| 11 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 12 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 13 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 14 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 15 | TABLE ACCESS BY INDEX ROWID | policy_dtl_tbl | 1 | 22 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 16 | INDEX UNIQUE SCAN | pk_pg | 1 | |
--------------------------------------------------------------------------------

View 2 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Application Express :: Assigning Value To Select List Only First Time Page Loads

Jan 3, 2013

I am trying to set the intial value of "Select List" to APP_USER the only first time when the page loads and use it to filter the report, any other time once the page is loaded the user should select another value from the Select List then click the submit button to display the details in the report, i tried to achieve this by setting "select List" creating LOV for Select List then set default=APP_USER but now the problem any time you click the submit button it reset the "Select List" from selected value back to default so how can you achieve this to set the value of Select List only the first time the page loads?

View 1 Replies View Related

How To Right Justify And Zero Fill In Oracle

Dec 9, 2008

How to right justify and zero fill in oracle ?

as example input data is '1' and i want to insert '0' in front of the input data and the output will be 01 .

View 2 Replies View Related

SQL & PL/SQL :: Assigning ID (Fill SID Column)

Feb 4, 2013

I have a table with 200k entries, which has ~12k unique combination of columns that I wish to assign an ID to. My real world problem is with addresses and street components (of which nulls are allowed); but this should be a reasonable example.

DROP TABLE tt;

CREATE TABLE tt AS
SELECT ROWNUM id,
CASE
WHEN MOD(ROWNUM, 107) = 0 THEN NULL
ELSE MOD(ROWNUM, 101)
END
c1,
CASE

[Code]...

Now I have 3 ways to assigned an ID (fill the SID column).

The first is congruent with the method most commonly used, but seems especially slow in this case. I think this is due to the COALESCE/TO_CHAR usage. Can this be optimized?

DECLARE
CURSOR c1 IS
SELECT a.*, sid_seq.NEXTVAL sid
FROM (SELECT DISTINCT c1, c2 FROM tt) a;
BEGIN
FOR s IN c1 LOOP
UPDATE tt d

[Code]...

The second I've made relies on the usage of a temp table, which I really don't like, but it is faster; so that is something at least.

CREATE TABLE ttt AS
SELECT a.*, sid_seq.NEXTVAL sid
FROM (SELECT DISTINCT c1, c2 FROM tt) a;

[Code]...

The third discards the sequence, and uses dense_rank. This allows us to use the ID (which might have an index, whereas c1 and c2 will not).

MERGE INTO tt d
USING (SELECT id, (SELECT COALESCE(MAX(sid), 317) FROM tt) + DENSE_RANK() OVER (ORDER BY c1, c2) sid FROM tt) s
ON (d.id = s.id)
WHEN MATCHED THEN
UPDATE SET d.sid = s.sid

The ideal solution would use the SEQUENCE, not use a temporary table and match/merge/update based on ID, and be 'fast'; but I cannot conceive of such a solution; does one exist? What would you do?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved