Home » SQL & PL/SQL » SQL & PL/SQL » problem in dynamic sql
problem in dynamic sql [message #247748] Wed, 27 June 2007 02:04 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi,

I want to create a sql in my function in such a way;
.........
 sqlString varchar2(1000);
........
Begin 
sqlString  :=  ' SELECT * FROM  '|| v_tablename ||'  where USERNAME =' ||username;




in above code v_tablename, username are varchar2 variables that have been passed to this function.

USERNAME in targeted table is a varchar2 field, so i have to inclose

value of this field username into qoutes('').

So please teel me how can i do the same.

here is my function
create or replace
function fn_GetRecord  (
 v_tablename IN varchar2
 )
 return SYS_REFCURSOR
IS
v_return_cur  SYS_REFCURSOR;
sqlsrtin varchar2(1000);
username varchar2(100) := 'dsd';
BEGIN
sqlsrtin := ' SELECT * FROM  '|| v_tablename ||'  where USERNAME =' ||username;
  OPEN v_return_cur FOR sqlsrtin ;
 
  return v_return_cur;
END ;
Re: problem in dynamic sql [message #247753 is a reply to message #247748] Wed, 27 June 2007 02:15 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Post the error which you are getting when u try to execute this function.
Re: problem in dynamic sql [message #247758 is a reply to message #247753] Wed, 27 June 2007 02:30 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Connecting to the database test.
ORA-00904: "DSD": invalid identifier
ORA-06512: at "DBO_NUSECUREDB.FN_GETRECORD", line 12
ORA-06512: at line 7
Process exited.
Disconnecting from the database test.
Re: problem in dynamic sql [message #247761 is a reply to message #247748] Wed, 27 June 2007 02:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Do not hardcode the username value, use binding instead.
For correct syntax consult OPEN-FOR Statement in PL/SQL User's Guide and Reference.
Re: problem in dynamic sql [message #247762 is a reply to message #247758] Wed, 27 June 2007 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
During execution, your sqlstring becomes:
SELECT * FROM <the table you give> where USERNAME = dsd

So the error as dsd is not a valid identifier.
You want to generate (I think):
SELECT * FROM <the table you give> where USERNAME = 'dsd'
with the quotes.

Fix your "sqlString := ..." statement.

Regards
Michel
Re: problem in dynamic sql [message #247766 is a reply to message #247762] Wed, 27 June 2007 02:46 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
@Michel I know that problem is with my sqlstring.

can u tell me how to fix my sqlstring.
1. i have to use '' to do so.
2 but i am using string operation as append so how can i accumlate single qoutes('').
Re: problem in dynamic sql [message #247768 is a reply to message #247748] Wed, 27 June 2007 02:52 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
hi,

thanx i have done by using binding.

as below........

sqlsrtin := ' SELECT * FROM  '|| v_tablename ||'  where USERNAME =:1';
  OPEN v_return_cur FOR sqlsrtin using username;
Re: problem in dynamic sql [message #247771 is a reply to message #247748] Wed, 27 June 2007 03:03 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, this is the best solution I know.
Thank for feedback.
Previous Topic: sql Functions
Next Topic: Union problem
Goto Forum:
  


Current Time: Sat Dec 10 01:09:28 CST 2016

Total time taken to generate the page: 0.10412 seconds