I've written my first table function and my first stored procedure. I think
I have a clear idea this is a lot different from Microsoft SQL Server. I
have a few things I can't seem to figure out that I hope some of you can
shed some light on.


- My procedure signature is CREATE OR REPLACE PROCEDURE FOO.BAR (IN
ROW_ID BIGINT DEFAULT 0). I can use table.ID=ROW_ID in my WHERE clauses in
the SELECT statements in my SQL just fine. However, I cannot pass it to a
tabular stored procedure that looks like this. What am I doing wrong:

DECLARE GLOBAL TEMPORARY TABLE BAR_LOAD AS (
SELECT * FROM TABLE(FOO.BARBYID(ROW_ID)) a
)
WITH DATA
WITH REPLACE;
How do pass a parameter?

- I verified by experiment and the docs that global temporary tables are
global to the job (and therefore JDBC connection if I'm using SQL
workbench. Is there any way to scope a temporary tables to just inside a
stored procedure? If not, is there some keyword to make a stored procedure
an atomic transaction so I don't have to worry about two jobs calling the
same stored procedure at the same time and overwriting my stored procedure?
- I seem to gather that outside of a stored procedure variables are only
global to the database instance. Is there some way to declare a variable
that is only visible to a job?

Is there a section of the 7.2 manuals besides the SQL Statement reference,
or a redbook I should be reading that would tell me these things? Is there
a good crash course in writing stored procedures with all these gotchas for
someone coming from other RDBMSes?

Justin

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].