Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate
Execute Immediate [message #186316] Mon, 07 August 2006 07:58 Go to next message
mkwdrs
Messages: 4
Registered: August 2006
Junior Member
Why does this not work? (I'm only showing top portion of func)

FUNCTION Ret_Sequence_Number(pReleaseDate VARCHAR2)
RETURN VARCHAR2
IS
vCount PLS_INTEGER := 0;
vSeq_Num PLS_INTEGER := 0;
vReturn_Value VARCHAR2(11) := NULL;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(1) ' ||
'FROM CSTQA03.Large_Bid_Report ' ||
'WHERE Seq_Num LIKE (:ReleaseDate)'
INTO vCount
USING ''''||UPPER(pReleaseDate)||'%''';

The count never comes back correct. When I run the manual query, the results are correct.

If I do not use bind variable, then it works (see following)
FUNCTION Ret_Sequence_Number(pReleaseDate VARCHAR2)
RETURN VARCHAR2
IS
vCount PLS_INTEGER := 0;
vSeq_Num PLS_INTEGER := 0;
vReturn_Value VARCHAR2(11) := NULL;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(1) ' ||
'FROM CSTQA03.Large_Bid_Report ' ||
'WHERE Seq_Num LIKE ('''||UPPER(pReleaseDate)||'%'')'
INTO vCount;

Re: Execute Immediate [message #186333 is a reply to message #186316] Mon, 07 August 2006 09:30 Go to previous messageGo to next message
mkwdrs
Messages: 4
Registered: August 2006
Junior Member
I finally figured it out. This msg can be deleted if needed.
Re: Execute Immediate [message #186336 is a reply to message #186333] Mon, 07 August 2006 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Care to share the problem?
Re: Execute Immediate [message #186360 is a reply to message #186316] Mon, 07 August 2006 12:49 Go to previous messageGo to next message
mkwdrs
Messages: 4
Registered: August 2006
Junior Member
EXECUTE IMMEDIATE
'SELECT COUNT(1) ' ||
'FROM CSTQA03.Large_Bid_Report ' ||
'WHERE Seq_Num LIKE (:ReleaseDate)'
INTO vCount
USING UPPER(pReleaseDate)||'%';
Re: Execute Immediate [message #186474 is a reply to message #186360] Tue, 08 August 2006 02:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry for the colloquial use of English.
What I meant was 'Would you care to share the solution to the problem that you had posted, as I cannot, by inspection, spot what the problem is, and would be interested in knowing both the details of the cause of the problem and the solution you chose'
Previous Topic: Removal of special characters
Next Topic: Administrationquestion
Goto Forum:
  


Current Time: Sat Dec 03 10:15:53 CST 2016

Total time taken to generate the page: 0.08717 seconds