Home » SQL & PL/SQL » SQL & PL/SQL » Handeling special charater '&' in oracle stored procedure (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Handeling special charater '&' in oracle stored procedure [message #582928] Tue, 23 April 2013 08:35 Go to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Hello Experts,

How to handle special charater '&' in stored procedure.

Following is a test code I want to Implement

CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS

BEGIN

INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
WHERE
startdate >= &pDateFirst
AND startdate <= &pDatelast ;

END ;
/
The code above resuts into INVALID state of stored procedure and cannot be executed.

Your suggestions will be greatly appreciated.

Thanks
Re: Handeling special charater '&' in oracle stored procedure [message #582930 is a reply to message #582928] Tue, 23 April 2013 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58479
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove &.

In addition you have a parameter "QueryID" that you don't use/

Regards
Michel
Re: Handeling special charater '&' in oracle stored procedure [message #582931 is a reply to message #582930] Tue, 23 April 2013 08:46 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
Thanks a lot for the reply.

I have to include '&'. Isn't there a way to successfully compile statemets like this?

I also tried chr(38) but doesn't work.
Re: Handeling special charater '&' in oracle stored procedure [message #582933 is a reply to message #582931] Tue, 23 April 2013 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 22470
Registered: January 2009
Senior Member
>I have to include '&'.
WHY?

They are being passed in as DATE arguments.
Re: Handeling special charater '&' in oracle stored procedure [message #582937 is a reply to message #582931] Tue, 23 April 2013 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58479
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to include '&'. Isn't there a way to successfully compile statemets like this?


No, you have not or your procedure is completly silly.

Regards
Michel
Re: Handeling special charater '&' in oracle stored procedure [message #582939 is a reply to message #582937] Tue, 23 April 2013 09:44 Go to previous messageGo to next message
averion
Messages: 42
Registered: January 2009
Location: US
Member
The codes poseted earlier was just an example to help understand the issue.

Fields '&DueryID','&Dates' actually represnt some special inputs and are used in the Dynamic SQL later in the procedure.

There has to be a way to include special characters in the Stored procedure.

Thanks a lot for your input guys!
Re: Handeling special charater '&' in oracle stored procedure [message #582940 is a reply to message #582939] Tue, 23 April 2013 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 22470
Registered: January 2009
Senior Member
>Fields '&DueryID','&Dates' actually represnt some special inputs and are used in the Dynamic SQL later in the procedure.
your imagination exceeds your capabilities.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

use of Bind Variable is better choice for affecting WHERE clause values
Re: Handeling special charater '&' in oracle stored procedure [message #582942 is a reply to message #582928] Tue, 23 April 2013 10:28 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi averion,

Please read here about Dynamic SQL. http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/dynamic.htm
You have different way to pass value in Dynamic sql.

But why you want to use substitution variable in PL/SQL code.


Thanks.
Re: Handeling special charater '&' in oracle stored procedure [message #582944 is a reply to message #582939] Tue, 23 April 2013 10:29 Go to previous message
Michel Cadot
Messages: 58479
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Fields '&DueryID','&Dates' actually represnt some special inputs and are used in the Dynamic SQL later in the procedure.


This is what parameters are for.
I think you do not know the basics in PL/SQL (or any programming language).
I advice you to FIRST read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: what is error in below code?
Next Topic: How to use a function
Goto Forum:
  


Current Time: Tue Jul 22 04:25:58 CDT 2014

Total time taken to generate the page: 0.12108 seconds