temp table scope
Hi,
the following code generates ora-942 error.
DECLARE
v_cnt NUMBER;
BEGIN
SELECT Count(*) INTO v_cnt FROM user_tables WHERE table_name = 'SEG';
IF v_cnt = 0 THEN
EXECUTE IMMEDIATE ('CREATE GLOBAL TEMPORARY TABLE seg (name VARCHAR2(30)) ON COMMIT DELETE ROWS');
END IF;
INSERT INTO seg SELECT segment_name FROM user_segments WHERE ROWNUM < 3;
EXECUTE IMMEDIATE('drop table seg');
END;
The global temp table was created but it is invisible to the plsql block. Why?
Thanks,
Sha
the following code generates ora-942 error.
DECLARE
v_cnt NUMBER;
BEGIN
SELECT Count(*) INTO v_cnt FROM user_tables WHERE table_name = 'SEG';
IF v_cnt = 0 THEN
EXECUTE IMMEDIATE ('CREATE GLOBAL TEMPORARY TABLE seg (name VARCHAR2(30)) ON COMMIT DELETE ROWS');
END IF;
INSERT INTO seg SELECT segment_name FROM user_segments WHERE ROWNUM < 3;
EXECUTE IMMEDIATE('drop table seg');
END;
The global temp table was created but it is invisible to the plsql block. Why?
Thanks,
Sha
0