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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ajexpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.