OraFAQ Forum: SQL & PL/SQL » Oracle Sequences

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Sequences (DB 11g, Forms 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Sequences [message #663084]

Mon, 22 May 2017 03:01

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

Report message to a moderator

Re: Oracle Sequences [message #663086 is a reply to message #663084]

Mon, 22 May 2017 04:38

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: Oracle Sequences [message #663087 is a reply to message #663086]

Mon, 22 May 2017 05:09

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.

Report message to a moderator

Re: Oracle Sequences [message #663089 is a reply to message #663087]

Mon, 22 May 2017 06:07

Bill B
Messages: 1971
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;

Report message to a moderator

Re: Oracle Sequences [message #663095 is a reply to message #663089]

Mon, 22 May 2017 07:16

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.

Report message to a moderator

Re: Oracle Sequences [message #663096 is a reply to message #663095]

Mon, 22 May 2017 07:27

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Oracle Sequences [message #663098 is a reply to message #663095]

Mon, 22 May 2017 07:34

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

Report message to a moderator

Re: Oracle Sequences [message #663101 is a reply to message #663098]

Mon, 22 May 2017 07:37

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Oracle Sequences [message #663104 is a reply to message #663101]

Mon, 22 May 2017 07:39

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.

Report message to a moderator

Re: Oracle Sequences [message #663106 is a reply to message #663104]

Mon, 22 May 2017 07:44

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Oracle Sequences [message #663107 is a reply to message #663104]

Mon, 22 May 2017 07:45

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Oracle Sequences [message #663109 is a reply to message #663107]

Mon, 22 May 2017 08:25

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.

Report message to a moderator

Previous Topic:	need help in rectifying error
Next Topic:	Multiple rows based on column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:30:25 CDT 2024