topic Re: passing variable to sql loader files in Operating System - Linux

passing variable to sql loader files

Gyankr — Thu, 27 Mar 2008 09:39:07 GMT

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

Re: passing variable to sql loader files

Bill Hassell — Thu, 27 Mar 2008 09:46:27 GMT

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.

Re: passing variable to sql loader files

Yogeeraj_1 — Thu, 27 Mar 2008 09:50:17 GMT

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

Re: passing variable to sql loader files

Gyankr — Thu, 27 Mar 2008 10:07:51 GMT

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

Re: passing variable to sql loader files

Yogeeraj_1 — Thu, 27 Mar 2008 10:40:55 GMT

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

Re: passing variable to sql loader files

Gyankr — Thu, 27 Mar 2008 11:17:38 GMT

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

Re: passing variable to sql loader files

Yogeeraj_1 — Thu, 27 Mar 2008 11:42:41 GMT

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