asked on

Need help with SP

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

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cobolinx1

ASKER

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

mrjoltcola

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.

cobolinx1

ASKER

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?

mrjoltcola

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.

cobolinx1

ASKER

I changed how I was doing the stored procedure