How to use variables in stored procedure

[Zoom1234]

Hi,

I have written a following stored procedure

CREATE OR REPLACE PROCEDURE SP_TEST
DECLARE 


tablename   	VARCHAR2(50)    ;
id             	NUMBER ;   

column1       	NUMBER  ;
column2       	VARCHAR2(50) ;
column3        	DATE ;

strencrypt      VARCHAR2(50); 
dateencrypt     DATE;
numencrypt     	NUMBER ;

loop_cnt    	NUMBER;
batchCount      NUMBER;
minrowid	NUMBER;
maxrowid 	NUMBER;
startid         NUMBER;
endid           NUMBER;
query_str VARCHAR2(1000);

BEGIN

tablename := 'table1';
id	  := 'ID';

column1	  := 'EMPID';
column2   := 'FIRSTNAME';
column3   := 'LAST_NAME';

strencrypt := 'test';
dateencrypt := '01-JAN-1881';

SELECT MIN(id), MAX(id) into minrowid, maxrowid FROM tablename;

startid := minrowid;
endid 	:= 0;

WHILE	endid <= maxrowid
LOOP
	IF loop_cnt > 1 THEN 
		startid := startid + batchCount + 1;
	END IF
	
	endid := startid + batchCount;
    /* form a query */	
    query_str := 'UPDATE ' || tablename || ' SET ' || column1 || '=' || strencrypt || ',' 
    			 || column2 || ' = ' || strencrypt || ',' 
    			 column3 || '=' || to_date(dateencrypt, 'dd-mmm-yyyy') ||      			 
    			 ' WHERE ' || id || ' between ' || startid || ' and ' || endid;
    
    /* execute query */ 
    EXECUTE IMMEDIATE query_str;

 loop_cnt := loop_cnt + 1;

END LOOP;

END SP_TEST ;

The task is to update certain columns in a table with certain values in batches.. Is the SP looking OK (syntaxwise).

Please provide the inputs.

 

[SantaMufasa]

Zoom,

"Syntaxwise", did you not try running the code to see if there were any problems?...One test is worth 100 expert opinions, especially when checking syntax.

Logically, however, I don't see where "batchCount" receives an initial numeric value. Therefore, the default value is NULL, and doing math with NULL creates a NULL result.

Let us know if you have additional specific questions about your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Zoom1234]

Hi Santa,
Thanks for the reply. I completely agree with you that One test is worth 100 expert opinions, especially when
checking syntax, but the real issue is I
don't have oracle installed on my machine right now and I have to send the code to someone else to test. So before sending hime, I just want to be sure that the changes will be as less as possible from my side.
Also I have initialised the variable for loop count..Good catch
What the code should do is to update columns which are text based by some specific value (say 'test'), columns which are number based with some specific value (say '0') and date column values with some specific value. Now since the table has large no of data, the update can be done in batches.
Please let me know your inputs.

 

[SantaMufasa]

Zoom,

You can download Oracle from Oracle's website for free (provided you don't live in a country that is on the "No Distribtion" list for Oracle). Download Oracle and test your code...You'll be so much happier if you don't have to write Oracle SQL in a vacuum.

I would be glad to test your code for you if you posted the "CREATE TABLE..." in "INSERT INTO..." statements that I need for testing your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

If you're going to build some SQL to update non-numeric values in a table, you need to make sure that they end up properly quoted. So you're going to have problems with this code (for example):

query_str := 'UPDATE ' || tablename || ' SET ' || column1 || '=' || strencrypt || ','

With the values you have, that's going to put this into query_str:

UPDATE table1 SET EMPID=test,

That's going to fail because [tt]test[/tt] isn't a column in the table. It need to be

UPDATE table1 SET EMPID='test',

So your generating code needs to be

query_str := 'UPDATE ' || tablename || ' SET ' || column1 || '=[red]''[/red]' || strencrypt || '[red]''[/red],'

Even that isn't good enough if it's possible for [tt]strencrypt[/tt] to contain ' characters. In that case, you need to do this:

query_str := 'UPDATE ' || tablename || ' SET ' || column1 || '=''' || [red]REPLACE([/red]strencrypt[red],'''','''''')[/red] || ''','

A better approach, and one that may reap performance benefits too, would be to use bind variables instead - see

http://www.akadia.com/services/ora_bind_variables.html

for an example.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd