-2

I have started learning PL SQL stored procedure and i have 2 interesting queestion to ask. In following example I execute employer_details and it return id and name of employee if found. every thing works fine but this procedure is not dynamic but table specific

CREATE OR REPLACE PROCEDURE employer_details 
IS 
CURSOR table_cur IS
SELECT id, name FROM employee where id in(12,23,34); -- point 1
table_rec table_cur%rowtype;
BEGIN
OPEN table_cur;
LOOP 
fetch table_cur into table_rec;
EXIT WHEN table_cur%notfound;
dbms_output.put_line(employee.id || ' ' ||employee.name); -- point 2
END LOOP;
END;
/

First question: Can we make it dynamic with following signature 

CREATE OR REPLACE PROCEDURE employer_details (tablename IN VARCHAR, ID in INTEGER, target IN INTEGER)

change line in procedure 

SELECT * FROM tablename where tablename.ID > target  ; (point 1)

I tried it but "table or view does not exist" error appeard (i am working with Oracle/Toad)

Second question: Can we see all columns as output, 

dbms_output.put_line(employee.id || ' ' ||employee.name); (point 2)

not only 2 columns but all column (*)

abidkhan303
  • 1,761
  • 3
  • 18
  • 31
  • what is `target IN INTEGER` parameter?? and yes all your requrement are possible. – smn_onrocks Jan 13 '15 at 12:19
  • 4
    You can pass a table name and use dynamic SQL to query it, but do you really need/want to do that? If the structure of the tables differs then outputting the column values like that can be done but isn't trivial (needs `dbms_sql`), and you shouldn't rely on `dbms_output` having anywhere to be displayed anyway. I'm not sure why this would be any better than a simple SQL select. Maybe you can step back and state what you really need to achieve here? – Alex Poole Jan 13 '15 at 12:21
  • then please guide me, taget id is, return rows which are greater than this specific id . its northing special – abidkhan303 Jan 13 '15 at 12:22
  • Then use only one parameter `target IN INTEGER` I mean just let me clear one thing do you want an out out of all employee whos id is greater than passing id?? – smn_onrocks Jan 13 '15 at 12:29
  • @smn_onrocks Yes, I want to display all columns (select *) which are > id – abidkhan303 Jan 13 '15 at 12:31
  • @AlexPoole I want to find a way where i can pass the table name and where clause and i can save dataset in txt file and then i can read it later. – abidkhan303 Jan 13 '15 at 12:33
  • My point is that it's reasonably complicated and any working answer is going to be a long way from your starting point; if you're really just starting to learn PL/SQL this seems like a bit of a leap. [It's certainly possible](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:88212348059), but there are tools that will do this (export from SQL Developer for example). I suppose I'm trying to stop you making life harder for yourself than you need too. – Alex Poole Jan 13 '15 at 12:42
  • normaly, dbms_output is more ore less for manually debugging. – PT_STAR Jan 13 '15 at 12:45

2 Answers2

1
create table my_poc
(id INTEGER,
name VARCHAR2(60),
 sal  number);

insert into my_poc values (1,'abc',1000);
insert into my_poc values (2,'abc',2000);
insert into my_poc values (3,'abc',3000);
insert into my_poc values (4,'abc',4000);
insert into my_poc values (5,'abc',5000);
insert into my_poc values (6,'abc',6000);



CREATE OR REPLACE PROCEDURE employer_details (p_id INTEGER,filter VARCHAR2,table_name varchar2, out_param out SYS_REFCURSOR)
IS
BEGIN
OPEN out_param for 'select * from '||table_name||' where '||filter||' > '||p_id;
END;

I think this is what you wanted.

smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
  • This is a out parameter it is `SYS_REFCURSOR` that will hold your output result set you can pass nay table name in the `table_name` variable and accordingly your out pot will be populated in the `out_param` variable – smn_onrocks Jan 13 '15 at 13:04
  • sorry for this but tell me what to write in ??? execute employer_details( 3000,'id', 'my_poc', ?????) – abidkhan303 Jan 13 '15 at 13:08
  • `declare my_cur SYS_REFCURSOR; begin -- Call the procedure employer_details(2,'id','my_poc',my_cur); end;` – smn_onrocks Jan 13 '15 at 13:09
  • 2
    @abidkhan303 - or [with a bind variable, like this](http://stackoverflow.com/a/8618084/266304). – Alex Poole Jan 13 '15 at 13:16
  • you can check this [link](http://docs.oracle.com/cd/A81042_01/DOC/sqlplus.816/a75664/ch35.htm) or this [one](https://mikesmithers.wordpress.com/2010/09/13/getting-output-from-ref-cursors-in-plsql/) – smn_onrocks Jan 13 '15 at 13:28
  • Thank you guys @smn_onrocks for helping me and guiding me on right direction. This is my first attept to understand this stuff, but i am still not sure why people could not understand and just to increase points they start giving -ve marking. If you could not encourage the beginners, you have no right to discourage. – abidkhan303 Jan 13 '15 at 14:29
0
  1. Yes, we can. Look at OPEN FOR clause.
  2. Yes, we can. Look at DBMS_SQL package.
  3. No, we should not do this without good reason. Generally it's bad practise.
Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28