OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Problem with Apex and primary key

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Problem with Apex and primary key

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

Problem with Apex and primary key [message #533552]

Wed, 30 November 2011 09:14

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: Problem with Apex and primary key [message #533554 is a reply to message #533553]

Wed, 30 November 2011 09:23

lio1972
Messages: 17
Registered: November 2011

Junior Member

select seq_s_sid.nextval from dual;

NEXTVAL
----------
1

Report message to a moderator

Re: Problem with Apex and primary key [message #533556 is a reply to message #533554]

Wed, 30 November 2011 09:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

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 1849 times)

Report message to a moderator

Re: Problem with Apex and primary key [message #533562 is a reply to message #533560]

Wed, 30 November 2011 09:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Problem with Apex and primary key [message #533563 is a reply to message #533562]

Wed, 30 November 2011 09:56

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

Report message to a moderator

Re: Problem with Apex and primary key [message #533565 is a reply to message #533563]

Wed, 30 November 2011 10:03

cookiemonster
Messages: 13962
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.

Report message to a moderator

Re: Problem with Apex and primary key [message #533578 is a reply to message #533565]

Wed, 30 November 2011 11:12

Littlefoot
Messages: 21823
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?)

Report message to a moderator

Re: Problem with Apex and primary key [message #533715 is a reply to message #533578]

Thu, 01 December 2011 03:20

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?

Report message to a moderator

Re: Problem with Apex and primary key [message #533716 is a reply to message #533715]

Thu, 01 December 2011 03:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It should be there. You shouldn't delete it.

Report message to a moderator

Previous Topic:	Send mail using UTL_TCP
Next Topic:	ORA-00942: Table or view does not exist

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 07 10:56:05 CST 2025