Forum Stats

  • 3,752,279 Users
  • 2,250,483 Discussions
  • 7,867,775 Comments

Discussions

ORA error - Sql script -

S567
S567 Member Posts: 405 Red Ribbon


Hi team,

I am able to see data in my dbms_output.put_line stmnts based on what i am fetching when iam trying to insert same using insert stmnt i am facing error called columns not allowed here.

I tried in server ways by passing values like this '||v_year|' but still not working. below is script..

This is sample data of my cursor

with test as(

select sysdate date_details from dual union all

select sysdate+1 date_details from dual )


set serveroutput on;

declare

v_year varchar2(10);

v_quarter varchar2(10);

v_weekofyear varchar2(10);

v_month varchar2(10);

v_weekday varchar2(10);

v_date varchar2(10);

v_sql varchar2(32567);

cursor c1 is (select date_details from date_detail);  

BEGIN

BEGIN

execute immediate 'create table date_details_targets_pros(s_year varchar2(10),quarter varchar2(10),weekofyear varchar2(10),s_month varchar2(10),

weekday varchar2(10),s_date varchar2(10))';

exception 

when others then

null;

END;

begin

for i in c1

loop

begin 

select 

to_char(i.date_details,'YYYY') 

,to_char(i.date_details,'q') 

,to_char(i.date_details,'ww')  

,to_char(i.date_details,'mon')  

,to_char(i.date_details,'day')  

,to_char(i.date_details,'ds') 

into v_year ,v_quarter,v_weekofyear,v_month,v_weekday,v_date from dual;


exception 

when others then

null;

END;


dbms_output.put_line(v_year);

dbms_output.put_line(v_quarter);

dbms_output.put_line(v_weekofyear);

dbms_output.put_line(v_month);

dbms_output.put_line(v_weekday);

dbms_output.put_line(v_date);


execute immediate 'insert into date_details_targets_pros (s_year,quarter,weekofyear,s_month,weekday,s_date)

values ( v_year , v_quarter , v_weekofyear,v_month, v_weekday,v_date)';

commit;

end loop;

end;


