0

I have a database called A2018 and three tables for testing SQL code: ALT, ALT1, ALT2.

So I am running this:

SELECT /*insert*/* FROM ALT;

This is the output I get:

        REM INSERTING into ALT
        SET DEFINE OFF;
        Insert into ALT (ENA,DYO) values ('2','3');
        Insert into ALT (ENA,DYO) values ('1','2');

So this only for one table and my question is how to achieve this output with all tables in a single sentence without doing it separate for each table like the output I get when exporting database from Tools.

This is what i am trying to achieve:

set echo off
set pagesize 0
set long 90000
SPOOL C:\Export15.sql;


SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
    FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
    FROM USER_INDEXES u;

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',u.constraint_name)
    FROM USER_CONSTRAINTS u;

select /*insert*/* from ALT ;--I WANT IT FOR ALL TABLES IN DATABASE AND NOT ONE BY ONE

SPOOL OFF;
8
  • You could script something in SQL*Plus. Commented Aug 29, 2016 at 18:51
  • Yes but what happens when tables become more than three?I will have to change the script every time?And if yes isn't there any better way?Thanks. Commented Aug 29, 2016 at 18:59
  • I'm pretty sure you could write a script that creates a script dynamically and executes it in order to handle a changing number of tables. Your question is a little vague though. Commented Aug 29, 2016 at 19:06
  • Do you have a link to see how ,so i can adjust it to my needs? Commented Aug 29, 2016 at 19:09
  • It's hard to give you much help without a complete question. Commented Aug 29, 2016 at 19:10

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.