Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Sequences (DB 11g, Forms 10)
Oracle Sequences [message #663084] Mon, 22 May 2017 03:01 Go to next message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
I am facing a challenge with oracle sequences. Seq created ok, created public synonym for it. I am failing to access the sequence Oracle forms. But if I run the same in PL/Sql it is working fine under the same user.

Pl/Sql
SQL> SELECT SEQ.NEXTVAL FROM DUAL;

NEXTVAL
---------
3780508

In forms I am getting identifier 'SEQ.NEXTVAL' must be declared.

Please help
Re: Oracle Sequences [message #663086 is a reply to message #663084] Mon, 22 May 2017 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it's really the same user then that's probably a forms bug. Create a function in the DB that returns seq.nextval, call that from the form.
And PL/SQL is a language, not a program. Do you mean sqlplus, or pl/sql developer?
Re: Oracle Sequences [message #663087 is a reply to message #663086] Mon, 22 May 2017 05:09 Go to previous messageGo to next message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
Sorry true it is sqlplus and not Pl/sql as you rightfully say it a programming language.
Re: Oracle Sequences [message #663089 is a reply to message #663087] Mon, 22 May 2017 06:07 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
Is the sequence in a different schema then the application is running in? If it is your have to explicitly grant select on the sequence. For example if the sequence is created under the DATA schema and is called MY_SEQ and the application is running under the APPS schema then in the DATA schema you must run the following command

grant execute on MY_SEQ to APPS;
Re: Oracle Sequences [message #663095 is a reply to message #663089] Mon, 22 May 2017 07:16 Go to previous messageGo to next message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
The sequence is in the TEST schema.

I created public synonym SEQ for TEST.SEQ

Granted select,alter on SEQ to USER

Connected as USER in sqlplus run the following SELECT SEQ.NEXTVAL from dual; all is well

Connected as USER in forms the above error comes

I run it in forms like SELECT SEQ.NEXTVAL INTO SEQNO FROM DUAL; it throws an error in forms that is.

Tried SELECT TEST.SEQ.NEXTVAL INTO SEQNO FROM DUAL ; in forms and it is working fine but that is not I want.

Have implemented the above in other projects and it worked fine.

Wish I could get an explanation as to why this error, I have tried to google it but still no explanation.

Re: Oracle Sequences [message #663096 is a reply to message #663095] Mon, 22 May 2017 07:27 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
You have not setup a public synonym. You may have issued the command but the schema must have the priviledge to create a public synonym. To see if it is setup then issue the following command in sqlplus as the USER account

select owner,object_type
FROM all_objects
where object_name = 'SEQ' ;

you should see the synonym belonging to the PUBLIC user. IF you don't then there is no public synonym.
Re: Oracle Sequences [message #663098 is a reply to message #663095] Mon, 22 May 2017 07:34 Go to previous messageGo to next message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
Got the following results

select owner,object_type
FROM all_objects
where object_name = 'SEQ' ;

OWNER OBJECT_TYPE
------------------------------ -------------------
PUBLIC SYNONYM
TEST SEQUENCE
Re: Oracle Sequences [message #663101 is a reply to message #663098] Mon, 22 May 2017 07:37 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
one other question. Was the grant on select issued directly to the USER schema or to a role. It should be granted to the user.
Re: Oracle Sequences [message #663104 is a reply to message #663101] Mon, 22 May 2017 07:39 Go to previous messageGo to next message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
I have tried both granting to user and to a role but still same result. It is so weird.
Re: Oracle Sequences [message #663106 is a reply to message #663104] Mon, 22 May 2017 07:44 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
This is weird. One more thing to try. In the USER schema issue the following command

create synonym EMP for TEST.EMP;

This avoids the public synonym and still stops you needing to hard code the other schema.
Re: Oracle Sequences [message #663107 is a reply to message #663104] Mon, 22 May 2017 07:45 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


PLEASE do NOT tell us what you do only SHOW us using COPY & PASTE of the whole session so we can reproduce exactly what you report.
Re: Oracle Sequences [message #663109 is a reply to message #663107] Mon, 22 May 2017 08:25 Go to previous message
mbudzi2017
Messages: 6
Registered: May 2017
Junior Member
Thank you guys, my solution was to drop and recreate the user TEST and all seems ok now. I greatly appreciate your help.
Previous Topic: need help in rectifying error
Next Topic: Multiple rows based on column
Goto Forum:
  


Current Time: Sun Dec 17 18:34:51 CST 2017

Total time taken to generate the page: 0.03022 seconds