Link to home
Start Free TrialLog in
Avatar of phoen08
phoen08

asked on

Dynamic SQL return temp table results

Hey guys,  I'm new to Oracle and having trouble returning results from a stored procedure created in SQL developer.  I'm not sure if it is possible to create a table in dynamic SQL and have results returned from that table in the same procedure.  I receive "invalid SQL statement" whenever I run the procedure in sql developer.  

Here is the procedure:
(
Input_Date varchar2,
Out_results OUT sys_refcursor
)

AS 

dyn_str char(4000);

BEGIN
dyn_str := 'Create table TestTable as
SELECT distinct Emp_NBR
FROM Employee
WHERE  to_char(Active_Date) >= to_char('''||Input_Date||''')
';
EXECUTE IMMEDIATE dyn_str;

dyn_str:= 'OPEN Out_results FOR
Select * from TestTable';
EXECUTE IMMEDIATE dyn_str;
END;

Open in new window


Any help is appreciated, thanks.

SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You should not create tables in PLSQL like this.  What if two people execute this code at the same time?

Just open the cursor for the select statement itself.  No need for the testtable.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of phoen08

ASKER

Thanks for all of the good suggestions.  I still have a few questions/issues.  I'm having issues due to invalid dates being stored in the date column.  The column type is varchar2.   I'm using the to_char function instead of the to_date function for this reason.  I receive ORA-01481:invalid number format model when using to_char and ORA-01847  day of month must be between 1 and last day of month when using to_date.  Is there anyway to correctly compare the varchar dates and handle any exceptions that are caused by invalid dates?

WHERE Active_Date >= to_char(Input_Date,'YYYYMMDD');
WHERE Active_Date >= to_date(Input_Date,'YYYYMMDD');

Open in new window


You are passing input_date as character...

so no need of WHERE Active_Date >= to_char(Input_Date,'YYYYMMDD');

Instead, you need to

--assuming you are passing input date in the format as YYYYMMDD
WHERE Active_Date >=TO_DATE(Input_Date, 'YYYYMMDD')
>>to_char(Input_Date,'YYYYMMDD');

input_date is already a string so trying to convert it back to a string will cause an error.

>> I'm having issues due to invalid dates being stored in the date column.

To do anything with true date comparisons, you will need to FIX the bad data.

What is the stored format of the dates in this column?  

If it is something like: YYYYMMDD (for example today is stored as 20110215 ), then just pass input_date in with that format and forget conversion to dates.

WHERE Active_Date >= input_date
Avatar of phoen08

ASKER

I'm having trouble with the dynamic sql statement that returns the results.  I receive "ORA-00900 invalid SQL statement" on the line where I'm returning results from a table created with dynamic sql. I assume that I have to use dynamic sql to return results from a dynamically created table in the same stored procedure?   The code that I'm working on is going to be added to larger stored procedure that creates tables with dynamic sql.
dyn_str:= 'OPEN Out_results FOR
Select * from TestTable';
EXECUTE IMMEDIATE dyn_str;

Open in new window

You should be using

OPEN Out_results FOR
Select * from TestTable;

No need to use dynamic sql

Open in new window

Avatar of phoen08

ASKER

Can I use that statement to return results from a table created with dynamic sql?  The table that holds the results is created in dynamic sql in the same procedure.
Please check slightwv's comment http://34896392 returning the records from the table

EXECUTE IMMEDIATE dyn_str will not return the result set
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of phoen08

ASKER

Thanks, I was able to resolve the issue.