Home » SQL & PL/SQL » SQL & PL/SQL » Need Insert Script dynamically (I want to generate Insert sctipt for different tables)
Need Insert Script dynamically [message #275258] Fri, 19 October 2007 01:33 Go to next message
gan.chowdary
Messages: 10
Registered: February 2007
Location: India
Junior Member

HI Experts,

I want to generate Insert script for a table(table will be different eact time).
Can any one please come with solution.

Regards,
Gandhi
Re: Need Insert Script dynamically [message #275259 is a reply to message #275258] Fri, 19 October 2007 01:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
use vi
Re: Need Insert Script dynamically [message #275262 is a reply to message #275259] Fri, 19 October 2007 01:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think that JRowbottom (?) has a neat excel file that generates the inserts for you.

But a lot depends on what data source you have. A bit more detail could help.

MHE
Re: Need Insert Script dynamically [message #275267 is a reply to message #275262] Fri, 19 October 2007 02:14 Go to previous messageGo to next message
gan.chowdary
Messages: 10
Registered: February 2007
Location: India
Junior Member

Not througt excel file.I want to generate from PL/SQL block.
Ex:if i give a table EMP

the result should be as follows

Insert into EMP(all columns from emp table)
values(First row values from emp table)

Insert into EMP(all columns from emp table)
values(Second row values from emp table)

and so on...

The following example can clear u.


--------------------------------------------

CREATE OR REPLACE PROCEDURE INSERT_SCRIPT_TEST AS
l_column_list VARCHAR(2000);
l_value_list VARCHAR(2000);
l_query VARCHAR(2000);
l_cursor INTEGER;
ignore NUMBER;
p_table VARCHAR2(30) :='TMP_TABLE_LIST'; --'CB_SUBSCRIBER_MASTER';

BEGIN
l_column_list := GET_COLS_1110(p_table);
DBMS_OUTPUT.PUT_LINE(l_column_list);
----l_query := get_query_1110(p_table,UPPER(p_default_col1),p_default_col1_value,UPPER(p_default_col2),p_default_col2_value);
l_query := GET_QUERY_1110(p_table,'SUBSCRIBER_CODE_N',1111,'IDENTIFICATION_NUM_V','ABCD');

l_cursor := dbms_sql.open_cursor;

DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list,2000);
ignore := DBMS_SQL.EXECUTE(l_cursor);
--
LOOP
IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(l_cursor, 1,l_value_list);
DBMS_OUTPUT.PUT_LINE('INSERT INTO '||p_table||'('||l_column_list||')');
DBMS_OUTPUT.PUT_LINE(' VALUES ('||l_value_list||');');
ELSE
EXIT;
END IF;
END LOOP;
END;
/

------------------
CREATE OR REPLACE FUNCTION GET_COLS_1110(p_table VARCHAR) RETURN VARCHAR
IS
l_cols VARCHAR(2000);
CURSOR l_col_cur(c_table VARCHAR) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER(c_table)
ORDER BY column_id;
BEGIN
l_cols := NULL;
FOR rec IN l_col_cur(p_table)
LOOP
l_cols := l_cols || rec.column_name || ',';
END LOOP;
RETURN SUBSTR(l_cols,1,LENGTH(l_cols)-1);
END;
/

--------------
CREATE OR REPLACE FUNCTION GET_QUERY_1110(p_table IN VARCHAR,p_default_col1 VARCHAR,p_default_col1_value VARCHAR,p_default_col2 VARCHAR,p_default_col2_value VARCHAR)
RETURN VARCHAR
IS

l_query VARCHAR(2000);
CURSOR l_query_cur(c_table VARCHAR ,c_default_col1 VARCHAR,c_default_col1_value VARCHAR,c_default_col2 VARCHAR,c_default_col2_value VARCHAR)
IS
SELECT
DECODE(column_name,c_default_col1,''''||REPLACE(c_default_col1_value,'''','''''')||'''',DECODE(column_name,c_default_col2,''''||REPLA CE(c_default_col2_value,'''','''''')||'''', 'decode('||column_name||',null,''null'','|| DECODE(data_type,'VARCHAR2','''''''''||'||column_name ||'||''''''''','DATE','''to_date(''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'') ||'''''',''''YYYY-MM-DD HH24:MI:SS'''')''',column_name) || ')' ))
column_query FROM user_tab_columns
WHERE table_name = UPPER(c_table)
ORDER BY column_id;
BEGIN

l_query := 'SELECT ';
FOR rec IN l_query_cur(p_table, p_default_col1,
p_default_col1_value, p_default_col2,
p_default_col2_value)
LOOP
l_query := l_query || rec.column_query || '||'',''||';
END LOOP;
l_query := SUBSTR(l_query,1,LENGTH(l_query)-7);

DBMS_OUTPUT.PUT_LINE(l_query || CHR(9) || p_table);
RETURN l_query || ' FROM ' || p_table;

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/
------------------------------------

Hope u understand my problem.
Here i am able to fetch the fields from Table but not data.


Regards,
Gandhi

Re: Need Insert Script dynamically [message #275268 is a reply to message #275267] Fri, 19 October 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Need Insert Script dynamically [message #275288 is a reply to message #275268] Fri, 19 October 2007 04:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah! Now I understand! You want to extract the values as insert statements! Let me see what we can come up with.

MHE
Re: Need Insert Script dynamically [message #275289 is a reply to message #275288] Fri, 19 October 2007 04:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Coming to think about it, this seems like a simple EXPORT/IMPORT thing.

Have you looked at this page yet?

MHE
Re: Need Insert Script dynamically [message #275301 is a reply to message #275288] Fri, 19 October 2007 06:17 Go to previous messageGo to next message
gan.chowdary
Messages: 10
Registered: February 2007
Location: India
Junior Member

Hi Maaher,

Tnx for u r reply.But i dont understand that and it dont seems solution for my problem.

Sql loaders are used to get data from FlatFile and inserts into DataBase right ??

So can u come up with a solution for that.



Gandhi
Re: Need Insert Script dynamically [message #275302 is a reply to message #275301] Fri, 19 October 2007 06:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're partially right: SQLLDR is used to load from a file to a database. But Tom Kyte has a zip file (here) that has "unload" utilities to create files you can use as data source for SQL*Loader to load into the target database.

The easiest way - by far - is to use export/import or datapump.

MHE
Re: Need Insert Script dynamically [message #275316 is a reply to message #275301] Fri, 19 October 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
gan.chowdary wrote on Fri, 19 October 2007 07:17


Tnx for u r reply.


Please read the rules of the forum. It's the first sticky. Ir maybe have a look at the foreign language forums.
Re: Need Insert Script dynamically [message #282202 is a reply to message #275258] Wed, 21 November 2007 03:02 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Here OP wants something like this :

He passes a table name into a PL/SQL procedure with primary key value of that table(It may be an option).
That procedure has to return the insert statement for that table and for that key value(It may be an option).
The table name which he going to pass may vary.

Say example we have table EMP and a column EMPNO = 7369. He will pass table name as EMP and value as 7369.
He wants to get desired output as insert statement for EMPNO = 7369.

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, GRADE, CREATED_BY,
CREATED_DT ) VALUES (
7369, 'SMITH', 'ENGG', 7902, TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 880, NULL, 20, NULL, NULL, NULL);
COMMIT;

Since the table name varies each time ,He does not want to take insert statement for that table using toad option or something else. He wants a process.

Even i had tried myself, could not get it.

Regards,
Kiran.
Re: Need Insert Script dynamically [message #282219 is a reply to message #282202] Wed, 21 November 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom definitive answer: How Can I unload data to a flat file

Regards
Michel
Re: Need Insert Script dynamically [message #282226 is a reply to message #275258] Wed, 21 November 2007 03:49 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ya sure, Thank you Michel.

Kiran.
Previous Topic: Rollback when create sequence
Next Topic: Simple Table Manipluation
Goto Forum:
  


Current Time: Sat Dec 10 13:12:14 CST 2016

Total time taken to generate the page: 0.09207 seconds