Home » Developer & Programmer » Forms » sql*plus configurations (date format) are not working in oracle form
sql*plus configurations (date format) are not working in oracle form [message #523750] Tue, 20 September 2011 04:20 Go to next message
zabltn
Messages: 45
Registered: September 2011
Member
Hi,

I have been developing and oracle application therefore i have installed the oracle express edition 10g database software and the oracle developer 10g.

I did some changes in sql*plus i mean i have changed the language(through 'nls_lang' to Arabic), i made a autonumber trigger, i changed the calender format (through 'alter_session...'), now all the changes are working fine from the oracle sql*plus but as i am running my form from form builder none of those changes are working.

i thought the oracle form and sql*plus don't have such link but i know i am wrong because i can add the tables i have created in sql*plus into the form, so i really need help please help me.
Re: sql*plus configurations are not working in oracle form [message #523753 is a reply to message #523750] Tue, 20 September 2011 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well alter session affects the current session. So that can't affect forms.
As for the rest - you'd have to tell us exactly what you did, what commands you used, for us to be able to tell what the problem is.
Re: sql*plus configurations are not working in oracle form [message #523754 is a reply to message #523753] Tue, 20 September 2011 04:39 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
these are the commands:


1. create sequence seqName;

2. create or replace trigger trigName
before insert on My_Table
for each row
begin
select seqName.nextval into :new.My_Table_Column1 from dual;
end;



3. alter session set nls_calendar='persian';

4. alter session set nls_date_format='yyyy/mm/dd';


through the 1st and 2nd commands my table's first column is incrementing automatically when ever user doing some data entry

through the 3th command i have changed the calender type and through the 4th one i have specified a date format for my oracle application.

So as i mentioned all is working in sql*plus but the oracle form still doesn't know the changes, i mean oracle form doesn't know at all the calender type, the date format, and autonumber trigger.

I am appreciating your help
kind regards.
Re: sql*plus configurations are not working in oracle form [message #523757 is a reply to message #523754] Tue, 20 September 2011 04:43 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I already said - alter session effects the current session only. Everytime you log on you get a new session. So forms can't possibly be affected by alter session commands run in sqlplus. You need to have forms issue the alter session command itself. Or use a DB logon trigger to do it. Or use alter system.

As for the trigger - did you create it in the same schema as the table?
Re: sql*plus configurations are not working in oracle form [message #523758 is a reply to message #523757] Tue, 20 September 2011 05:02 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Yes it is in the same schema.
This is why i worried that at least one thing should work between them.
Uusing a DB logon trigger, do you mean form open trigger?
And would you please guide me how to use alter_system?
Re: sql*plus configurations are not working in oracle form [message #523767 is a reply to message #523758] Tue, 20 September 2011 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is the trigger enabled?
Give us the results of the following sql:
SELECT onwer, status FROM all_triggers WHERE trigger_name ='<trigger name>';
SELECT owner FROM all_tables WHERE table_name = '<table name>';


And a DB logon trigger is a database trigger on the logon DB event.

You could use a forms on-logon trigger instead.

As for alter system - the documentation is your friend:
alter system
Re: sql*plus configurations are not working in oracle form [message #523769 is a reply to message #523767] Tue, 20 September 2011 05:51 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
both, result is:
no rows selected. Sad

[Updated on: Tue, 20 September 2011 05:52]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #523771 is a reply to message #523769] Tue, 20 September 2011 06:13 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You did replace <table name> with the correct table name didn't you? And the table name needs to be in upper case.
If so then you're connected to the wrong database or user.
Re: sql*plus configurations are not working in oracle form [message #523773 is a reply to message #523771] Tue, 20 September 2011 06:33 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
No, i have not replaced its name. And actually i have six tables and for all i have made a trigger like this to be auotonumber.

one thing else that i have noticed is that the trigger is normally working in sql plus as i had said before, but i dont know why i cant see those all triggers i had made, i mean after running the below command it says ' now rows selected.':

select trigger_name from all_triggers where table_name = '<any_one_of_those_six_tables';

Re: sql*plus configurations are not working in oracle form [message #523774 is a reply to message #523773] Tue, 20 September 2011 06:46 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Copy and paste exactly what you run, no edits, so I can see if you're doing something wrong.
Re: sql*plus configurations are not working in oracle form [message #523944 is a reply to message #523774] Wed, 21 September 2011 04:16 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
SELECT * FROM all_triggers WHERE trigger_name ='PITrigger';
SELECT * FROM all_tables WHERE table_name = 'Personal_Information';

i am running these commands and it says:
no rows selected.

note: i have not renamed the table's name.

[Updated on: Wed, 21 September 2011 04:17]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #523952 is a reply to message #523771] Wed, 21 September 2011 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 20 September 2011 12:13
And the table name needs to be in upper case.

So it should be:
SELECT * FROM all_triggers WHERE trigger_name ='PITRIGGER';
SELECT * FROM all_tables WHERE table_name = 'PERSONAL_INFORMATION';
Re: sql*plus configurations are not working in oracle form [message #523953 is a reply to message #523952] Wed, 21 September 2011 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
In addition please read and follow How to use [code] tags and make your code easier to read?
Re: sql*plus configurations are not working in oracle form [message #524004 is a reply to message #523953] Wed, 21 September 2011 06:31 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Sure, And Thanks.

In this case it prints out the owner of the table and trigger wHich is SYSTEM and the status of the trigger which is ENABLED.
Re: sql*plus configurations are not working in oracle form [message #524020 is a reply to message #524004] Wed, 21 September 2011 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've created tables in system? Stop doing that. Sys and system are special oracle accounts, they work differently to normal users. You should never create objects in either. Drop any objects you've created in system. Create a new user. Recreate the objects in that user. Try again.
Re: sql*plus configurations are not working in oracle form [message #524811 is a reply to message #524020] Tue, 27 September 2011 00:45 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Yes i have created them in SYSTEM, now as you told me to make a different user and then try, I made a user but i could not grant sysdba privilege to that user, it says insufficient privilege, i think this why i am doing this from SYSTEM user. Then i tried to log in to SYS and grant the privilege for new user, so as i am trying to log-in it asks me to enter the password but i have not assigned any password to the SYS user. i searched alot but i couldn't find how to log in to SYS and grant sysdba privilege to the new use.

And i want to thank you from your continues help.
Re: sql*plus configurations are not working in oracle form [message #524813 is a reply to message #524811] Tue, 27 September 2011 01:10 Go to previous messageGo to next message
Littlefoot
Messages: 21691
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you think that your newly created user needs to be granted the DBA role? What do you plan to do with it? For a "normal" user (i.e. the one you mentioned in your first message, that is supposed to develop an application), this role is unnecessary.

As of SYS password you have forgotten, this Forum contains quite a few topics that cover this issue; learn how to use its search utility. This is one of these discussions. See if it helps.
Re: sql*plus configurations are not working in oracle form [message #527206 is a reply to message #524813] Mon, 17 October 2011 00:15 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Dear Littlefoot,
I had a problem now it is solved. thanks,

Dear cookiemonster,
I have created anothor user and tried, but still the trigger which i have created in this user through sql*plus is not working in oracle form. And the alter system command is also not working.
Re: sql*plus configurations are not working in oracle form [message #527239 is a reply to message #527206] Mon, 17 October 2011 03:50 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do the trigger and the table both belong to this new user?
What alter system command are you using?
Re: sql*plus configurations are not working in oracle form [message #527250 is a reply to message #527239] Mon, 17 October 2011 04:22 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Yes After i loged in to this new user i have created, i created a different/new table and trigger.

Below is the command i used for the alter system:
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;

Re: sql*plus configurations are not working in oracle form [message #527254 is a reply to message #527250] Mon, 17 October 2011 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
For the alter system to have an effect you need to restart the DB.

As for the trigger: Triggers always fire when their associated table is modified with the relvant action (insert,update,delete) unless the trigger is disabled. You can check that by querying the status column in all_triggers.
If the trigger is enabled then the only other explanation is that you aren't modifying the table the trigger is on. Either because you are logged in as the wrong user, or you're logged into the wrong DB.
Re: sql*plus configurations are not working in oracle form [message #527261 is a reply to message #527254] Mon, 17 October 2011 05:05 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
For the alter system i would say that i did restart the database but no result.

For the trigger i would say that i checked it is enabled, and i have created the triggr, sequence and table in the same newly created user <user1> and also the database is the default oracle database.

Please note that when i am doing the insert action on my table in sql*plus the trigger(to have autonumber field in my table) is working corectly, but as i am running my oracle form and trying to do some entry in a datafield then the trigger is not working.

[Updated on: Mon, 17 October 2011 05:06]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #527269 is a reply to message #527261] Mon, 17 October 2011 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Triggers don't randomly stop working depending on which program connects to the DB.
Run this query in sqlplus and forms (use the when-new-form-instance trigger to run it and a message to display the result):
SELECT USER, instance_name, host_name FROM v$instance;
Re: sql*plus configurations are not working in oracle form [message #527387 is a reply to message #527269] Tue, 18 October 2011 00:22 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
In sql*plus:
SQL> conn user1/user1 @myNSN
Connected.
SQL> SELECT USER, instance_name, host_name FROM v$instance;
SELECT USER, instance_name, host_name FROM v$instance
                                           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> conn user1/user1 @myNSN as sysdba
Connected.
SQL> SELECT USER, instance_name, host_name FROM v$instance;

USER                           INSTANCE_NAME
------------------------------ ----------------
HOST_NAME
----------------------------------------------------------------
SYS                            xe
PC32




In oracle form, WHEN_NEW_FORM_INSTANCE trigger:
DECLARE
CURSOR test_cur IS SELECT user, instance_name, host_name FROM v$instance;
BEGIN
FOR test_rec IN test_cur LOOP
DBMS_OUTPUT.PUT_LINE (test_rec.user  || ' ' || emp_test.instance_name || ' ' ||test_rec.host_name);
END LOOP;
END;


But after i am compiling this trigger it gives the following error:
Error 201 at line 2, column 62
  identifier 'V$INSTANCE' must be declared


Note: I think it is because i am loged on as <user1>, therefore it could not identify the 'V$INSTANCE', as once the sql*plus couldn't identify it also.
Re: sql*plus configurations are not working in oracle form [message #527394 is a reply to message #527387] Tue, 18 October 2011 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 21691
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you - as a privileged user:
grant select on v$instance to user1;
and run the form again?
Re: sql*plus configurations are not working in oracle form [message #527398 is a reply to message #527394] Tue, 18 October 2011 01:09 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
SQL> conn user2/user2@myNSN as sysdba
Connected.
SQL> grant select on v$instance to user1;
grant select on v$instance to user1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

[Updated on: Tue, 18 October 2011 01:10]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #527411 is a reply to message #527398] Tue, 18 October 2011 01:31 Go to previous messageGo to next message
Littlefoot
Messages: 21691
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
My bad, sorry. Should have been
grant select on v_$instance to user1;
Re: sql*plus configurations are not working in oracle form [message #527474 is a reply to message #527411] Tue, 18 October 2011 04:25 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Now it compiles without any error, but after running the form i cant see any message or output, i mean it compiles and runs without error and without any result.
Re: sql*plus configurations are not working in oracle form [message #527475 is a reply to message #527474] Tue, 18 October 2011 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's because dbms_output doesn't work in forms. I told you to use the message builtin.
Also you don't need a for loop, that query will only ever return one row.
Re: sql*plus configurations are not working in oracle form [message #527485 is a reply to message #527475] Tue, 18 October 2011 05:15 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Ohh sorry,

Now in both places, sql*plus and oracle-form the result is:
USER1 xe PC32
Re: sql*plus configurations are not working in oracle form [message #527488 is a reply to message #527485] Tue, 18 October 2011 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 21691
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So does it mean that your problem is solved?
Re: sql*plus configurations are not working in oracle form [message #527493 is a reply to message #527488] Tue, 18 October 2011 05:51 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Unfortunately, still neither of them, the trigger to have an auto-number field and the alter system to change permanently the date's format are not working.

[Updated on: Tue, 18 October 2011 05:52]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #527502 is a reply to message #527493] Tue, 18 October 2011 06:43 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Back to squre one then.
Double check in all_triggers and all_tables that the trigger and table both belong to USER1. Check there are no other triggers on the table.
Post the code of the trigger.
Re: sql*plus configurations are not working in oracle form [message #528152 is a reply to message #527502] Sat, 22 October 2011 04:37 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
SQL> conn user1/user1 @myNSN
Connected.
SQL> create table testtable (sn number primary key, name varchar2(20));

Table created.

SQL> create sequence testseq;

Sequence created.


SQL> ed
Wrote file afiedt.buf

  1  create trigger testtrgr
  2  before insert on testtable
  3  for each row
  4  begin
  5  select testseq.nextval into :new.sn from dual;
  6* end;
SQL> /

Trigger created.

SQL> select owner from all_triggers where trigger_name='TESTTRGR';


OWNER
------------------------------
USER1

SQL> select owner from all_tables where table_name='TESTTABLE';

OWNER
------------------------------
USER1

[Updated on: Sat, 22 October 2011 04:39]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #528176 is a reply to message #528152] Sat, 22 October 2011 12:36 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
When you insert a record in the form how do you check the trigger isn't working?
Do you query the record with sqlplus and see what values actually got inserted?
Re: sql*plus configurations are not working in oracle form [message #528193 is a reply to message #528176] Sat, 22 October 2011 23:30 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Yes, in sql*plus when i am inserting a <NAME> without any <SN or Serial Number> to the testtable the <NAME> inserts and the <SN> is increasing automatically by one. But when i am running the form and then directly going to the <NAME> data field to insert some data without entering any <SN> it errors:
FRM 40202: Field must be entered.

And i know it is because a primary key data field must be entered first (SN Field), so for this reason i have created the trigger to fire automatically while inserting data to the table but it is not working at all.

[Updated on: Sat, 22 October 2011 23:33]

Report message to a moderator

Re: sql*plus configurations are not working in oracle form [message #528216 is a reply to message #528193] Sun, 23 October 2011 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I should have asked for the exact error ages ago obviously. There is nothing wring with the trigger.
The trigger fires when the insert statement is executed in the DB.
The form is checking if that field is being entered when you click on save. Since it isn't it raises an error instead of trying to run the insert in the DB. There's no way the trigger could fire.
Set the required property of the field to No.
Re: sql*plus configurations are not working in oracle form [message #528222 is a reply to message #528216] Sun, 23 October 2011 04:56 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Quote:
Set the required property of the field to No.

Ohhhh, Thank you very much finally it worked Smile


As you know i had mentioned at the beginning of this post that due to the alter_session command i am not able to change permanently the date's format, i mean in sql*plus it works fine but in forms the date's format doesn't change. I have tried alter_system command but it didn't work also.
Therefore if you could please help me to change permanently the date's format and calender type in the oracle form it would be my pleasure.
Re: sql*plus configurations are not working in oracle form [message #528330 is a reply to message #528222] Mon, 24 October 2011 05:28 Go to previous messageGo to next message
zabltn
Messages: 45
Registered: September 2011
Member
Please help..
Re: sql*plus configurations are not working in oracle form [message #528414 is a reply to message #528330] Mon, 24 October 2011 14:52 Go to previous messageGo to previous message
Littlefoot
Messages: 21691
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you put this piece of code into WHEN-NEW-FORM-INSTANCE trigger (use date format you need):
begin
  forms_ddl('alter session set NLS_date_format = ''yyyy/mm/dd''');
end;
Previous Topic: written english characters only in text item (merged 2)
Next Topic: FRM-30351: No list elements defined for list item.
Goto Forum:
  


Current Time: Mon Dec 06 06:49:28 CST 2021