Ora 01756 ошибка - Не ошибается лишь тот, кто ничего не делает!

ORA-01756

ORA-01756: строка в кавычках не завершена правильно

Причина:

Строка в кавычках должна быть завершена одной кавычкой (‘).

Действие:

Вставьте закрывающую кавычку, затем выполните оператор снова.

I keep getting the following message every time I try to run this code in oracle. The code is as follows:

DROP TABLE movie;
CREATE TABLE movie (movie_id NUMBER(5)  PRIMARY KEY,
title VARCHAR2(45) NOT NULL,
description VARCHAR2(250) NOT NULL,
released_by NUMBER(3) NOT NULL,
released_on DATE NOT NULL);

INSERT INTO movie (movie_id, title, description, released_by, released_on)VALUES ('1', 'Edge of Tomorrow', 'Lieutenant Colonel Bill Cage is a skilled tactician who has honed his abilities through his experiences as a soldier. However, there is still much he can learn, and soon he is going to get his chance.', '1', '07-OCT-2014');

INSERT INTO movie (movie_id, title, description, released_by, released_on)VALUES('2', 'Captain America: Winter Soldier', 'Steve Rogers is finding it difficult to adjust to living life in the contemporary world. He is working for S.H.I.E.L.D. and begins to suspect a mystery is brewing there.', '2', '09-SEP-2014');

INSERT INTO movie (movie_id, title, description, released_by, released_on)VALUES('3', 'Fed Up', 'America’s problem with obesity is caused by our inactivity. Or is it? Katie Couric and Stephanie Soechtig tempt us to restructure our beliefs about the American diet, through this thought-provoking expose.', '3', '09-SEP-2014');

