Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL - Passing PARAMETER as a TABLE
icon6.gif  PL/SQL - Passing PARAMETER as a TABLE [message #429901] Fri, 06 November 2009 03:44 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi akl.

Here is my plan...
CREATE OR REPLACE PROCEDURE P_NIS_DEL_NONMIGRATED (table_name IN varchar2, column_name IN varchar2) as

cursor s1 is SELECT rowid, t.column_name
from table_name t
where not exists (select LOCN_TTNAME from LOCATIONS where LOCN_TTNAME = column_name); 

-- cursor s1 is SELECT rowid, t.LOCN_TTNAME from LOCATIONS t;
  
i number := 0;
j number := 0;
start_time date := sysdate;
proc_name VARCHAR2(36) := 'P_NIS_DEL_NONMIGRATED_'||table_name;

begin
  for c1 in s1 loop
      delete from table_name where rowid = c1.rowid;

      i := i + 1;              -- Commit after every 1500 records
      if i > 1500 then
         commit;
         i := 0;
      end if;
	
	j := j + 1; 
	
  end loop;
  commit;
  
     dbms_output.put_line(chr(0));
	 dbms_output.put_line('DELETION for '||table_name);
     dbms_output.put_line('-------------------------------------------------');
     dbms_output.put_line(chr(0));
     DBMS_OUTPUT.PUT_LINE (j ||' rows have been deleted!'); 
	 dbms_output.put_line(chr(0));
	 
	insert into PROCEDURE_TRACKING_LOG
	(procedure_name, table_name, rows_inserted, rows_updated, rows_deleted, 
rows_inserted_of_deletion, rows_inserted_after_deletion, 
	rows_rejected, start_time, end_time, 
elapse_time, remarks, subject_area)
	values
	(proc_name,table_name,NULL,NULL,j,NULL,NULL,NULL,start_time,sysdate,
(sysdate-start_time)* 86400,
	NULL,'DELETION (NIS - NON MIGRATED)');
	
  commit;
  
end;
/

That is I want to pass a table_name as well as its column_name as the parameter to the procedure.

And then the procedure will execute according to the given parameters.

Is it possible to do something like that?

Thank you very much.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

[Updated on: Fri, 06 November 2009 04:54] by Moderator

Report message to a moderator

Re: PL/SQL - Passing PARAMETER as a TABLE [message #429903 is a reply to message #429901] Fri, 06 November 2009 03:47 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
DYNAMIC SQL
SEARCH IN http://tahiti.oracle.com
Re: PL/SQL - Passing PARAMETER as a TABLE [message #429906 is a reply to message #429901] Fri, 06 November 2009 03:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is about the worst way to achieve what you want.
First of all, you do not want to provide people with a procedure that can effectively remove all records from all tables.
I can not imagine that you don't foresee for which tables this procedure is going to be used. So, write the static sql-commands for each of these tables and use IF-THEN constructs to execute the desired one.

Then the deletion itself. You first fetch, one row at a time, each row you want to delete.
If you have sufficient rollback/undo space, just do the delete in one go, without intermediate commits.
If you cannot do that for whatever reason, don't fetch the rows you will delete, but use the cursor-query in your delete-statement and bind the number of rows hit by using WHERE ROWNUM <= 1500
icon7.gif  Re: PL/SQL - Passing PARAMETER as a TABLE [message #430137 is a reply to message #429903] Mon, 09 November 2009 02:06 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Its_me_ved wrote on Fri, 06 November 2009 17:47
DYNAMIC SQL
SEARCH IN http://tahiti.oracle.com

Thanks for the idea... http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

So here is what I got...
CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number := 0;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   j := sql%rowcount;
	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (to_char(j)||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/


But now, there is one more little problem. How do I capture the number of the deleted rows? Coz' it seems that it cannot be treated like the standard PL/SQL convention I guess.

Thank you for helping.
Re: PL/SQL - Passing PARAMETER as a TABLE [message #430140 is a reply to message #429906] Mon, 09 November 2009 02:10 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Frank wrote on Fri, 06 November 2009 17:57
This is about the worst way to achieve what you want.
First of all, you do not want to provide people with a procedure that can effectively remove all records from all tables.
I can not imagine that you don't foresee for which tables this procedure is going to be used. So, write the static sql-commands for each of these tables and use IF-THEN constructs to execute the desired one.

Then the deletion itself. You first fetch, one row at a time, each row you want to delete.
If you have sufficient rollback/undo space, just do the delete in one go, without intermediate commits.
If you cannot do that for whatever reason, don't fetch the rows you will delete, but use the cursor-query in your delete-statement and bind the number of rows hit by using WHERE ROWNUM <= 1500

Thank you very much for your alert and also thank you for your advice.

It's just that, those tables that are going to be deleted are the huge one. So I guess by doing it within a PL/SQL with a subsequent commit, it would be save.

And since it's going to use the same concept of deletion, that's why I was thinking of creating some flexible PL/SQL rather than a single PL/SQL for each one.

That's all.

Besides, since the procedure requires parameters to execute, there will be less harm I guess?

Thank you.
Re: PL/SQL - Passing PARAMETER as a TABLE [message #430141 is a reply to message #430140] Mon, 09 November 2009 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Besides, since the procedure requires parameters to execute, there will be less harm I guess?

Search for SQL INJECTION.

Regards
Michel
icon7.gif  Re: PL/SQL - Passing PARAMETER as a TABLE [message #430268 is a reply to message #429901] Mon, 09 November 2009 19:38 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Thanks Michael for your concern.
icon9.gif  Re: PL/SQL - Passing PARAMETER as a TABLE [message #430269 is a reply to message #429901] Mon, 09 November 2009 19:40 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
So anyone could help me for that row count?

I've been looking for a solution but it seems they are too complicated for just what I want to achieve. Sad
Re: PL/SQL - Passing PARAMETER as a TABLE [message #430270 is a reply to message #430269] Mon, 09 November 2009 20:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
aimy wrote on Mon, 09 November 2009 17:40
So anyone could help me for that row count?

I've been looking for a solution but it seems they are too complicated for just what I want to achieve. Sad


TOO COMPLICATED?

feedback := DBMS_SQL.EXECUTE (cursor_handle);
j := sql%rowcount;

Re: PL/SQL - Passing PARAMETER as a TABLE [message #430304 is a reply to message #430270] Tue, 10 November 2009 00:14 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 10 November 2009 10:07
aimy wrote on Mon, 09 November 2009 17:40
So anyone could help me for that row count?

I've been looking for a solution but it seems they are too complicated for just what I want to achieve. Sad


TOO COMPLICATED?

feedback := DBMS_SQL.EXECUTE (cursor_handle);
j := sql%rowcount;


Thanks..

But it displays 0 as the output.. Sad
exec ZZZ_P_DYNAMIC('ZZZ_DYNAMIC','ANY_COLUMN','SL');


DELETION for ZZZ_DYNAMIC
-------------------------------------------------

0 rows have been deleted!


PL/SQL procedure successfully completed.


Here is my complete procedure:
CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);
   j := sql%rowcount;

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (j||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/

Supposedly it should yields 5731 rows have been deleted!

Please help.

Thank you very much. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif
Re: PL/SQL - Passing PARAMETER as a TABLE [message #430318 is a reply to message #430140] Tue, 10 November 2009 01:00 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
And since it's going to use the same concept of deletion, that's why I was thinking of creating some flexible PL/SQL rather than a single PL/SQL for each one.


This will a performance bottleneck.
Re: PL/SQL - Passing PARAMETER as a TABLE [message #430331 is a reply to message #430304] Tue, 10 November 2009 02:11 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
CREATE OR REPLACE PROCEDURE ZZZ_P_DYNAMIC 
   (table_in IN VARCHAR2, column_in in VARCHAR2, string_in in VARCHAR2)
IS
   cursor_handle INTEGER;
   feedback INTEGER;
   j number;
      
BEGIN
   /* Create a cursor to use for the dynamic SQL */
   cursor_handle := DBMS_SQL.OPEN_CURSOR;

   /* Construct the SQL statement and parse it in native mode. */
   DBMS_SQL.PARSE 
      (cursor_handle,'delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''',
       DBMS_SQL.NATIVE);
	   	   
	commit;

   /* You should always execute your DDL! */
   feedback := DBMS_SQL.EXECUTE (cursor_handle);
   j :=DBMS_SQL.LAST_ROW_COUNT ;

   DBMS_SQL.CLOSE_CURSOR (cursor_handle);
   commit;
   
	dbms_output.put_line(chr(0));
	dbms_output.put_line('DELETION for ZZZ_DYNAMIC');
	dbms_output.put_line('-------------------------------------------------');
	dbms_output.put_line(chr(0));
	DBMS_OUTPUT.PUT_LINE (j||' rows have been deleted!');
	dbms_output.put_line(chr(0));
	
END ZZZ_P_DYNAMIC;
/
CREATE OR REPLACE PROCEDURE zzz_p_dynamic (
   table_in    IN   VARCHAR2,
   column_in   IN   VARCHAR2,
   string_in   IN   VARCHAR2
)
IS
   sql_query   VARCHAR2 (2000);
   j           NUMBER;
BEGIN
sql_query:='delete from '||table_in||' where '||column_in||' like ''%'||string_in||'%'||'''';


   EXECUTE IMMEDIATE sql_query;

   j := SQL%ROWCOUNT;
   COMMIT;
   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line ('DELETION for ZZZ_DYNAMIC');
   DBMS_OUTPUT.put_line ('-------------------------------------------------');
   DBMS_OUTPUT.put_line (CHR (0));
   DBMS_OUTPUT.put_line (j || ' rows have been deleted!');
   DBMS_OUTPUT.put_line (CHR (0));
END zzz_p_dynamic;
/

both work
Quote:
BlackSwan wrote on Tue, 10 November 2009 10:07
aimy wrote on Mon, 09 November 2009 17:40
So anyone could help me for that row count?

I've been looking for a solution but it seems they are too complicated for just what I want to achieve.

TOO COMPLICATED?

feedback := DBMS_SQL.EXECUTE (cursor_handle);
j := sql%rowcount;



from
http://download-west.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm

In the following situations, you must use native dynamic SQL instead of the DBMS_SQL package:

The dynamic SQL statement retrieves rows into records.

You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, or single-row SELECT statement.


[Updated on: Tue, 10 November 2009 02:23]

Report message to a moderator

Re: PL/SQL - Passing PARAMETER as a TABLE [message #430358 is a reply to message #430331] Tue, 10 November 2009 03:28 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Thank you very much sir Anand.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

Besides helping to correct my original PL/SQL, you also had given me a much nicer and simpler PL/SQL that is very clear and intuitive.

Thank you thank you so much.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif I really appreciate it.

May God be always with you.. http://www.cichlidmadness.com/forums/style_emoticons/default/nodding.gif
icon9.gif  Re: PL/SQL - Passing PARAMETER as a TABLE [message #431676 is a reply to message #429901] Thu, 19 November 2009 01:55 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hello again..

Now I would like to extend this matter to commit for every 1000 rows.

Because the table that I want to delete is so huge, so the deletion is always stuck with the snapshot/rollback error.

I have to tried adding this:
	if j > 500 then
	commit;
	end if;


But it seems that the script will continue to finish the deletion before the j value is fetched, I presume.

Thank you.
Re: PL/SQL - Passing PARAMETER as a TABLE [message #431714 is a reply to message #431676] Thu, 19 November 2009 04:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The "snapshot" error as you call it, is CAUSED by the intermediate commits, not solved.
Re: PL/SQL - Passing PARAMETER as a TABLE [message #431715 is a reply to message #431676] Thu, 19 November 2009 04:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The correct solution to this is to get your DBA to size the undo/rollback space correctly for the work that needs to be done.

Previous Topic: Not able to see package body only able to see specfication
Next Topic: Cursor Fetch For More than one tables
Goto Forum:
  


Current Time: Sat Dec 10 17:02:23 CST 2016

Total time taken to generate the page: 0.10068 seconds