Create Table, insert values and then select those within stored procedure

Question

I have this stored procedure:

CREATE or replace PROCEDURE TESTx()

BEGIN



DECLARE tableOneCount INTEGER; 
DECLARE tableTwoCount INTEGER;
DECLARE strCmd VARCHAR(500);



SET tableOneCount = (SELECT COUNT(*) FROM proj);
SET tableTwoCount = (SELECT COUNT(*) FROM proj2);

SET msg = tableOneCount + tableTwoCount;

Create table tempa(name varchar(50), counter integer);
Insert into tempa(name, counter) values ('counter1', tableOneCount);
Insert into tempa(name, counter) values ('counter2', tableTwoCount);
Insert into tempa(name, counter) values ('counter_all', msg);

SET strCmd=(SELECT * FROM tempa);

EXECUTE IMMEDIATE(strCmd);

drop table tempa;

END @

It should just count the 2 tables add them and then return a table with the results. Since I haven't figured out another way to do it I create a temp table and insert everything there to then just return a select statement. Somehow though this is not working since the select statement is running into an error.

I tried using

declare global temporary table session.tempa
(name varchar(50), counter integer) 
on commit preserve rows not logged;

but that gives me the error, that the amount of rows and variables are not matching (SQL0117N)

Any idea on how to get this to work?

Rahul · Accepted Answer · 2014-10-15 10:10:16Z

1

You last statement (dynamic query formation is not correct) throwing error which should be like below

SET strCmd="SELECT * FROM tempa";
EXECUTE IMMEDIATE(strCmd);

Though you don't need to form a dynamic query statement here at all. You can just return the select like SELECT * FROM tempa.

Again your procedure can be shorten like

CREATE or replace PROCEDURE TESTx()
BEGIN

DECLARE tableOneCount INTEGER;
SET tableOneCount = (SELECT COUNT(*) FROM proj) + (SELECT COUNT(*) FROM proj2);

select 'counter1', count(*) from proj

union all

select 'counter2', COUNT(*) FROM proj2

union all 

select 'counter_all', tableOneCount from dual

END @

edited Oct 15, 2014 at 10:10

answered Oct 15, 2014 at 10:04

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thevagabond Over a year ago

I get an error on this that the select statement is unexpected and that it needs a <psm_labellable_stmt>.... when I put the select in a set something= clause and then execute immediate the variable it can't find dual.... any idea why? I need the whole table as a result... all 3 rows

Rahul Over a year ago

Read the answer properly. You don't need a prepared statement at all just do select * from tempa. That will do.

Thevagabond Over a year ago

I get SQL0104N from that: On ,msg);" follows the unexpected token "SELECT *" possible tokens are "<delete> The version you posted (the shortened one) gives the same error with the token problem: <psm_labellable_stmt>

Collectives™ on Stack Overflow

Create Table, insert values and then select those within stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related