Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Procedure with table name as parameter (Oracle 11g)
Oracle Procedure with table name as parameter [message #681756] Wed, 26 August 2020 12:48 Go to next message
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;
--moderator update: I've added [code] tags to make it more readable. Doesn't help with the bugs, though Sad 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 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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(Cool */ ... 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 #681760 is a reply to message #681758] Thu, 27 August 2020 00:45 Go to previous messageGo to next message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
Thanks Michel Cadot for suggestion.

Could you tell what will be code changes required as this procedure use to be take table name as parameter and insert data from one schema to another with table name same in both environment.
Re: Oracle Procedure with table name as parameter [message #681761 is a reply to message #681760] Thu, 27 August 2020 07:01 Go to previous messageGo to next message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
"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???
Re: Oracle Procedure with table name as parameter [message #681763 is a reply to message #681760] Thu, 27 August 2020 08:34 Go to previous messageGo to next message
EdStevens
Messages: 1317
Registered: September 2013
Senior Member
sunilpatro08 wrote on Thu, 27 August 2020 00:45
Thanks Michel Cadot for suggestion.

Could you tell what will be code changes required as this procedure use to be take table name as parameter and insert data from one schema to another with table name same in both environment.
YOu have to pass your "parameter" to the procedure just like you do for pTableName and p_array_size.
Re: Oracle Procedure with table name as parameter [message #681764 is a reply to message #681763] Thu, 27 August 2020 08:37 Go to previous messageGo to next message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
ok thanks for answer but what will be the inside code suggestion as just pointed invalid so what will be the chaanges i need to do apart from parameter
Re: Oracle Procedure with table name as parameter [message #681766 is a reply to message #681761] Thu, 27 August 2020 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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 #681771 is a reply to message #681766] Fri, 28 August 2020 05:35 Go to previous messageGo to next message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
Thanks for your input Michel Cadot.

what about the bulk insertion with LIMIT concept for huge table?
Re: Oracle Procedure with table name as parameter [message #681772 is a reply to message #681771] Fri, 28 August 2020 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How huge is huge for you?
It seems to be 5000 or 10000 which is far from huge, I have tables with 10 millions rows on my laptop.
What is the max number of rows in the tables you want to manage with this procedure?

Re: Oracle Procedure with table name as parameter [message #681773 is a reply to message #681772] Fri, 28 August 2020 05:48 Go to previous messageGo to next message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
for betterment of INSERTION i have just running insertion of 10000 each as batch if rows are more then 5000.

In my environment, tables having records starting with 1-36 millions
Re: Oracle Procedure with table name as parameter [message #681774 is a reply to message #681773] Fri, 28 August 2020 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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

Re: Oracle Procedure with table name as parameter [message #681775 is a reply to message #681774] Fri, 28 August 2020 06:01 Go to previous message
sunilpatro08
Messages: 7
Registered: August 2020
Junior Member
Thanks for your suggsetions / comments for this procedure. I will look into the same.

If further help required i will ask.
Previous Topic: Future PriceCalculation based on 3 columns
Next Topic: Oracle: create a partition by reference (partition by reference(fk)) on non referenced table
Goto Forum:
  


Current Time: Tue May 18 00:20:59 CDT 2021