CODEWITHSUNDEEP
sqloracle-databaseplsql
Eoin2211
Eoin2211

Reputation: 911

PL SQL Create table with concatenated fields

I need to create a table and add certain characters to each field. I need each field to be enclosed by "" and I also need the fields to be pipe delimited | My code is as follows

Begin
execute immediate 'Create Table dbo.Temp_Weekly_Export_File as
Select ''"'' ||A.unique_id || ''"|'' as unique_id,
   ''"'' ||A.name || ''"|'' as name,
   ''"'' ||A.alt_name || ''"|'' as alt_name,
   ''"'' ||A.entity_type || ''"|'' as entity_type, 
   ''"'' ||A.party_type || ''"|'' as party_type,
   ''"'' ||A.reference_# || ''"|'' as reference_#, 
   ''"'' ||A.addr1 || ''"|'' as addr1 ,
   ''"'' ||A.addr2 || ''"|'' as addr2,
   ''"'' ||A.addr3 || ''"|'' as addr3,
   ''"'' ||A.town || ''"|'' as town,
   ''"'' ||A.county || ''"|'' as county,
   ''"'' ||A.postcode || ''"|'' as postcode, 
   ''"'' ||A.country || ''"|'' as country,
   ''"'' ||A.alt_address1 || ''"|'' as alt_address1, 
   ''"'' ||A.alt_address2 || ''"|'' as alt_address2,
   ''"'' ||A.alt_address3|| ''"|'' as alt_address3, 
   ''"'' ||A.alt_town || ''"|'' as alt_town,
   ''"'' ||A.alt_county || ''"|'' as alt_county, 
   ''"'' ||A.alt_post_code || ''"|'' as alt_post_code,
   ''"'' ||A.alt_country || ''"|'' as alt_country, 
   ''"'' ||A.nationality || ''"|'' as nationality,
   ''"'' ||A.dob || ''"|'' as dob,
   ''"'' ||A.individual_id || ''"|'' as individual_id,
   ''"'' ||A.individual_id_type || ''"|'' as individual_id_type ,
   ''"'' ||A.country_of_registration|| ''"|'' as country_of_registration, 
   ''"'' ||A.company_id || ''"|'' as company_id,
   ''"'' ||A.company_id_type || ''"|'' as company_id_type  ,
   ''"'' ||A.source_country || ''"|'' as source_country,
   ''"'' ||A.source_system || ''"|'' as source_system,
   ''"'' ||A.transaction_type|| ''"|'' as transaction_type
   From dbo.Temp_Weekly_Export A';
   End;

I am getting the following error message 10:22:26 ORA-00923: FROM keyword not found where expected

Upvotes: 0

Views: 95

Answers (3)

Brian Leach
Brian Leach

Reputation: 2101

I like ALEKSEJ's answer. Whenever I have to use double quotes, I substitute the Q quote syntax. I find it easier to read and to make changes. I no longer have to double up the quotes. Here is Alek's solution using Q quotes:

BEGIN
      SELECT    'select '
             || RTRIM (
                    LISTAGG (q'['"' ||]' ||  column_name || q'[||'"|' as ]' || column_name || ',')
                        WITHIN GROUP (ORDER BY column_name)
                  , ','
                )
             || ' from '
             || table_name
        INTO vsql
        FROM user_tab_columns
       WHERE table_name = 'SOURCE'
    GROUP BY table_name;

    --
    vsql   := 'create table target as ' || vsql;

    EXECUTE IMMEDIATE vsql;
END;

Q quotes can be embraced with the paired symbols

q'[ ]', q'{ }', q'< >', or q'( )'

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

Without need to explicitly write all the column names, you may use the following

Setup:

create table source(colA, colB, colC) as (
    select 1, 2, 3 from dual union all
    select 4, 5, 6 from dual
)

The plsql:

declare
    vSQL varchar2(1000);
begin

    select 'select ' || rtrim(listagg('''"''||' || column_name || '||''"|'' as ' || column_name || ',') within group ( order by column_name), ',') || ' from ' || table_name
    into vSQL
    from user_tab_columns
    where table_name = 'SOURCE'          
    group by table_name;
    --
    vSQL := 'create table target as ' || vSQL;
    execute immediate vSQL;
end;

The result:

SQL> select * from target;

COLA       COLB       COLC
---------- ---------- ----------
"1"|       "2"|       "3"|
"4"|       "5"|       "6"|

Upvotes: 2

XING
XING

Reputation: 9886

See example below. You just need to additional ' around "

declare
var varchar2(2000);
BEGIN

 var:= 'Create  Table Temp_Weekly_Export_File as
        Select ''"'' ||UNIQUE_ID|| ''"|'' as unique_id,
         ''"'' ||NAME || ''"|'' as name
         From Test ';

   --dbms_output.put_line(var);
     EXECUTE IMMEDIATE var;

END;

Demo:

SQL> CREATE TABLE  TEST
(
  UNIQUE_ID  VARCHAR2(103 CHAR),
  NAME       VARCHAR2(103 CHAR)
);

Table created.

SQL> declare
var varchar2(2000);
BEGIN

 var:= 'Create  Table Temp_Weekly_Export_File as
        Select ''"'' ||UNIQUE_ID|| ''"|'' as unique_id,
         ''"'' ||NAME || ''"|'' as name
         From Test ';

    --dbms_output.put_line(var);
    EXECUTE IMMEDIATE var;

END;  
  /

PL/SQL procedure successfully completed.

SQL> select * from Temp_Weekly_Export_File;

no rows selected

Upvotes: 1

Related Questions