Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Oracle - Questions re stored procedure, Global Temporary Tables

CPS, C-EMR, PM

Last Post by Anonymous 1 second ago

6 Posts

3 Users

0 Reactions

90 Views

RSS

dr2706

(@dr2706)

Posts: 3

Active Member

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

dr2706

(@dr2706)

Posts: 3

Active Member

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

dr2706

(@dr2706)

Posts: 3

Active Member

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

StephenAdamsMD

(@stephenadamsmd)

Posts: 23

Eminent Member

What about using a CTE instead?

Posted : September 13, 2016 10:33 am

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

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

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

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

24 Forums
9,942 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed