| Please correct the code [message #239288] |
Mon, 21 May 2007 23:24  |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
The following code shows no compilation errors, but during execution it is ignored.
forms_ddl('Create or replace view mr_craft_bdown_view as
select * from mr_craft_bdown
where bd_from >= ('||to_char(:init.date_fr)||')
and bd_from <= ('||to_char(:init.date_to)||');');
When the ':init.date_fr' and ':init.date_to' are replaced by actual dates, the statement inside bracket executes from the sql prompt & the view is replaced, but same code using DDL when tried to execute from Developer Form, it is just ignored.
Can somebody help me to correct it?
Samir
[Updated on: Mon, 21 May 2007 23:48] Report message to a moderator |
|
|
|
| Re: Please correct the code [message #239356 is a reply to message #239288] |
Tue, 22 May 2007 02:16  |
Flyhard
Messages: 21
Registered: April 2007
Location: Germany
|
Junior Member |
|
|
Form the Help of FORMS_DDL:
If you use FORMS_DDL to execute a single DDL statement:
- Omit the trailing semicolon to avoid an invalid character error.
Best Regards
Fly
|
|
|
|
|
|
| Re: Please correct the code [message #239368 is a reply to message #239359] |
Tue, 22 May 2007 03:01 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What is the data type of 'mr_craft_bdown.bd_from' column? Is it a character or a date? What data type is used for form items 'init.date_fr' and 'init_date_to'?
Regarding the code you have used, I'd say all of them are DATEs. If so, why are you using the TO_CHAR function here? I'd say that it should be either TO_DATE, or no function at all.
Saying that it works fine when you put actual "date" values is, I'm afraid, wrong. You are using STRINGS which are recognized only because of NLS settings you use. On another machine, this might be different and your code would fail (probably with a "not a valid month" error).
When dealing with dates, tell Oracle so:...
WHERE bd_From BETWEEN TO_DATE(:init.date_fr, 'dd.mm.yyyy') AND
TO_DATE(:init.date_to, 'dd.mm.yyyy')
I don't know format you chose for form items, so this is just an example. Try to use it in actual code and see how it works.
By the way, why would you want to create a view from a form? Where's a point in doing that (except testing what FORMS_DDL can do)? Why wouldn't you create a view using a SQL*Plus, for example, and use it in your form?
|
|
|
|
| Re: Please correct the code [message #239371 is a reply to message #239368] |
Tue, 22 May 2007 03:33 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
I have tried the way you have suggested, but it is not working. The data type for 'mr_craft_bdown.bd_from','init.date_fr'and 'init_date_to'is date. The format is ddmmyy.
Basically this form is used to insert data into the temp table, which will be further used to run a report. The view should select only that data, which is in between the input dates. Based on the data in temp table & view, the report will be generated.
|
|
|
|
| Re: Please correct the code [message #241080 is a reply to message #239371] |
Mon, 28 May 2007 05:11 |
|
|
hie samir,
I the below helps you
SQL> select * from emp where to_char(hiredate,'DD-MM-YYYY') = '17-12-1980';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
if I want to use the same select statement in a form on a click of a button then in the 'WHEN-BUTTON-PRESSED' trigger i would use
forms_ddl('select * from emp where to_char(hiredate,'||''''||'DD-MM-YYYY'||''''||') = '||''''||'17-12-1980'||'''');
|
|
|
|
| Re: Please correct the code [message #241084 is a reply to message #241080] |
Mon, 28 May 2007 05:23 |
|
|
i appologize i have made a mistake
we cannot use a select statement in forms_ddl but i hope you understand what i am trying to do here....
'''' - this creates a ' in the sql statement ....
let me rewrite your code
forms_ddl('Create or replace view mr_craft_bdown_view as select * from mr_craft_bdown
where bd_from >= (to_char('||''''||:init.date_fr)||''''||')and bd_from <= (to_char('||''''||:init.date_to)||'''');
try this i am sure it will work.
Best regards
Mudabbir
|
|
|
|
| Re: Please correct the code [message #241224 is a reply to message #241084] |
Mon, 28 May 2007 22:53 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Dear mudabbir,
I have tried the solution that you have given. Firstly it gives compilation errors, secondly in the code as suggested by littlefoot, there should not be function to_char, for a date field. It should be either to_date or no function at all.
I corrected your code to
forms_ddl('Create or replace view mr_craft_bdown_view as select * from mr_craft_bdown where bd_from >= to_char ('||''''||:init.date_fr||''''||')and bd_from <= to_char('||''''||:init.date_to||'''');
I have removed paranthesis before to_char and after date_fr. Now it doesn't show any compilation error, but it is simply ignored when excuting the form. Whether you user to_char or not, the results are same.
As stated by you
'''' - this creates a ' in the sql statement ....
From the code that you have wrote:
forms_ddl('Create or replace view mr_craft_bdown_view as select * from mr_craft_bdown
where bd_from >= (to_char('||''''||:init.date_fr)||''''||')and bd_from <= (to_char('||''''||:init.date_to)||'''');
I am not able to understand, what is fancy of concating one ' with another ' (i.e. '''' )(Red coloured), without any variable in between.
Please give some better solution.
Thanks
With Regards,
svloke
|
|
|
|
| Re: Please correct the code [message #241263 is a reply to message #241224] |
Tue, 29 May 2007 00:54 |
|
|
hi samir
try this
Try and see what is the sql statement generated before you issue the forms_ddl command .and then try the same statement on SQL Plus. In your form create a variable of varchar2(100) and also create a field on your form of varchar2 type
Declare
chk_sql varchar2(100);
Begin
.
.
.
.
chk_sql := 'Create or replace view mr_craft_bdown_view as
select * from mr_craft_bdown
WHERE bd_From BETWEEN
TO_DATE('||''''||:init.date_fr||''''||','||''''||'dd.mm.yyyy'||''''||') AND
TO_DATE('||''''||:init.date_to||''''||','||''''||'dd.mm.yyyy'||'''');
:block1.<FIELD ON FORM > := chk_sql;
--forms_ddl('Create or replace view mr_craft_bdown_view as
--select * from mr_craft_bdown
--WHERE bd_From BETWEEN
--TO_DATE('||''''||:init.date_fr||''''||','||''''||'dd.mm.yyyy'||''''||')AND
--TO_DATE('||''''||:init.date_to||''''||','||''''||'dd.mm.yyyy'||'''');
.
.
.
.
End;
Mudabbir
|
|
|
|
| Re: Please correct the code [message #241963 is a reply to message #239288] |
Thu, 31 May 2007 04:06 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Hi Mudabbir,
Thanks for the new solution. But it too is not working.
Please explain, how any action can take place just by assigning some script to the text field, when no trigger is assigned to that field or any other field in the form to execute the text field code.
In short, where is the activity of
:block1.<FIELD ON FORM > := chk_sql; ?????
Please reply
|
|
|
|
| Re: Please correct the code [message #241969 is a reply to message #239288] |
Thu, 31 May 2007 04:22 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Hi Mudabbir,
Thanks for the new solution. But it too is not working.
Please explain, how any action can take place just by assigning some script to the text field, when no trigger is assigned to that field or any other field in the form to execute the text field code.
In short, where is the activity of
:block1.<FIELD ON FORM > := chk_sql; ?????
Please reply
|
|
|
|
| Re: Please correct the code [message #241971 is a reply to message #239288] |
Thu, 31 May 2007 04:28 |
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
|
Senior Member |
|
|
ORIGINAL CODE:
forms_ddl('Create or replace view mr_craft_bdown_view as
select * from mr_craft_bdown
where bd_from >= ('||to_char(:init.date_fr)||')
and bd_from <= ('||to_char(:init.date_to)||');');
NEW CODE:
forms_ddl('Create or replace
view mr_craft_bdown_view as
select * from mr_craft_bdown
where bd_from >=
('''||to_char(:init.date_fr, 'DD-MON-YYYY')||''')
and bd_from <= ('''||to_char(:init.date_to, 'DD-MON-YYYY')||''');');
What is marked in red in the above is the change in the code.
[Updated on: Thu, 31 May 2007 04:28] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Please correct the code [message #242430 is a reply to message #241969] |
Sat, 02 June 2007 01:00 |
|
|
Hi Samir
i have tested this code and it works....
forms_ddl('Create or replace view mr_craft_bdown_view as
select * from mr_craft_bdown
WHERE bd_From BETWEEN
TO_DATE('||''''||:init.date_fr||''''||','||''''||'dd.mm.yyyy'||''''||')AND
TO_DATE('||''''||:init.date_to||''''||','||''''||'DD-MM-YYYY'||''''||')'); |
|
|
|
| Re: Please correct the code [message #242438 is a reply to message #242430] |
Sat, 02 June 2007 02:21 |
|
|
Hi samir
Check the attached form
check the ON-LOGON trigger and change the host string accordingly.
also note that the select statement is accessing emp table. This form works perfectly when connected to schema "SCOTT".
I hope this will help.
-
Attachment: test1.fmb
(Size: 52.00KB, Downloaded 996 times)
|
|
|
|
| Re: Please correct the code [message #242544 is a reply to message #242438] |
Sun, 03 June 2007 22:32 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Hi mudabbir,
Thanks for the reply.
The code in on-logon trigger is not visible. When I try to open it in form builder, it gives message "PDE-PEP006 Encoded Programme unit has unknown format". In which version of Oracle Form Builder, you have developed the form?
Please post the code script.
svloke
|
|
|
|
| Re: Please correct the code [message #242545 is a reply to message #242430] |
Sun, 03 June 2007 23:20 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Dear mudabbir,
In runtime, the existing data is cleared, but new data is not seen. I think it is a form builder version problem. The version in my machine is 5.0.6.8.0.
Is it possible to upgrade the version from net ?
svloke
|
|
|
|
| Re: Please correct the code [message #242548 is a reply to message #242545] |
Mon, 04 June 2007 00:24 |
|
|
Hi Samir,
i have designed the form in 6i.
the code on the ON-LOGON tigger is just
Logon('scott','Tiger','@','TEST');
Yes you can download Developer 6i from the net.
Let me know if you need anything else...
Mudabbir
|
|
|
|
| Re: Please correct the code [message #242558 is a reply to message #242548] |
Mon, 04 June 2007 01:48 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
hi Mudabbir
Please explain the functionality of on-logon trigger.
With help of this trigger is it possible that the user will get connected to the system automaticaly, without logon screen?
In the help, the format given is "LOGON(un,pw||'@'||cn)"
I tried it in a form, but at run time of the exe, it is giving error message "FRM-91111: Internal Error: window system startup failure."
Can you please suggest me the URL from where I can download Developer 6i from the net?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
