Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate for Creating Table (Oracle 10g)
Execute Immediate for Creating Table [message #355251] Thu, 23 October 2008 05:11 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am tryng to create a table, synonym and trigger in a procedure.
It gets compiled.
 CREATE OR REPLACE PROCEDURE s_create_table
 is
 begin
   EXECUTE IMMEDIATE
       'CREATE TABLE BasketItemss (
       ID number PRIMARY KEY,
       LineID number,
       BasketID number,
       ProductItemNumber varchar2(25),
       Quantity number,
       Price number(8,2),
       SubTotal number(10,2),
       AllocatTo1 varchar2(50),
       AllocatTo2 varchar2(50),
       TemplateID varchar2(16),
       AlternativePartNO varchar2(255),
       CategoryType char(1)
       )';
 EXECUTE IMMEDIATE 'CREATE SEQUENCE BasketItemss_id_seq';
     EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER BasketItemss_trg
       BEFORE INSERT ON basketItemss
        FOR EACH ROW
          BEGIN
            SELECT BasketItemss_id_seq.nextval INTO :new.ID FROM dual;
          end';
 end;


I get below error:

SQL> exec s_create_table;
BEGIN s_create_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.S_CREATE_TABLE", line 4
ORA-06512: at line 1


I am able to ceate the objects in the above procedure independently on sql prompt but when I execute the procedure I get this error.

Please look into this,

