Home » SQL & PL/SQL » SQL & PL/SQL » Passing variables in cursor
Passing variables in cursor [message #250304] Mon, 09 July 2007 10:40 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
I have a stored procedure, I want user to pass two variables with this stored procedure one is column name and other is column value. Procedure does select on a table with where condition on this column and value and display the records..

create or replace PROCEDURE DISPLAY_CLIENT_STATUS1(p_col_name IN VARCHAR2,
p_col_value IN VARCHAR2)
IS
CURSOR display_cstatus_cursor IS
SELECT clientid
,clientname
,Firstname
,lastname
,Add1
,Add2
,startdate
,expireddate
FROM clientinfor
WHERE p_col_name =p_col_value
ORDER BY lastname desc;
BEGIN
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('* * * * * * * * D I S P L A Y F I L E S T A T U S * * * * * * * *');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.CID CLIENTNAME FNAME LNAME ADD1 ADD2 STDATE EXPIRE DATE ');
DBMS_OUTPUT.PUT_LINE('.------ ----------- -------- -------- ------ ----------- --------- ------------');
FOR display_cstatus_rec IN display_cstatus_cursor
LOOP
DBMS_OUTPUT.PUT_LINE('.' ||
RPAD(LPAD(display_cstatus_rec.clientid,3,' '),8,' ') ||
RPAD(display_cstatus_rec.clientname,15,' ') ||
RPAD(display_cstatus_rec.Firstname,10,' ') ||
RPAD(display_cstatus_rec.lastname,8,' ') ||
RPAD(display_cstatus_rec.Add1,16,' ') ||
RPAD(display_cstatus_rec.Add2,245,' ') ||
RPAD(display_cstatus_rec.startdate,16,' ') ||
RPAD(display_cstatus_rec.expireddate,11,' '));
END LOOP;
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Exception in display_cstatus '|| ' Errormsg: ' || sqlerrm);
END;

I am facing problem in passing the column name, please help...
Re: Passing variables in cursor [message #250308 is a reply to message #250304] Mon, 09 July 2007 10:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
You can't do it that way, you have to use dynamic SQL.

Regards
Michel
Previous Topic: PLS-00382: expression is of wrong type
Next Topic: LPAD in select
Goto Forum:
  


Current Time: Fri Dec 09 21:34:33 CST 2016

Total time taken to generate the page: 0.10543 seconds