Oracle Apex, Forms, Reports: May 2017

SET_BLOCK_PROPERTY DEFAULT_WHERE property

Advance Default Where

I use the oralce forms builder 10g version.

I want to get the display records according to the criteria of research as

Assume that DESCRIPTION field "NATURE %" when I click the search the block button to display the record whose DESCRIPTION starts with "NATURE CATS," NATURE DOGS etc...

I used

SET_BLOCK_PROPERTY ('BLOCK_NAME ', DEFAULT_WHERE,' where as DESCRIPTION: QF_VALUE');

It gives me error DESCRIPTION is not a valid identifier.

Because BLOCK NAME DESCRIPTION article DATABASE NON-base.

I have description data in another table, where I can join the 2 tables.

Please let me how can know I get the required data.

Thank you.

Set the block based on the code description code

I guess, you have a description code in your BLOCK_NAME building that is based on your description table, then try as follows

SET_BLOCK_PROPERTY ('BLOCK_NAME', DEFAULT_WHERE, ' description_code IN (SELECT description_code ' |) ")

'OF description_table' |

"Description WHERE AS % | : QF_VALUE | » %');

Tags: Oracle Development

Similar Questions

Pré-requête default set_block_property where

I need some information about
SET_BLOCK_PROPERTY, DEFAULT_WHERE
How it works

I use this code

SET_BLOCK_PROPERTY('bank_recon',

DEFAULT_WHERE, 'v_bankcode= ''' || :button.bankcode || ''' and ' || 'v_chq= ''' || :button.chq_no ||

'''');

but when I add another condition not working

SET_BLOCK_PROPERTY('bank_recon',

DEFAULT_WHERE, 'v_bankcode= ''' || :button.bankcode || ''' and ' || 'v_chq= ''' || :button.chq_no ||

''' and ' || 'recon is null'||

'''');

Please guide me and inform me of how this is working... how to use his *' ' *.

Try

DECLARE

v_sql VARCHAR2(300);

BEGIN

v_sql := 'v_bankcode= ' || '''' || :button.bankcode || '''' || ' and v_chq= ' || :button.chq_no || ' and recon is null';

SET_BLOCK_PROPERTY('bank_recon', DEFAULT_WHERE, v_sql);

END;

If you have doubts about the syntax, you can check it by displaying a message like

message('sql=' || v_sql);PAUSE;

Set_block_property with as and between and function

HelloW All

I need to find record where column name between

: Text_item1 and: Text_item2 with Default_where

For example Manu guide me to find record with

SET_BLOCK_PROPERTY ('Block53 ', DEFAULT_WHERE,' name as "' |: block70.text |) '%''');
go_block ('block53');
EXECUTE_QUERY;

Now I need

To find our folder including the name: block70. Text1 and: Block70.text2 with as a function

For example

I have a column: NAME

I want to know registration of name of column by default when the option where name between Text1 and Text2

I hope you guys understand my requirement

Concerning

Wasim Ismail

Hai,

Try

SET_BLOCK_PROPERTY('', DEFAULT_WHERE, 'ID LIKE ''' || :.CODE || '%'' AND NAME LIKE ''' || :.EMP_NAME || '%''');

GO_BLOCK('');

EXECUTE_QUERY;

Kind regards

Manu.

If this answer is useful or appropriate, please mark. Thank you.

List: error record must be entered and deleted everything first

Hi all
I created a form in this form, I used the data block.
in this data block, I chose option multiple records.
It shows several text fields, its ok
and I created a new block of data to a button
This button, I wrote a code pl/sql trigger when click
Here is code

declare

cursor test is

select location_id,street_address from locations where country_id ='US';

begin

for i in test loop

:LOCATIONS.LOCATION_ID:=i.location_id;

:LOCATIONS.STREET_ADDRESS:=i.street_address;

next_record;

end loop;

end;

This code works very well with the trigger a time new form instance

but when-click of the mouse, it gives an erros

Record must be seized and remove the first

how to solve?

Set your where condition for the datablock in the db--> Property Palette block-> Where Clause

or set programmatically using Set_Block_Property (, DEFAULT_WHERE,) in your motion trigger.

Number of documents

Hi all

I use form builder 6i, I have a browser tab in my form with a block and a lot of check box (5) to browse the criteria that may be verified and concatenated with | 'and' in a variable (condition) to make a (value) for the (Default_Where) property of the block.

As you know, I can't make a statement like this:

Select count (*) in count from MyTable where condition;

It causes an exception because select needs to report to the State column names in where would adopt, but I can just use the condition accumulated in the manner below:

