Prompt a user for input in oracle

Question

How to prompt a user for input in oracle using SQLTools v1.6? I tried the following PL/SQL block:

DECLARE 
 type tes_tab is table of test_table%rowtype;
 test_tab tes_tab;    
BEGIN

  execute immediate 'SELECT * FROM test_table WHERE memid=' || &MemID
       bulk collect into test_tab;

  for i in 1..test_tab.count 
  loop
    dbms_output.put_line(test_tab(i).memid || ' ' || test_tab(i).clmtype);
  end loop;

END;

It prompts the user for input during compile time. However, the value cannot be used in the select statement. Is there any other way to achieve what I am seeking.

The table contains 852269 records with multiple rows for a single memID(I have provided a few rows here):

 MEMID       CLMTYPE   PAIDAMT   SERVICEDATE           PAIDDATE
----------- --------- --------- --------------------- ---------------------
 220000096   RX         77.22    06.05.2011 00:00:00   27.05.2011 00:00:00
 220000096   RX         77.22    04.02.2011 00:00:00   27.02.2011 00:00:00
 220000096   RX         93.36    01.01.2011 00:00:00   27.01.2011 00:00:00
 220000096   RX         15.07    09.08.2012 00:00:00   07.09.2012 00:00:00
 220000096   RX        105.52    06.01.2012 00:00:00   04.02.2012 00:00:00
 220000096   RX         93.36    02.03.2011 00:00:00   27.03.2011 00:00:00
 220000096   RX        105.52    09.11.2011 00:00:00   09.12.2011 00:00:00
 220000096   RX        105.52    10.03.2012 00:00:00   13.04.2012 00:00:00
 220000096   RX         50.52    06.04.2011 00:00:00   27.04.2011 00:00:00

Tharunkumar Reddy · Answer 1 · 2015-08-12T05:30:44.893

1

Small modifications to your code

DECLARE 
  vblMemID test_table%rowtype;
BEGIN
  EXECUTE IMMEDIATE'SELECT * FROM test_table WHERE memid='||&MemID into vblMemID ;
dbms_output.put_line(vblMemID.column1_name||' '||vblMemID.column2_name);
END;

Note: Column1_name and column2_name means your table column names. And the above code will works if your select query returns only one row. If it returns more than one row we have to use another method. If you need i will post that code.

Edit The below code will work if a select query will returns more than one row. For more than one row we have to use collections or sysref cursors in oracle.

DECLARE 
 type tes_tab is table of test_table%rowtype;
test_tab tes_tab;
BEGIN

execute immediate 'SELECT * FROM test_table WHERE memid='||&MemID bulk collect into test_tab;

for i in 1..test_tab.count 
loop
dbms_output.put_line(test_tab(i).column1_name||' '||test_tab(i).column2_name);
end loop; 
END;

edited Aug 12 '15 at 05:30

answered Aug 12 '15 at 05:02

Tharunkumar Reddy

2,773
18
32

The table has five columns. And I need to display multiple rows for a single MemID like you said. – hsuyaa Aug 12 '15 at 05:17
@hsuyaa Do you need to execute a select query dynamically? – Tharunkumar Reddy Aug 12 '15 at 05:19
Yes, it must be dynamic. – hsuyaa Aug 12 '15 at 05:24
Now you can display five columns. And edit dbms_output statement based on your requirement. – Tharunkumar Reddy Aug 12 '15 at 05:32
Didn't work as you expected. It says, 'column2_name(clmtype in my table) is not declared'. – hsuyaa Aug 12 '15 at 05:55
Please post your table structure, data and your pl/sql block. – Tharunkumar Reddy Aug 12 '15 at 06:07
I have executed above code with emp table it is working for me i am not facing any issues. – Tharunkumar Reddy Aug 12 '15 at 06:07
If I skip other columns,i.e. keep only the memID in dbms_output statement, then only the member IDs are printed. However, if I add other columns, then it gives error 'component 'CLMTYPE' must be declared'. – hsuyaa Aug 12 '15 at 07:09
@HSUYAA please post your plsql block and your table columns. – Tharunkumar Reddy Aug 12 '15 at 07:11
Which tool you are using?? I am using oracle sql developer it is working fine. – Tharunkumar Reddy Aug 12 '15 at 07:25
If yo have any mismatch between test_table%rowtype and table_name present in select query then only it will throws an error. – Tharunkumar Reddy Aug 12 '15 at 07:29
I am using SQLTools. I have mentioned it above. – hsuyaa Aug 12 '15 at 07:29

Prompt a user for input in oracle

1 Answers1