MIME-Version: 1.0
In-Reply-To: <1340961022.46688.YahooMailNeo@web29010.mail.ird.yahoo.com>
References: <CABe10sZNaXyiQ823TOFEdijmj-406V4ZesEd6PjFU2Q8brrS3A@mail.gmail.com>
 <A250F0C68C23514CA9F3DF63D60EE10E163BBD18@onews31> <CABe10sZ3ab4adK=_HUkr10yWDzH3mr86VctnFP4uNK1UXLSkvA@mail.gmail.com>
 <1340961022.46688.YahooMailNeo@web29010.mail.ird.yahoo.com>
From: Steve Baldwin <stbaldwin@multiservice.com>
Date: Fri, 29 Jun 2012 19:23:26 +1000
Message-ID: <CAC-6Hs3w7P9jVTY-nemsDouyBRQmyR7D=5v95hCVH3yeU76uEA@mail.gmail.com>
Subject: Re: Insert within PLSQL fails
To: hrishys@yahoo.co.uk
Cc: ORACLE-L <oracle-l@freelists.org>
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: stbaldwin@multiservice.com

Hi,
I am pretty sure that this is a compile-time error rather than a run-time
error.

If the row_chain_demo table does not exist at the start of execution you
need to make the insert dynamic.

HTH

Steve

On Fri, Jun 29, 2012 at 7:10 PM, hrishy <hrishys@yahoo.co.uk> wrote:

> Hi
> I have the following piece of plsql.
> Insert within the plsql fails any idea ?
>
> insert into row_chain_demo (col1,col256) values(1,'Hello World');
>             *
> ERROR at line 43:
> ORA-06550: line 43, column 13:
> PL/SQL: ORA-00942: table or view does not exist
> ORA-06550: line 43, column 1:
> PL/SQL: SQL Statement ignored
>
>
> declare
>
> l_create_table_query varchar2(2000) default 'create table row_chain_demo
> (col1 number) segment creation immediate' ;
> l_alter_table_query  varchar2(2000) ;
> l_column_name user_tab_columns.column_name%type default 'col' ;
>
>
> begin
>
> for x in (select *
>             from dual
>             where not exists (select null
>                                 from user_tables
>                                 where table_name = upper('row_chain_demo')
> ) )
> loop
>   execute immediate l_create_table_query;
> end loop;
>
> dbms_output.put_line (l_create_table_query);
>
> for l_cntr in 2..256
> loop
>   l_column_name       := 'col' || l_cntr ;
>   l_alter_table_query := 'alter table row_chain_demo add  ' ||
> l_column_name || ' char(2000) ' ;
>
>
>   for x in (select *
>                 from dual
>                 where not exists (select null
>                                     from user_tab_columns
>                                     where table_name =
> upper('row_chain_demo')
>                                     and column_name = upper( l_column_name
> )
>                                  )
>             )
>   loop
>     execute immediate l_alter_table_query ;
>
>   end loop;
>
> end loop;
> commit;
>
> insert into row_chain_demo (col1,col256) values(1,'Hello World');
>
> end;
> /
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


---------------------------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
---------------------------------------------------------------------------------------


--
http://www.freelists.org/webpage/oracle-l