CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Ray Bae
Ray Bae

Reputation: 99

Create A Temporary Table In PL-SQL Trigger

This is probably something silly that I'm over looking.

I have this code in a trigger. The full trigger is here: (There's more code above this code) http://pastebin.com/KcBZdEmt

CREATE TABLE ApprList AS 
SELECT
U.*
FROM pmm$PmmReleaseRequest R
INNER JOIN dbo$ManagedEntity ME
ON ME.ManagedEntityID = R.ManagedSystemID
INNER JOIN dbo$SmartRuleAssetCache SRC
ON SRC.AssetID = ME.AssetID
INNER JOIN dbo$UserGroup_SmartRule_Role GSR
ON GSR.SmartRuleId = SRC.SmartRuleId
AND GSR.RoleId IN (2,3)
INNER JOIN dbo$AppUser_UserGroup UG
ON UG.GroupID = GSR.UserGroupId
AND UG.UserID <> R.UserID
INNER JOIN dbo$AppUser U ON UG.UserID = U.UserID
WHERE R.ReleaseRequestID = ReleaseRequestID
AND U.UserID <> RequestorUserID;

On the folling line:

CREATE TABLE ApprList AS

I get:

Error(111,1): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge

It's probably best to take a peak at the pastebin file.

Thanks in advance.

Upvotes: 0

Views: 3308

Answers (1)

kevinskio
kevinskio

Reputation: 4551

You cannot mix DDL and DML statements together without using EXECUTE IMMEDIATE. However the whole idea of creating a table in a trigger while perfectly fine in SQL Server is not a best practice in Oracle. Use a global temporary table instead. Create this table and then insert into it from the trigger.Something like this sample

CREATE GLOBAL TEMPORARY TABLE YourSchema.Yourtable
-- Create table

( 
  pk_id           NUMBER(9) NOT NULL,
  company         VARCHAR2(20 VARCHAR2) not null,
  voucher_type    VARCHAR2(3 VARCHAR2) not null,
  voucher_no      NUMBER(10) not null,
  year_period_key NUMBER not null
)
on commit PRESERVE rows;

Upvotes: 3

Related Questions