| procedure creates the insert statement [message #372841] |
Tue, 13 March 2001 07:58  |
rakesh
Messages: 25
Registered: March 2001
|
Junior Member |
|
|
Hello Sir
How R u I am Fine With My Health and Grat Oracle DBA Job i need Help From U sir I want Script From U
That Is procedure Take value user(table name ) that is i have some tables ok tables have rows ok , i want out put from the that procedure is if the table have the 100 rows ok that should create or out put should be 100 insert statement all actual values from what table name i have submitted so i want script because our clients dont knows the sql loader and exp imp utilities they are technically not that much sound so i will spool the whatever out from this script i will send them they can run this script ok and another thing is condition is the we have to avoid bad characters like &, ' etc single code and ampersant and pls send script to me i am tryinfg here
Yours Site team Member
Rakesh
Oracle DBA
|
|
|
|
| Re: procedure creates the insert statement [message #372847 is a reply to message #372841] |
Tue, 13 March 2001 11:30  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Use TOAD, or see the following script...
As far as & is concerned, in SQLplus you need to "set scan off" to disable variable substitution.
PROCEDURE punloadrecord (
ppowner IN VARCHAR2,
pptablename IN VARCHAR2,
ppwhereclause IN VARCHAR2)
IS
/****************************************************************************
Author: Sean Cassidy (scassidy@ozemail.com.au) December 1999
Purpose: Unload a record to be a SQL INSERT Statement.
Primarily for one record but will handle if many.
Parameters
Name Details
---------------- ------------------------------------------------------------
ppOwner The Owner of the table.
ppTableName The Name of the Table from which the record is to be
Unloaded from
ppWhereClause A CLAUSE to identify the record. May select multiple records
NB: This Procedure is about as robust as those little sachets
of International Roast Coffee that you get in cheap hotel rooms
It will fall over for any number of reasons and does not handle LONG
****************************************************************************/
CURSOR ctablecolumns (cpowner VARCHAR2, cptablename VARCHAR2)
IS
SELECT atc.column_name, atc.data_type
FROM all_tab_columns atc, all_tables ats
WHERE
--== Join to All_Tables so we can't get views (Inserts would fail) ==--
atc.owner = UPPER (cpowner)
AND atc.owner = ats.owner
AND atc.table_name = ats.table_name
AND ats.table_name = UPPER (cptablename)
AND atc.data_type IN ('DATE', 'NUMBER', 'VARCHAR2');
rtablecolumns ctablecolumns%ROWTYPE;
--
TYPE ttctable IS TABLE OF ctablecolumns%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE tcursortable IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
TYPE tvaltable IS TABLE OF VARCHAR2(2000)
INDEX BY BINARY_INTEGER;
lvcursortable tcursortable;
lvtctable ttctable;
lvvaluetable tvaltable;
lvselectioncursor INTEGER;
lvselectstatement VARCHAR2(2000);
lvcolumnlist VARCHAR2(2000);
i BINARY_INTEGER := 0;
lvnumcolumns NUMBER := 0;
lvcurrentrow ROWID;
lvignore NUMBER;
lvcurrentvalue VARCHAR2(2000);
BEGIN -- pUnloadRecord;
--== Fetch our columns into a pl/sql table ==--
FOR rtablecolumns IN ctablecolumns (ppowner, pptablename)
LOOP
i := i + 1;
lvtctable (i) := rtablecolumns;
END LOOP;
lvnumcolumns := i;
<<columnlist_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
-- Add each column name to a list
IF lvcolumnlist IS NULL
THEN
lvcolumnlist := lvtctable (i).column_name;
ELSE
lvcolumnlist := lvcolumnlist || ','|| lvtctable (i).column_name;
END IF; -- ColumnList is NULL
IF lvtctable (i).data_type = 'DATE'
THEN
--== All the quotes are so our strings have enough in the end ==--
lvselectstatement :=
'SELECT ''to_date(''''''||to_char('||
lvtctable (i).column_name ||
', ''yyyymmddhh24miss'')||
'''''', ''''yyyymmddhh24miss'''')'' FROM '||
pptablename ||
' WHERE rowid = :x';
ELSIF lvtctable (i).data_type = 'NUMBER'
THEN
lvselectstatement :=
'SELECT to_char('||
lvtctable (i).column_name ||
')'||
' FROM '||
pptablename ||
' WHERE rowid = :x';
ELSIF lvtctable (i).data_type = 'VARCHAR2'
THEN
lvselectstatement :=
'SELECT ''''''''||'||
lvtctable (i).column_name ||
'||'''''''' FROM '||
pptablename ||
' WHERE rowid = :x';
END IF; -- Data type
lvcursortable (i) := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lvcursortable (i), lvselectstatement, DBMS_SQL.native);
END LOOP column_list_loop;
--===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
--== Because DBMS_SQL requires us to call define_column for each ==--
--== column, we cannot have dynamic number of columns fetched ==--
--== Instead, we need to fetch each column seperately... Bugger! ==--
--===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
lvselectstatement :=
'SELECT rowid'|| ' FROM '|| pptablename || ' WHERE '|| ppwhereclause;
lvselectioncursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lvselectioncursor, lvselectstatement, DBMS_SQL.native);
DBMS_SQL.define_column_rowid (lvselectioncursor, 1, lvcurrentrow);
lvignore := DBMS_SQL.execute (lvselectioncursor);
<<fetch_rows_loop>>
LOOP
IF DBMS_SQL.fetch_rows (lvselectioncursor) > 0
THEN
DBMS_SQL.column_value (lvselectioncursor, 1, lvcurrentrow);
<<column_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
DBMS_SQL.define_column (lvcursortable (i), 1, lvcurrentvalue, 2000);
DBMS_SQL.bind_variable (lvcursortable (i), 'x', lvcurrentrow);
lvignore := DBMS_SQL.execute (lvcursortable (i));
IF DBMS_SQL.fetch_rows (lvcursortable (i)) > 0
THEN
DBMS_SQL.column_value (lvcursortable (i), 1, lvcurrentvalue);
ELSE
lvcurrentvalue := NULL;
END IF;
IF lvcurrentvalue IS NULL
THEN
lvcurrentvalue := 'NULL';
END IF;
lvvaluetable (i) := lvcurrentvalue;
END LOOP column_loop;
--== Output this row ==--
DBMS_OUTPUT.put_line (
'INSERT INTO '|| ppowner || '.'|| pptablename || '('
);
pprintcolumnlist (lvcolumnlist);
DBMS_OUTPUT.put_line (') VALUES (');
<<output_values>>
FOR i IN 1 .. lvnumcolumns
LOOP
IF i > 1
THEN
DBMS_OUTPUT.put (',');
END IF;
pprintstring (lvvaluetable (i));
END LOOP output_values;
DBMS_OUTPUT.put_line (')');
DBMS_OUTPUT.put_line ('/');
ELSE
EXIT fetch_rows_loop;
END IF; -- fetch > 0
END LOOP fetch_rows_loop;
--== Close all the cursors ==--
<<column_cursor_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
DBMS_SQL.close_cursor (lvcursortable (i));
END LOOP column_cursor_loop;
DBMS_SQL.close_cursor (lvselectioncursor);
END punloadrecord;
/
|
|
|
|
|
|
|
|
|
|
| Re: procedure creates the insert statement [message #373017 is a reply to message #372864] |
Thu, 22 March 2001 13:17 |
Samreen Surayya
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
Hey Salman Khan,
Please why are you using foul language
in normal conversation. Please realize the Critical importance of communicating with others.
May be he is not able to explain the things more clearly that could happen with anyone.Every one need not be in the same boat as you are.
Thanks,
Samreen .
|
|
|
|
| Re: procedure creates the insert statement [message #373018 is a reply to message #372864] |
Thu, 22 March 2001 13:17 |
Samreen Surayya
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
Hey Salman Khan,
Please why are you using foul language
in normal conversation. Please realize the Critical importance of communicating with others.
May be he is not able to explain the things more clearly that could happen with anyone.Every one need not be in the same boat as you are.
Thanks,
Samreen .
|
|
|
|
| Re: procedure creates the insert statement [message #373019 is a reply to message #372864] |
Thu, 22 March 2001 13:17 |
Samreen Surayya
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
Hey Salman Khan,
Please why are you using foul language
in normal conversation. Please realize the Critical importance of communicating with others.
May be he is not able to explain the things more clearly that could happen with anyone.Every one need not be in the same boat as you are.
Thanks,
Samreen .
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Help [message #373854 is a reply to message #372841] |
Tue, 15 May 2001 23:44 |
suneeldixit
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
Sir,
i have the problem,
problem is
Suppose u have 2 rows in a table,
and i want to insert the 3 row between the existing two rows..
What will be the query for it
with regards
Suneel Dixit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
