Solved

Dynamic SQL return temp table results

Posted on 2011-02-14
13
902 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 167 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 333 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 333 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 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