INSERT INTO movie (movie_id, title, description, released_by, released_on)VALUES('4', 'Godzilla', 'The legendary tale of Godzilla comes roaring back to life. This time, it's the modern era, and Godzilla is a giant lizard who has been made fearsome through the interference of radiation.', '1', '16-SEP-2014');

INSERT INTO movie (movie_id, title, description, released_by, released_on)VALUES('5', 'Neighbors', 'New parents Mac and Kelly settle into domesticity in a quiet neighborhood. The tranquility they have been afforded ceases to exist when a fraternity moves into the house next door.', '2', '14-SEP-2014');

COMMIT;

Below is what I get when I try to execute it in Oracle:

Table dropped.


Table created.


1 row created.


1 row created.


1 row created.

ERROR:
ORA-01756: quoted string not properly terminated



1 row created.


Commit complete.

SQL>

Any help would be greatly appreciated. Thank you.

Источник

The ORA-01756: quoted string not correctly terminated issue occurs when one of the surrounding single quotes in the character, string, or date value is missing. In Oracle, the string value, character value, and date value are all wrapped by a single quote mark. If any of the single quote marks is missing, the string will not be properly terminated. The error message ORA-01756: quoted string not correctly terminated will be shown.

If either the opening or closing single quotations are absent, or if any single quote is inserted inside the string, the error will occur. If a single quote is discovered, Oracle will consider it to be the beginning of a string and another single quotation to be the end of a string. Oracle will throw an error ORA-01756: quoted string not correctly terminated if the closing quote is not found.

When this ORA-01756 error occurs

You attempted to run a statement that included a string that was not enclosed by two single quotes. One of the quotes was entered without the second quote that accompanied it. Oracle will display an error message if the beginning or closing single quotes are missing, or if a single quote is inserted in between two strings.

insert into emp values (1, 'kim);

Error starting at line : 8 in command -
insert into emp values (1, 'kim);
Error at Command Line : 8 Column : 28
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Root Cause

A string will be recognized by Oracle if it is enclosed by single quotes. The string should begin and end with a single quotation mark. Oracle could not end a string if one of the single quotes was missing. The error message ORA-01756: quoted string not properly terminated will be displayed if the string is not properly ended.

Solution 1

Oracle does not recognize strings that do not begin with a single quotation and end with a single quote. The end of a single quotation is regarded as the start of a string. Oracle will recognize a string with unexpected content. The absence of a single quotation results in an unidentifiable string.

Problem

insert into emp values (1, kim');

Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Solution

insert into emp values (1, 'kim');
1 row inserted.

Solution 2

If the string begins with a single quotation and does not end with a single quote, Oracle does not recognize it as a string. Even if the string is terminated, the content will continue to be recognized. Oracle will examine the string with additional content until it reaches the next single quotation. As a consequence, unexpected content will be created.

Problem

insert into emp values (1, 'kim);

Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Solution

insert into emp values (1, 'kim');
1 row inserted.

Solution 3

If a string has a single quotation in between, the single quote is treated as the string’s end. The string in between will be truncated as a result of this. If a string contains a single quotation, the quote must be escaped. The single quotation will be escaped by putting another single quote after it. Oracle will identify two single quotes in a string as a single quote in a string.

Problem

insert into test values (1, 'This's very good');

Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Solution

insert into test values (1, 'This''s very good');
1 row inserted.

Solution 4

The error ORA-01756: quoted string not properly terminated occurs in select statements while identifying the string. If the string contains a missed quotes, the error will occur.

Problem 1

select * from emp where name = 'kim;
Error report -
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Solution

select * from emp where name = 'kim';

Solution 5

This is an another example using the select statement.

Problem

select 'Emp id = ||id from emp;

ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"

Solution

select 'Emp id = '||id from emp;

Источник

please let me know the issue with following script (sql,oracle 10g)

  1  DECLARE @colname AS NVARCHAR(50)
  2  DECLARE @tablename AS NVARCHAR(500)
  3  DEClARE @query AS NVARCHAR(500)
  4  SET @colname = 'select wk_units1 from cnt_sls_dm.fct_sales_summary'
  5  SET @tablename = 'SELECT tablename from dmi_user.fct_sales_meta'
  6  set @query='select '+@colname+' FROM '+@tablename+'
  7* EXECUTE sp_executesql @query
SQL> /
ERROR:
ORA-01756: quoted string not properly terminated

OMG Ponies

324k80 gold badges520 silver badges499 bronze badges

asked Sep 2, 2009 at 12:04

This error is quite self-describing, you have an unterminated quote.

You are trying to run an SQL Server code in Oracle. This won’t work.

You cannot just turn T-SQL into PL/SQL by mere copying.

I corrected the syntax, but most probably you will need much more work than that.

DECLARE
    colname NVARCHAR2(50);
    tname   NVARCHAR2(500);
    query   NVARCHAR2(500);
BEGIN
    SELECT  wk_units1
    INTO    colname
    FROM    cnt_sls_dm.fct_sales_summary;
    SELECT  tablename
    INTO    tname
    FROM    dmi_user.fct_sales_meta;
    query := 'SELECT ' || colname || ' FROM ' || tname;
END;

answered Sep 2, 2009 at 12:10

QuassnoiQuassnoi

411k91 gold badges613 silver badges613 bronze badges

Change line 6 to

set @query='select '+@colname+' FROM '+@tablename

answered Sep 2, 2009 at 12:05

D’Arcy RittichD’Arcy Rittich

166k40 gold badges288 silver badges283 bronze badges

The 6th line seems not correct in your example:

set @query='select '+@colname+' FROM '+@tablename+'

You finish the line with a '. Either you remove the +', either you finish your request with a where statement…

answered Sep 2, 2009 at 12:08

Romain LinsolasRomain Linsolas

79.1k49 gold badges200 silver badges272 bronze badges

This looks a lot like tSql rather than pl SQl, You might want to use || to concatenate strings in Oracle and varchar2 instead of nvarchar

answered Sep 2, 2009 at 12:15

Jon SpokesJon Spokes

2,5892 gold badges18 silver badges20 bronze badges

following is the correct way to answer the question……..
running fine……….
thanks to all who helped……

-Irveen

DECLARE
     type fct_sales_summary_cur is table of dmi_user.fct_sales_summary_cmp_1%rowtype index by binary_integer;
     cur_rec fct_sales_summary_cur;
            colname NVARCHAR2(50);
            tname   NVARCHAR2(500);
            query   VARCHAR2(500);
            cnt     number:=1;
    BEGIN
            loop
            SELECT  colname
            INTO    colname
            FROM    dmi_user.FCT_SALES_META
            where   sno=cnt;
            SELECT  tablename
            INTO    tname
            FROM    dmi_user.fct_sales_meta
            WHERE sno=cnt;
            --query:='select * from dmi_user.fct_sales_summary_cmp';
            query := 'SELECT '|| colname ||' FROM '||tname;
            -- dbms_output.put_line(colname);
            -- dbms_output.put_line(tname);
            --dbms_output.put_line(query);
            execute immediate query bulk collect into cur_rec;
            --dbms_output.put_line(cur_rec);
            dbms_output.put_line('------Table-Sno -----' || cnt);
            for i in cur_rec.first..cur_rec.last loop
            dbms_output.put_line(cur_rec(i).wk_units1);
            end loop;
            cnt:=cnt+1;
    exit when cnt=4;
    end loop;
    END;
/

answered Sep 3, 2009 at 10:47

Irveen Irveen

1051 gold badge4 silver badges9 bronze badges

Источник

Posted on

2018-09-14

Database

Encounter ORA-01756 error when insert data

Solve ORA-01756 error when insert data

Problem encountered

When insert the big text into database like html, developer break the lines and concat using ||.

There’s no problem run such script in Oracle SQL Developer, but encounter ORA-01756: Quoted String Not Properly Terminated error when execute script using SQLPlus.

Solution

Generally ORA-01756 caused by tried to execute a statement that contained a string that was not surrounded by two single quotes. One of the quotes was entered without the second accompanying quote. like the script as below:

1
2

insert into posamezna_zival(ID_zivali, Datum_rojstva, Spol, Namestitev, Mati, Oce, Prihod, Odhod, Sorta_FK, Kupec_FK) 
values ('SI 9267 9903', 1.4.2010, 'M', 'hlev 4', 'SI 42144700', 'SI 707005', 1.4.2010, 'XXX', 3,  'XXX);

But it may also cause by big insert/update script in one block, for such case we should use BEGIN … END Compound-Statement Syntax to solve such problem.

 DECLARE
   tempimg BLOB;
   tempdir BFILE:=BFILENAME('TEST_DIR','green.jpg');
   BEGIN
   INSERT INTO TEST01 VALUES ('green.jpg',EMPTY_BLOB()) RETURNING CONTENT INTO TEMPIMG;
   DBMS_LOB.FILEOPEN(tempdir);
   DBMS_LOB.LOADFROMFILE(tempimg,tempdir,DBMS_LOB.GETLENGTH(tempdir));
   DBMS_LOB.FILECLOSE(tempdir);
   COMMIT;
   END;
   /

Источник