Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA error - Sql script -

S567Jul 22 2021

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;

Comments

User_H3J7U

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

Frank Kulash

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

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

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.

1 - 4

Post Details

Added on Jul 22 2021
4 comments
123 views