Home » SQL & PL/SQL » SQL & PL/SQL » Creating procedure
Creating procedure [message #210135] Tue, 19 December 2006 09:39 Go to next message
venu_kmnn
Messages: 8
Registered: August 2005
Location: hyderabad
Junior Member

Hi everybody.

How can i retrieve whole data from emp table using stored procedure with out using any parameters.
After alter emp table(adding new columns)
Then also i want to retrieve whole data include new column data with out changing procedure.


thanks
venu k
Re: Creating procedure [message #210150 is a reply to message #210135] Tue, 19 December 2006 11:32 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
SQL> create table t1 (col1 varchar2(10));

Table created.

SQL> insert into t1 values ('Col1_value');

1 row created.

SQL> CREATE OR REPLACE PACKAGE ref_cur_tst
  2  IS
  3  	-- weak ref cursor
  4  	TYPE t_cur IS REF CURSOR;
  5  
  6  	PROCEDURE get_t1 (cv_cur IN OUT t_cur);
  7  END;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY ref_cur_tst
  2  AS
  3  	PROCEDURE get_t1 (cv_cur IN OUT t_cur)
  4  	IS
  5  	BEGIN
  6  	   OPEN cv_cur FOR 'SELECT * FROM t1';
  7  	END;
  8  END;
  9  /

Package body created.

SQL> set serveroutput on;
SQL> DECLARE
  2  	t1_row	     t1%rowtype;
  3  	cv_c1	ref_cur_tst.t_cur;
  4  BEGIN
  5  	ref_cur_tst.get_t1 (cv_c1);
  6  
  7  	LOOP
  8  	   FETCH cv_c1 INTO t1_row;
  9  	   EXIT WHEN cv_c1%NOTFOUND;
 10  	   DBMS_OUTPUT.put_line (t1_row.col1);
 11  	END LOOP;
 12  	CLOSE cv_c1;
 13  END;
 14  /
Col1_value                                                                      

PL/SQL procedure successfully completed.

SQL> alter table t1 add ( col2 varchar2(10));

Table altered.

SQL> update t1 set col2= 'Col1_value';

1 row updated.

SQL> alter package ref_cur_tst compile body;

Package body altered.

SQL> DECLARE
  2  	t1_row	     t1%rowtype;
  3  	cv_c1	ref_cur_tst.t_cur;
  4  BEGIN
  5  	ref_cur_tst.get_t1 (cv_c1);
  6  
  7  	LOOP
  8  	   FETCH cv_c1 INTO t1_row;
  9  	   EXIT WHEN cv_c1%NOTFOUND;
 10  	   DBMS_OUTPUT.put_line (t1_row.col1||' '||t1_row.col2);
 11  	END LOOP;
 12  	CLOSE cv_c1;
 13  END;
 14  /
Col1_value Col1_value                                                           

PL/SQL procedure successfully completed.
Previous Topic: How to insert an array from an xml into a query?
Next Topic: How to select unique columns - pls read to understand better
Goto Forum:
  


Current Time: Wed Dec 07 10:59:13 CST 2016

Total time taken to generate the page: 0.21603 seconds