Four Stars

DDL script

Hi

I have a DDL script that contains creation of tables & sequences. I want to create a job for it. Plz let me know what components can I use for the same

  • Data Integration
9 REPLIES
Nine Stars TRF
Nine Stars

Re: DDL script

Hi,

 

tXxxxxRow (where Xxxxx is the db name, like Oracle, Mysql, and so on) is what you expect.


TRF
Four Stars

Re: DDL script

I already tried using tOracleRow however it takes only 1 SQL query. My requirement is to execute multiple SQL queries like running a script file containing DDL statements
Nine Stars TRF
Nine Stars

Re: DDL script

did you try to enclose commands between ""?

"create table blablabla;
create sequence blablabla;"

 


TRF
Four Stars

Re: DDL script

Yes; I already tried that enclosing between "" & separated by ; However the error which I get is 'SQL command not properly ended'.
Ten Stars

Re: DDL script

Try this.....

 

"begin
execute immediate 'CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
)';
execute immediate 'CREATE TABLE address
( address_id number(10) NOT NULL,
  address_line_1 varchar2(50) NOT NULL,
  city varchar2(50)
)';
commit;
end;"
Rilhia Solutions
Nine Stars TRF
Nine Stars

Re: DDL script

Is it a plain text script or a generated one using variables and concatenation?
Can you share it?

TRF
Four Stars

Re: DDL script

It is a normal script generated by SQL developer. It does not have variables or concatenation.
Four Stars

Re: DDL script

In which component should I give this?
Ten Stars

Re: DDL script

The code I gave was an example of how you should code your tOracleRow component. The "Begin" and "End" keywords are standard Oracle functionality to allow multiple statements to fired together. Here is a description of the "Execute immediate" statemement (https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm). 

Rilhia Solutions