END;

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 415 Bronze Trophy

    Don't use pl/sql. You can replace your script with one command: create table as select.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,925 Red Diamond

    Hi, @S567

    Don't use PL/SQL without a good reason. Use SQL instead. (This was said already, but it's important enough to repeat.)

    If you have a good reason to use PL/SQL, don't use dynamic SQL without a good reason. The INSERT above can be done in PL/SQL without dynamic SQL. Creating tables in PL/SQL does require PL/SQL, but it's usually a terrible idea. If you must use PL/SQL for the INSERT, that doesn't mean you have to use it for creating the table

    Don't use EXCEPTION without a good reason. There is never a good reason to say

    exception 

    when others then

    null;

    Oracle's default error handling includes messages that help you find exactly what problems occur and exactly where the occur. The EXCEPTION section above is just hiding that important information. Most PL/SQL blocks do not benefit from, let alone need, EXCEPTION handlers. Most exception handlers do not benefit from, let alone need WHEN OTHERS. When WHEN OTHERS is used, it should always be followed by RAISE.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,317 Red Diamond

    Don't use PL/SQL without a good reason. Use SQL instead. (This was said already, but it's important enough to repeat.)


    Good enough to be repeated again.

    Creating tables on-the-fly is never good design. Tables should already be known and created at design time. There is hardly ever a good reason to create them in PL/SQL code.

    But let's look at your code and see what's wrong with it..

    declare
      v_year varchar2(10);
      v_quarter varchar2(10);
      v_weekofyear varchar2(10);
      v_month varchar2(10);
      v_weekday varchar2(10);
      v_date varchar2(10);
      v_sql varchar2(32567);
      cursor c1 is
        select date_details
        from   date_detail;  
    BEGIN
      BEGIN
        execute immediate 'create table date_details_targets_pros(s_year varchar2(10),quarter varchar2(10),weekofyear varchar2(10),s_month varchar2(10),weekday varchar2(10),s_date varchar2(10))';
      exception
        -- Assuming there is a valid reason (which I doubt) for creating a table at runtime
        -- this would actually be a valid reason to have an exception handler, in case the table already exists
        -- However you should be specific about the exceptions you want to handle
        -- e.g. ORA-00955: name is already used by an existing object would be an appropriate exception to handle
        -- To do that, you would need to use something like:
        --   (in the declaration section)
        --   table_already_exists EXCEPTION;
        --   pragma exception_init(table_already_exists, -955);
        --   (then in the exception handler)
        --   when table_already_exists then
        --     null;
        when others then
          null;
      END;
      begin
        -- do you seriously need to poorly assign those values using a select from dual?
        -- better would be...
        -- 
        -- for i in c1
        -- loop
        --   v_year := to_char(i.date_details,'YYYY');
        --   v_quarter := to_char(i.date_details,'q');
        --   v_weekofyear := to_char(i.date_details,'ww');
        --   v_month := to_char(i.date_details, 'mon'); -- consider also using the nls_date_language e.g. to_char(i.date_details,'mon','nls_date_language=english')
        --   v_weekday := to_char(i.date_details, 'day'); -- consider nls_date_language as above
        --   v_date := to_char(i.date_details, 'ds');
        --   ... your other processing
        --  
        -- end loop;
        -- 
        for i in c1
        loop
          begin 
            select 
            to_char(i.date_details,'YYYY') 
            ,to_char(i.date_details,'q') 
            ,to_char(i.date_details,'ww')  
            ,to_char(i.date_details,'mon')  
            ,to_char(i.date_details,'day')  
            ,to_char(i.date_details,'ds') 
            into v_year ,v_quarter,v_weekofyear,v_month,v_weekday,v_date from dual;
          -- This exception handler is completely inappropriate.
          -- There's no reasonable reason why any exception should be raised from the above statement.
          -- and if there was, you'd certainly need to know about it.
          exception 
            when others then
              null;
          end;
          dbms_output.put_line(v_year);
          dbms_output.put_line(v_quarter);
          dbms_output.put_line(v_weekofyear);
          dbms_output.put_line(v_month);
          dbms_output.put_line(v_weekday);
          dbms_output.put_line(v_date);
    
    
          -- When you issue an insert statement, because of your dynamic creation of the table you have to
          -- use dynamic SQL to reference it.
          -- However, dynamic SQL, if at all needed, should be used correctly with proper bind variables
          -- e.g.
          --
          --   execute immediate 'insert into date_details_targets_pros (s_year, quarter, weekfoyear, s_month, weekday, s_date) values (:1, :2, :3, :4, :5, :6)'
          --            using v_year, v_quarter, v_weekofyear, v_month, v_weekday, v_date;
          --
          -- or if you have lots of bind variables you'd likely need to resort to the DBMS_SQL package instead.
          --
          -- As you have written your statement you are passing the whole SQL string to the SQL engine and it
          -- has no idea what v_year, v_quarter, v_weekofyear, v_month, v_weekday or v_date are.
          -- Hence why you are getting your error message about unknown columns.
          execute immediate 'insert into date_details_targets_pros (s_year,quarter,weekofyear,s_month,weekday,s_date) values ( v_year , v_quarter , v_weekofyear,v_month, v_weekday,v_date)';
          --
          -- committing inside a cursor loop is just plain wrong
          -- it can cause exceptions to be raised (especially if processing large amounts of data) as you're in the middle of a query
          -- committing should only be done once all the logical business processing has been completed i.e. once the cursor loop has finished.
          -- multiple commits in side a loop can also cause multiple writer threads to be generated on the server which can cause conflict
          -- and impact performance on the server for other users.
          commit;
        end loop;
      end;
    END;
    

    You also seem to use an excessive amount of BEGIN... END blocks. You only really need to do that if you want to nest processing and handle exceptions at that nested level. That last block has no reasonable exception handling in it, so the BEGIN END isn't needed.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond

    PL/SQL is not a scripting language.

    To stress this - PL/SQL is not a scripting language.

    DO NOT TREAT IT AS ONE.

    PL/SQL is a high level imperative, structured, statically type, and object orientated language. It is an implementation of the Ada (Pascal family) language.

    One designs and writes applications and interfaces and structured programs in PL/SQL. Not scripts using SQL*Plus. Just as one does not use the C language to write scripts for Linux on Windows .

    Do not treat a sophisticated programming language as a large hammer for driving screws into wood and metal.

    mathguy