Home » SQL & PL/SQL » SQL & PL/SQL » Long Sql used for Ref Cursor - Getting Invalid Identifier error
Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277800] Wed, 31 October 2007 12:32 Go to next message
tots
Messages: 2
Registered: August 2000
Junior Member
I am trying to send a long sql to a ref cursor, but I am getting Invalid Identifier error.

The reason for passing a dynamic sql is that depending on a condition i am changing the where clause in the sql statement

l_query1 := 'SELECT distinct ' || l_study_no || ' ,a.BASELINE_NUMBER,a.ALLOCATION_NUMBER,a.AGE, a.GENDER
FROM TBL1 a inner join TBL2 b ON a.product_name = ''' || p_prod_nm || ''')'

if UPPER(substr(p_sc, 1)) = 'V' then
l_query1 := l_query1 || ' OR NVL(A.hos, ''L'') != NVL(B.hos, ''K''))' ;
else
l_query1 := l_query1 || ' OR NVL(A.cos, ''L'') != NVL(B.cos,''K''))' ;
end if;

Open cur for l_query1
Loop
Fetch cur in currec;


Please help me out withis and let me know if you need some more inputs.

Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277801 is a reply to message #277800] Wed, 31 October 2007 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Read & Follow Posting Guidelines as stated here: http://www.orafaq.com/forum/t/88153/0/

Print out SQL statement BEFORE invoking EXECUTE IMMEDIATE.
CUT & PASTE the statement into SQL*Plus & post complete results back here using <code tags>
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277846 is a reply to message #277800] Wed, 31 October 2007 19:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
you said you are getting an error. why did you not post the error message?
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277872 is a reply to message #277846] Thu, 01 November 2007 00:39 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

Quote:

''' || p_prod_nm || ''')'
and also avoid using this in your query.it may create problems for you.


regards,
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277903 is a reply to message #277872] Thu, 01 November 2007 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why might it cause problems?
It looks ok to me.

I think the problem is unmatched brackets.
There is a ')' without a matching '(' at the end of the initial assignation of l_query1, and both the optional appends to l_query1 have one more ')' than they do '('
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277906 is a reply to message #277872] Thu, 01 November 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Until you format as Ana said you, we can't see if you wrote " or '', for instance.

Regards
Michel

[Updated on: Thu, 01 November 2007 02:34]

Report message to a moderator

Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277908 is a reply to message #277906] Thu, 01 November 2007 02:47 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

won't it be subjected to sql injection.may be i m wrong.

regards,
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277909 is a reply to message #277908] Thu, 01 November 2007 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to follow the guidelines and repost in a proper way?

Regards
Michel
Re: Long Sql used for Ref Cursor - Getting Invalid Identifier error [message #277961 is a reply to message #277908] Thu, 01 November 2007 05:42 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
dhananjay wrote on Thu, 01 November 2007 08:47

hi,

won't it be subjected to sql injection.may be i m wrong.

regards,

You are correct. Always be careful pasting contents of variables directly into sql statements.
Previous Topic: CONCATINATION MULTIPLE COULMN VALUES
Next Topic: how to insert ?
Goto Forum:
  


Current Time: Sat Nov 02 13:19:13 CDT 2024