How to test a statement result in Oracle 10g?

I'm trying to execute those SQL statements with Oracle 10g  and get an error in the second  statement:

Declare vProcessDt varchar2(12);
vProcessDt:= '4/16/2010';
 
SELECT
'SELECT  E.MEM_NO           
  FROM  UHP_ELIG  where  A.CREATE_DT = to_date('''|| vProcessDt ||''', ''mm/dd/yyyy'')';


divteamAsked:
Who is Participating?
 
ajexpertConnect With a Mentor Commented:
I am not sure why did you put SELECT twice.
My suggestions:
1.  Date should be properly formated   i.e. '04/16/2010'
2. Remove SELECT statment if not requried
3. You have to put the proper aliasis i.e. A.E.
 
I have put DBMS_STATMENT so that you can execute the query
4.  Put EXECUTE IMMEDIATE once you test the query (commented in the code)



Declare 

vProcessDt varchar2(12);

  
v_sql    VARCHAR2(4000);

BEGIN


vProcessDt:= '04/16/2010';

V_SQL:= 

'
SELECT  E.MEM_NO          
  FROM  UHP_ELIG  where  A.CREATE_DT = to_date('''|| vProcessDt ||''', ''mm/dd/yyyy'')';
  
  dbms_output.put_line(v_sql);
  
  --EXECUTE IMMEDIATE (v_sql);
  
  end;

Open in new window

0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
You forgot to put dual or table name in the end


SELECT
'SELECT  E.MEM_NO
 FROM  UHP_ELIG  where  A.CREATE_DT = to_date('''|| sysdate ||''', ''mm/dd/yyyy'')' from dual
0
 
divteamAuthor Commented:
I actually didn't in the SQL I executed there was ... from tblname.
The error I get is  ORA-065500 : line 2
encountered symbol "="  
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shru_0409Commented:
Declare
vProcessDt varchar2(12) := '16-04-2010';
v_sql varchar2(100);
Begin

v_sql := 'SELECT  E.MEM_NO FROM  UHP_ELIG A where  to_char(A.CREATE_DT,'''||'dd-mm-yyyy'||''') = '''||vProcessDt||'''';

try this ...
but i have some doubt about your query in select e.mem_no and table name alise is differ..
can u paste your full query.....
0
 
ajexpertCommented:
shru_0409:
Can you explain what is the difference between your comment and mine?
0
 
shru_0409Commented:
i m using to_char and u to_date...
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> The error I get is  ORA-065500 : line 2 encountered symbol "="  

As per your code , you are assigning the values in Declare block rather than Begin thats why you are getting  ORA-065500 error at line 2.

To reproduce this error, do the following.

Declare
  vProcessDt varchar2(12);
  vProcessDt:= '4/16/2010';
begin
  dbms_output.put_line(to_date(vProcessDt, 'mm/dd/yyyy'));
end;
/

0
 
ajexpertCommented:
shru_0409:
It needs to be converted to DATE format,  as it is already VARCHAR2;
virdi_ds:
I believe everything is incoroporated in my comments and code
 
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
I am sorry, I hadn't read all comments.
0
 
ajexpertCommented:
No problem
0
 
divteamAuthor Commented:
The whole point was for me to see the sql  string , before I put it inside a proc and execute it without knowing what it is. I worked with SQL server before and could do that easily.
Now where do I actually read the sql string?
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
You can use DBMS_OUTPUT.PUT_LINE(STR) inside the procedure. Also you need to set serveroutpu on use the following
SET SERVEROUTPUT ON
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.