Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE Statement
EXECUTE IMMEDIATE Statement [message #239435] Tue, 22 May 2007 07:24 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good Morning.

I used EXECUTE IMMEDIATE Statement to create a table and I get error when I executed my procedure. Please help me. I would appreciate that.

Here is my procedure
CREATE OR REPLACE PROCEDURE create_table
IS
BEGIN
    
    EXECUTE IMMEDIATE('Create table PARTITION (deptno	varchar2(4),
					       lname	   	varchar2(20),
					       fname	   	varchar2(20),
					       ssn	   	varchar2(9),
					       hire_date  	varchar2(4),
					       salary	varchar2(6))
                                               Partition by list (deptno)
                                               (Partition dept_1009 values(1009),
                                               Partition dept_2009 values(2009))');               
END create_table; 
/


And this is the error:

SQL> execute create_table;
BEGIN create_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "NG326.CREATE_TABLE", line 5
ORA-06512: at line 1
Re: EXECUTE IMMEDIATE Statement [message #239439 is a reply to message #239435] Tue, 22 May 2007 07:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error occurs because your user has the CREATE TABLE privilege granted to them via a role, rather than having it granted directly.

I have to ask WHY????
Why would you create a procedure to create a table instead of just creating the table?
If you're planning to create and drop tables on the fly, then change your plan now. Don't do it. Just don't. It's a bad plan.
Re: EXECUTE IMMEDIATE Statement [message #239449 is a reply to message #239435] Tue, 22 May 2007 07:51 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
The reason that I used procedure to create table because after insert values to this table by selecting data from other table. I have to update this table.

Here is my assignment:
1. Create table Partition
2. Load data to Partition by inserting rows from Employee table
3. Update Partition by setting column PANNEL = ‘1’ (sorry I missed this column in my procedure) for all rows where
PARTITION(deptno, ssn) = DEPT(deptno, ssn)

For that reason, I think I have to create a procedure to create a table then after that I can update column PANNEL by using cursor to select deptno, ssn from DEPT table.

Please show me the best way to do it. I appreciate that.
Re: EXECUTE IMMEDIATE Statement [message #239464 is a reply to message #239449] Tue, 22 May 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ PARTITION is a reserved word, don't use it at a name
2/ Oracle version is mandatory to help you
3/ I don't see any business reason to create this table on the fly (in a procedure) and not precreate it in SQL script. In Oracle, we don't create and drop tables on the fly. Objects are part of the application, you don't change your application code on the fly, don't you?

Regards
Michel

[Updated on: Tue, 22 May 2007 09:00]

Report message to a moderator

Re: EXECUTE IMMEDIATE Statement [message #239493 is a reply to message #239435] Tue, 22 May 2007 08:54 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi.

Thanks for your reply. But if I create table in SQL script, do I need to create a procedure to update the table?

Thanks
Re: EXECUTE IMMEDIATE Statement [message #239495 is a reply to message #239493] Tue, 22 May 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe yes, maybe not, it depends on your environment.
You can do it in SQL but if your security policy says no update in SQL, all updates in procedure then you have to do it in procedure.

Regards
Michel
Re: EXECUTE IMMEDIATE Statement [message #239496 is a reply to message #239435] Tue, 22 May 2007 09:06 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

hai go to this like some documents are available link


Reds
Thangam
Re: EXECUTE IMMEDIATE Statement [message #239567 is a reply to message #239435] Tue, 22 May 2007 12:24 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you for your replies. I tried to update the table by using sql script but it did not work.

update Partition
set pannel = 1
where deptno = (select deptno from dept)
and ssn = (select ssn from dept);


Would you please to help me? Thank you very much
Re: EXECUTE IMMEDIATE Statement [message #239573 is a reply to message #239435] Tue, 22 May 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
> it did not work.
lamnguyen14,
My car did not work.
Please tell me how to fix it.


Please read & FOLLOW the #1 STICKY post at the top of this forum.
Re: EXECUTE IMMEDIATE Statement [message #239575 is a reply to message #239567] Tue, 22 May 2007 12:36 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
10.2.0.3.0 - 64bit Production
SQL> show user
USER is "SYS"
SQL> CREATE OR REPLACE PROCEDURE create_table
IS
BEGIN
EXECUTE IMMEDIATE('Create table PARTITION (deptno varchar2(4),
lname varchar2(20),
fname varchar2(20),
ssn varchar2(9),
hire_date varchar2(4),
salary varchar2(6))
Partition by list (deptno)
(Partition dept_1009 values(1009),
Partition dept_2009 values(2009))');
END create_table;
/

Procedure created.

SQL> execute create_table;

PL/SQL procedure successfully completed.

SQL>
SQL> desc partition
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO VARCHAR2(4)
LNAME VARCHAR2(20)
FNAME VARCHAR2(20)
SSN VARCHAR2(9)
HIRE_DATE VARCHAR2(4)
SALARY VARCHAR2(6)

[Updated on: Tue, 22 May 2007 12:49]

Report message to a moderator

Re: EXECUTE IMMEDIATE Statement [message #239581 is a reply to message #239575] Tue, 22 May 2007 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
USER is "SYS"

What a bad idea!
NEVER EVER use SYS but for maintenance purpose.
When you use SYS, YOU DON'T EXECUTE THE SAME CODE that with any other user.
Whatever you do with SYS will never validate or invalidate something you do with another user.

The proof: you execute the same thing as OP but he can't do it and you can. Moreover, I don't see what is the purpose of your post.

Regards
Michel

[Updated on: Tue, 22 May 2007 12:57]

Report message to a moderator

Re: EXECUTE IMMEDIATE Statement [message #239583 is a reply to message #239435] Tue, 22 May 2007 12:59 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Sort of a non-sequetor, but why would you define SALARY as a VARCHAR2?
Re: EXECUTE IMMEDIATE Statement [message #239584 is a reply to message #239567] Tue, 22 May 2007 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just write in SQL what you wrote:
Quote:
Update Partition by setting column PANNEL = ‘1’ [(...)]for all rows where PARTITION(deptno, ssn) = DEPT(deptno, ssn)

update partition
set pannel = '1'
where (deptno, ssn) in (select deptno, ssn from dept)
/

Regards
Michel

[edit: fix typo]

[Updated on: Tue, 22 May 2007 13:05]

Report message to a moderator

Re: EXECUTE IMMEDIATE Statement [message #239585 is a reply to message #239435] Tue, 22 May 2007 13:01 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>HIRE_DATE VARCHAR2(4)
or use VARCHAR2 for a DATE datatype; never, ever a good idea.
Re: EXECUTE IMMEDIATE Statement [message #239617 is a reply to message #239435] Tue, 22 May 2007 14:38 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much.
Previous Topic: Deduction accrued balances
Next Topic: Need help in creating materialized view for the data extracted from LONG datatype.
Goto Forum:
  


Current Time: Fri Dec 09 09:56:45 CST 2016

Total time taken to generate the page: 0.10279 seconds