Hi all,

I was trying to set up a where clause at runtime. GETUSER is a function to fetch the user_id and is very simple as the script below shows:

CREATE OR REPLACE FUNCTION getuser(p_val varchar2) 
    RETURN VARCHAR2 IS
BEGIN
--simplified for testing purpose
    RETURN 'N';
END;

My where clause is USER_ID=GETUSER('USER_ID'). If given during datasource design time it works fine. However if the same where clause is given at runtime I am getting the following error on api_datasource.executequery.

Exception message => Invalid filter clause. 
Please verify that where clause "USER_ID=GETUSER('USER_ID')" is correct.
Procedure => api_datasource.executequery
Exception code => e_invalidWhereClause
unsecure events sent from client => N

If the underlying query is copied from formspider to sql plus the same is running without error as well.

Further, if the following is given as where clause no error is returned during design time or runtime:

USER_ID = api_session.getvaluetx('USER_ID')

Can some body help me to solve this problem?

We are involved in conveting our Oracle Forms application into formspider. There are lots of runtime where clauses in oracle forms. We need the same functionality in formspider as well. Further, our converted formspider application and already running Oracle forms application should be able to run transparently using the same internal settings, for example, the above where clause.

With regards.

George.

asked 01 Jun '14, 11:37

grajan777's gravatar image

grajan777
1011226
accept rate: 10%


Hi George,

Thanks for your feedback, I created a ticket in our issue management system about this problem. As a temporary workaround you can call your GETUSER function by adding the datasource schema prefix, such as:

USER_ID=[datasourceSchemaName].GETUSER('USER_ID')

Hope this helps,
Ibrahim

link

answered 05 Jun '14, 03:49

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

Hi,

This issue is still not resolved in formspider 1.9 as I have rechecked it today.

Regards.

George.