Regards,
Soni
Re: Execute Immediate for Creating Table [message #355263 is a reply to message #355251] Thu, 23 October 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's boring to see the same question over and over.

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: Execute Immediate for Creating Table [message #355264 is a reply to message #355251] Thu, 23 October 2008 05:30 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Check grants/ priviledges to create table on user.
Re: Execute Immediate for Creating Table [message #355273 is a reply to message #355264] Thu, 23 October 2008 05:48 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am able to create tables, synonyms, procedures if I create simply on sql prompt. But only when I create them using execute immediate in procedure, I am getting the error.

Please help me on this as I am suck on ths for long time.

Regards,
Soni
Re: Execute Immediate for Creating Table [message #355275 is a reply to message #355251] Thu, 23 October 2008 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read the link Michel posted.
The answers are all in there.
Re: Execute Immediate for Creating Table [message #355285 is a reply to message #355275] Thu, 23 October 2008 06:11 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

The link says that I need to give permission for all objects in a procedures.

How do I give permission for the three objects I created inside the procedure without executing it as without executing the procedure the objects won't be created?

Once I execute the procedure for creating the objects I get the error as I gave earlier.

Please bear with me if I am being impossible.

I read through the link given, please guide me on this.

Regards

Re: Execute Immediate for Creating Table [message #355290 is a reply to message #355251] Thu, 23 October 2008 06:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

How do I give permission for the three objects I created inside the procedure without executing it as without executing the procedure the objects won't be created?


Dont need to grant on the specified objects . You have ensure that you have Grant/privilege for Creating

TABLE
SEQUENCE &
TRIGGER

in your case

Smile
Rajuvan.
Re: Execute Immediate for Creating Table [message #355300 is a reply to message #355290] Thu, 23 October 2008 06:38 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

I gave all three grants.
The objects got created but still the procedure

SQL>  CREATE OR REPLACE PROCEDURE s_create_table
  2   is
  3   begin
  4    EXECUTE IMMEDIATE
  5        'CREATE TABLE BasketItemss (
  6        ID number PRIMARY KEY,
  7        LineID number,
  8        BasketID number,
  9        ProductItemNumber varchar2(25),
 10        Quantity number,
 11        Price number(8,2),
 12        SubTotal number(10,2),
 13        AllocatTo1 varchar2(50),
 14        AllocatTo2 varchar2(50),
 15        TemplateID varchar2(16),
 16        AlternativePartNO varchar2(255),
 17        CategoryType char(1)
 18        )';
 19        BEGIN
 20        EXECUTE IMMEDIATE 'CREATE SEQUENCE BasketItemss_id_seq';
 21        END;
 22        BEGIN
 23          EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER BasketItemss_trg
 24            BEFORE INSERT ON basketItemss
 25             FOR EACH ROW
 26               BEGIN
 27                 SELECT BasketItemss_id_seq.nextval INTO :new.ID FROM dual;
 28               end';
 29        END;
 30   End;
 31  /

Procedure created.

SQL> exec s_create_table;
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "SCOTT.S_CREATE_TABLE", line 23
ORA-06512: at line 1

Warning: PL/SQL compilation errors.

SQL> show err
No errors.



What could have caused the compilation error at line 23?
Re: Execute Immediate for Creating Table [message #355304 is a reply to message #355300] Thu, 23 October 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error is not in the compilation of the procedure but in the compilation of the trigger.
Query user_errors.
or:
show errors trigger BasketItemss_trg

Regards
Michel
Re: Execute Immediate for Creating Table [message #355305 is a reply to message #355304] Thu, 23 October 2008 06:46 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

I got the below error:
SQL> show errors trigger BasketItemss_trg;
Errors for TRIGGER BASKETITEMSS_TRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/17     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         ; <an identifier> <a double-quoted delimited-identifier>
         The symbol ";" was substituted for "end-of-file" to continue.
Re: Execute Immediate for Creating Table [message #355307 is a reply to message #355251] Thu, 23 October 2008 06:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER BasketItemss_trg
       BEFORE INSERT ON basketItemss
        FOR EACH ROW
          BEGIN
            SELECT BasketItemss_id_seq.nextval INTO :new.ID FROM dual;
          end';


I think you need a ';' after 'end' and before (') ( comma)

Smile
Rajuvan.
Re: Execute Immediate for Creating Sequence [message #355322 is a reply to message #355307] Thu, 23 October 2008 09:09 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am able to create sequence in the below procedure :
  CREATE OR REPLACE PROCEDURE s_create_sequence
  is
  begin
   EXECUTE IMMEDIATE
      'CREATE SEQUENCE BasketItemss_id_seq';
 END;
/


But the same command for creating sequence using execute immediate is not working in another precedure.

CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(
     LineID  IN  number,
     BasketID IN number,
     RequestedPage IN number,
     PageSize OUT number,
     PageCount OUT NUMBER,
     RowCount OUT number
     )
 IS
 BasketItem_rec  BasketItems%ROWTYPE;
 TYPE BasketItemCurTyp IS REF CURSOR;
 basketItemcur BasketItemCurTyp; 
 From1 NUMBER;
 To1 NUMBER;
 ReqPageVal NUMBER := RequestedPage;
 strsql varchar2(200);

 BEGIN
 PageSize := '20';
 PageCount := '';
 RowCount :='';

EXECUTE IMMEDIATE 
  'CREATE SEQUENCE BasketItemss_id_seq'; --*****************
    
if ReqPageVal is not null then
    DECLARE
    BasketItemss_rec  BasketItemss%ROWTYPE;
    TYPE BasketCurTyp IS REF CURSOR;
    basketcur BasketCurTyp; 
    Begin
       Insert into BasketItemss(ID,LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
              AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType)
              (select BasketItemss_id_seq.nextval, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
              AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType from BasketItems
        Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID));
       strsql := 'Select count(*) from BasketItems';
       EXECUTE Immediate strsql  INTO RowCount;
       if RowCount <= PageSize THEN
           PageCount := 1;
       else
         strsql := 'SELECT CIEL(RowCount/PageSize) FROM dual';
         EXECUTE IMMEDIATE strsql INTO pagecount;
            IF PageCount <> (RowCount/PageSize) THEN
               PageCount := PageCount + 1;
            ELSE
              PageCount := PageCount + 0;
           END IF;
      End IF;
      if ReqPageVal > PageCount then
         ReqPageVal := PageCount;
      End if;
      if ReqPageVal < 1 then
         ReqPageVal := 1;
      End if;
      if ReqPageVal > 1 then
         From1 := ((ReqPageVal -1) * PageSize) + 1;
         To1 := (ReqPageVal * PageSize) ;
      else
         From1 := 1;
         To1 := PageSize;
     End if;
      strsql := 'select * from BasketItemss where id >= ' ||from1|| ' And id <= ' ||To1;
      OPEN basketcur FOR strsql;
         LOOP
           FETCH basketcur INTO BasketItemss_rec;
             EXIT WHEN basketcur%NOTFOUND;      
             -- process record
         END LOOP;
      CLOSE basketcur;
    end;
else
    PageCount := -1;
    RowCount := -1;
    strsql := 'Select LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,AllocatTo1,AllocatTo2,TemplateID,
                      AlternativePartNO,CategoryType From BasketItems
                Where LineID = NVL(LineID,LineID) AND BasketID = NVL(BasketID,BasketID)';
    OPEN basketItemcur FOR strsql;
         LOOP
           FETCH basketItemcur INTO BasketItem_rec;
             EXIT WHEN basketItemcur%NOTFOUND;     
             -- process record
         END LOOP;
      CLOSE basketItemcur;
End if;
END;


When I try to create this procedure, it gives the below error:-
Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE XSP_BASKETITEMSFORSELECT:

LINE/COL ERROR
-------- -------------------------------------------------------
31/2     PL/SQL: SQL Statement ignored
33/23    PL/SQL: ORA-02289: sequence does not exist


I was able to do this sometimes back but don't know what happened to this procedure....

Please have a look at this.
All the permission are in place as you can see I can create another procedure for sequence creation.

Regards,
Soni
Re: Execute Immediate for Creating Table [message #355323 is a reply to message #355251] Thu, 23 October 2008 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
The procedure can't compile because the sequence doesn't exist at compile time. Oracle can not tell that you're going to create it using dynamic sql.
For this to work all code that references the sequence would have to be dynamic sql as well.
My advise - don't create the sequence dynamically, just create it normally or you'll keep running into problems like this.
Re: Execute Immediate for Creating Table [message #355414 is a reply to message #355323] Thu, 23 October 2008 23:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
OP opened a new thread here.
Therefor, I lock this one.
Previous Topic: Dump large tables...
Next Topic: Convert a Number into Words
Goto Forum:
  


Current Time: Thu Dec 08 02:00:20 CST 2016

Total time taken to generate the page: 0.09324 seconds