OraFAQ Forum: Client Tools » Help with accepting input from the user after executing SQL stmts in PL/SQL procedure

Home » Other » Client Tools » Help with accepting input from the user after executing SQL stmts in PL/SQL procedure (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472768]

Mon, 23 August 2010 15:58

geneeyuss
Messages: 53
Registered: June 2010

Member

I have a table of datasets in table MATRIX.
I would like to display all the possible values in each
column and after that accept input from the user so I could use to select data from another table.
I am not able to use the ACCEPT COMMAND in oracle to this after running these SQL stmts.
Could I know how to do it?

CREATE TABLE MATRIX 
(
	ID NUMBER(15),
	NAME VARCHAR2(15),
	CD1 NUMBER(4),
	CD2 NUMBER(4),
	CD3 NUMBER(4),
	CONSTRAINT MATRIX_PK
	PRIMARY KEY (ID)
)
ORGANIZATION INDEX
/

INSERT INTO matrix VALUES (123, 'dataset1', 1, 1, 1);
INSERT INTO matrix VALUES (124, 'dataset1', 1, 2, 1);
INSERT INTO matrix VALUES (125, 'dataset1', 1, 2, 2);
INSERT INTO matrix VALUES (126, 'dataset1', 1, 3, 1);
INSERT INTO matrix VALUES (127, 'dataset1', 1, 3, 3);
INSERT INTO matrix VALUES (128, 'dataset1', 2, 1, 1);
INSERT INTO matrix VALUES (129, 'dataset1', 2, 1, 2);
INSERT INTO matrix VALUES (130, 'dataset1', 3, 1, 1);
INSERT INTO matrix VALUES (131, 'dataset1', 3, 2, 1);

16:44:41 B2USDB@doirtx01 23-AUG-10 SQL> CREATE TABLE MATRIX
16:44:47   2  (
16:44:47   3    ID NUMBER(15),
16:44:47   4    NAME VARCHAR2(15),
16:44:47   5    CD1 NUMBER(4),
16:44:47   6    CD2 NUMBER(4),
16:44:47   7    CD3 NUMBER(4),
16:44:47   8    CONSTRAINT MATRIX_PK
16:44:47   9    PRIMARY KEY (ID)
16:44:47  10  )
16:44:47  11  ORGANIZATION INDEX
16:44:47  12  /

Table created.

Elapsed: 00:00:00.06
16:44:47 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (123, 'dataset1', 1, 1, 1);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (124, 'dataset1', 1, 2, 1);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (125, 'dataset1', 1, 2, 2);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (126, 'dataset1', 1, 3, 1);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (127, 'dataset1', 1, 3, 3);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (128, 'dataset1', 2, 1, 1);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (129, 'dataset1', 2, 1, 2);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (130, 'dataset1', 3, 1, 1);

1 row created.

Elapsed: 00:00:00.01
16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> INSERT INTO matrix VALUES (131, 'dataset1', 3, 2, 1);

1 row created.

Elapsed: 00:00:00.01


16:44:52 B2USDB@doirtx01 23-AUG-10 SQL> select * from matrix;

        ID NAME                   CD1        CD2        CD3
---------- --------------- ---------- ---------- ----------
       123 dataset1                 1          1          1
       124 dataset1                 1          2          1
       125 dataset1                 1          2          2
       126 dataset1                 1          3          1
       127 dataset1                 1          3          3
       128 dataset1                 2          1          1
       129 dataset1                 2          1          2
       130 dataset1                 3          1          1
       131 dataset1                 3          2          1

9 rows selected.

Below is the pl/sql procedure I wrote. I hope this time I am following rules. Could someone help me please on this.


DECLARE 
  cd_value VARCHAR2(50);
  
  PROCEDURE p_print_codes(v_code_nme IN VARCHAR2) 
  IS  
    TYPE code_nt_typ IS TABLE OF NUMBER(4) ;
    v_code_nt_typ code_nt_typ;
    test1 VARCHAR2(100);
    v_sql VARCHAR2(32767);
  BEGIN
    v_sql := 'SELECT DISTINCT '  || v_code_nme || ' FROM matrix ORDER BY 1';
    DBMS_OUTPUT.PUT(v_code_nme || ': ');
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_code_nt_typ;
    FOR idx IN v_code_nt_typ.FIRST..v_code_nt_typ.LAST LOOP
      IF idx = 1 THEN
        DBMS_OUTPUT.PUT(v_code_nt_typ(idx));
      ELSE
        DBMS_OUTPUT.PUT(', ' || v_code_nt_typ(idx));
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END p_print_codes;

