Home » SQL & PL/SQL » SQL & PL/SQL » String Literal Required (10.2.0.1.0, WIN XP)
String Literal Required [message #328687] Sat, 21 June 2008 10:28 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am trying to run this block but it gives me 'String Literal Required error'

Here is the code

  declare 
     a varchar2(30) := 'D:\'; 
     b varchar2(20); 
     stmt varchar2(100); 
  begin
     b := substr(a,1,1); 
    stmt := 'create directory '||b||' as '||'||a||'; 
    execute immediate stmt; 
    end;
  2  /


Quote:
declare a varchar2(30) := 'D:\'; b varchar2(20); stmt varchar2(100); begin b :=
substr(a,1,1); stmt := 'create directory '||b||' as'||'||a||'; execute immediate
stmt; end;
*
ERROR at line 1:
ORA-01780: string literal required
ORA-06512: at line 1



Can you spot the problem?

Re: String Literal Required [message #328688 is a reply to message #328687] Sat, 21 June 2008 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can you spot the problem?
I suspect that you are (ab)using some client other than sqlplus.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: String Literal Required [message #328689 is a reply to message #328687] Sat, 21 June 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want a quote inside a string you have to put it twice, for instance.

Have a look at "Text Literals" section in SQL Reference.

Regards
Michel
Re: String Literal Required [message #328699 is a reply to message #328687] Sat, 21 June 2008 16:54 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It is a good practice to use dbms_output.put_line to display the value of your sql statement (stmt), then copy and paste the result and test it in SQL*Plus. Notice the difference between the two statements displayed below and the results below that when you copy and paste and try to run them. It makes it easier to see where the problems are.

SCOTT@orcl_11g> declare
  2    a varchar2(30) := 'D:\';
  3    b varchar2(20);
  4    stmt varchar2(100);
  5  begin
  6    b := substr(a,1,1);
  7    stmt := 'create directory '||b||' as '||'||a||';
  8    dbms_output.put_line (stmt);
  9    stmt := 'create or replace directory ' || b ||' as ''' || a || '''';
 10    dbms_output.put_line (stmt);
 11  end;
 12  /
create directory D as ||a||
create or replace directory D as 'D:\'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> create directory D as ||a||
  2  /
create directory D as ||a||
                      *
ERROR at line 1:
ORA-01780: string literal required


SCOTT@orcl_11g> create or replace directory D as 'D:\'
  2  /

Directory created.

SCOTT@orcl_11g> 




Previous Topic: RUNSTATS Package Error
Next Topic: ORA-01438
Goto Forum:
  


Current Time: Sun Dec 11 03:57:02 CST 2016

Total time taken to generate the page: 0.07995 seconds