Home » Developer & Programmer » Forms » Please correct the code
Please correct the code [message #239288] Mon, 21 May 2007 23:24 Go to next message
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 Go to previous messageGo to next message
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 #239359 is a reply to message #239356] Tue, 22 May 2007 02:23 Go to previous messageGo to next message
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
Junior Member
It is not working.
Please suggest better option.

Regards,
Samir
Re: Please correct the code [message #239368 is a reply to message #239359] Tue, 22 May 2007 03:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #241992 is a reply to message #241971] Thu, 31 May 2007 05:33 Go to previous messageGo to next message
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
Junior Member
Hi hemavb!

Thanks, but sorry to say, code is not working.

svloke
Re: Please correct the code [message #241996 is a reply to message #239288] Thu, 31 May 2007 05:38 Go to previous messageGo to next message
hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE
Senior Member
what is the error you are getting?
Re: Please correct the code [message #242004 is a reply to message #241996] Thu, 31 May 2007 05:58 Go to previous messageGo to next message
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
Junior Member
I am neither getting any compilation error nor the view is created.

The statement is just ignored.

svloke
Re: Please correct the code [message #242430 is a reply to message #241969] Sat, 02 June 2007 01:00 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 181 times)
Re: Please correct the code [message #242544 is a reply to message #242438] Sun, 03 June 2007 22:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous messageGo to next message
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?


Re: Please correct the code [message #242560 is a reply to message #242545] Mon, 04 June 2007 02:00 Go to previous message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

the ON-LOGGON trigger fires as soon as the form starts. the parameters to be used in this trigger are
1) LOGOUT
2) LOGON('un,pw||'@'||cn)"

Quote:
I tried it in a form, but at run time of the exe, it is giving error message "FRM-91111


runtime of exe? how does that happen?
anyways you can download from the below sites

http://www.cedet.dk/ours/otndownloads.html

http://www.oracle.com/technology/software/products/forms/index.html
Previous Topic: Help:Exception when_no_data_found
Next Topic: Version Control System
Goto Forum:
  


Current Time: Sat Dec 03 14:23:11 CST 2016

Total time taken to generate the page: 0.24041 seconds