Home » Developer & Programmer » JDeveloper, Java & XML » ORA-01780 string literal required error while fetching data from xml table. (11g )
ORA-01780 string literal required error while fetching data from xml table. [message #656982] Tue, 25 October 2016 01:39 Go to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

Hi all , I am working on xml table for first time.
Below is what i tried and details of error
which i got while fetching data from xml table.
Kindly help.

  1  create table jitender_xml
  2               (id number
  3*         ,data xmltype)
SQL> /

Table created.

  1  insert into jitender_xml
  2  values(1,xmltype('<jitender_xml>
  3  <Employee empid="1111" type="admin">
  4  <first_name>john</first_name>
  5  <last_name> cena</last_name>
  6  <age> 30 </age>
  7  <email_id> abc@gmail.com</email_id>
  8  </Employee>
  9* </jitender_xml>'))
SQL> /

1 row created.

  1  insert into jitender_xml
  2  values(2,xmltype('<jitender_xml>
  3  <Employee empid="2222" type="boss">
  4  <first_name>dwayne</first_name>
  5  <last_name> johnson</last_name>
  6  <age> 37 </age>
  7  <email_id> ddd@gmail.com</email_id>
  8  </Employee>
  9* </jitender_xml>'))
SQL> /

1 row created.

  1  insert into jitender_xml
  2  values(3,xmltype('<jitender_xml>
  3  <Employee empid="3333" type="manager">
  4  <first_name>steve</first_name>
  5  <last_name> austin</last_name>
  6  <age> 40 </age>
  7  <email_id> sa@gmail.com</email_id>
  8  </Employee>
  9  <Employee empid="4444" type="nri">
 10  <first_name>khali</first_name>
 11  <last_name> singh</last_name>
 12  <age> 35 </age>
 13  <email_id> ks@gmail.com</email_id>
 14  </Employee>
 15* </jitender_xml>'))
SQL> /

1 row created.

SQL> select * from jitender_xml
  2  /

        ID DATA
---------- -----------------------------------------------
         1 <jitender_xml>
           <Employee empid="1111" type="admin">
           <first_name>john</first_name>
           <last_name> cena</last_name>
           <age> 30 </age>
           <email_id> abc@gmail.com</email_id>
           </Employee>
           </jitender_xml>

         2 <jitender_xml>
           <Employee empid="2222" type="boss">
           <first_name>dwayne</first_name>
           <last_name> johnson</last_name>
           <age> 37 </age>
           <email_id> ddd@gmail.com</email_id>
           </Employee>
           </jitender_xml>

         3 <jitender_xml>
           <Employee empid="3333" type="manager">
           <first_name>steve</first_name>
           <last_name> austin</last_name>
           <age> 40 </age>
           <email_id> sa@gmail.com</email_id>
           </Employee>
           <Employee empid="4444" type="nri">
           <first_name>khali</first_name>
           <last_name> singh</last_name>
           <age> 35 </age>
           <email_id> ks@gmail.com</email_id>
           </Employee>
           </jitender_xml>



  1  SELECT j.id, x.*
  2       FROM jitender_xml j,
  3            XMLTABLE ('/jitender_xml/Employee'
  4                      PASSING j.data
  5                      COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6                              lastname VARCHAR2(30) PATH 'last_name') x
  7*     WHERE j.id = 1
SQL> /
                                                  *
ERROR at line 5:
ORA-01780: string literal required

[Updated on: Tue, 25 October 2016 01:40]

Report message to a moderator

Re: ORA-01780 string literal required error while fetching data from xml table. [message #656983 is a reply to message #656982] Tue, 25 October 2016 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me:
SQL> SELECT j.id, x.*
  2  FROM jitender_xml j,
  3  XMLTABLE ('/jitender_xml/Employee'
  4  PASSING j.data
  5  COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6  lastname VARCHAR2(30) PATH 'last_name') x
  7  WHERE j.id = 1
  8  /
        ID FIRSTNAME                      LASTNAME
---------- ------------------------------ ------------------------------
         1 john                            cena

1 row selected.

SQL> @v

Oracle version: 11.2.0.4.0
You need to provide your version number with 4 decimals.

Re: ORA-01780 string literal required error while fetching data from xml table. [message #656984 is a reply to message #656983] Tue, 25 October 2016 02:13 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

though we use 11g and 12c , but we are not allowed to do any dml/ddl operations on that.
i was doing testing on my local pc which has 10g installed with version no. 10.2.0.5.0 .
Re: ORA-01780 string literal required error while fetching data from xml table. [message #656985 is a reply to message #656984] Tue, 25 October 2016 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did it in 10.2.0.4 and it works:
SQL> SELECT j.id, x.*
  2  FROM jitender_xml j,
  3  XMLTABLE ('/jitender_xml/Employee'
  4  PASSING j.data
  5  COLUMNS firstname VARCHAR2(30) PATH 'first_name',
  6  lastname VARCHAR2(30) PATH 'last_name') x
  7  WHERE j.id = 1
  8  /
        ID FIRSTNAME                      LASTNAME
---------- ------------------------------ ------------------------------
         1 john                            cena

1 row selected.

SQL> @v

Oracle version: 10.2.0.4.0
Re: ORA-01780 string literal required error while fetching data from xml table. [message #656986 is a reply to message #656985] Tue, 25 October 2016 02:32 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

i dont now why but it is not working for me Sad
Re: ORA-01780 string literal required error while fetching data from xml table. [message #656987 is a reply to message #656986] Tue, 25 October 2016 02:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which OS? I remember Barbara and I had different behaviors between Windows and Linux on 10g.
As your real versions are 11g and 12c, I advise you download and install one of these versions on your local PC.

Previous Topic: Loading XML in Oracle table
Next Topic: Calling Java function from pl/sql
Goto Forum:
  


Current Time: Thu Mar 28 17:59:03 CDT 2024