Solved

Dynamic SQL return temp table results

Posted on 2011-02-14
13
896 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 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 76

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
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now