Solved

Need help with SP

Posted on 2009-07-08
6
240 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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

756 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