...
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>
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 Replies)
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... (1 Reply)
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... (2 Replies)
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... (2 Replies)
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 (4 Replies)
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... (1 Reply)
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... (7 Replies)
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... (12 Replies)
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... (4 Replies)
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"
... (2 Replies)
Discussion started by: sujitdas2104
2 Replies
LEARN ABOUT MOJAVE
sql::translator::parser::dbix::class
SQL::Translator::Parser::DBIx::Class(3) User Contributed Perl Documentation SQL::Translator::Parser::DBIx::Class(3)NAME
SQL::Translator::Parser::DBIx::Class - Create a SQL::Translator schema from a DBIx::Class::Schema instance
SYNOPSIS
## Via DBIx::Class
use MyApp::Schema;
my $schema = MyApp::Schema->connect("dbi:SQLite:something.db");
$schema->create_ddl_dir();
## or
$schema->deploy();
## Standalone
use MyApp::Schema;
use SQL::Translator;
my $schema = MyApp::Schema->connect;
my $trans = SQL::Translator->new (
parser => 'SQL::Translator::Parser::DBIx::Class',
parser_args => {
dbic_schema => $schema,
add_fk_index => 0,
sources => [qw/
Artist
CD
/],
},
producer => 'SQLite',
) or die SQL::Translator->error;
my $out = $trans->translate() or die $trans->error;
DESCRIPTION
This class requires SQL::Translator installed to work.
"SQL::Translator::Parser::DBIx::Class" reads a DBIx::Class schema, interrogates the columns, and stuffs it all in an $sqlt_schema object.
Its primary use is in deploying database layouts described as a set of DBIx::Class classes, to a database. To do this, see "deploy" in
DBIx::Class::Schema.
This can also be achieved by having DBIx::Class export the schema as a set of SQL files ready for import into your database, or passed to
other machines that need to have your application installed but don't have SQL::Translator installed. To do this see "create_ddl_dir" in
DBIx::Class::Schema.
PARSER OPTIONS
dbic_schema
The DBIx::Class schema (either an instance or a class name) to be parsed. This argument is in fact optional - instead one can supply it
later at translation time as an argument to "translate" in SQL::Translator. In other words both of the following invocations are valid and
will produce conceptually identical output:
my $yaml = SQL::Translator->new(
parser => 'SQL::Translator::Parser::DBIx::Class',
parser_args => {
dbic_schema => $schema,
},
producer => 'SQL::Translator::Producer::YAML',
)->translate;
my $yaml = SQL::Translator->new(
parser => 'SQL::Translator::Parser::DBIx::Class',
producer => 'SQL::Translator::Producer::YAML',
)->translate(data => $schema);
add_fk_index
Create an index for each foreign key. Enabled by default, as having indexed foreign key columns is normally the sensible thing to do.
sources
Arguments: @class_names
Limit the amount of parsed sources by supplying an explicit list of source names.
SEE ALSO
SQL::Translator, DBIx::Class::Schema
AUTHORS
See "CONTRIBUTORS" in DBIx::Class.
LICENSE
You may distribute this code under the same terms as Perl itself.
perl v5.18.2 2014-01-22 SQL::Translator::Parser::DBIx::Class(3)