Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 4th, 2004, 08:57 AM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored procedure alter table error

When I try to save this stored procedure I get an error on the alter statement. Both the update and alter statements work individually, Oracle just doesn't seem to like the alter statement. Any ideas? Thanks!

CREATE OR REPLACE PROCEDURE sp_R2_REFRESH_PROJECT_ISSUE IS

BEGIN

UPDATE Controls_pbis
SET PEO = 'NONE'
WHERE PEO IS NULL;

ALTER TABLE controls_pbis
ADD project_5 varchar2(5);

END;
 
Old June 4th, 2004, 09:12 AM
Authorized User
 
Join Date: Jul 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Justine,

That's because the ALTER TABLE statement is a DDL statement. You will have to use Dynamic SQL to execute it from within a stored procedure.

Something like:

EXECUTE IMMEDIATE 'ALTER TABLE controls_pbis ADD project_5 varchar2(5)' ;

for Oracle 8i and above, or use DBMS_SQL package.

Cheers,
Prat


 
Old June 4th, 2004, 09:51 AM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Awesome thanks.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Temp Table Variable in Stored Procedure rweide SQL Server ASP 2 April 15th, 2011 03:50 PM
Create table/grid from stored procedure bootsy Classic ASP Basics 0 May 22nd, 2008 10:39 AM
Add new Table to Stored Procedure pallone SQL Server 2000 2 February 14th, 2007 12:58 PM
Stored Procedure That Updates Table GailCG Classic ASP Professional 1 January 22nd, 2006 01:11 PM
Build a table from a Stored Procedure rogue248 SQL Server 2000 2 October 7th, 2004 10:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.