Home » Developer & Programmer » Application Express & MOD_PLSQL » Problem with Apex and primary key
Problem with Apex and primary key [message #533552] Wed, 30 November 2011 09:14 Go to next message
lio1972
Messages: 17
Registered: November 2011
Junior Member
Hi I am having problem with my primary key when I try to make a insert form in Apex SID (the primary key) is hidden,and it is giving me the null primary key error
What could be my problem
Thanks


desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(9)
FNAME NOT NULL VARCHAR2(10)
LNAME NOT NULL VARCHAR2(10)
STREET NOT NULL VARCHAR2(30)
CITY VARCHAR2(10)
PIN VARCHAR2(10)
DOB DATE
SEX VARCHAR2(6)
CATEGORY VARCHAR2(10)
NATIONALITY VARCHAR2(10)
SMOKER VARCHAR2(10)
SPECIAL_NEEDS VARCHAR2(10)
CURRENT_STATUS VARCHAR2(10)
COMMENTS VARCHAR2(10)
LEASE_NUMBER NUMBER(9)
S_SSN NUMBER(10)


PROCEDURE 
insert_student (s_sid number,f_fname  varchar2, l_lname varchar2, 
s_street varchar2,c_city varchar2, p_pin varchar2, d_dob date, 
s_sex varchar2,c_category varchar2,n_nationality varchar2, 
s_smoker varchar2, s_special_needs varchar2, c_current_status varchar2,
c_comments varchar2, l_lease_number number, s_s_ssn number)
IS
begin
insert into student values(seq_s_sid.nextval, f_fname, l_lname,
 s_street, c_city, p_pin, d_dob , s_sex, c_category,n_nationality,
 s_smoker, s_special_needs, c_current_status, c_comments,
 l_lease_number,s_s_ssn );

end insert_student;

[Updated on: Wed, 30 November 2011 09:18]

Report message to a moderator

Re: Problem with Apex and primary key [message #533553 is a reply to message #533552] Wed, 30 November 2011 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

select seq_s_sid.nextval from dual;

post results from SQL above
Re: Problem with Apex and primary key [message #533554 is a reply to message #533553] Wed, 30 November 2011 09:23 Go to previous messageGo to next message
lio1972
Messages: 17
Registered: November 2011
Junior Member
select seq_s_sid.nextval from dual;

NEXTVAL
----------
1
Re: Problem with Apex and primary key [message #533556 is a reply to message #533554] Wed, 30 November 2011 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
does below work any better?
PROCEDURE Insert_student (s_sid            NUMBER,
                          f_fname          VARCHAR2,
                          l_lname          VARCHAR2,
                          s_street         VARCHAR2,
                          c_city           VARCHAR2,
                          p_pin            VARCHAR2,
                          d_dob            DATE,
                          s_sex            VARCHAR2,
                          c_category       VARCHAR2,
                          n_nationality    VARCHAR2,
                          s_smoker         VARCHAR2,
                          s_special_needs  VARCHAR2,
                          c_current_status VARCHAR2,
                          c_comments       VARCHAR2,
                          l_lease_number   NUMBER,
                          s_s_ssn          NUMBER)
IS
  new_val NUMBER;
BEGIN
  new_val := SELECT seq_s_sid.nextval FROM DUAL;

  INSERT INTO student
  VALUES     (new_val,
              f_fname,
              l_lname,
              s_street,
              c_city,
              p_pin,
              d_dob,
              s_sex,
              c_category,
              n_nationality,
              s_smoker,
              s_special_needs,
              c_current_status,
              c_comments,
              l_lease_number,
              s_s_ssn );
END insert_student;  

[Updated on: Wed, 30 November 2011 09:29]

Report message to a moderator

Re: Problem with Apex and primary key [message #533560 is a reply to message #533556] Wed, 30 November 2011 09:47 Go to previous messageGo to next message
lio1972
Messages: 17
Registered: November 2011
Junior Member
Thanks
This is working the same like mine( i replaced it any way)
If I try to insert something from the command line is OK
the problem is when I am try to insert something from created from me APEX form
then I have
ORA-01400: cannot insert NULL into ("GUEORGUL"."STUDENT"."SID")
  • Attachment: Promary.JPG
    (Size: 87.60KB, Downloaded 59 times)
Re: Problem with Apex and primary key [message #533562 is a reply to message #533560] Wed, 30 November 2011 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
privileges acquired via ROLE do NOT apply within named PL/SQL procedures
direct GRANT must be issued to USER
which schema is running the procedure?
which schema owns the procedure?
which schema owns the SEQUENCE?
which schema owns tables involved?
Re: Problem with Apex and primary key [message #533563 is a reply to message #533562] Wed, 30 November 2011 09:56 Go to previous messageGo to next message
lio1972
Messages: 17
Registered: November 2011
Junior Member
Thanks
I am new with Apex ,and I am lost right now
Could you explain ,if you can add snapshots will be great
Thanks
Re: Problem with Apex and primary key [message #533565 is a reply to message #533563] Wed, 30 November 2011 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why would you think roles are part of the problem with that error?
If OP doesn't have select on the sequence they'll get a sequence does not exist error.

I would suspect the culprit is either:
a) triggers
b) there's another bit of code also inserting into the same table and it's really that that is giving the error.
Re: Problem with Apex and primary key [message #533578 is a reply to message #533565] Wed, 30 November 2011 11:12 Go to previous messageGo to next message
Littlefoot
Messages: 19329
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What calls your INSERT_STUDENT procedure?

It appears that default Apex processing inserts a record before you do (i.e. your code is never executed). If you used a wizard (I guess you did), is there any "GET_PK" process there?

What did you choose as a default primary key source while creating that page? Wizard suggests a sequence, an existing database trigger or offers you to create a new one. Depending on that choice, certain code is being created.

Perhaps you should start over and do that carefully (perhaps even reading some documentation that'll guide you through the page creation). (By the way, which Apex version do you use?)
Re: Problem with Apex and primary key [message #533715 is a reply to message #533578] Thu, 01 December 2011 03:20 Go to previous messageGo to next message
abarnybox
Messages: 49
Registered: November 2011
Member
I'm having the same problem,
I selected "existing sequence" in the wizard and selected the sequence and yes there is a GET_PK process, should there not be? shall I delete it?
Re: Problem with Apex and primary key [message #533716 is a reply to message #533715] Thu, 01 December 2011 03:23 Go to previous message
Littlefoot
Messages: 19329
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It should be there. You shouldn't delete it.
Previous Topic: Send mail using UTL_TCP
Next Topic: ORA-00942: Table or view does not exist
Goto Forum:
  


Current Time: Tue Jul 29 20:17:58 CDT 2014

Total time taken to generate the page: 0.09173 seconds