(24 Mar '16, 10:02) grajan777

Hi all,

I have a similar problem with a more difficult where clause: NVL(A.PERIOD, '-') = NVL('201509', NVL(A.PERIOD, '-')) AND NVL(A.ROUTE_FLAG, '-') = NVL('', NVL(A.ROUTE_FLAG, '-'))

The clause works perfect if I concatenate with the other part of the query: "SELECT A.PERIOD, A.ROUTE_FLAG FROM TEST_SCHEMA.TEST_TABLE A WHERE ".

Does anybody know what is the proper usage of the setFilterWhereClause procedure?

Thanks in advice! Szilard

link

answered 25 Nov '15, 18:52

S7ilA's gravatar image

S7ilA
152
accept rate: 0%

Hi Szilard,

I guess your design time query contains the "A" alias. However in the setFilterWhereClause API, you cannot reference the designtime query alias since Formspider adds your where clause to the desing time query in the following way:

select * from ( [yourDesintimeQuery} ) where [fiterWhereClause]

Therefore please retry after removing the alias: NVL(PERIOD, '-') = NVL('201509', NVL(PERIOD, '-')) AND NVL(ROUTE_FLAG, '-') = NVL('', NVL(ROUTE_FLAG, '-'))

Hope this helps,
Ibrahim

link

answered 26 Nov '15, 03:14

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

Hi Ibrahim,

It seems that the problem is more complex. My designtime query is a 'SELECT ... FROM DUAL', to ensure the proper number of columns (150).

SELECT 1 COL001, 1 COL002, ..., 1 COL149, 1 COL150 FROM DUAL

At runtime, before I populate a specific grid-panel I try to change the defaultQuery of the datasource used in grid, to the certain query (holding the number of columns, and "aliased" the column names specificated at designtime, in grid-panel).

Since I could'nt find a way to add/change bindVar of a datasource at runtime, I tried to change the WHERE clause. Without success so far.

The table alias which I used is the part of the changed (runtime) query. I tried to create the runtime query without table alias, but the result is the same. If I refer to the designtime column-alias in the where clause, I got a completly empty error message.

Do you have any suggestion?

Regards, Szilard

link

answered 26 Nov '15, 05:40

S7ilA's gravatar image

S7ilA
152
accept rate: 0%

Opps, I use the FS 1.8.1 (Build 41) trial... Maybe this is the source of the problem.

(26 Nov '15, 05:50) S7ilA

Hi Szilard,

This API should work correctly in FS 1.8.1, I don't think that it's a version specific problem. Can you post your changed(runtime) query?
Also, I see that all of your designtime query columns are number datatyped, please note the datatype of your query runtime query columns should match the designtime dataypes.

Regards,
Ibrahim

(26 Nov '15, 07:10) Ibrahim Sand... ♦♦

Hi,

I think, it should be the problem. The designtime query is just an initiator, so all the values are 1. The runtime query's datatypes are quiet diverse.

Well, how should I create a panel with a dynamic built grid? We have hundreds of reports, so it would be very complicated and time-consuming to create (and maintain) all necessary objects.

My runtime query:

SELECT PERIOD COL001,
RUNID COL002,
ROUTE_FLAG COL003,
INTERFACE_SRC_ID COL004,
INTERFACE_MODDATE COL005,
ORDHEA_ID COL006,
ORDER_NUMBER COL007,
ORDER_TYPE_ID COL008,
ORDER_CREASON_ID COL009,
ORDER_CREATE_DATE COL010,
ORDER_SUBMISSION_DATE COL011,
ORDER_DONE_DATE COL012,
ORDER_CREATED_BY COL013,
ORDER_DEAL COL014,
ORDER_DEAL_CODE COL015,
ORDER_COMM_DEAL_CODE COL016,
TERULET_KODJA COL017,
CUSTOMER_ID COL018,
CUSTOMER_MT_ID COL019,
CUSTOMER_NAME COL020,
CUSTOMER_BUSNSS_FLAG COL021,
CUSTOMER_PCG_CODE COL022,
CUSTOMER_PREV_MT_ID COL023,
DOCUMENT_ID COL024,
CLSERPOI_ID_ACT COL025,
CLSERPOI_ID_PAIR COL026,
CLCAPOTE_NAME_ACT COL027,
CLSEPOTE_NAME_ACT COL028,
CLSEPOTE_NAME_PAIR COL029,
SAC_REKOD_FLAG COL030,
SAC_WS_SERVICE_FLAG COL031,
SAC_CABLE_TV_ANALOG_FLAG COL032,
SAC_CABLE_TV_DIGITAL_FLAG COL033,
SAC_CABLE_ED3_IPTV_FLAG COL034,
SAC_SAT_TV_INDIVIDUAL_FLAG COL035,
SAC_SAT_TV_GROUP_FLAG COL036,
SAC_COPPER_ADSL_QOS COL037,
SAC_COPPER_IPTV_QOS COL038,
SAC_COPPER_PSTN_QOS COL039,
SAC_CABLE_PHONE_QOS COL040,
SAC_CABLE_INTERNET_QOS COL041,
SAC_NAKED_TELEPHONY_QOS COL042,
SAC_NAKED_INTERNET_QOS COL043,
SAC_NAKED_TV_QOS COL044,
SAC_FIBRE_TELEPHONY_QOS COL045,
SAC_FIBRE_INTERNET_QOS COL046,
SAC_FIBRE_TV_QOS COL047,
SAC_LTO_FLAG COL048,
SAC_CTV_PROVIDER COL049,
CAMPAIGN_ID COL050,
CAMPAIGN_NAME COL051,
CLADDRESS_ID COL052,
CONCATENATED_ADDRESS COL053,
COMPETITOR_PRESENCE COL054,
PR_ORDITEM_ID COL055,
PR_ORITACT_CODE COL056,
PR_PRODUCT_SOURCE_ID COL057,
PR_ASSET_ID COL058,
RA_ORDITEM_ID COL059,
RA_ORITACT_CODE COL060,
RA_PRODUCT_SOURCE_ID COL061,
RA_ASSET_ID COL062,
RP_SR_ORDITEM_ID COL063,
RP_SR_ORITACT_CODE COL064,
RP_SR_PRODUCT_SOURCE_ID COL065,
RP_SR_ASSET_ID COL066,
RK_ORDITEM_ID COL067,
RK_ORITACT_CODE COL068,
RK_PRODUCT_SOURCE_ID COL069,
RK_ASSET_ID COL070,
RATEPLAN_PROLONG_FLAG COL071,
ORDERED_QUANTITY COL072,
CATEGORY_ALAP COL073,
CATEGORY_ID COL074,
SEMA_ID COL075,
WEIGHT_DIFF COL076,
PAIR_INTERFACE_SRC_ID COL077,
LOADDATE COL078,
RESOURCE_CODE COL079,
ORDER_ITEM_DONE_DATE COL080,
LOADING_PERIOD COL081,
1 COL082,
1 COL083,
1 COL084,
1 COL085,
1 COL086,
1 COL087,
1 COL088,
1 COL089,
1 COL090,
1 COL091,
1 COL092,
1 COL093,
1 COL094,
1 COL095,
1 COL096,
1 COL097,
1 COL098,
1 COL099,
1 COL100,
1 COL101,
1 COL102,
1 COL103,
1 COL104,
1 COL105,
1 COL106,
1 COL107,
1 COL108,
1 COL109,
1 COL110,
1 COL111,
1 COL112,
1 COL113,
1 COL114,
1 COL115,
1 COL116,
1 COL117,
1 COL118,
1 COL119,
1 COL120,
1 COL121,
1 COL122,
1 COL123,
1 COL124,
1 COL125,
1 COL126,
1 COL127,
1 COL128,
1 COL129,
1 COL130,
1 COL131,
1 COL132,
1 COL133,
1 COL134,
1 COL135,
1 COL136,
1 COL137,
1 COL138,
1 COL139,
1 COL140,
1 COL141,
1 COL142,
1 COL143,
1 COL144,
1 COL145,
1 COL146,
1 COL147,
1 COL148,
1 COL149,
1 COL150
FROM FRMGUI.WIDE_TABLE

Regards, Szilard

link

answered 26 Nov '15, 08:06

S7ilA's gravatar image

S7ilA
152
accept rate: 0%

edited 26 Nov '15, 08:28

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×3
×2

Asked: 01 Jun '14, 11:37

Seen: 1,450 times

Last updated: 24 Mar '16, 10:02


© Copyright Gerger 2017