Improve company productivity with a Business Account.Sign Up

x
?
Solved

Dynamic SQL return temp table results

Posted on 2011-02-14
13
Medium Priority
?
910 Views
Last Modified: 2013-12-19
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
Comment
Question by:phoen08
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 668 total points
ID: 34892114
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
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34892319
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
 
LVL 13

Expert Comment

by:riazpk
ID: 34894095
Checkout:

Result Sets from Stored Procedures In Oracle

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551171813078805685
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 79

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1332 total points
ID: 34896392
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
 

Author Comment

by:phoen08
ID: 34897988
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 34898036
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
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34898371
>>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
 

Author Comment

by:phoen08
ID: 34901246
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 34901275
You should be using

OPEN Out_results FOR
Select * from TestTable;

No need to use dynamic sql

Open in new window

0
 

Author Comment

by:phoen08
ID: 34901347
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 34901510
Please check slightwv's comment http://34896392 returning the records from the table

EXECUTE IMMEDIATE dyn_str will not return the result set
0
 
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1332 total points
ID: 34902485
>> 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
 

Author Comment

by:phoen08
ID: 34939618
Thanks, I was able to resolve the issue.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question