How to SELECT whole table with OTL, and save it to file?

Question

Here is the problem, I dont know how many attributes or which type are the attributes in table and I need simple select statment like: SELECT * FROM TABLE1; to write down to file.

And this need to be done with otlv4 wrapper.

Please help.

otl_stream i(50, // buffer size             
     "select * from test_tab where f1>=:f<int> and f1<=:f*2",                 // SELECT statement           
     db // connect object      
     );     
int ac=0;
     char bc[64];
     memset(bc, 0, 64);

     while(!i.eof())
     {

        i >> ac >> bc;
        cout << "Boooo " << ac << "  " << bc << endl;
     }

This is example where I know how many attributes are there and which type are there. But what if I dont know that??

Run a describe tablename command to get the description for the table. Then you can format the output accordingly to write to a file. — DumbCoder, Sep 27 '10 at 09:07
well i dont know how to bind all atributes to variables?? I can get variable type for attribute but i need to have that variable initialized allready to put value of attribute into variable. Understand question?? There are no generic return type that I can cast later. — Gile, Sep 27 '10 at 09:30
@Gile - Can you run any DB commands or not ? If you can run select , describe should be supported also ? Did you get at all what I suggested ? First get the description of the table and then it would be easier to read the data, depending on data type, returned from your query ? — DumbCoder, Sep 27 '10 at 10:18
I can run commands. Here is the example code: int ac=0; char bc[64]; memset(bc, 0, 64); while(!i.eof()) { i >> ac >> bc; cout << "Boooo " << ac << " " << bc << endl; } It is easy here to bind variables ac and bc because I know their type before writing code. But I am in situation where I dont know that and I dont know how to use description. Can you give me example? Please. Example: How will you extract data from table that you dont know nothing about. — Gile, Sep 27 '10 at 10:25
@Gile - Instead of the select statement use "describe tablename" -> which will return you the table structure(fieldname,datatype) . After you have got the table structure, you can run a select statement and parse the data accordingly as you now have the table structure to know what datatype to expect ? — DumbCoder, Sep 27 '10 at 11:50
Problem is not in SQL statement, I execute simple SQL statement(SELECT * FROM TABLE1). The problem is with the stream where I need to initialize variables to get value from attributes (stream >> variable) if atrribute is INTEGER variable needs to be int. So do i need to initialize all possible variable types before retrieving data. That is a little stupid. Is there any function that can get me entire row from table in string so I can write it down. I do not need data to exploit them later in code, just need to write it to file. — shake, Sep 27 '10 at 21:14
Do you need to write the file in C++, or is it OK if Oracle does the file creation? There are a bunch of ways to do this in Oracle, although most of them only work from the server and not the client. — Jon Heller, Sep 28 '10 at 04:11

Brett Rossier · Answer 1 · 2010-09-29T20:30:03.763

A file stream along with OTL's check_end_of_row() and set_all_column_types() functions should do what you're asking. While looping on an OTL stream's eof check, for each row you could loop on the end of row check, and send each attribute's value from the OTL stream to the file stream. After each end of row check, send your line break code(s) to the file stream. Setting all column types to str should allow you to use just one variable to process all attribute values in a row.

This example from the OTL docs demonstrates how to use the set_all_column_types function. You must create a stream first, set the option, then open the stream. If you create and open the stream at the same time, it won't work.

How to SELECT whole table with OTL, and save it to file?

1 Answers1