[es] - pocetnicki pl/sql problem

Jim Jackson
Veljko Sarac
Beograd

Član broj: 4456
Poruke: 97
*.sbb.co.yu

Profil

^{24.03.2004. u 19:45 - pre 244 meseci}

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

Odgovor na temu