Home » SQL & PL/SQL » SQL & PL/SQL » Statements within variable,
Statements within variable, [message #330425] Mon, 30 June 2008 03:00 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I am trying to execute the following code in a package

var := 'select substr(psPobVendorShipmentRAID,
                                 instr(','||psPobVendorShipmentRAID||',', ',', 1, rn),
                                 instr(','||psPobVendorShipmentRAID||',', ',', 1, rn+1)
                                 - instr(','||psPobVendorShipmentRAID||',', ',', 1, rn) - 1) COLUMN_VALUE
                                 from (select rownum rn from dual 
                                 connect by level <= length(psPobVendorShipmentRAID)-length(replace(psPobVendorShipmentRAID,',',''))+1)';
                                 
OPEN p_cursor for var;
return p_cursor;
close p_cursor;


I am trying to execute the query in the variable.

However as the quotation marks are not placed properly, I am getting the following error.

Compilation errors for PACKAGE BODY MASYS.RTLGSCLAIMITEMDETAILS

Error: PLS-00103: Encountered the symbol "," when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

Could you please advise?

Thanks,

Sharath

[Updated on: Mon, 30 June 2008 03:15]

Report message to a moderator

Re: Statements within variable, [message #330427 is a reply to message #330425] Mon, 30 June 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to put a quote in a string you have to double it.

Regards
Michel
Re: Statements within variable, [message #330428 is a reply to message #330425] Mon, 30 June 2008 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is that to embed a ' inside a string, you have to use ''

Try this, and see if it works:
  var := 'select substr(psPobVendorShipmentRAID,
                                 instr('','''||psPobVendorShipmentRAID||''','', '','', 1, rn),
                                 instr('','''||psPobVendorShipmentRAID||''','', '','', 1, rn+1)
                                 - instr('','''||psPobVendorShipmentRAID||''','', '','', 1, rn) - 1) COLUMN_VALUE
                                 from (select rownum rn from dual 
                                 connect by level <= length(psPobVendorShipmentRAID)-length(replace(psPobVendorShipmentRAID,'','',''''))+1)';
Re: Statements within variable, [message #330482 is a reply to message #330428] Mon, 30 June 2008 04:56 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
thank you very much! it works perfectly...
Re: Statements within variable, [message #330490 is a reply to message #330425] Mon, 30 June 2008 05:04 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you are in 10g or above you could use something like this mentioned in the link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617

Regards

Raj
Previous Topic: collection iterator pickler fetch
Next Topic: Is it possibe to Update 2 or more table in single query ?
Goto Forum:
  


Current Time: Thu Dec 08 10:41:49 CST 2016

Total time taken to generate the page: 0.17618 seconds