sql*plus configurations (date format) are not working in oracle form [message #523750] |
Tue, 20 September 2011 04:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #523754 is a reply to message #523753] |
Tue, 20 September 2011 04:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #523773 is a reply to message #523771] |
Tue, 20 September 2011 06:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #524020 is a reply to message #524004] |
Wed, 21 September 2011 07:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #527254 is a reply to message #527250] |
Mon, 17 October 2011 04:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #527387 is a reply to message #527269] |
Tue, 18 October 2011 00:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #528152 is a reply to message #527502] |
Sat, 22 October 2011 04:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #528193 is a reply to message #528176] |
Sat, 22 October 2011 23:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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.
|
|
|
|
|
|