Home » SQL & PL/SQL » Client Tools » SP2-0311: string expected but not found
SP2-0311: string expected but not found [message #269763] Mon, 24 September 2007 07:59 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
I have the following script



CREATE TABLE INFO ( 
  PRV_ID    NUMBER (6)    NOT NULL, 
  PRV_NAME  VARCHAR2 (32));

CREATE TABLE PRV_SERVICES_T ( 
  SERVICE_ID    NUMBER (6)    NOT NULL, 
  SERVICE_NAME  VARCHAR2 (32), 
  HELP_URL      VARCHAR2 (128));

CREATE TABLE PRV_SERVICES ( 
  PRV_ID      NUMBER (6)    NOT NULL, 
  SERVICE_ID  NUMBER (6) );



@abc_xyz_interface_provider.sql
SET DEFINE ON
SET ESCAPE \
SET VERIFY OFF
VARIABLE p_prv_name varchar2;
DEFINE p_default = 'ALL';

DECLARE
   n_Prv_id       info.Prv_id%TYPE;
   n_Srv_id       Prv_Services_t.Service_id%TYPE;
   n_Srv_name     Prv_Services_t.Service_name%TYPE;
  -- n_prv_name     info.prv_name%type := '&l_prv_name';
   n_help_url     prv_services_t.help_url%TYPE;

   cursor c_info is 
      select prv_id 
      from info 
      where prv_name in &l_prv_name
          or ('&p_default' in &l_prv_name and prv_name <> 'ttt')
      order by prv_id;
BEGIN
   -- ensure the service is defined in the prv_services_T table.
   BEGIN
      SELECT SERVICE_ID
        INTO n_Srv_ID
        FROM Prv_Services_t
       WHERE Service_ID = 92;          
   EXCEPTION
      WHEN OTHERS THEN
         INSERT INTO Prv_Services_T
                     ( SERVICE_ID, SERVICE_NAME, HELP_URL )
              VALUES (92, 'xyzInterface', NULL );
   END;

   -- ensure the service is defined in the prv_services table.
    FOR r_info IN c_info LOOP
     --- give downstream service : 92 for given provider or providers
        BEGIN
      SELECT PRV_ID, Service_ID
        INTO n_prv_ID, n_Srv_ID
        FROM Prv_Services
       WHERE Prv_id = r_info.prv_id 
         AND Service_ID = 92;
   EXCEPTION
      WHEN OTHERS THEN
         INSERT INTO Prv_Services
                     (Prv_id,
                      Service_ID
                     )
              VALUES (r_info.prv_id,
                      92
                     );
   END;
    END LOOP;
END;
/
commit;




in abc_xyz_interface_provider.sql, i have

define l_prv_name = ('eee')

when i run the above code, i get


SP2-0311: string expected but not found

PL/SQL procedure successfully completed.


Commit complete.


when i comment the set escape \, its working fine, can anyone
please tell me whats wrong? and why i am getting this error
when i remove the comment for set escape \?

Re: SP2-0311: string expected but not found [message #269773 is a reply to message #269763] Mon, 24 September 2007 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
define l_prv_name = ('eee')

SQL> define l_prv_name = ('eee')
SQL> def l_prv_name
DEFINE L_PRV_NAME      = "('eee')" (CHAR)

Works fine for me.

Quote:
when i comment the set escape \, its working fine,

As it is AFTER the previous what is the relation with it?

SQL> SET DEFINE ON
SQL> SET ESCAPE \
SQL> SET VERIFY OFF
SQL> VARIABLE p_prv_name varchar2;
SQL> DEFINE p_default = 'ALL';
SQL> 

All is working fine for me.

Post the whole "abc_xyz_interface_provider.sql" content.
Do you have a new line at the end?

Regards
Michel
Re: SP2-0311: string expected but not found [message #269861 is a reply to message #269763] Mon, 24 September 2007 21:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
when i comment the set escape \, its working fine, can anyone
please tell me whats wrong? and why i am getting this error
when i remove the comment for set escape \?


This is situation that I faced when I had 2 days starting learning oracle.The problem is because you tried to set two times "escape \".

The another one prior to current one is already set. Double setting just warned a message to you. You can simply close current session and then reenter the whole code you will see it's working fine.
Re: SP2-0311: string expected but not found [message #269867 is a reply to message #269763] Mon, 24 September 2007 22:34 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
@mike..

no, thats the exact code i have

@arju

i did not set it prior to this script? so where is the chance
for double setting?

Re: SP2-0311: string expected but not found [message #269871 is a reply to message #269763] Mon, 24 September 2007 22:51 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
so where is the chance
for double setting?

Even if you run too times a script and inside it escape / there.Then, Two times it will be set. So error will produce.
Re: SP2-0311: string expected but not found [message #269877 is a reply to message #269867] Mon, 24 September 2007 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no, thats the exact code i have

"no" is for what?
If it is an answer to my question: "Do you have a new line at the end?" then add this newline character. SQL*Plus does not like file that does not end with a newline character. It does not hurt but generates an error message (depending on the version).

Regards
Michel
Re: SP2-0311: string expected but not found [message #279493 is a reply to message #269861] Thu, 08 November 2007 15:38 Go to previous message
Ajai_Muraleedharan
Messages: 1
Registered: November 2007
Location: Nashua
Junior Member
Hi Arju..I also faced the same issue and your suggestion worked for me...thanks..
Previous Topic: Can't initialize OCI error occured during the login process using TOAD 9.0
Next Topic: New Page/Break on commands
Goto Forum:
  


Current Time: Sun Dec 04 02:35:06 CST 2016

Total time taken to generate the page: 0.07688 seconds