Home » SQL & PL/SQL » SQL & PL/SQL » simple select from sys.user_tab_columns going all wrong in pl/sql (oracle 10g)
simple select from sys.user_tab_columns going all wrong in pl/sql [message #323809] Thu, 29 May 2008 10:06 Go to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Hi, I have the following simple script which should simply add a column to a table if it doesn't already have it:

DECLARE
    table_name          VARCHAR2 (30)   := 'SITE_STATISTICS';
    column_name         VARCHAR2 (30)   := 'SITE_MONTH';
    column_data_type    VARCHAR2 (30)   := 'DATE';
    column_count        INTEGER;
    dynamic_sql         VARCHAR2 (4000);
BEGIN
    SELECT COUNT (1) acount
    INTO   column_count
    FROM   SYS.USER_TAB_COLUMNS utc
    WHERE  utc.table_name LIKE table_name
 AND utc.column_name LIKE column_name
 AND utc.data_type LIKE column_data_type;

    SYS.DBMS_OUTPUT.ENABLE (4000);
    IF column_count = 0 THEN
        dynamic_sql :=
            '
            ALTER TABLE SITE_STATISTICS ADD (SITE_MONTH DATE);
            ';

        EXECUTE IMMEDIATE dynamic_sql;
    ELSE
        DBMS_OUTPUT.put_line ('Not running Dynamic Sql, found ' ||
       column_count || ' corresponding rows in sys.user_tab_columns');
    END IF;
END;
/

EXIT;


At a given site, this column definitely does not exist.
Running:
SELECT COUNT (1)
FROM   SYS.USER_TAB_COLUMNS utc
WHERE  utc.TABLE_NAME = 'SITE_STATISTICS'
 AND utc.COLUMN_NAME = 'SITE_MONTH'
 AND utc.DATA_TYPE = 'DATE'

Returns a value of 0

But when I run my script I get the output:
Quote:
Not running Dynamic Sql, found 128 corresponding rows in sys.user_tab_columns


I'm not a DBA but this is pretty simple. Am I missing something?

[Updated on: Thu, 29 May 2008 10:20] by Moderator

Report message to a moderator

Re: simple select from sys.user_tab_columns going all wrong in pl/sql [message #323813 is a reply to message #323809] Thu, 29 May 2008 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
which user/schema owns SITE_STATISTICS table?
(Hopefully not SYS)
Re: simple select from sys.user_tab_columns going all wrong in pl/sql [message #323814 is a reply to message #323809] Thu, 29 May 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't put a ';' in your dynamic statement
2/ Don't use SYS.USER... use USER...
3/ Don't use LIKE if there is no joker character, use =
4/ "utc.table_name LIKE table_name" (and others) is equivalent to "utc.table_name LIKE utc.table_name", fields in query take precedence over variable name. Change your variable names.

Regards
Michel
Re: simple select from sys.user_tab_columns going all wrong in pl/sql [message #323818 is a reply to message #323814] Thu, 29 May 2008 10:33 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Thank you very much, prefacing the variable names with v_ (ugh) and removing the semicomma in my alter table statement did the trick.

One more question, if I want to alter multiple columns like this:
        dynamic_sql :=
            '
            ALTER TABLE SITE_STATISTICS ADD (SHIP_CONTANK INTEGER);
            ALTER TABLE SITE_STATISTICS ADD (SHIP_CONRAIL INTEGER);
            ALTER TABLE SITE_STATISTICS ADD (SHIP_CONOTHER INTEGER);

            ALTER TABLE SITE_STATISTICS ADD (RECPT_CONTANK INTEGER);
            ALTER TABLE SITE_STATISTICS ADD (RECPT_CONRAIL INTEGER);
            ALTER TABLE SITE_STATISTICS ADD (RECPT_CONOTHER INTEGER);

            ALTER TABLE SITE_STATISTICS ADD (SITE_MONTH DATE);';

        EXECUTE IMMEDIATE dynamic_sql;


I can't just do that because it bitches about the semicolon, is there a way to run multiple statements without breaking it up?
I would ultimately like to reuse this script with other column names
Re: simple select from sys.user_tab_columns going all wrong in pl/sql [message #323820 is a reply to message #323818] Thu, 29 May 2008 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No...
or maybe:
dynamic_sql :=
'Begin
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (SHIP_CONTANK INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (SHIP_CONRAIL INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (SHIP_CONOTHER INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (RECPT_CONTANK INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (RECPT_CONRAIL INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (RECPT_CONOTHER INTEGER)'';
execute immediate ''ALTER TABLE SITE_STATISTICS ADD (SITE_MONTH DATE)'';
End';
EXECUTE IMMEDIATE dynamic_sql;

Razz

Or you can also remark that you can add several columns in one statement.

Regards
Michel
Re: simple select from sys.user_tab_columns going all wrong in pl/sql [message #323823 is a reply to message #323820] Thu, 29 May 2008 10:47 Go to previous message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
thank you very much
Previous Topic: Need a clarification regarding value sets
Next Topic: partitioning
Goto Forum:
  


Current Time: Sat Dec 03 00:59:01 CST 2016

Total time taken to generate the page: 0.16626 seconds