Solved

Need help with SP

Posted on 2009-07-08
6
236 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now