Solved

Need help with SP

Posted on 2009-07-08
6
241 Views
Last Modified: 2013-12-19
I've moved to Oracle from sybase and am having a hard time grasping the temporary table and stored procedure concept. Why does this not work?
create or replace procedure test()
--create a temp table to hold my final results
create global temporary table final_results(
id varchar2(8),  
id_num number,
comment varchar2(100),
comment2 varchar2 (10),
total_num  number);
 
-- insert some stuff into the final results
insert into final_results(id,id_num,comment,comment2)
with
Letter as (
  blah.blah blah..),
... I do a bunch of stuff inbetween and it works fine ... then end my with with a select
 select id,id_num,comment,comment2 from all;
 
 
--declare a variable to store a value
DECLARE
    a NUMBER;
BEGIN
    SELECT count(employee_id) INTO a FROM employee;
    
    update final_results set total_num = a;
 END;
-- get my results from the final_results table
    select * from final_results
-- drop my temp table
    drop table final_resul;ts;

Open in new window

0
Comment
Question by:cobolinx1
[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
  • 3
  • 3
6 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24808129
global temporary tables are meant to be created beforehand, once.

You use them in each session, and by default, once the transaction is over, the rows are cleared for your next use of the table.

Each user / session gets their own view of the table that does not interfere with other sessions.

No need to create / drop it in a procedure, that is point of a global temp table.

0
 

Author Comment

by:cobolinx1
ID: 24813057
Ok I took that out and but it still compiles with errors. I added a SYS_REFCURSOR because I need to return a table
create or replace procedure test RETURN SYS_REFCURSOR
IS
  result SYS_REFCURSOR;
-- insert some stuff into the final results
insert into final_results(id,id_num,comment,comment2)
with
Letter as (
  blah.blah blah..),
... I do a bunch of stuff inbetween and it works fine ... then end my with with a select
 select id,id_num,comment,comment2 from all;
 
 
--declare a variable to store a value
DECLARE
    a NUMBER;
BEGIN
    SELECT count(employee_id) INTO a FROM employee;
    
    update final_results set total_num = a;
    open result for  select * from Ident_NonIdent;
    return result;
 
 END;
   

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24813437
Sorry, the code you are posting has a mix of SQL and comments (blah blah blah is not code) so  cannot really tell what to fix. Please post actual code and/or add comment characters to clarify what your comments are so I can differentiate.

You should use "show errors" to see the errors after a compile.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:cobolinx1
ID: 24827003
To mask it will really take a long time but the guts of the with is not the problem. If I run it seperately its fine. It seems like if It runs to the end of the with and then stops on the declare. Is there something like a go in oracle? Do I have to put the with instide of the begin block, or make two blocks? If I change the bottom part to this:


DECLARE
    a NUMBER;
BEGIN
    SELECT count(employee_id) INTO a FROM employee;
   
    update final_results set total_num = a;
 END;
select * from final_results

and highlight everything from insert into final_results down it works fine. When I put in its a procedure and try to return results it compiles but with exceptions.

do you call the show errors at the end or does that have to go in a block?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24827037
show errors is a sqlplus command that you run after compiling a procedure. It shows errors from the

SQL> show errors

It essentially is a shortcut for selecting from USER_ERRORS which is populated when you compile.
0
 

Author Closing Comment

by:cobolinx1
ID: 31601315
I changed how I was doing the stored procedure
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

Suggested Solutions

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…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

752 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