set_block_property ('MyBLOCK', default_where, condition);

I used it to run the query, and it works well... but the problem is I want to know the number of records returned by the query in order to display it in a display of the browser element.

So... what should I do? Any clue?

Thank you

Damien

You can use a column of SYNTHESIS in a CONTROL block to count the records in the data block.

You will need to set the property "Single Document" command block Yes and the record-breaking 'Query' property data block Yes.

Problem with the join relationship condition master / detail

Hello
I need to join with master detail such as if there is no trace of the foreign key in detail to the primary master key, the foreign key must be attached to another column based on certain conditions in the master. For example:

Control columns: pk1, rk1, rk2
Detail of the columns: fk1, a, b, c

I created a relationship with detail with the join condition, pk1 = fk1 in master.
But according to value rk1, I actually join fk1 to rk2 in master and if the condition fails then join the pk1. I can't put this condition in the relationship that the form does not accept complex join conditions.

I hope that my example has not confused everyone.

Can someone help me to achieve this goal.

TIA

TIA,
I agree with DM, rather than using the RELATIONSHIP between the blocks that I would create logic to manually set the DEFAULT_WHERE clause on the block of RETAIL with the criteria you need to MASTER block. This can be a little difficult if you want to form work the same way if you had defined a relationship because you will have to create all the triggers event coordination. The most difficult part is to choose the right triggers to coordinate the synchronization when a user accesses a folder of master to another. For just the basics (the block of retail is questioned when the user navigates to the retail block), you could do the following:
1. create a When-new-folder-Instance trigger on the Master block to capture the relevant details to query the block detail and define the DEFAULT_WHERE.
2 create a trigger in a times - news - block - Instance on the block of retail to run the query on the block of retail and display the details records.

For example:

/* Sample When-New-Record-Instance trigger */

DECLARE

v_temp_where VARCHAR2(500); /* make larger if needed. */

BEGIN

IF ( :MASTER_BLOCK.First_Item IS NOT NULL ) THEN

/* this is needed for when the user navigates to a NEW record in the MASTER Block. */

/* Add your conditional logic here to determine how to build your WHERE clause */

IF ( RK1 = 'Dependent Values' ) THEN

v_temp_where := 'PK1 = FK1';

ELSE

v_temp_where := 'RK2 = FK1';

END IF;

Set_Block_Property('DETAIL_BLOCK',ONETIME_WHERE,v_temp_where);

/* If you are using Forms 6i, the ONETIME_WHERE property is not available so you will have to */

/* use the DEFAULT_WHERE property instead. */

END IF;

END;

/* Sample When-New-Block-Instance trigger

BEGIN

/* This sample code assumes the user has not navigated to a New Record */

/* so there will always be a NOT NULL default where assigned to the DETAIL BLOCK.*/

Clear_Block(ASK_COMMIT); /* just in case the user made changes... */\

Execute_Query;

END;

Please understand it is the EXAMPLE of Code and is intended to be a starting point for you - not the complete solution.

I hope this helps.
Craig...

If a response is appropriate or useful, please mark accordingly

Oracle error: How do you change forms Datablock data type of query?

Hey Experts.

I am a newbie in Oracle Forms.

I have a problem regarding the block of data in database.

I need to change the data by NO block due to the overflow of information in our database.

Question is how/where can I start to change/modify the codes?

Thanks in advance!

Bellerose

Baski101 wrote:

...

I deleted the relationship between the blocks and the module is now running. I want to ask you the questions and following...

1. What does the relationship your datablock/s? It runs now, but I want to know why.

2 How/where you start updating of the DEFAULT_WHERE clause? I mean, I have to create another program unit and ignore the 'old'? Change the units of the old program? Where and what I remove/add?

...

Question 1: A relationship only applies to "Base table data blocks." Relationships allow synchronizations between master and detail of data blocks. Basically, this means that when you select a record in the block Master, the retail block will automatically display folders related to the selected record of the master. In earlier versions of Oracle Forms, the developer had to manually perform this synchronization.

Question 2: Connection to block non-database - you do not use the DEFAULT_WHERE property. For blocks of the Base Table, it depends on your needs. For example, if your form has a search function and shows you a canvas "before" that allows the user to enter criteria for the search, and the user then had to click a button to perform the search, you would put your code in triggering When button button. If your username is simply the form view application [for example: Forms 6i and earlier - F7 (enter query) or F8 (run the query) and Forms 9i or higher - F11 (enter query) or Ctrl + F11 (run the query)] then there is no need to change the DEFAULT_WHERE because the forms accomplishes this task for you. Another example would be - if you have a block of the base table, but the user has the ability to use the elements in array to another basic search - you can use the motion of a block of the base table to change the selected property DEFAULT_WHERE to use the elements of the other base table to filter your data.

I hope this helps.

Craig...

Ask a form by date.

Hello. I use oracle form builder to make a form where I can ask employees tickets (tickets they sweep through bar codes). I have a text element where I enter data and then the query runs using a query pre trigger to display the corresponding fields only.

However, I am not able to do to date. When I insert a date in my text object and click on the button Search, which has the trigger of the query, it returns no records found but one.

I tried many ways, but I'm going with unhandle trigger error or display all dates in a table below which I don't want to because I do a search by date or he said no record found.

Help me solve this problem. Thank you.

I had the same problem.

Just use SET_BLOCK_PROPERTY in a query previously trigger.

DECLARE
the_sql varchar2 (300);
BEGIN
v_sql: = ' trunc (date_created) = "' | to_char(:Control.search_val,'dd-mon-rrrr') | " ' ;

SET_BLOCK_PROPERTY ('', default_where, v_sql);
message ("sql ='|") v_sql); -to check the syntax of the place where clause.
END;

That should do the job :)

view records by month

Hi all.

I want to display records according to month.
I did see something like that.

create or replace view month_timer (item1, amount,item_date) as

select item1,sum(amount),item_date

from table

where trunc(item_date) between to_date('01-jan-2010') and trunc(sysdate)

AND item_TYPE!=4

group by item1,item_date

ORDER BY item_date

any suggestions?

Sarah

Sarah,

Try the following.

(1) declare a global variable in the WHEN_NEW_FORM_INSTANCE and set the value to * 1 * to that.
(2) WHEN TIMER-EXPIRED trigger, write,

GO_BLOCK('');

SET_BLOCK_PROPERTY('', DEFAULT_WHERE, 'TO_NUMBER(TO_CHAR(, ''MM'')) = ' || TO_NUMBER(:GLOBAL.));

EXECUTE_QUERY;

:GLOBAL. := TO_NUMBER(:GLOBAL.) + 1;

IF TO_NUMBER(:GLOBAL.) = 13 THEN

:GLOBAL. := 1;

END IF;

I hope this helps.

Kind regards

Manu.

If my answer or the answer to another person has been useful or appropriate, please mark accordingly

Find a record

HelloW All

I create a 2 Block with the same Table

Ist block containing 2 columns with Tablour Style and 15 time Record ist

Id_detail and Name_detail

2nd block contain 2 column and a button with the Style of form

ID and Name
The button name is OK

Now I need when I run form and enter the Id and press ok its find all those who register as id

For example

ID = 010

Id_detail
010001
010002
010003
010004
010005

And as on all record comes in the ID_detail column

I hope that you guys understand

Concerning

Therese

Hai,

Try

SET_BLOCK_PROPERTY('', DEFAULT_WHERE, 'ID_DETAIL LIKE ''' || :.ID || '%''');

GO_BLOCK('');

EXECUTE_QUERY;

Kind regards

Manu.

If this answer is useful or appropriate, please mark. Thank you.

Published by: Manu. July 2, 2009 15:19

How to use SET_BLOCK_PROPERTY with DEFAULT_WHERE

Hello

Guide to please the following

I have a block of retail, containing various columns like dyear, dmonth, itemcategoryid etc., locationid. I want to filter the records that correspond to four parameters only. I wrote like this (mentioned below), but it gives a correct result.

GO_BLOCK ('dep_vw');
set_block_property ('dep_vw', default_where, 'locationid =' |) "'|: cb1.locid | '''|| ' and
Trim (dmonth) =' | "'|: cb1.monthh | '''|| 'and dyear =' | "'|: cb1.yearr | '''|| 'and itemcategoryid =' | "'|: cb1.catgry_id | ''') ;

Control block (cb1) has variable parameters.

Any solution please.

Kind regards.

