| Oracle Procedure with table name as parameter [message #681756] |
Wed, 26 August 2020 12:48  |
 |
sunilpatro08
Messages: 7
Registered: August 2020
|
Junior Member |
|
|
Oracle Procedure with table name as parameter.
the below procedure is to be usedd for data insertion with table name from parameter. So i got error for creating TABLE TYPE in the below code. I want to create a TABLE type of %ROWTYPE on which table name pass as parameter. Please guide me.
here is the error on this line - TYPE TAB_NAME IS TABLE OF pTableName%rowtype;
Also if anything u feel not good then suggest the same.
create or replace PROCEDURE INSERT_BULK_DATA_TABS
(
pTableName IN USER_TABLES.table_name%type,
p_array_size IN PLS_INTEGER DEFAULT 10000
)
IS
COUNT_TB_ROWS NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('INPUT TABLE NAME.. '||pTableName);
EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL(8) */ COUNT(1) INTO COUNT_TB_ROWS FROM :A@DEV_DBLINK' USING pTableName;
IF COUNT_TB_ROWS <= 5000 THEN
EXECUTE IMMEDIATE 'INSERT INTO :TABLE_NAME SELECT * FROM :TABLE_NAME@DEV_DBLINK' USING pTableName;
ELSE
-- ELSE part exection start
DECLARE
type rc is ref cursor;
TAB_CURSR rc;
sql_stmt VARCHAR2(200);
-- Delacre collection of table type
TYPE TAB_NAME IS TABLE OF pTableName%rowtype;
V_TAB_TYPE TAB_NAME;
BEGIN
sql_stmt := 'SELECT * FROM '||pTableName||';' ;
OPEN TAB_CURSR FOR sql_stmt;
LOOP
FETCH TAB_CURSR BULK COLLECT INTO V_TAB_TYPE LIMIT p_array_size;
FORALL i IN V_TAB_TYPE.FIRST..V_TAB_TYPE.LAST
EXECUTE IMMEDIATE 'INSERT INTO '||pTableName||' VALUES :1' USING V_TAB_TYPE(i);
COMMIT;
EXIT WHEN TAB_CURSR%NOTFOUND;
END LOOP;
CLOSE TAB_CURSR;
COMMIT;
END;
-- ELSE part exection end
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('');
END; jw.
[Updated on: Wed, 26 August 2020 14:14] by Moderator Report message to a moderator |
|
|
|
| Re: Oracle Procedure with table name as parameter [message #681758 is a reply to message #681756] |
Wed, 26 August 2020 14:10  |
 |
Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
There are many errors in your procedure:
- "FROM :TABLE_NAME@DEV_DBLINK": you cannot use an object as bind variable, bind variable are only for data
(* in addition you have 2 bind variables in the statement text but pass only 1)
- "pTableName%rowtype": this expression is invalid: as the type is defined at compile time it cannot depends on the parameter value
- "WHEN OTHERS THEN": this is the biggest bug you can ever write in PL/SQL; read WHEN OTHERS
- Using table name like this is a security hole, you are vulnerable to SQL injection
- "SELECT COUNT(1)": the proper expression is "COUNT(*)" why do you want Oracle to count some "1"?
- "SELECT /*+ PARALLEL(
 */ ... FROM :A@DEV_DBLINK": are you sure the remote site can and wants you to open 9 parallel processes?
- "OPEN ... LOOP FETCH ... FORALL ...": bad practice, you should use INSERT SELECT
- "FORALL ... EXECUTE IMMEDIATE 'INSERT INTO '||pTableName||' VALUES :1' USING V_TAB_TYPE(i)" is not possible, FORALL applies only on INSERT, UPDATE, DELETE statements, not on EXECUTE IMMEDIATE
- "COMMIT": NEVER commits inside a procedure, the caller knows if it wants to commit or not, NOT the procedure
(* in addition you commit when the number of rows is > 5000 but not < 5000, how the caller will know?)
- ...
[Updated on: Wed, 26 August 2020 14:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Procedure with table name as parameter [message #681766 is a reply to message #681761] |
Thu, 27 August 2020 10:07 |
|
Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
sunilpatro08 wrote on Thu, 27 August 2020 14:01"FROM :TABLE_NAME@DEV_DBLINK": you cannot use an object as bind variable, bind variable are only for data
(* in addition you have 2 bind variables in the statement text but pass only 1) -- because there 2 bind variables uses 1 name so used one so suggest what need to changes..
"pTableName%rowtype": this expression is invalid: as the type is defined at compile time it cannot depends on the parameter value -- so how to make that possible as i need to take table name as parameter and create table type of the same..
"OPEN ... LOOP FETCH ... FORALL ...": bad practice, you should use INSERT SELECT -- what to use can u suggest...
"FORALL ... EXECUTE IMMEDIATE 'INSERT INTO '||pTableName||' VALUES :1' USING V_TAB_TYPE(i)" is not possible, FORALL applies only on INSERT, UPDATE, DELETE statements, not on EXECUTE IMMEDIATE -- here how to make dynamic INSERT statment???
Replace the whole procedure by one "line" for all cases:
EXECUTE IMMEDIATE '
INSERT INTO '||DBMS_ASSERT.SQL_OBJECT_NAME(pTableName)||'
SELECT * FROM '||DBMS_ASSERT.QUALIFIED_SQL_NAME(pTableName||'@DEV_DBLINK'); [Updated on: Thu, 27 August 2020 10:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Oracle Procedure with table name as parameter [message #681774 is a reply to message #681773] |
Fri, 28 August 2020 05:58 |
|
Michel Cadot
Messages: 68420
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
On a server, 50 millions rows can be handled in a whole you don't need to split the load.
Anyway, if you really want to do it (but why?), you can't do it like you want to do because, as I said, this requires the object to be known at compile time.
You have to do it manually using DBMS_SQL package.
You can also investigate DBMS_PARALLEL_EXECUTE package which will build the chunks (but I'm not sure it'll work with a remote database).
[Updated on: Fri, 28 August 2020 07:34] Report message to a moderator |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
