Home » SQL & PL/SQL » SQL & PL/SQL » Insert statement with bind variable and Create statement (Oracle 10.1)
Insert statement with bind variable and Create statement [message #664381] Fri, 14 July 2017 15:01 Go to next message
jaggub
Messages: 1
Registered: July 2017
Junior Member
Helpers,
I have two problems.

1.

I am facing problem while using insert statement with bind variable.
After inserting one row, the new and old line is truncated from right side.
Last few characters are truncated.
I tried set linesize option with bigger value but it is not working.

Help me out because I need to copy new line with data into a file.

2.

I am facing problem with create statement.
I am typing create command on sql prompt. I am typing one column specification per line.
Now while giving large text in one line, sometimes it wrapped to next line.
While giving check constraint for one column,
For e.g.
check (status in ('aaaaa','bbbbbb','cccccccc','dddd
dd'))

last 2 character of dddddd wraped to next line.

It doesn't give error in creation but at the time of inserting data in status column, dddddd value gives constraint violation error.

Is it required to write whole word in line?

Thanks in advance.
Re: Insert statement with bind variable and Create statement [message #664383 is a reply to message #664381] Fri, 14 July 2017 18:53 Go to previous messageGo to next message
BlackSwan
Messages: 25741
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

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

don't tell us what you see happens.
SHOW us using COPY & PASTE

We don't know what client software you are abusing.

Re: Insert statement with bind variable and Create statement [message #664384 is a reply to message #664381] Fri, 14 July 2017 19:19 Go to previous message
Barbara Boehmer
Messages: 8777
Registered: November 2002
Location: California, USA
Senior Member
1. Are you doing this from SQL*Plus or from something else like SQL*Developer or Toad? If from SQL*Plus, then it may be do to some setting. Please post the result of SHOW ALL.

2. You cannot split any identifier or value between lines. If you do so, then it stores the line feed as part of the value, so values without the linefeed are rejected and values with the linefeed are accepted. Please see the demonstration below that shows that.

SCOTT@orcl_12.1.0.2.0> create table test_tab
  2    (status	varchar2(8),
  3  check (status in ('aaaaa','bbbbbb','cccccccc','dddd
  4  dd')))
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into test_tab values ('dddddd')
  2  /
insert into test_tab values ('dddddd')
*
ERROR at line 1:
ORA-02290: check constraint
(SCOTT.SYS_C0034101) violated


SCOTT@orcl_12.1.0.2.0> insert into test_tab values ('dddd
  2  dd')
  3  /

1 row created.
Previous Topic: Count Distinct Not Working in Case Select Oracle SQL
Next Topic: Case statement in From Clause
Goto Forum:
  


Current Time: Mon Dec 11 01:08:46 CST 2017

Total time taken to generate the page: 0.01309 seconds