• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

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.

0
phoen08
Asked:
phoen08
  • 4
  • 4
  • 4
  • +1
3 Solutions
 
ajexpertCommented:
I see few problems here

1.  Input_date is VARCHAR2, so do you convert again TO_CHAR?
2.  You should use format when you say TO_CHAR(active_date)
3.  I prefer to use VARCHAR2 instead of dyn_str char(4000)

I have made an attempt to correct your code
CREATE OR REPLACE PROCEDURE PR_EE
(
Input_Date varchar2,
Out_results OUT sys_refcursor
)

AS 

dyn_str VARCHAR2(4000);

BEGIN
dyn_str := 'Create table TestTable as
SELECT distinct Emp_NBR
FROM Employee
WHERE  to_char(Active_Date,''MM/DD/YYYY'') >= '''||Input_Date||'''
';

dbms_output.put_line(dyn_str);
EXECUTE IMMEDIATE dyn_str;


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

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
riazpkCommented:
Checkout:

Result Sets from Stored Procedures In Oracle

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551171813078805685
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Now that I'm back on a real computer check out the following test below.
drop table tab1 purge;
create table tab1(emp_nbr number, active_date date);

insert into tab1 values(1,sysdate);
insert into tab1 values(2,sysdate-1);
commit;

create or replace procedure testproc 
(
  Input_Date in varchar2,
  Out_results OUT sys_refcursor
)
AS 
BEGIN
 open out_results for 
	SELECT distinct Emp_NBR
	FROM tab1
	WHERE Active_Date >= to_date(Input_Date,'MM/DD/YYYY');
END;
/

show errors

--now test it from sqlplus
var myCur refcursor

exec testproc('02/15/2011',:myCur);

print myCur

Open in new window

0
 
phoen08Author Commented:
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


0
 
ajexpertCommented:
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')
0
 
slightwv (䄆 Netminder) Commented:
>>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
0
 
phoen08Author Commented:
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

0
 
ajexpertCommented:
You should be using

OPEN Out_results FOR
Select * from TestTable;

No need to use dynamic sql

Open in new window

0
 
phoen08Author Commented:
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.
0
 
ajexpertCommented:
Please check slightwv's comment http://34896392 returning the records from the table

EXECUTE IMMEDIATE dyn_str will not return the result set
0
 
slightwv (䄆 Netminder) Commented:
>> from a table created with dynamic sql?

DO NOT CREATE A DYNAMIC TABLE.  If you are wanting a TEMP table like in other databases, this is not the way to do it in Oracle.

From what you posted, you don't need a dynamic table.

Try the code in http:#a34896392 (ajexpert had the link a little off...)
0
 
phoen08Author Commented:
Thanks, I was able to resolve the issue.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now