set_block_property('dep_vw',default_where,'locationid ='''||:cb1.locid||''' and

trim(dmonth)='''||:cb1.monthh||''' and dyear='''||:cb1.yearr||''' and itemcategoryid='''||:cb1.catgry_id||'''');

-Clément

Where clause in the "default_where" of get_block_property is not cancelled...

Hello

I developed a form with two blocks.

The first - block of non - database contains the criteria.

Database of the other - block-displays the records according to the inserted.

Also, there is a button that executes the query criteria.

The code of the BUTTON WHEN PRESSED is as follows:

declare

v_where varchar2 (3000);

Start

v_where: = null;

go_block ('EKKREMOTHTES_AIT_TSG');

clear_block (no_validate);

v_where: = get_block_property ('EKKREMOTHTES_AIT_TSG', default_where);

If: blk_criteria.site_id is not null then

v_where: = v_where |' and site_id = "'|: blk_criteria.site_id | ''';

end if;

If: blk_criteria.yphr_klimakio is not null then

v_where: = v_where |' and yphr_klimakio = "'|: blk_criteria.yphr_klimakio | ''';

end if;

If: blk_criteria.send_date_from is not null and: blk_criteria.send_date_to is not null then

v_where: = v_where |' and send_date between to_date('''|| to_char(:blk_criteria.send_date_from,'dd/mm/yyyy')||''',''dd/mm/yyyy'') and

to_date('''|| to_char(:blk_criteria.send_date_to,'dd/mm/yyyy') | " (', "dd/mm/yyyy)";

end if;

If: blk_criteria.eq_group1 is not null then

v_where: = v_where |' and eq_group1 = "' | : blk_criteria.eq_group1 | " ' ;

end if;

If: blk_criteria.eq_type1 is not null then

v_where: = v_where |' and eq_type1 = "' | : blk_criteria.eq_type1 | " ' ;

end if;

If: blk_criteria.eq_group2 is not null then

v_where: = v_where |' and eq_group2 = "' | : blk_criteria.eq_group2 | " ' ;

end if;

If: blk_criteria.eq_type2 is not null then

v_where: = v_where |' and eq_type2 = "' | : blk_criteria.eq_type2 | " ' ;

end if;

blk_def_where ('EKKREMOTHTES_AIT_TSG', v_where); / * a form routine that makes the set_block_property(,default_where) * /.

do_key ('execute_query');

end;

The above code works when the user inserts the criteria for the first time. Later when the user deletes certain criteria - clear criteria in the block of criteria-

and he pushed the button of the query results are exactly the same...

After have debugged the problem, clause v_where is not nullied (do not get the value zero) in order to get the new criteria.

Can you identify the error?

Note: I use Oracle Forms 10 g Db 11 g v.2

Thank you very much

SIM

SIM,

The procedure "blk_def_where" sets the block where clause by using the DEFAULT_WHERE or the ONETIME_WHERE constanct? Gets it to the user that the same results as the DEFAULT_WHERE sets the property WHERE permanently so the second time through, the 'v_where' is NOT null. Check your BLK_DEF_WHERE procedure and if it does not use the ONETIME_WHERE constanct, change isn't. The alternative would be to put DEFAULT_WHERE = NULL after the call to the built-in EXECUTE_QUERY.

Craig...

Oracle Forms - "DEFAULT_WHERE" is lost when used an apostrophe

Hi all

I faced a problem with Oracle Forms for a long time and I have no idea of what is happening so far.

I have a block based on a stored table. I used the following statement to set the WHERE clause for this block:

set_block_property ('MTI', default_where, l_where);

Variable l_where has the following form:

l_where: = ' WHERE art_nr IN (')

|| "SELECT the md5 ma_nww_ww_dat art_nr."

|| 'WHERE trunc (sysdate) BETWEEN md5.gueltig_ab AND md5.gueltig_bis'

|| ' AND UPPER (md5.art_bez_altern) AS "' | : gi_intern.txt_match_name | '%''';

The problem arises when the user tries to search for an article, using the MATCH_NAME an apostrophe (for example, GRANT). In this case, the where conditions are lost and all records are returned as a result.

Could you help me understand what the problem is?

Thank you very much!

Raluca

I think you should put the link reference inside the clause, instead of its value.

I mean: If you set the where_clause ' like mycol. "' | : my_item |' % "'

you to hard-code the value of: my_item at the time of the set_block_property.

I could do this instead (simplifying):

l_where: = ' upper (md5.art_bez_altern) like: gi_intern.txt_match_name | » %'' ';

Thus, the: my_item value will be assessed at the moment execute_query, and I think that forms will address the apostrophe in the variable to better link if hardcode you just what you do in your example.

It is, forms will treat it in the same way it would if you were query mode enter and entering a filter: with variable binding.

In fact, I think that this kind of filter you can even enter at the time of the design (with single apostrophes)

In addition, liaison is supposed to be better for the performance of subsequent queries.

The records retrieved maximum property programmatically setting

In my data block, I want to restrict the number of record queried dynamically, which is, in some cases, it should display all the archives and in some case scenario only 5.

