Home » SQL & PL/SQL » SQL & PL/SQL » problem with plsql tables
problem with plsql tables [message #216989] Wed, 31 January 2007 03:08 Go to next message
seenujanu
Messages: 53
Registered: August 2006
Location: chennai
Member
hi friends,

i am getting the problem with this.

i have compiled this with no errors.
it is not printing the value of previous record
i need the previous record_id from the current id in emps table ,is this ok with this function
Plz get back to me if any thing changes or modifications or goes wrong in this .....


CREATE OR REPLACE FUNCTION sathif(p_empno IN NUMBER
)
RETURN NUMBER
IS
CURSOR emp_cur
IS
SELECT empno
FROM emps
WHERE empno = p_empno;

r_cur emp_cur%rowtype;

TYPE EmpTabTyp IS TABLE OF emp_cur%ROWTYPE
INDEX BY BINARY_INTEGER;

emp_tab EmpTabTyp;
v_id number;
v_id1 number;
BEGIN
v_id := emp_tab.PRIOR(p_empno);
v_id1 := emp_tab.NEXT(p_empno);

DBMS_OUTPUT.PUT_LINE(v_id ||'test');
DBMS_OUTPUT.PUT_LINE(v_id1 ||'test1');

RETURN v_id;
END;



DECLARE
v_asdf number(20);
BEGIN
v_asdf := SATHIF(7934);
DBMS_OUTPUT.PUT_LINE(v_asdf||'fail');
END;

Regards

Seenu
Re: problem with plsql tables [message #216992 is a reply to message #216989] Wed, 31 January 2007 03:24 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Based on the code sample given, I have absolutely no idea what you are trying to do here and I don't think you have much either.

a) Where do you populate EMP_TAB ? You need to open the cursor and fetch rows into it before there will be any data.

b) Why are you trying to use the input parameter as an index to the table ? Even assuming you had populated it from the cursor, this will not work. If populated from the cursor, your table would be an array starting at 1 and moving forward sequentially. This will not relate in any way whatsoever to the employee number.

You could create a random access PL/SQL table keyed on the empno, but I would expect to see this declared more like:

TYPE EmpTabTyp IS TABLE OF emp_cur%ROWTYPE
INDEX BY EMP.EMPNO%TYPE;

But you would still have to populate it somewhere.
Re: problem with plsql tables [message #216994 is a reply to message #216992] Wed, 31 January 2007 03:36 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cthulhu must be a mind-reader - he copied my thoughts to his message Smile

If this
OP
i need the previous record_id from the current id in emps table

means that for a given 'empno' you want to return previous (i.e. smaller) 'empno', would such a function (if you insist on functions) do the job?
CREATE OR REPLACE FUNCTION Sathif (p_empno IN NUMBER)
   RETURN NUMBER
IS
  retval EMP.empno%TYPE;
BEGIN
  SELECT MAX(empno)
    NTO retval
    FROM EMP
    WHERE empno < p_empno;
	
  RETURN retval;	
END;

SQL> select sathif(7934) previous_empno from dual;

PREVIOUS_EMPNO
--------------
          7902

SQL>

Re: problem with plsql tables [message #217246 is a reply to message #216994] Thu, 01 February 2007 05:35 Go to previous message
seenujanu
Messages: 53
Registered: August 2006
Location: chennai
Member
Hi Friends,Seniors

i just need the previous record_id from the current id in emps table,i got that.
i got the value from the above SQL Query specified.
Thanks a lot for giving valuable immediate suggestions and the solution gave me fruitful results.
Once again Thanks to all of u

Regards

Seenu

Previous Topic: Searching Text Query - Using SQL
Next Topic: dynamic sql
Goto Forum:
  


Current Time: Wed Dec 07 18:58:26 CST 2016

Total time taken to generate the page: 0.09473 seconds