Creating IN list in PLSQL script dynamically by using shell script Post: 302910857

Sponsored Content

Top Forums Shell Programming and Scripting Creating IN list in PLSQL script dynamically by using shell script Post 302910857 by durden_tyler on Monday 28th of July 2014 03:02:40 PM

07-28-2014

Registered User

Quote:

Originally Posted by LoneRanger

...
I have a PLSQL script which has a IN list where it takes some ids as input. For example

SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID IN (comma separated list )

I want to run this quest inside a shell script but I would like to prepare the IN list dynamically where the employee ids will be there in the same directory in a different file in the below format.

File Name - employee_list
234
456
784
123

How can I insert all the employee ID into the PLSQL script in one go ?...

You haven't mentioned your Oracle version. To solve this problem, you could use INSTR/SUBSTR functions in any (modern) version of Oracle, or if you have 10g or higher, you could use regular expressions.

Code:

SQL>
SQL> --
SQL> -- Check the data in the EMPLOYEES table for a few EMPLOYEE_IDs
SQL> --
SQL> select employee_id, first_name, last_name
  2    from employees
  3   where employee_id in (100, 121, 145, 179, 202);
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL> --
SQL> var str varchar2(30)
SQL> exec :str := '100,121,145,179,202';
 
PL/SQL procedure successfully completed.
 
SQL> print str
 
STR
------------------------------
100,121,145,179,202
 
SQL>
SQL> --
SQL> -- Query 1: Should work in any (non-ancient / real-life) version of Oracle
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
  2    from employees e,
  3         (
  4              select substr(','||:str||',',
  5                            instr(','||:str||',',',',1,level) + 1,
  6                            instr(','||:str||',',',',1,level+1) - instr(','||:str||',',',',1,level) - 1
  7                           ) as token
  8                from dual
  9               connect by level <= length(:str) - length(replace(:str,',')) + 1
 10         ) x
 11   where e.employee_id = x.token;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL> --
SQL> -- Query 2: Should work in Oracle 10g and higher
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
  2    from employees e,
  3         (
  4              select regexp_substr(:str,'[^,]+',1,level) as token
  5                from dual
  6               connect by level <= length(:str) - length(replace(:str,',')) + 1
  7         ) x
  8   where e.employee_id = x.token;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL>

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script dynamically case in VAR

Hallo, I am working on a kdialog. This shall be able to load the required commands from a .conf file. First step runs good by loading the entries (selectabel entries) in a variable: MIRRORSELECT=$(kdialog --radiolist "Select your nearest mirror" $VAR1) The kdialog is accordingly correct...

2. Programming

UNIX Shell Script to Create a Document of a PLSQL code.

Hi All, I am supposed to present the documentation for the PLSQL code (PACKAGES, PROCEDURE, FUNCTIONS) of my application. There are sufficient comments in my code. Has anyone written any Shell Script Utility which can parse the PLSQL code and generate some kind of document ( preferrably HTML not...

3. Shell Programming and Scripting

Shell Script to Parse PLSQL code?

4. Shell Programming and Scripting

Dynamically creating text files using shell script

Hi All, I want to create a shell script which dynamically create text files. i am using the following script $i=1 while do cat > test_$i.txt done but while running the script it was stopping(the cursor not going to next step, i have to enter ctrl+c to make it stop). it is creating only...

5. Programming

running PLSQL scripts through shell script

I am running the following ealth checks on my server there are two databases in my server . MODEL1 and MODEL2 i connect with the first database as sqlplus model1/password Then i exceute a query select x from table

6. Programming

Executing shell script from PLSQL

Hi All, I have a requirement to mv a file in unix from plsql procedure. for that i have created a java host procedure, a host_command, given all grants as per ORACLE-BASE - Oracle8i Shell Commands From PL/SQL but i am getting an error ""Process err :/bin/sh: mv Not Found"" kindly let me...

7. Shell Programming and Scripting

Execute shell script from plsql trigger

Hi, I have been assigned a job which requires me to send mails from unix(Mailx) upon on certain actions triggered in the database. On insert/update of a certain field into one of the database tables the shell script present in Unix box responsible to send mail though mailx needs to be triggered...

8. Shell Programming and Scripting

Creating variables dynamically and using it in script?

Hi, I have a problem that I am trying to solve and would greatly appreciate some input to solve this. I have a file containing variable length of line. Each line in the file has values separated by "," and i need to grep for these values in a some files. For example below is a sample file with 3...

9. Shell Programming and Scripting

calling a plsql procedure through shell script

I have a plsql procedure inside a package which is having one IN parameter .I want to call that procedure through a shell script and that IN parameter is a column of a table in the database. So my requirement is that i need to loop all the entries of that IN parameter from the table through shell...

10. Homework & Coursework Questions

How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: 2. Relevant commands, code, scripts, algorithms: #! /bin/ksh v="ORG_ID" ...

LEARN ABOUT OSX

script

SCRIPT(1)						    BSD General Commands Manual 						 SCRIPT(1)

NAME

     script -- make typescript of terminal session

SYNOPSIS

     script [-akq] [-t time] [file [command ...]]

DESCRIPTION

     The script utility makes a typescript of everything printed on your terminal.  It is useful for students who need a hardcopy record of an
     interactive session as proof of an assignment, as the typescript file can be printed out later with lpr(1).

     If the argument file is given, script saves all dialogue in file.	If no file name is given, the typescript is saved in the file typescript.

     If the argument command is given, script will run the specified command with an optional argument vector instead of an interactive shell.

     The following options are available:

     -a      Append the output to file or typescript, retaining the prior contents.

     -k      Log keys sent to program as well as output.

     -q      Run in quiet mode, omit the start and stop status messages.

     -t time
	     Specify time interval between flushing script output file.  A value of 0 causes script to flush for every character I/O event.  The
	     default interval is 30 seconds.

     The script ends when the forked shell (or command) exits (a control-D to exit the Bourne shell (sh(1)), and exit, logout or control-D (if
     ignoreeof is not set) for the C-shell, csh(1)).

     Certain interactive commands, such as vi(1), create garbage in the typescript file.  The script utility works best with commands that do not
     manipulate the screen.  The results are meant to emulate a hardcopy terminal, not an addressable one.

ENVIRONMENT

     The following environment variable is utilized by script:

     SHELL  If the variable SHELL exists, the shell forked by script will be that shell.  If SHELL is not set, the Bourne shell is assumed.  (Most
	    shells set this variable automatically).

SEE ALSO

     csh(1) (for the history mechanism).

HISTORY

     The script command appeared in 3.0BSD.

BUGS

     The script utility places everything in the log file, including linefeeds and backspaces.	This is not what the naive user expects.

     It is not possible to specify a command without also naming the script file because of argument parsing compatibility issues.

     When running in -k mode, echo cancelling is far from ideal.  The slave terminal mode is checked for ECHO mode to check when to avoid manual
     echo logging.  This does not work when in a raw mode where the program being run is doing manual echo.

BSD
								 January 22, 2004							       BSD