?
Solved

Dynamic SQL return temp table results

Posted on 2011-02-14
13
Medium Priority
?
903 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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
Technology Partners: 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!

 
LVL 77

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 77

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 77

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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