Home » Other » Client Tools » Help with accepting input from the user after executing SQL stmts in PL/SQL procedure (10g)
Help with accepting input from the user after executing SQL stmts in PL/SQL procedure [message #472768] Mon, 23 August 2010 15:58 Go to next message
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.
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 Go to previous messageGo to next message
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).
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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;
/

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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Barbara Boehmer
Messages: 9077
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>

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


Current Time: Fri Mar 29 00:30:19 CDT 2024