Home » Other » General » temp tables in PL/SQL
temp tables in PL/SQL [message #103246] Wed, 19 March 2003 19:15 Go to next message
Rohini
Messages: 13
Registered: December 2001
Junior Member
The following code is within my stored proc. Why do I get ORA-00911: invalid character message at the Execute Immediate line?

/* Collect resource id's that are part of Blended Agt */
temptb_1 := 'CREATE GLOBAL TEMPORARY TABLE ResourcesinSetsofSetType107
(call_resource_id NUMBER(12),
call_resource_set_id NUMBER(12),
name VARCHAR2(40),
CONSTRAINT ResourcesinSetsofSetType107_pk
PRIMARY KEY (call_resource_id)
) ON COMMIT DELETE ROWS;
INSERT INTO ResourcesinSetsofSetType107
(cres2set.call_resource_id, creset.call_resource_set_id, creset.name)
SELECT cres2set.call_resource_id, creset.call_resource_set_id, creset.name
FROM call_resource2set cres2set,
call_resource_set creset
WHERE cres2set.call_resource_set_id = creset.call_resource_set_id
AND creset.resource_set_type_id = 107
ORDER BY 1';
--COMMIT';
EXECUTE IMMEDIATE temptb_1;

Appreciate the help. Thanks!

Report message to a moderator

Re: temp tables in PL/SQL [message #103333 is a reply to message #103246] Fri, 13 June 2003 00:28 Go to previous messageGo to next message
VB
Messages: 6
Registered: June 2003
Junior Member
Hi Rohini,

I was able to create the procedure, please
see the text attached. You need to use different
Execute immediate for the different SQL's.

Create or replace procedure joshi as
temptb_1 varchar2(4000);
temp2 varchar2(4000);

Begin
/* Collect resource id's that are part of Blended Agt */

temptb_1 := 'CREATE GLOBAL TEMPORARY TABLE ResourcesinSetsofSetType107(call_resource_id NUMBER(12),
call_resource_set_id NUMBER(12),
name VARCHAR2(40),
CONSTRAINT ResourcesinSetsofSetType107_pk
PRIMARY KEY (call_resource_id))
ON COMMIT DELETE ROWS';

EXECUTE IMMEDIATE temptb_1;

temp2 := 'INSERT INTO ResourcesinSetsofSetType107
(call_resource_id,call_resource_set_id, name)
select 1,2,''aabc'' from dual';

EXECUTE IMMEDIATE temp2;

--SELECT cres2set.call_resource_id, creset.call_resource_set_id, creset.name
--FROM call_resource2set cres2set, call_resource_set creset
--WHERE cres2set.call_resource_set_id = creset.call_resource_set_id
--AND creset.resource_set_type_id = 107
--ORDER BY 1';
End;
/

Take care,
Vibhu Bahl

Report message to a moderator

Re: temp tables in PL/SQL [message #103334 is a reply to message #103333] Fri, 13 June 2003 10:54 Go to previous messageGo to next message
Rohini
Messages: 13
Registered: December 2001
Junior Member
Thanks Vibhu, I will try this. It may also solve my problem of only needing these tables during the session, not beyond.

Report message to a moderator

Re: temp tables in PL/SQL [message #103335 is a reply to message #103334] Fri, 13 June 2003 16:51 Go to previous messageGo to next message
Rohini
Messages: 13
Registered: December 2001
Junior Member
Here is my stored proc logic. My question is how do I get rid of the global temp tables before the next time the proc is run?

/* Collect resource id's that are part of Blended Agt */
temptb_1 := 'CREATE GLOBAL TEMPORARY TABLE RESOURCESINSETSOFSETTYPE107
(CALL_RESOURCE_ID NUMBER(12),
CALL_RESOURCE_SET_ID NUMBER(12),
NAME VARCHAR2(40))
ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE temptb_1;

sql_stmt := 'INSERT INTO RESOURCESINSETSOFSETTYPE107 (SELECT cres2set.call_resource_id, creset.call_resource_set_id, creset.name
FROM call_resource2set cres2set,call_resource_set creset
WHERE cres2set.call_resource_set_id = creset.call_resource_set_id AND creset.resource_set_type_id = 107 ) ';

EXECUTE IMMEDIATE sql_stmt;

/* Collect resource id's that are NOT part of a Blended Agt */
temptb_2 := 'CREATE GLOBAL TEMPORARY TABLE RESOURCESNOTINSETSSETTYPE107
(CALL_RESOURCE_ID NUMBER(12),
CALL_RESOURCE_SET_ID NUMBER(12),
NAME VARCHAR2(40))
ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE temptb_2;

sql_stmt := 'INSERT INTO RESOURCESNOTINSETSSETTYPE107 (SELECT cres2set.call_resource_id, creset.call_resource_set_id, creset.name FROM call_resource2set cres2set,call_resource_set creset WHERE cres2set.call_resource_set_id = creset.call_resource_set_id
and creset.resource_set_type_id != 107) ';

EXECUTE IMMEDIATE sql_stmt;

/* Collect resource ID's that are in intersection of above groups
Resource ID's in Agent Applic summary are created under Resource Sets defined
in ACD environment. These ACD resource ID's are part of the Bl Agent Group but under
the ACD resource sets. So need to collect resource ID's that belong to ACD and
Bl Agt resource sets */
temptb_3 := 'CREATE GLOBAL TEMPORARY TABLE RESOURCES_IN_BOTH_SETS
(CALL_RESOURCE_ID NUMBER(12),
CALL_RESOURCE_SET_ID NUMBER(12),
NAME VARCHAR2(40))
ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE temptb_3;

sql_stmt := 'INSERT INTO RESOURCES_IN_BOTH_SETS (Select distinct RESOURCESINSETSOFSETTYPE107.call_resource_id, RESOURCESINSETSOFSETTYPE107.call_resource_set_id, RESOURCESINSETSOFSETTYPE107.name FROM RESOURCESINSETSOFSETTYPE107, RESOURCESNOTINSETSSETTYPE107
WHERE RESOURCESINSETSOFSETTYPE107.call_resource_id = RESOURCESNOTINSETSSETTYPE107.call_resource_id )';

