Re: Insert within PLSQL fails

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Fri, 29 Jun 2012 10:25:06 +0100 (BST)
Message-ID: <1340961906.64231.YahooMailNeo_at_web29020.mail.ird.yahoo.com>

Steve
Yes its a compile time error.
And you are correct i have to make the insert dynamic thanks for helping me out.

---

 From: Steve Baldwin <stbaldwin_at_multiservice.com> To: hrishys_at_yahoo.co.uk
Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Friday, 29 June 2012, 14:53
Subject: Re: Insert within PLSQL fails  

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_at_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

Received on Fri Jun 29 2012 - 04:25:06 CDT