jrara jrara - 4 years ago 543
SQL Question

SQLPlus: ORA-01756: quoted string not properly terminated

I'm trying to update a table using SQLPlus in Oracle. My update statement is as follows, but I'm getting errors on this:

UPDATE mytable
SET mycol =
'
CREATE OR REPLACE function stg.myfun(i_run_id number, i_rec number default 0) return number as
begin
insert into stg.tab (col1,col2,col3,col4,col5,col6,col7)
select src.col1, src.col2, i_run_id, src.col4,''myval'', src.s1, src.s2
from stg.sourcetab src
order by col1;
return SQL%ROWCOUNT;
exception WHEN OTHERS THEN
IF SQLCODE = -1
THEN
if i_rec>15
then raise;
else return stg.myfun(i_run_id, i_rec+1);
end if;
ELSE
raise;
end if;
end;
'
where 1 = 1
and gn = 'value';
commit;


Errors are:

ERROR:
ORA-01756: quoted string not properly terminated

SP2-0734: unknown command beginning "return SQL..." - rest of line ignored.
SP2-0734: unknown command beginning "exception ..." - rest of line ignored.
SP2-0734: unknown command beginning "IF SQLCODE..." - rest of line ignored.
SP2-0042: unknown command "THEN" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0734: unknown command beginning "if i_rec>1..." - rest of line ignored.
SP2-0042: unknown command "then raise" - rest of line ignored.
SP2-0734: unknown command beginning "else retur..." - rest of line ignored.
SP2-0042: unknown command "end if" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0042: unknown command "ELSE" - rest of line ignored.
SP2-0042: unknown command "raise" - rest of line ignored.
SP2-0042: unknown command "end if" - rest of line ignored.
SP2-0042: unknown command "end" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0042: unknown command "'" - rest of line ignored.
SP2-0734: unknown command beginning "where 1 = ..." - rest of line ignored.
SP2-0734: unknown command beginning "and gn..." - rest of line ignored.

Answer Source

One way could be by wrapping your statement within a simple PL/SQL block:

SQL> begin
  2  UPDATE mytable
  3     SET mycol =
  4            '
  5  CREATE OR REPLACE function stg.myfun(i_run_id number, i_rec number default 0) return number as
  6  begin
  7   insert into stg.tab (col1,col2,col3,col4,col5,col6,col7)
  8    select src.col1, src.col2, i_run_id, src.col4,''myval'', src.s1, src.s2
  9      from stg.sourcetab src
 10     order by col1;
 11   return SQL%ROWCOUNT;
 12  exception WHEN OTHERS THEN
 13   IF SQLCODE = -1
 14    THEN
 15     if i_rec>15
 16     then raise;
 17     else return stg.myfun(i_run_id, i_rec+1);
 18     end if;
 19    ELSE
 20     raise;
 21   end if;
 22  end;
 23  '
 24  where 1 = 1
 25  and gn = 'value';
 26  end;
 27  /

PL/SQL procedure successfully completed.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download