?
Solved

Need help with SP

Posted on 2009-07-08
6
Medium Priority
?
248 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
  • 3
  • 3
6 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1500 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

592 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