Pull data from Oracle DB into file with sqlplus must provide schema name

Question

I am trying to pull data(use sqlplus) from Oracle DB into a file, but as that table's last column is default empty string, how to handle it ?

Note: I have verify the 3rd column existing in Oracle DB, the script works fine on other table if last column is not empty, but for this last column empty table, it failed.

Error information is:

[...@... scripts]$ ./myscript.sh
|| TRIM(THIRD_COLUMN) FROM TEST_TABLE
                 *
ERROR at line 3:
ORA-00942: table or view does not exist

The related sqlplus is below:

My_Table="TEST_TABLE"
sqlplus -s  ${USER}/${PASS}@${HOST} <<EOF
SET PAGESIZE 0
SET COLSEP "|"
...some SET here...
SPOOL $FILE
Select TRIM(FIRST_COLUMN)|| '|'
|| TRIM(SECOND_COLUMN)|| '|'
|| TRIM(THIRD_COLUMN) FROM $My_Table;
SPOOL OFF
EXIT
EOF

How can I pull the last column together with all other columns especially when it is empty string ? Could someone help to figure out, thank you.

Community · Accepted Answer · 2017-05-23 12:24:50Z

0

Error suggest that problem is not column but table that you're passing by variable $My_Table. You're passing variables wrong way. Please have a look on that thread.
$My_Table is variable in bash. When you run script first step is running SQLplus and there this variable not exist.

In your case it should be something like:

./myscript.sh $My_Table $FILE

And in your script use &1 in place of $My_Table and &2 in place of $FILE

And if you build your query like that you don't need to SET COLSEP "|" as result of your query is single column so either use COLSEP or || to concatenate all cols

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Dec 13, 2016 at 20:00

Kacper

4,8182 gold badges23 silver badges35 bronze badges

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

6 Comments

Lampard Over a year ago

i have rechecking the error message and edit in right format, could you have to check again ? thanks and sorry for previous wrong information which may confuse you.

Kacper Over a year ago

@Lampard try now. The problem seem to be with wrong way of passin variable

Lampard Over a year ago

as I tried, not because of input variable issue, i set the My_Table="TEST_TABLE" before sqlplus part, also add it in original post here. Still suspect the issue caused relate to last empty column

Kacper Over a year ago

@Lampard so please run sqlplus directly and execute your query without variables. Empty column is not a problem as you can do select '' from table;

Kacper Over a year ago

@Lampard After that line sqlplus -s ${USER}/${PASS}@${HOST} <<EOF you're in SQLplus $My_Table not exists there and it can't be resolved. You have to set name after SQLplus run. Setting it before makes the same problem.

|

Collectives™ on Stack Overflow

Pull data from Oracle DB into file with sqlplus must provide schema name

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related