I have a global variable, I put that scenario. And inspiring that I put the property block as follows, at the request of pre(I think this is the place, advice me if I'm wrong)

IF: OVERALL. VAR_Q = N THEN

set_block_property ('MY_BLOCK', MAX_RECORDS_FETCHED, 5);

n: = Get_Block_Property ('MY_BLOCK', RECORDS_TO_FETCH);

on the other

set_block_property ('MY_BLOCK', MAX_RECORDS_FETCHED, 1000);

n: = Get_Block_Property ('MY_BLOCK', RECORDS_TO_FETCH);

END IF;

message('MAX_RECORDS_FETCHED:'|| n);

But these changes do not take effect. and to this message I'm always 0 in both cases.

I thank in advance.

Here's how to fix it

For limited scenario, I put the variable as "n" and in the other scenario he set to 'Y '. Then, in the previous query write something similar to below

IF: OVERALL. VAR_Q = N THEN

one_where: = one_where | "AND H_DATE > = (SELECT MIN (H_DATE))

SELECT * FROM MY_TABLE WHERE H_CODE = "' | : MY_TABLE. H_CODE |

(' ORDER BY DESC H_DATE)

WHERE ROWNUM<=>

END IF;

This dynamically allow me to restrict the number of recording during execution.

Thank you very much, Zlatko Sirotic, I appreciate your help in time to accomplish my task.

Kind regards

problem with default_where

Hi all
I create a new form name is (inq_items)
This form contains a block of two canva and two window
block 1-> name (blkkey) no block database-> canva (search)--> window1
block 2-> block of database name (items)-> canva (items)-> window2

in canva (search), I create a txt element to query
and I create a button to push that button contains this query
trigger (when-pressed key)

DECLARE

WHERE_CLAUSE VARCHAR2 (3000) := '';

BEGIN

WHERE_CLAUSE := '(CODE = :BLKKEY.CODE OR :BLKKEY.CODE IS NULL )AND '

||'(BAR_CODE = :BLKKEY.BAR_CODE OR :BLKKEY.BAR_CODE IS NULL ) AND '

||'(CAT_NO = :BLKKEY.CAT_NO OR :BLKKEY.CAT_NO IS NULL ) AND '

||'(SUB_CAT_NO = :BLKKEY.SUB_CAT_NO OR :BLKKEY.SUB_CAT_NO IS NULL ) AND '

||'(SUPPLIER_NO = :BLKKEY.SUPPLIER_NO OR :BLKKEY.SUPPLIER_NO IS NULL ) AND ';

SET_BLOCK_PROPERTY ('TBL_WHS_ITEMS', default_where, WHERE_CLAUSE);

EXECUTE_QUERY;

END;

I need to this query runs given to another block (items), how can I do?
Sorry for my English
Thank you to

Andreas is correct - your where clause has a problem at the end.

We will build cleaner where clause:

DECLARE

WH_CL VARCHAR2(3000);

AND_TXT VARCHAR2(5);

PROCEDURE CHECK_PART(COL_NAME VARCHAR2) IS

BEGIN

IF NAME-IN('BLKKEY.'||COL_NAME) IS NOT NULL THEN

WH_CL := WH_CL || AND_TXT

|| COL_NAME ||'= :BLKKEY.' || COL_NAME;

AND_TXT := ' AND ';

END IF;

END CHECK_PART;

BEGIN

CHECK_PART('CODE');

CHECK_PART('BAR_CODE');

CHECK_PART('CAT_NO');

CHECK_PART('SUB_CAT_NO');

CHECK_PART('SUPPLIER_NO');

SET_BLOCK_PROPERTY ('TBL_WHS_ITEMS', default_where, WH_CL);

EXECUTE_QUERY;

END;

If you ask how to make sure at least one of your BLKKEY items is not null, you can add this before the SET_BLOCK_PROPERTY:

IF WH_CL IS NULL THEN

MESSAGE(' PLEASE ENTER AT LEAST ONE FIELD');

RAISE FORM_TRIGGER_FAILURE;

END IF;

.. .and why people use tabs like this in their code?

Oracle Apex, Forms, Reports

Sunday, May 21, 2017

How to see many Short Cut extension in Oracle ?

Wednesday, May 17, 2017

CONNECT BY LEVEL <=10

Sunday, May 7, 2017

RENAME COLUMN TABLE VIEW in Oracle

Advance Default Where

SET_BLOCK_PROPERTY DEFAULT_WHERE property

To generate a PDF using JavaScript in Oracle APEX from a collection

Total Pageviews

Pages