EXECUTE IMMEDIATE sql_stmt;

/* Collect resource ID's that are in intersection of sets created above and belong to
Blended Agent Group defined in DataMart -- after the ACD data is written to summary */
temptb_4 := 'CREATE GLOBAL TEMPORARY TABLE RESOURCES_IN_SUPERSET
(CALL_RESOURCE_ID NUMBER(12),
CALL_RESOURCE_SET_ID NUMBER(12),
CALL_RESOURCE_SUPERSET_ID NUMBER(12),
NAME VARCHAR2(40))
ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE temptb_4;


sql_stmt := 'INSERT INTO RESOURCES_IN_SUPERSET
(SELECT
cres2set.call_resource_id, cres_set2superset.call_resource_set_id,
cres_superset.call_resource_superset_id, cres_superset.Name
FROM
call_resource_set2superset cres_set2superset, call_resource2set cres2set,
call_resource_superset cres_superset WHERE (cres_superset.resource_superset_type_id = 5 AND
cres_superset.call_resource_superset_id = cres_set2superset.call_resource_superset_id) and
cres_set2superset.call_resource_set_id = cres2set.call_resource_set_id
AND cres_set2superset.call_resource_superset_id = cres_superset.call_resource_superset_id ) ';

EXECUTE IMMEDIATE sql_stmt;

Report message to a moderator

Re: temp tables in PL/SQL [message #103341 is a reply to message #103335] Mon, 16 June 2003 00:52 Go to previous messageGo to next message
VB
Messages: 6
Registered: June 2003
Junior Member
Hi Rohini,

You can write the following code to delete each temporary table before created it in the procedure.
/*
Begin
execute immediate 'Drop table ResourcesinSetsofSetType107 ';
Exception
when others then
null;
end;
*/

See my test procedure again.

Create or replace procedure joshi as
temptb_1 varchar2(4000);
temp2 varchar2(4000);

Begin

Begin
execute immediate 'Drop table ResourcesinSetsofSetType107 ';
Exception
when others then
null;
end;

/* Collect resource id's that are part of Blended Agt */

temptb_1 := 'CREATE GLOBAL TEMPORARY TABLE ResourcesinSetsofSetType107
(call_resource_id NUMBER(12),
call_resource_set_id NUMBER(12),
name VARCHAR2(40),
CONSTRAINT ResourcesinSetsofSetType107_pk
PRIMARY KEY (call_resource_id))
ON COMMIT DELETE ROWS';

EXECUTE IMMEDIATE temptb_1;

temp2 := 'INSERT INTO ResourcesinSetsofSetType107
(call_resource_id,call_resource_set_id, name)
select 1,2,''aabc'' from dual';

EXECUTE IMMEDIATE temp2;

--SELECT cres2set.call_resource_id, creset.call_resource_set_id, creset.name
--FROM call_resource2set cres2set, call_resource_set creset
--WHERE cres2set.call_resource_set_id = creset.call_resource_set_id
--AND creset.resource_set_type_id = 107
--ORDER BY 1';
End;

Hope this is what you were looking to do in the
procedure.

Best Regards,
Vibhu

Report message to a moderator

Re: temp tables in PL/SQL [message #103343 is a reply to message #103341] Mon, 16 June 2003 10:38 Go to previous messageGo to next message
Rohini
Messages: 13
Registered: December 2001
Junior Member
Thanks Vibhu!
Actually I did add the Drop stmts before the Creates, the only problem is, since the temp tables don't exist the first time the proc is executed, I get a table not exists error.
Do I have to create these temp tables on the database as part of my install at a customer site before I can use this proc? Is there no way to create the temp tables if not present and drop if present 'on the fly' in Oracle? For example in MS SQL Server, since it cleans up hash tables after the proc runs, we don't encounter these problems. Is there no easy way to recreate this type of logic with Oracle temp tables?

Report message to a moderator

Re: temp tables in PL/SQL [message #103344 is a reply to message #103343] Mon, 16 June 2003 22:36 Go to previous message
VB
Messages: 6
Registered: June 2003
Junior Member
Hi RJ!

Yes I know that error will occur that's why I had given
the Drop statement in Begin --- Exception ---- End;
So if there are no tables already existing the program will go in the exception part which handles it and after that the temp tables are created.

See the sample code which I had sent earlier.
Just add the exception handing part in your code after writing drop commands.

Report message to a moderator

Previous Topic: web portfolio for job interview ?? help!
Next Topic: indecision
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 09:37:29 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.