We help IT Professionals succeed at work.

Need help with SP

cobolinx1
cobolinx1 asked
on
Medium Priority
267 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

Comment
Watch Question

Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Top Expert 2009

Commented:
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.

Author

Commented:
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?
Top Expert 2009

Commented:
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.

Author

Commented:
I changed how I was doing the stored procedure
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.