BEGIN

  /*p_print_codes('LN_MDFC_MODE_CD');
  p_print_codes('LN_ST_CD');
  p_print_codes('LN_AMRT_TYP_CD');
  p_print_codes('LN_IVSR_TYP_CD');
  p_print_codes('LN_LIEN_PRRY_TYP_CD');
  p_print_codes('LN_MTG_TYP_CD');
  p_print_codes('LN_MDFC_FOUT_RSN_CD');
  p_print_codes('LN_TRL_MDFC_DNAL_RSN_CD');*/
  p_print_codes('cd1');
  p_print_codes('cd2');
  p_print_codes('cd3');

  ACCEPT cd_value PROMPT 'Enter the name of the table to check: '
  dbms_output.put_line('CD: &&cd_value');

END ;
/

I would like to output from the SQL shown and only after that accept an
input from the user based on the SQL output.

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472773 is a reply to message #472768]

Mon, 23 August 2010 17:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

When all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12005.htm#sthref1778

ACCEPT is a sqlplus command; & is NOT a PL/SQL command.
PL/SQL runs deep inside the RDBMS Engine & has no direct access to the Operating System (or end user).

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472786 is a reply to message #472773]

Mon, 23 August 2010 21:36

geneeyuss
Messages: 53
Registered: June 2010

Member

Is there any other way of doing what I want to do? Any help will be appreciated.

Thanks in advance.

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472787 is a reply to message #472786]

Mon, 23 August 2010 22:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any other way of doing what I want to do
Why does the value need to come directly from the keyboard?
Why can't you read it from a table or from an OS File using UTL_FILE?

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472791 is a reply to message #472787]

Mon, 23 August 2010 23:05

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If your pl/sql block containing the procedure is part of a sql script that you are running, then just move the accept ... prompt after the block, outside the block, then put the dbms_output in a separate block after that, as shown below.

DECLARE 
  cd_value VARCHAR2(50);
  
  PROCEDURE p_print_codes(v_code_nme IN VARCHAR2) 
  IS  
    TYPE code_nt_typ IS TABLE OF NUMBER(4) ;
    v_code_nt_typ code_nt_typ;
    test1 VARCHAR2(100);
    v_sql VARCHAR2(32767);
  BEGIN
    v_sql := 'SELECT DISTINCT '  || v_code_nme || ' FROM matrix ORDER BY 1';
    DBMS_OUTPUT.PUT(v_code_nme || ': ');
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_code_nt_typ;
    FOR idx IN v_code_nt_typ.FIRST..v_code_nt_typ.LAST LOOP
      IF idx = 1 THEN
        DBMS_OUTPUT.PUT(v_code_nt_typ(idx));
      ELSE
        DBMS_OUTPUT.PUT(', ' || v_code_nt_typ(idx));
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END p_print_codes;

BEGIN
  p_print_codes('cd1');
  p_print_codes('cd2');
  p_print_codes('cd3');
END ;
/
ACCEPT cd_value PROMPT 'Enter the name of the table to check: '
begin
  dbms_output.put_line('CD: &&cd_value');
end;
/

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #473033 is a reply to message #472791]

Wed, 25 August 2010 09:13

geneeyuss
Messages: 53
Registered: June 2010

Member

below is the error I am getting. Could someone please help me on this.


SQL> DECLARE
10:11:20   2    cd_value VARCHAR2(50);
10:11:20   3
10:11:20   4    PROCEDURE p_print_codes(v_code_nme IN VARCHAR2)
10:11:20   5    IS
10:11:20   6      TYPE code_nt_typ IS TABLE OF NUMBER(4) ;
10:11:20   7      v_code_nt_typ code_nt_typ;
10:11:20   8      test1 VARCHAR2(100);
10:11:20   9      v_sql VARCHAR2(32767);
10:11:20  10    BEGIN
10:11:20  11      v_sql := 'SELECT DISTINCT '  || v_code_nme || ' FROM matrix ORDER BY 1';
10:11:20  12      DBMS_OUTPUT.PUT(v_code_nme || ': ');
10:11:20  13      EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_code_nt_typ;
10:11:20  14      FOR idx IN v_code_nt_typ.FIRST..v_code_nt_typ.LAST LOOP
10:11:20  15        IF idx = 1 THEN
10:11:20  16          DBMS_OUTPUT.PUT(v_code_nt_typ(idx));
10:11:20  17        ELSE
10:11:20  18          DBMS_OUTPUT.PUT(', ' || v_code_nt_typ(idx));
10:11:20  19        END IF;
10:11:20  20      END LOOP;
10:11:20  21      DBMS_OUTPUT.PUT_LINE('');
10:11:20  22    END p_print_codes;
10:11:20  23
10:11:20  24  BEGIN
10:11:20  25    p_print_codes('cd1');
10:11:20  26    p_print_codes('cd2');
10:11:20  27    p_print_codes('cd3');
10:11:20  28  END ;
10:11:20  29  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 14
ORA-06512: at line 25


Elapsed: 00:00:00.04
10:11:20 B2USDB@doirtx01 25-AUG-10 SQL> ACCEPT cd_value PROMPT 'Enter the name of the table to check: '
Enter the name of the table to check: begin
SQL>   dbms_output.put_line('CD: &&cd_value');
SP2-0734: unknown command beginning "dbms_outpu..." - rest of line ignored.
SQL> end;
SP2-0042: unknown command "end" - rest of line ignored.
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 14
ORA-06512: at line 25


Elapsed: 00:00:00.01

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #473036 is a reply to message #473033]

Wed, 25 August 2010 09:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

One implementation approach is
First make it work, then make it fancy.
If you can not produce code that works with hardcoded values, you won't succeed when you attempt to use dynamic SQL
post working procedure that with a single hardcoded value.

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #473041 is a reply to message #473036]

Wed, 25 August 2010 09:46

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Enter the name of the table to check: begin

You answer the question with the first line of your code "begin".
Obviously this was not executed in interactive but from a (shell) script (or blindy paste code).
If you hide things and don't post what you REALLY do then we can't help.

Regards
Michel

[Updated on: Wed, 25 August 2010 10:17]

Report message to a moderator

Re: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #473046 is a reply to message #473033]

Wed, 25 August 2010 10:13

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The following is a run of the corrected code that I posted yesterday. All you have to do is run it from SQL*Plus and input when prompted.

SCOTT@orcl_11gR2> CREATE TABLE MATRIX
  2  (
  3  	     ID NUMBER(15),
  4  	     NAME VARCHAR2(15),
  5  	     CD1 NUMBER(4),
  6  	     CD2 NUMBER(4),
  7  	     CD3 NUMBER(4),
  8  	     CONSTRAINT MATRIX_PK
  9  	     PRIMARY KEY (ID)
 10  )
 11  ORGANIZATION INDEX
 12  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (123, 'dataset1', 1, 1, 1);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (124, 'dataset1', 1, 2, 1);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (125, 'dataset1', 1, 2, 2);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (126, 'dataset1', 1, 3, 1);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (127, 'dataset1', 1, 3, 3);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (128, 'dataset1', 2, 1, 1);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (129, 'dataset1', 2, 1, 2);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (130, 'dataset1', 3, 1, 1);

1 row created.

SCOTT@orcl_11gR2> INSERT INTO matrix VALUES (131, 'dataset1', 3, 2, 1);

1 row created.

SCOTT@orcl_11gR2> select * from matrix;

        ID NAME                   CD1        CD2        CD3
---------- --------------- ---------- ---------- ----------
       123 dataset1                 1          1          1
       124 dataset1                 1          2          1
       125 dataset1                 1          2          2
       126 dataset1                 1          3          1
       127 dataset1                 1          3          3
       128 dataset1                 2          1          1
       129 dataset1                 2          1          2
       130 dataset1                 3          1          1
       131 dataset1                 3          2          1

9 rows selected.

SCOTT@orcl_11gR2> DECLARE
  2    cd_value VARCHAR2(50);
  3  
  4    PROCEDURE p_print_codes(v_code_nme IN VARCHAR2)
  5    IS
  6  	 TYPE code_nt_typ IS TABLE OF NUMBER(4) ;
  7  	 v_code_nt_typ code_nt_typ;
  8  	 test1 VARCHAR2(100);
  9  	 v_sql VARCHAR2(32767);
 10    BEGIN
 11  	 v_sql := 'SELECT DISTINCT '  || v_code_nme || ' FROM matrix ORDER BY 1';
 12  	 DBMS_OUTPUT.PUT(v_code_nme || ': ');
 13  	 EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_code_nt_typ;
 14  	 FOR idx IN v_code_nt_typ.FIRST..v_code_nt_typ.LAST LOOP
 15  	   IF idx = 1 THEN
 16  	     DBMS_OUTPUT.PUT(v_code_nt_typ(idx));
 17  	   ELSE
 18  	     DBMS_OUTPUT.PUT(', ' || v_code_nt_typ(idx));
 19  	   END IF;
 20  	 END LOOP;
 21  	 DBMS_OUTPUT.PUT_LINE('');
 22    END p_print_codes;
 23  
 24  BEGIN
 25    p_print_codes('cd1');
 26    p_print_codes('cd2');
 27    p_print_codes('cd3');
 28  END ;
 29  /
cd1: 1, 2, 3
cd2: 1, 2, 3
cd3: 1, 2, 3

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> ACCEPT cd_value PROMPT 'Enter the name of the table to check: '
Enter the name of the table to check: cd2
SCOTT@orcl_11gR2> begin
  2    dbms_output.put_line('CD: &&cd_value');
  3  end;
  4  /
old   2:   dbms_output.put_line('CD: &&cd_value');
new   2:   dbms_output.put_line('CD: cd2');
CD: cd2

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic:	Remove/Prevent "PL/SQL procedure successfully completed" message
Next Topic:	(Substitution Variables) not working in "SQL Developer" Environment !!!!!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 21:52:16 CST 2025