How can I pass variable to sqlplus script and use same variable as table-prefix in Oracle query?

Question

I am having following sql script namely check_and_create_tbl.sql

DECLARE
t_count NUMBER;

BEGIN
  SELECT count(*) into t_count FROM all_objects WHERE object_name = '&1_EMPLOYEE' AND object_type = 'TABLE';

  if t_count <= 0 then
    execute immediate 'CREATE TABLE ' || &1 || '_EMPLOYEE (
      ID    NUMBER(3)
    , NAME  VARCHAR2(30) NOT NULL
    )';
  end if;
END;

Now I am calling above script in my bash program's function like below.

function exe_orcl {
  exe_command+=("/opt/bin/sqlplus -s <ORCL_USER>/<ORCL_PASS>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<ORCL_HOST>)(PORT=<ORCL_PORT>)))(CONNECT_DATA=(SERVICE_NAME=<ORCL_SNAME>))) ")

  while (( $# > 0 )); do
    exe_command+=("$1")
    shift 1
  done

  echo "SQLPLUS command  =  ${_cmd[@]}"

  "${exe_command[@]}" && { echo "SQLPLUS Success"; } || { echo "SQLPLUS Failed"; }
}

function exe_orcl_sql_file {
  sql_file=$1
  prefix="TBL_SALE"
  shift 1

  exe_orcl << EOF
  whenever oserror exit 9;
  whenever sqlerror exit SQL.SQLCODE;
  SET NEWPAGE NONE
  @${sql_file} ${prefix} ${@};
  exit
  EOF
}

Now in my bash program I try to execute sql file like below.

exe_orcl_sql_file "/tmp/check_and_create_tbl.sql" || { echo "SQLPLUS execution error"; }

I am seeing the log as below.

SQLPLUS command  =  /opt/bin/sqlplus -s XXXX/XXXX@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX)))(CONNECT_DATA=(SERVICE_NAME=XXXX))) 

SQLPLUS Success

But I do not see the TBL_SALE_EMPLOYEE created in Oracle DB, please guide me as to what am i missing here?

Andrew Sayer · Accepted Answer · 2020-12-14 13:03:44Z

1

You need to remember that the variable is input by sql*plus, you also have to realise that '_' is a valid part of a variable name so you need to clearly point out where the end should be.

DECLARE
t_count NUMBER;

BEGIN
  SELECT count(*) into t_count FROM all_objects WHERE object_name = '&1._EMPLOYEE' AND object_type = 'TABLE';

  if t_count <= 0 then
    execute immediate 'CREATE TABLE  &1._EMPLOYEE (
      ID    NUMBER(3)
    , NAME  VARCHAR2(30) NOT NULL
    )';
  end if;
END;
/

answered Dec 14, 2020 at 13:03

Andrew Sayer

2,3461 gold badge9 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

axnet Over a year ago

Thanks, Andrew if I remove / from the last line, The table is not created. I do not want queries to be printed on console.

Andrew Sayer Over a year ago

/ is to submit the statement to the database. Not exactly sure what you’re seeing and what you don’t want to see, but your -s switch in the sqlplus call should prevent anything from being written to console by sqlplus anyway.

axnet Over a year ago

also, in Both the cases when t_count=1 and when t_count=0, i see **execute immediate 'CREATE TABLE TBL_SALE_EMPLOYEE ** being printed on console, which led me to thinking that if condition is really working or not.

Andrew Sayer Over a year ago

The client (sqlplus) knows nothing about what gets executed on the database unless there is some feedback (dbms_output etc), all it knows is that it submitted an anonymous block and it was either executed successfully or failed with an error. I’m not sure where the echoing of anything is coming from - I don’t think it’s SQL*plus, unless you have a (g)login.sql script doing set echo on.

Collectives™ on Stack Overflow

How can I pass variable to sqlplus script and use same variable as table-prefix in Oracle query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related