Notifications
Clear all
Topic starter
Hi,
I am an experienced SQL Server programmer. As the source of a Crystal report I want to create a stored procedure that
1. asks for 2 parameters. startdate and enddate
2. Creates a temporary table
3. Runs some SQL to populate that table based on the 2 parameters
4. Returns the output of the stored procedure to crystal
I have done this hundreds of times in SQL Server. Oracle gives me an error message I don't understand with only a few lines of the stored procedure coded.
Can somebody tell me what I need to do?
Thanks,
Dick
The stub of my stored procedure follows:
CREATE OR REPLACE PROCEDURE MU_SEC_MSGS_SENT_BY_PROVIDER (startdate date, enddate date)
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE PROV_NPI_NUM_DENOM
(NPI VARCHAR2(10),
SEARCHNAME VARCHAR2(78),
NO_OF_PTS_SEC_MSG_SENT INT,
NO_DISTINCT_PTS_SEEN INT);
--sql to populate table would go here
SELECT * FROM PROV_NPI_NUM_DENOM;
END;
The error message I am getting is
- Error(6,1): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
Can somebody explain to me why it's complaining?
Posted : September 12, 2016 4:43 am
Topic starter
Although it's probably obvious, I forgot to mention that I am going against the EMR, so it is Oracle that is compolaining.
Thanks,
Dick
Posted : September 12, 2016 4:45 am
Topic starter
Hi,
Here's some more info. I tried the code
CREATE GLOBAL TEMPORARY TABLE PROV_NPI_NUM_DENOM
(NPI VARCHAR2(10),
SEARCHNAME VARCHAR2(78),
NO_OF_PTS_SEC_MSG_SENT INT,
NO_DISTINCT_PTS_SEEN INT);
–sql to populate table would go here
SELECT * FROM PROV_NPI_NUM_DENOM;
outside of a stored procedure and it works the way I would have expected.
Dick
Posted : September 12, 2016 5:43 am
What about using a CTE instead?
Posted : September 13, 2016 10:33 am
My understanding is that Oracle stored procedures do not work the same way as SQL stored procedures in that they do not return multiple columns/rows. You might take a look here:
http://stackoverflow.com/questions/101033/how-to-return-multiple-rows-from-the-stored-procedure-oracle-pl-sql
That said, I usually end up writing my Crystal reports as views with the attributes I would have sent in set as return values. I then use crystal parameters to set the value of those attributes.
Posted : September 13, 2016 11:35 am
I was looking at some examples of stored procedures for crystal reports here:
http://stackoverflow.com/questions/1803318/pl-sql-procedure-and-a-crystal-report
http://www.pdssoftware.com/newsletter/mar07/page6.htm
From their syntax it looks like what you need to do is create a temporary table (it is used as a template and will never be used from what I can tell) and then create a Reference cursor using the temp table and finally create a stored procedure using the reference cursor.
It looks like what you were trying to do in the code you pasted is create a temporary table as part of the stored procedure. Create the temp table first, then a reference cursor, then the stored procedure.
Alternatively you can do what I do and write Views. I have some very complicated views that have a lot of functionality. One of them is 300 lines long and that is after I cut it down by referencing some other views that I wrote as well as custom functions. As long as I use indexes, the views run quickly.
If you describe what data you are trying to pull into the report I might be able to provide you some further direction.
Posted : September 14, 2016 4:44 am
