Home » SQL & PL/SQL » SQL & PL/SQL » inserting a row into a table (sql developer by oracle,windows7)
inserting a row into a table [message #442130] Thu, 04 February 2010 22:04 Go to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
hi every one i have problem with the below query.i am unable to insert data there is something wrong going on in that.

The main aim of the query is to insert the data from dialog box and for one column called userid the value should become combination of 1st letter of firstname and 1st 7letters of lastname with all letters in lower case.here is what i did.can any one suggest

insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name','lower(substr(&&first_name,1,1)||substr(&&last_name,1,7))',&sal ary);
Re: inserting a row into a table [message #442132 is a reply to message #442130] Thu, 04 February 2010 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
rarely, if ever, is sql developer used as User Interface or is this just a homework assignment

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: inserting a row into a table [message #442133 is a reply to message #442130] Thu, 04 February 2010 22:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
,&sal ary);

it will raise
ORA-00917: missing comma
.

space should be removed.

whats the data type for this...Quote:
userid
As you are inserting string.Check the datatype.

Re read your Query...Eliminate the errors By exucuting for one line....
Most of the members do ot so the home work for you.
As Blackswan suggeted...read the forum guide line from your next post follow them.

Goodluck
sriram Smile
Re: inserting a row into a table [message #442147 is a reply to message #442133] Fri, 05 February 2010 00:49 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
Its not a home work its a solution for a practice question while solving them i wrote my own query for a question this is the one.i am unable to understand whats wrong in this.i am using user interface oracle sql developer.here is what you need
there is another problem in this the dialog box for first name and last name are popping up twice even though i used && for 2nd first_name and last_name.

USERID VARCHAR2(8 )


query:
insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name','lower(substr(&&first_name,1,1)||substr(&&last_name,1,7))',&sal ary);


error:
old:insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name','lower(substr(&&first_name,1,1)||substr(&&last_name,1,6))',&sal ary)
new:insert into my_employee(id,last_name,first_name,userid,salary) values(6,'Mark','Anthony','lower(substr(Anthony,1,1)||substr(Mark,1,7))',1230)

Error starting at line 1 in command:
insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name','lower(substr(&&first_name,1,1)||substr(&&last_name,1,7))',&sal ary)
Error report:
SQL Error: ORA-12899: value too large for column "ORA6"."MY_EMPLOYEE"."USERID" (actual: 44, maximum: 8 )
12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"
*Cause: An attempt was made to insert or update a column with a value
which is too wide for the width of the destination column.
The name of the column is given, along with the actual width
of the value, and the maximum allowed width of the column.
Note that widths are reported in characters if character length
semantics are in effect for the column, otherwise widths are
reported in bytes.
*Action: Examine the SQL statement for correctness. Check source
and destination column data types.
Either make the destination column wider, or use a subset
of the source column (i.e. use substring).


[LF disabled smilies]

[Updated on: Fri, 05 February 2010 00:58]

Report message to a moderator

Re: inserting a row into a table [message #442148 is a reply to message #442147] Fri, 05 February 2010 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you not understand in Quote:
value too large for column "ORA6"."MY_EMPLOYEE"."USERID" (actual: 44, maximum: Cool
Re: inserting a row into a table [message #442149 is a reply to message #442147] Fri, 05 February 2010 00:55 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

can you please check the datatype declared for Userid. You may be trying to insert a number where you may have declared it as VARCHAR2. Try with a NUMBER datatype for USERID or insert 1 with single quote ie '1'.

Or you may be trying to insert a large number which is out of range for a NUMBER datatype.

[Updated on: Fri, 05 February 2010 00:57]

Report message to a moderator

Re: inserting a row into a table [message #442151 is a reply to message #442148] Fri, 05 February 2010 01:01 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
yes i understand that instead of 8character its getting more than that but,in the query it is clear shown i took sub-string for 8letter...and i made it to 6charaters also even then i am geting same error.
Re: inserting a row into a table [message #442152 is a reply to message #442147] Fri, 05 February 2010 01:03 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
If you examined the INSERT statement after substitution:
raghava205 wrote on Fri, 05 February 2010 07:49
new:
insert into my_employee(id,last_name,first_name,userid,salary)
values(6,'Mark','Anthony','lower(substr(Anthony,1,1)||substr(Mark,1,7))',1230)

you should resume whether you want to put into USERID this:
SQL> select length( 'lower(substr(Anthony,1,1)||substr(Mark,1,7))' ) from dual;

LENGTH('LOWER(SUBSTR(ANTHONY,1,1)||SUBSTR(MARK,1,7))')
------------------------------------------------------
                                                    44

1 row selected.

SQL> 
Seems to mi like misplaced quotes.
Re: inserting a row into a table [message #442153 is a reply to message #442151] Fri, 05 February 2010 01:04 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
the values are
id=6, last_name=Anthony, first_name= Mark, userid= manthony, salary=1230
Re: inserting a row into a table [message #442156 is a reply to message #442152] Fri, 05 February 2010 01:07 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
yes its length is 44 but it is a sub-query rite so the value after executing it will be inserted in to the table so the lenght of that value should be considered.but here it is taking sub-query as value.this is what i dint understand
Re: inserting a row into a table [message #442157 is a reply to message #442130] Fri, 05 February 2010 01:13 Go to previous messageGo to next message
John Watson
Messages: 6396
Registered: January 2010
Location: Global Village
Senior Member
Check out these, and your problem will become clear:
select 'lower(substr(&&first_name,1,1)||substr(&&last_name,1,7))' from dual;
select lower(substr('&&first_name',1,1)||substr('&&last_name',1,7)) from dual;
Re: inserting a row into a table [message #442158 is a reply to message #442156] Fri, 05 February 2010 01:14 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
raghava205 wrote on Fri, 05 February 2010 08:07
yes its length is 44 but it is a sub-query rite so the value after executing it will be inserted in to the table so the lenght of that value should be considered.but here it is taking sub-query as value.this is what i dint understand

Ehm, no. As the expression is enclosed with quotes, it is taken a string literal.
Just try to execute that statement I posted. Make changes, so it INSERTs the desired value. In the end, replace literals with substitution variables.
Re: inserting a row into a table [message #442161 is a reply to message #442157] Fri, 05 February 2010 01:19 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
when we give &&first_name and &&last_name in single quotes it will consider &&first_name and &&last_name as the string and it will take substring from that.
In main query i put single quotes to complete sub-query because userid value is a string it should be put in single quotes that is y.....
Re: inserting a row into a table [message #442162 is a reply to message #442158] Fri, 05 February 2010 01:20 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

Better you just copy & paste below query. Yopu have misplaced "'" in USERID value.if you add it for whole 'lower(.....)' it will consider as full string. If you place it lower('..') it will work as you are thinking.
insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name',lower(substr('&&first_name',1,1)||substr('&&last_name',1,6)),&salary);

[Updated on: Fri, 05 February 2010 01:22]

Report message to a moderator

Re: inserting a row into a table [message #442164 is a reply to message #442162] Fri, 05 February 2010 01:26 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&last_name','&first_name',lower(substr('&&first_name',1,1)||substr('&&last_name',1,6)),&s alary);


this query is working but,the problem in this is thhe first_name and last_name are asked twice......that means dialog box is popping up twice for each
Re: inserting a row into a table [message #442165 is a reply to message #442130] Fri, 05 February 2010 01:29 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
hey cool i got the solution this is the solution for it

insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&&last_name','&&first_name',lower(substr('&first_name',1,1)||substr('&last_name',1,7)),&s alary);
Re: inserting a row into a table [message #442167 is a reply to message #442130] Fri, 05 February 2010 01:30 Go to previous messageGo to next message
raghava205
Messages: 9
Registered: February 2010
Location: norfolk
Junior Member
THANK YOU FOR EVERY ONE FOR HELPING ME......
Re: inserting a row into a table [message #442169 is a reply to message #442164] Fri, 05 February 2010 01:32 Go to previous message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

Now try these. It wont ask twice.
insert into my_employee(id,last_name,first_name,userid,salary) values(&id,'&&last_name','&&first_name',lower(substr('&first_name',1,1)||substr('&last_name',1,6)),&salary);


remark: place & and && based on your requirement.
Previous Topic: Tuning Select statement
Next Topic: display query as per requirement
Goto Forum:
  


Current Time: Mon Sep 26 21:28:57 CDT 2016

Total time taken to generate the page: 0.09998 seconds