Home » Developer & Programmer » Forms » Using dual table in oracle form (Oracle Forms 6i)
Using dual table in oracle form [message #667786] Tue, 16 January 2018 01:56 Go to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
Good day, my friends,
I'm trying to use the dual table in oracle forms, but when I execute the query, an error appeared: FRM-40505: ORACLE error: unable to perform query.
What should I do next Sad ?
Here is my code.
DECLARE
N1 VARCHAR(10);
N2 VARCHAR(10);
MY_QUERY VARCHAR(100);
BEGIN
N1 := ''||''' '''||'';
N2 := ''||''''''||'';
MY_QUERY := 'REPLACE(SUBSTR('''||:MY_LAYOUT.DATA_INPUT||''',1,3)),'||N1||','||N2||') FROM DUAL;
GO_BLOCK('MY_BLOCK');
SET_BLOCK_PROPERTY('MY_BLOCK',DEFAULT_WHERE, MY_QUERY);
EXECUTE_QUERY;
END;
Re: Using dual table in oracle form [message #667787 is a reply to message #667786] Tue, 16 January 2018 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should display MY_QUERY (use MESSAGE built-in) so that you'd see what's going on.

It seems that DUAL is not the problem here, but different number of opened and closed brackets in MY_QUERY (opened: 2, closed: 3). Fix that.
Re: Using dual table in oracle form [message #667788 is a reply to message #667787] Tue, 16 January 2018 02:21 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@Littlefoot.
Aww, sorry for this mistake, I have already fixed it, but the result was still the same Sad.
I doubt that there are a table name DUAL, existing in Oracle form, and it could be choose in Data Block Wizard. I'm going to try it.
Re: Using dual table in oracle form [message #667789 is a reply to message #667788] Tue, 16 January 2018 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said: display how MY_QUERY looks like. You use it as a DEFAULT WHERE for a block. I don't know what :MY_LAYOUT.DATA_INPUT contains. It is possible that DUAL is used in a WHERE clause, such as

select ... 
from ...
where id in (select 1 from dual)
(yes, a stupid example, but it is correct as far as Oracle is concerned).

It seems that you should remove FROM DUAL completely from MY_QUERY; why did you use it, anyway?

And - once again - make sure MY_QUERY is correctly set. There are many single quotes, you have to pay attention so that they match. Otherwise, it won't compile or won't work properly. That's why I said that you should display its value. You should create it step-by-step, adding parts of it one by one - first compose the SUBSTR, then apply REPLACE to it. Test frequently!

Re: Using dual table in oracle form [message #667791 is a reply to message #667789] Tue, 16 January 2018 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
That code you originally posted can't possibly give that error because that code can't possibly compile.
You're missing a quote at the end of the line that assigns my_query. Consequently the following go_block, set_block_property, execute_query and END are all part of the string.
If you put in the missing quote then my_query gets assigned to:
REPLACE(SUBSTR('BLAH',1,3)),' ','') FROM DUAL;
which is just non-sense.

What are you actually trying to do to the where clause?
Re: Using dual table in oracle form [message #667793 is a reply to message #667791] Tue, 16 January 2018 04:19 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@cookiemonster
@Littlefoot
All I am trying to do is, taking a string from the input, so my fisrt query be like:
SELECT (REPLACE(SUBSTR(:MY_LAYOUT.MY_ORi_STRING),1,3),' ','') FROM DUAL.After that, I will get a string form this query (I called it, RES VARCHAR(100)).
Then, I would like to execute a where-clause query, based on the result that I have got from the 1st query. So the 2nd query be like:
SELECT TNAME FROM EMP WHERE TNAME LIKE 'RES'.
That's all I'm trying to do. Is that possible in Form 6i ? If possible please guide me.
Thanks.
Re: Using dual table in oracle form [message #667795 is a reply to message #667793] Tue, 16 January 2018 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why bother using dual?
You're over-complicating it.
You can simply make the where clause of the block be:
tname like REPLACE(SUBSTR(:MY_LAYOUT.MY_ORi_STRING),1,3),' ','')
Re: Using dual table in oracle form [message #667796 is a reply to message #667795] Tue, 16 January 2018 04:31 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@cookemonster
I mean
..LIKE '%....%'
How could I put this '%' into that query Sad.
Re: Using dual table in oracle form [message #667797 is a reply to message #667796] Tue, 16 January 2018 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
with concatenation:
tname like '%'||REPLACE(SUBSTR(:MY_LAYOUT.MY_ORi_STRING),1,3),' ','')||'%'
Re: Using dual table in oracle form [message #667798 is a reply to message #667797] Tue, 16 January 2018 04:34 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
#cookiemonster
Damn cool, will try it right now, tks for your advice Very Happy.
Re: Using dual table in oracle form [message #667801 is a reply to message #667798] Wed, 17 January 2018 01:44 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@cookiemonster.
It wasn't work Sad.
My query:
SELECT TNAME FROM EMP WHERE %REPLACE(SUBSTR(:MY_LAYOUT.MY_ORI_STRING,1,3),' ','')%

[Updated on: Wed, 17 January 2018 01:45]

Report message to a moderator

Re: Using dual table in oracle form [message #667802 is a reply to message #667801] Wed, 17 January 2018 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, come on! Does this query look OK to you?

You must create a valid WHERE clause, not unusable concatenation of some (key)words.
Re: Using dual table in oracle form [message #667803 is a reply to message #667802] Wed, 17 January 2018 03:00 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@Littlefoot
Sr, my bad Sad. The query should be:
SELECT TNAME FROM EMP WHERE TNAME LIKE '%'||REPLACE(SUBSTR(:MY_LAYOUT.MY_ORI_STRING,1,3),' ','')||'%'.
But the form still can not perform this query Sad.
*Update. It works, it finally works, thank you all, really appreciate it Very Happy Very Happy.

[Updated on: Wed, 17 January 2018 03:14]

Report message to a moderator

Re: Using dual table in oracle form [message #667804 is a reply to message #667803] Wed, 17 January 2018 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13787
Registered: September 2008
Location: Rainy Manchester
Senior Member
Where, precisely, have you put that select in the form?
Re: Using dual table in oracle form [message #667805 is a reply to message #667803] Wed, 17 January 2018 03:13 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Run it in SQL*Plus (or any tool you use). Instead of :MY_LAYOUT.MY_ORI_STRING use value from form's item. What does that select return? Make sure that EMP table actually contains TNAMEs that satisfy the WHERE condition.

Post copy/paste of your SQL*Plus session so that we could see what's going on.
Re: Using dual table in oracle form [message #667806 is a reply to message #667805] Wed, 17 January 2018 03:16 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@Littlefoot
@cookiemonster
*Update. It works, it finally works, thank you all, really appreciate it Very Happy Very Happy.
Re: Using dual table in oracle form [message #667807 is a reply to message #667806] Wed, 17 January 2018 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you did it.

Please, post the final code; someone might benefit from it.
Re: Using dual table in oracle form [message #667809 is a reply to message #667807] Wed, 17 January 2018 19:10 Go to previous messageGo to next message
NDKA739125
Messages: 33
Registered: January 2018
Member
@Littlefoot,
Sorry for my late reply. I've just realized that the SUBSTR should be in the first place. So my final query be like:
SELECT TNAME 
FROM EMP 
WHERE TNAME LIKE '%'||SUBSTR(REPLACE(:MY_LAYOUT.MY_ORI_STRING,' ',''),1,3)||'%'.


[EDITED by LF: removed [quote] tags and applied [code] ones]

[Updated on: Thu, 18 January 2018 00:15] by Moderator

Report message to a moderator

Re: Using dual table in oracle form [message #667812 is a reply to message #667809] Thu, 18 January 2018 00:15 Go to previous message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you!
Previous Topic: Fields Should be Highlighted in Different Colors
Next Topic: How to Open HTML file in same form with current items
Goto Forum:
  


Current Time: Sat Jan 25 20:26:04 CST 2020