Re: passing variable to sql loader files (2417 Views)
Reply
Frequent Advisor
Gyankr
Posts: 53
Registered: ‎12-07-2007
Message 1 of 7 (2,417 Views)

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
Please use plain text.
Honored Contributor
Bill Hassell
Posts: 14,205
Registered: ‎05-29-2000
Message 2 of 7 (2,417 Views)

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.
Please use plain text.
Honored Contributor
Yogeeraj_1
Posts: 4,613
Registered: ‎11-25-2001
Message 3 of 7 (2,417 Views)

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)
Please use plain text.
Frequent Advisor
Gyankr
Posts: 53
Registered: ‎12-07-2007
Message 4 of 7 (2,417 Views)

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
Please use plain text.
Honored Contributor
Yogeeraj_1
Posts: 4,613
Registered: ‎11-25-2001
Message 5 of 7 (2,417 Views)

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)
Please use plain text.
Frequent Advisor
Gyankr
Posts: 53
Registered: ‎12-07-2007
Message 6 of 7 (2,417 Views)

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

Please use plain text.
Honored Contributor
Yogeeraj_1
Posts: 4,613
Registered: ‎11-25-2001
Message 7 of 7 (2,417 Views)

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)
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation