Operating System - Linux
1748154 Members
3589 Online
108758 Solutions
New Discussion юеВ

passing variable to sql loader files

 
Gyankr
Frequent Advisor

passing variable to sql loader files

Hi all,

I would like to know if we can pass a variable from a shell script to an sql loader control file.

I know we can pass a variable to an sql file from unix shell but not sure if this is possible in sql loaders

Regards,
Gyan
6 REPLIES 6
Bill Hassell
Honored Contributor

Re: passing variable to sql loader files

If the SQL loader will accept stdin then you can do it a couple of ways:

echo "some_cmd $VAR1 $VAR2" | sql_loader

or from a here document:

cat << EOF
some_cmd $VAR1 $VAR2
EOF

The here document is easier to use for multiple lines for the loader.


Bill Hassell, sysadmin
Yogeeraj_1
Honored Contributor

Re: passing variable to sql loader files

hi Gyan,

The SQLLDR control file is just a static flat file, you cannot really pass a parameter to that file. Especially when the latter is used by the SQLLDR tool.

You can try to generate the SQLLDR file on-the-fly based on variables input to custom generator script.

If you have anything specific, we can post it here so that we can further assist you.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Gyankr
Frequent Advisor

Re: passing variable to sql loader files

unix scripts sends a variable (say region),
and in the control file we need use it like this

delete from table where exists (select 1 from table where region_code='$region')

.....


Regards,
Gyan
Yogeeraj_1
Honored Contributor

Re: passing variable to sql loader files

hi gyan,

This is a bit confusing. Normally, a SQLLDR control file looks as follows:

LOAD DATA
DISCARDMAX 999
APPEND
INTO TABLE MYTAB130
(
LDRDATE "sysdate",
REGION CONSTANT 'PHX',
STATION "1",
WRKLINE position(1:80) char(80) "rtrim(:WRKLINE)"
)


Will it be possible to post a complete file?

revert!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Gyankr
Frequent Advisor

Re: passing variable to sql loader files

hi yogeeraj,

if i see your the above code,it is mentioned like
APPEND
INTO TABLE MYTAB130

instead of appending in the table MYTAB130,we need to delete records like

delete from MYTAB130 where exists (select 1 from someother table where region='$region') and the rest would continue.
(
LDRDATE "sysdate",
REGION CONSTANT 'PHX',
STATION "1",
WRKLINE position(1:80) char(80) "rtrim(:WRKLINE)"
)

I am yet to write such a file.

Regards,
gyan

Yogeeraj_1
Honored Contributor

Re: passing variable to sql loader files

Hi Gyan,

Unfortunately, SQLLDR has a rather restrictive syntax. The possible operations on the underlying table are only: INSERT, APPEND, REPLACE, TRUNCATE.


You may wish to have a look at the High-Level Syntax Diagrams concerning SQLLDR which are found at:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch05.htm#1261

Can you please describe what you are trying to achieve?

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)