Архив форумов ЦИТФорума

[Legus]

Подскажите пожалуйста новичку!
Как пользоваться временными таблицами и можно ли создавать их в хранимой процедуре, делать SELECT из них в процедуре.
В Microsoft SQLServer это делается элементарно, а в ORACLE что-то не получается, а мне это нужно для отчетов в Crystal Reports.

[flint]

Example 2-1 Creating a Session-Specific Temporary Table
CREATE GLOBAL TEMPORARY TABLE ...
[ON COMMIT PRESERVE ROWS ]

Example 2-2 Creating a Transaction-Specific Temporary Table
CREATE GLOBAL TEMPORARY TABLE ...
[ON COMMIT DELETE ROWS ]

[Legus]

Спасибо, я это знаю, меня интересовало, можно ли в теле хранимой процедуры создавать временную таблицу?
Все дело в том, что данные в этой таблицы я вижу в одной коннекции, если я открою другую, то увижу пустую таблицу, а Crystal Report при создании отчета, как раз окрывает свою коннекцию.

[Shestakov]

In first, I'm sorry about Engish, i can't type for russian.
Answer:
Yes you can create global temoraty tables
in store procedures.
In my mind you have problems with priviligies.
If you going to use GTT in store procedures you MUST have :
CREATE TABLE or CREATE ANY TABLE system privileges pass to you directly, not thru roles.

second problem is:
create global temporary table - command is part
of sql (not pl/sql) language
then u MUST use dynamic sql for create GTT objects in database.

Example:
create or replace
function New_Tree_Table(p_tbl varchar2 default 'TMP_TREE',
p_own varchar2 default 'SLIDEADM') return boolean is
str varchar2(2000);
begin
str := 'DROP GLOBAL TEMPORARY TABLE '||p_own||'.'||p_tbl;
begin
execute immediate str;
exception when others then null;
end;
--
str := 'CREATE GLOBAL TEMPORARY TABLE :OWN.:TBL '||
'( ID_NODE NUMBER, ID_ROOT NUMBER, '||
' CATALOG_BOM NUMBER, XR_BOM NUMBER, T_NODE CHAR(1) )' ;
execute immediate str using p_own, p_tbl;
return Recreate_Tree(p_tbl, p_own, 'I');
exception
when OTHERS then return false;
end;
/


a lot of oracle information you can find
on dbasupport.com

[Legus]

Спасибо за информацию, теперь меня мучает еще один вопрос: Может ли хранимая процедура в ORACLE вернуть SELECT из этой же временной таблицы. То есть я хотел сказать, если я создал таблицу в хранимой процедуре, потом заполнил данными, могу ли я вернуть из этой же процедуры содержимое этой таблицы?

[Shestakov]

This is more interesting question.
It depend from:
1. What exactly you need?
2. Do you know set of fields of temporary table or not?

1. variants:
1.1 -- you going to get set of rows and u know list of fields.
U can use collections with ref cursors:

-------------------------------------------
type t_rc is record (
filed_1 ...,
filed_2 ...,
..
field_n);
type t_tc is table of rc index by binary_integer;
type t_cursor IS REF CURSOR;

cr t_cursor;
inst_tc t_tc;
..
i pls_integer := 1;
begin
..
open cr for 'select ... from ur_gtt_name ...';
loop
FETCH cr INTO
inst_tc[i],field_1,
inst_tc[i],field_2 ... ;
EXIT WHEN cr%NOTFOUND;
-- process row here
END LOOP;

-------------------------------------------
1.2 -- you need only 1 row from table

execute immediate 'select ...' into a,b,c ..;

2.1 If u don't know structure of the table?
(only table_name and owner)
this is a little bit more interesting.

u probably can't use dynamic sql directly.
u should use package dbms_sql.

Stages:
declare

i_sql integer default dbms_sql.open_cursor;

begin

-- create select from oracle dictionary
str := 'select ';
sep := '';
for dd_rec in (select * from all_tab_columns where owner = '.UPPER.' and table_name = '..UPPER..') loop
str := str || sep || dd_rec.colunm_name;
sep := ',';
end loop;

str := str||' from '||....;

dbms_sql.parse( i_sql, str, DBMS_SQL.NATIVE);
j := 0;
for i in 1..1000 loop -- man # of columns in oracle
begin
j := j + 1;
dbms_sql.define_column(i_sql,i,c_value,4000);
exception when others then exit;
end;
end loop;

k := dbms_sql.execute(i_sql);

loop
-- process per rows
exit when (dmms_sql.fetch_rows(i_sql)

[Legus]

Спасибо за информацию. Попробу с этим помутить, может выйдет то, что мне нужно.