Ok, sad sam malo čitao i GET_LINE stvarno ne uzima liniju koju uneses vec onu koju je stampao....
http://www.jlcomp.demon.co.uk/faq/plsql_input.html
Citat:
Firstly, while it is possible to request a prompt from a user, by calling an external procedure or Java Stored Procedure, it is a very bad idea, as it can end up leaving a transaction or a piece of PL/SQL code waiting for ever. It should never be implemented in a production environment. So why would you want to do this then. Well, typically to test your procedure, feed in different values and determine different execution paths.
OK, so how do you do it then. The answer is you call an executable piece of code, eg a JAVA Stored Procedure which calls some Java Code or an external procedure(In whichever 3GL is supported on your system, eg C. The executable will then prompt you. Typically, you could use Java to prompt for a value, return this to the Java Stored Procedure, which would inturn return to the calling PL/SQL module.
Nije bas odgovor na tvoje pitanje ali ;-)
Evo ti koda za interaktivnu skriptu i SQL-u koja kupi korisnikov unos. Mozda moze da ti pomogne.
Code:
Rem Filename : ckqa.sql
Rem
Rem This script gets the Input from the user and spools them to the ckparams.txt file.
Rem The parameters are then validated by running ckvalqa.sql
Rem The ckparams.txt file is then run to get the values in the environment variables.
Rem
Rem Author : Oracle Corporation UK
Rem
Rem ***************** Connect to the database as SYS
WHENEVER SQLERROR EXIT FAILURE
prompt Enter TNS connect String for server
accept CONN prompt 'Enter TNS : '
prompt Enter password for SYS ....'
conn sys@&conn as sysdba
set serveroutput on size 1000000
set feedback off
set verify off
Rem ***************** Determine Repository size
prompt
prompt Enter the Repository size you wish to create. Small(S),Medium(M),Large(L) ...
accept repsize prompt 'Enter S/M/L : '
Rem Validate the choice.
declare
x_invalidchoice exception;
begin
if UPPER('&repsize') not in ('S','s','M','m','L','l') then
raise x_invalidchoice;
end if;
exception
when x_invalidchoice then
raise_application_error(-20001,'Invalid choice !! Please choose either S/M/L. ');
end;
/
WHENEVER SQLERROR CONTINUE
set feedback on
Rem *************** Temporary table for holding the parameter values
Rem The temporary table is required for Validation purpose.
prompt Dropping temporary table CKRAU_TEMP
prompt
drop table CKRAU_TEMP;
prompt Creating temporary table CKRAU_TEMP
prompt
create table CKRAU_TEMP ( paramname varchar2(30),
paramtype varchar2(30),
value varchar2(50)
);
set feedback off
Rem ***************** Set up parameters for creating tablespace.
prompt
prompt Enter datafile location and name for CONSTANT_GROW_INDEXES...
accept DF_NAME1 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for CONSTANT_GROW_TABLES...
accept DF_NAME2 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for DEPENDENCY_INDEXES...
accept DF_NAME3 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for DEPENDENCY_TABLES...
accept DF_NAME4 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for DIAGRAM_INDEXES...
accept DF_NAME5 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for DIAGRAM_TABLES...
accept DF_NAME6 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for LOB_DATA...
accept DF_NAME7 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for RAPID_GROW_INDEXES...
accept DF_NAME8 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for RAPID_GROW_TABLES...
accept DF_NAME9 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for SYSTEM_META_INDEXES...
accept DF_NAME10 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for SYSTEM_META_TABLES...
accept DF_NAME11 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for TEMPORARY_INDEXES...
accept DF_NAME12 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for TEMPORARY_TABLES...
accept DF_NAME13 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for VERSION_INDEXES...
accept DF_NAME14 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for VERSION_TABLES...
accept DF_NAME15 prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for REPOS_RBS tablespace...
accept RDFILENAME prompt 'Enter datafile : '
prompt
prompt Enter datafile location and name for REPOS_TEMP tablespace...
accept TEMPFILENAME prompt 'Enter datafile : '
prompt
prompt For ten or more subordinate users, we recommend giving access using public synonyms.
prompt Do you wish to grant create/drop public synonym privilege to Repository Owner ?
accept MOREUSERS prompt 'Enter Choice (Y/N) : '
spool ckparams.txt
begin
dbms_output.put_line(' Rem PARAMETER DEFINITION FILE ');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem TNS Connect String ');
dbms_output.put_line(' define CONN = &CONN');
dbms_output.put_line(' define TBS_NAME1 = CONSTANT_GROW_INDEXES');
dbms_output.put_line(' define DF_NAME1 = &DF_NAME1');
dbms_output.put_line(' define TBS_NAME2 = CONSTANT_GROW_TABLES');
dbms_output.put_line(' define DF_NAME2 = &DF_NAME2');
dbms_output.put_line(' define TBS_NAME3 = DEPENDENCY_INDEXES');
dbms_output.put_line(' define DF_NAME3 = &DF_NAME3');
dbms_output.put_line(' define TBS_NAME4 = DEPENDENCY_TABLES');
dbms_output.put_line(' define DF_NAME4 = &DF_NAME4');
dbms_output.put_line(' define TBS_NAME5 = DIAGRAM_INDEXES');
dbms_output.put_line(' define DF_NAME5 = &DF_NAME5');
dbms_output.put_line(' define TBS_NAME6 = DIAGRAM_TABLES');
dbms_output.put_line(' define DF_NAME6 = &DF_NAME6');
dbms_output.put_line(' define TBS_NAME7 = LOB_DATA');
dbms_output.put_line(' define DF_NAME7 = &DF_NAME7');
dbms_output.put_line(' define TBS_NAME8 = RAPID_GROW_INDEXES');
dbms_output.put_line(' define DF_NAME8 = &DF_NAME8');
dbms_output.put_line(' define TBS_NAME9 = RAPID_GROW_TABLES');
dbms_output.put_line(' define DF_NAME9 = &DF_NAME9');
dbms_output.put_line(' define TBS_NAME10 = SYSTEM_META_INDEXES');
dbms_output.put_line(' define DF_NAME10 = &DF_NAME10');
dbms_output.put_line(' define TBS_NAME11 = SYSTEM_META_TABLES');
dbms_output.put_line(' define DF_NAME11 = &DF_NAME11');
dbms_output.put_line(' define TBS_NAME12 = TEMPORARY_INDEXES');
dbms_output.put_line(' define DF_NAME12 = &DF_NAME12');
dbms_output.put_line(' define TBS_NAME13 = TEMPORARY_TABLES');
dbms_output.put_line(' define DF_NAME13 = &DF_NAME13');
dbms_output.put_line(' define TBS_NAME14 = VERSION_INDEXES');
dbms_output.put_line(' define DF_NAME14 = &DF_NAME14');
dbms_output.put_line(' define TBS_NAME15 = VERSION_TABLES');
dbms_output.put_line(' define DF_NAME15 = &DF_NAME15');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Tablespace for Rollback Segment ');
dbms_output.put_line(' define RTBLSPACE = REPOS_RBS');
dbms_output.put_line(' Rem File name and location for Rollback Segment tablespace ');
dbms_output.put_line(' define RDFILENAME = &RDFILENAME');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Name of Rollback Segment ');
dbms_output.put_line(' define RBSNAME = REPOS_RBS_SEGMENT');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Name of temporary tablespace for Repository Owner');
dbms_output.put_line(' define TEMPTBLSPACE = REPOS_TEMP');
dbms_output.put_line(' Rem File name and location for temporary tablespace ');
dbms_output.put_line(' define TEMPFILENAME = &TEMPFILENAME');
dbms_output.put_line(' Rem File size of temporary tablespace for Repository Owner');
dbms_output.put_line(' define TEMPFILESIZE = 5M');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Name of Repository Owner');
dbms_output.put_line(' define REPOSOWNER = REPOS_MANAGER');
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Default tablespace for Repository Owner');
dbms_output.put_line(' define DEFTABSPACE = TEMPORARY_TABLES');
dbms_output.put_line(' Rem Choice indicating whether Repository Owner has privilege ');
dbms_output.put_line(' Rem to create/drop public synonyms ');
dbms_output.put_line(' define MOREUSERS = &MOREUSERS');
if UPPER('&repsize') = 'S' then
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Default tablespace size for default tablespaces (SMALL Repository) ');
dbms_output.put_line(' define CONST_GROW_INDX_SIZE = 2048k');
dbms_output.put_line(' define CONST_GROW_TAB_SIZE = 1080k');
dbms_output.put_line(' define DEP_INDX_SIZE = 5120k');
dbms_output.put_line(' define DEP_TAB_SIZE = 660k');
dbms_output.put_line(' define DIAG_INDX_SIZE = 2048k');
dbms_output.put_line(' define DIAG_TAB_SIZE = 240k');
dbms_output.put_line(' define LOB_DATA_SIZE = 5000k');
dbms_output.put_line(' define RAP_GROW_INDX_SIZE = 122880k');
dbms_output.put_line(' define RAP_GROW_TAB_SIZE = 44600k');
dbms_output.put_line(' define SYS_META_INDX_SIZE = 30720k');
dbms_output.put_line(' define SYS_META_TAB_SIZE = 18000k');
dbms_output.put_line(' define TEMP_INDX_SIZE = 20000k');
dbms_output.put_line(' define TEMP_TAB_SIZE = 7000k');
dbms_output.put_line(' define VER_INDX_SIZE = 5120k');
dbms_output.put_line(' define VER_TAB_SIZE = 2800k');
elsif UPPER('&repsize') = 'M' then
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Default tablespace size for default tablespaces (MEDIUM Repository) ');
dbms_output.put_line(' define CONST_GROW_INDX_SIZE = 7030k');
dbms_output.put_line(' define CONST_GROW_TAB_SIZE = 4440k');
dbms_output.put_line(' define DEP_INDX_SIZE = 11500k');
dbms_output.put_line(' define DEP_TAB_SIZE = 2280k');
dbms_output.put_line(' define DIAG_INDX_SIZE = 2340k');
dbms_output.put_line(' define DIAG_TAB_SIZE = 660k');
dbms_output.put_line(' define LOB_DATA_SIZE = 500k');
dbms_output.put_line(' define RAP_GROW_INDX_SIZE = 574220k');
dbms_output.put_line(' define RAP_GROW_TAB_SIZE = 117600k');
dbms_output.put_line(' define SYS_META_INDX_SIZE = 25000k');
dbms_output.put_line(' define SYS_META_TAB_SIZE = 18000k');
dbms_output.put_line(' define TEMP_INDX_SIZE = 20000k');
dbms_output.put_line(' define TEMP_TAB_SIZE = 7000k');
dbms_output.put_line(' define VER_INDX_SIZE = 6000k');
dbms_output.put_line(' define VER_TAB_SIZE = 5400k');
elsif UPPER('&repsize') = 'L' then
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Default tablespace size for default tablespaces (LARGE Repository) ');
dbms_output.put_line(' define CONST_GROW_INDX_SIZE = 11000k');
dbms_output.put_line(' define CONST_GROW_TAB_SIZE = 9000k');
dbms_output.put_line(' define DEP_INDX_SIZE = 15000k');
dbms_output.put_line(' define DEP_TAB_SIZE = 4800k');
dbms_output.put_line(' define DIAG_INDX_SIZE = 5000k');
dbms_output.put_line(' define DIAG_TAB_SIZE = 1320k');
dbms_output.put_line(' define LOB_DATA_SIZE = 5000k');
dbms_output.put_line(' define RAP_GROW_INDX_SIZE = 875000k');
dbms_output.put_line(' define RAP_GROW_TAB_SIZE = 231500k');
dbms_output.put_line(' define SYS_META_INDX_SIZE = 25000k');
dbms_output.put_line(' define SYS_META_TAB_SIZE = 20400k');
dbms_output.put_line(' define TEMP_INDX_SIZE = 20000k');
dbms_output.put_line(' define TEMP_TAB_SIZE = 7000k');
dbms_output.put_line(' define VER_INDX_SIZE = 20000k');
dbms_output.put_line(' define VER_TAB_SIZE = 9720k');
end if;
dbms_output.put_line(' Rem ');
dbms_output.put_line(' Rem Default Storage parameters for tablespace ');
dbms_output.put_line(' define INITIAL_VALUE = 100k');
dbms_output.put_line(' define NEXT_VALUE = 100k');
dbms_output.put_line(' define PCTINC_VALUE = 0');
dbms_output.put_line(' define MINEXT_VALUE = 1');
dbms_output.put_line(' define MAXEXT_VALUE = UNLIMITED');
end;
/
spool off
WHENEVER SQLERROR CONTINUE
@ckparams.txt
prompt
pause Hit enter to Continue with validation process ....
@ckvalqa.sql
set feedback on
prompt
prompt " ****************************************************************************** "
prompt " * Please verify the above values. * "
prompt " * Edit 'ckparams.txt' to make any changes. Then run ckparams.txt * "
prompt " ****************************************************************************** "
prompt