Home » SQL & PL/SQL » SQL & PL/SQL » comments on approach
comments on approach [message #295889] Wed, 23 January 2008 17:14 Go to next message
maham_158
Messages: 5
Registered: January 2008
Junior Member
Hi All,

I have written this procedure in two different ways. I need your comments on the approach. Both procedures work propely, means gives the required output. I am confused as i have posted my procedure on the fourm and firends said that your approach is not good and posted anoter approach but i am confused whats the use of fatch while i am not using it. Hope someone could help to finish this confusion. I am posting here both approaches.

My approach:

create or replace PROCEDURE the_with_char (tablename in varchar2, fieldname in varchar2)

AUTHID CURRENT_USER

IS


	SQLST		VARCHAR2(500);

	THEVALUE	VARCHAR2(250);

BEGIN

--.THE 

			SQLST := 'UPDATE '||UPPER(TABLENAME)|| ' SET '||UPPER(FIELDNAME);

			SQLST :=SQLST ||'=LTRIM('||UPPER(FIELDNAME)||',''.'')';

			SQLST :=SQLST ||' WHERE SUBSTR('||UPPER(FIELDNAME)||',1,5)=''.THE ''';
			
			DBMS_OUTPUT.PUT_LINE(SQLST);

			BEGIN

				EXECUTE IMMEDIATE SQLST;

					EXCEPTION WHEN OTHERS THEN

						NULL;

			END;

Friends approach:

create or replace PROCEDURE TEST (tablename in varchar2, fieldname in varchar2)

AUTHID CURRENT_USER

IS

TYPE THECURTYP IS REF CURSOR;

	THE_CUR		THECURTYP;

	SQLST		VARCHAR2(250);

	THEVALUE	VARCHAR2(250);

	CNT		NUMBER;

BEGIN

--.THE 

	SQLST := 'SELECT COUNT('||UPPER(FIELDNAME)||') COUNTER FROM ' ||UPPER(TABLENAME);

	SQLST := SQLST || ' WHERE SUBSTR('||UPPER(FIELDNAME)||',1,5)=''.THE ''';


	DBMS_OUTPUT.PUT_LINE(SQLST);


	OPEN THE_CUR FOR SQLST;

	FETCH THE_CUR INTO CNT;
	
		DBMS_OUTPUT.PUT_LINE(CNT);
	
	CLOSE THE_CUR;

IF CNT>0 THEN 		



SQLST := 'UPDATE '||UPPER(TABLENAME)|| ' SET '||UPPER(FIELDNAME);

			SQLST :=SQLST ||'=LTRIM('||UPPER(FIELDNAME)||',''.'')';

			SQLST :=SQLST ||' WHERE SUBSTR('||UPPER(FIELDNAME)||',1,5)=''.THE ''';
			
			DBMS_OUTPUT.PUT_LINE(SQLST);

			
				EXECUTE IMMEDIATE SQLST;

				DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

END IF;

			EXCEPTION WHEN OTHERS THEN

			DBMS_OUTPUT.PUT_LINE(SQLERRM);

COMMIT;


END TEST;

/


[Code Formatted by moderator]

[Updated on: Wed, 23 January 2008 20:16] by Moderator

Report message to a moderator

Re: comments on approach [message #295896 is a reply to message #295889] Wed, 23 January 2008 20:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I mean this in the nicest possible way - and I truly mean no offence; your friend is an idiot.

The equivalent in real life is making a shopping list, driving to the supermarket to see if they have all of the things on your list, driving home, crossing off the things on the list that they don't have, and then driving back to buy the things that they do have.

Ross Leishman
Re: comments on approach [message #295958 is a reply to message #295889] Thu, 24 January 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, 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.
Indent the code.
Remove useless empty lines.
Use the "Preview Message" button to verify.

Regards
Michel
Re: comments on approach [message #296207 is a reply to message #295896] Fri, 25 January 2008 01:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rleishman wrote on Thu, 24 January 2008 03:23

The equivalent in real life is making a shopping list, driving to the supermarket to see if they have all of the things on your list, driving home, crossing off the things on the list that they don't have, and then driving back to buy the things that they do have.

Ross Leishman


Nice analogy. Have to remember that one!
Re: comments on approach [message #296315 is a reply to message #295889] Fri, 25 January 2008 13:34 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would be interested in your freinds explanation of why he thinks his way is so much better than yours? Ask him to post an answer here.

Kevin
Previous Topic: Row Order in External Table
Next Topic: Eliminate duplicate emails in String
Goto Forum:
  


Current Time: Tue Dec 06 16:18:20 CST 2016

Total time taken to generate the page: 0.05987 seconds