Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

C-program connecting with olog()

МішаSep 4 2020 — edited Sep 5 2020

I have a fairly simple C-program, which connects to Oracle databases using the olog() function:

        if (olog(&lda, NULL,

            (OraText *)user, -1,

            (OraText *)password, -1,

            (OraText *)sid, sidlen,

            OCI_LM_DEF)) {

                errx(EX_NOPERM, "Logging into Oracle failed: %s",

                    oraerr(&lda, buf, sizeof(buf)));

        }

This usually just works... Recently we started deploying new Oracle servers (version 19c) and found the above code failing with ORA-01017 (invalid credentials), even though the same username/password are working with other tools (such as sqlplus).

What finally helped was:

  1. Set SQLNET.ALLOWED_VERSION_CLIENT=8.
  2. Reset the passwords -- to the same strings -- after the above change.

Why were these manipulations necessary? I understand, that olog() is an old API, but, as long as it is still included in the client library (we now use 18c on the client), it should "just work", shouldn't it?

It is not any less secure, than the newer OCILogon() -- both take username and password in clear text. So why does using olog() require gimmicks on the server to work -- and fails with a bogus error-message until the gimmicks are applied?

Perhaps, there is a way to make it work -- such as by setting something in the lda-structure (currently I simply bzero it before calling olog()).

(To those, who'd switch topic to tell me to change the API: no, I cannot do that, because the same program still needs to compile against much older Oracle-clients on some of our systems.)

Comments

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ:

Use the analytic ROW_NUMBER function to assign numbers 1, 2, 3, ... to each row, with a separate series for each worker and date.  Each distinct combination of worker and assigned number will result in a row of output.

CarlosDLG

You will usually get quicker and more accurate answers if you post create table statements and inserts with some sample data, along with an explanation of the specific problem you are facing.

Here is one way to do it:

WITH test_data as

(

SELECT 'Project_A' project,'John' worker,date '2016-05-23' the_date FROM DUAL union all

SELECT 'Project_A','Mary',date '2016-05-23'  FROM DUAL union all

SELECT 'Project_A','Mary',    date '2016-05-24' FROM DUAL union all

SELECT 'Project_A','Steve',date '2016-05-24' FROM DUAL union all

SELECT 'Project_A','Mary',date '2016-05-25' FROM DUAL union all

SELECT 'Project_A','Mary',date '2016-05-26'  FROM DUAL union all

SELECT 'Project_B','John',date '2016-05-23' FROM DUAL union all

SELECT 'Project_B','Steve',date '2016-05-24' FROM DUAL

)

select worker,c23,c24,c25,c26 from

(

  SELECT t.*, dense_rank() over (PARTITION BY worker ORDER BY project) AS rn

  FROM test_data t

)

pivot

(

  MAX(project) FOR the_date in (date '2016-05-23' AS C23,date '2016-05-24' AS C24,date '2016-05-25' AS C25,date '2016-05-26' AS C26)

)

ORDER BY worker;

Results:

WORKERC23C24C25C26
JohnProject_A
JohnProject_B
MaryProject_AProject_AProject_AProject_A
Steve Project_A
Steve Project_B
Paulzip

You cannot dynamically generate your columns (e.g. generate your column names based on the data) without using special techniques (dynamic SQL), SQL has to have static columns at execution time.

Here's another way to do it.

select worker, d1, d2, d3, d4

from

(

  select project, worker,

         dense_rank() over (order by the_date) as date_rank,

         dense_rank() over (order by project) as project_rank

  from test_data t

)

pivot (

  max(project)

  for date_rank in (1 as D1, 2 as D2, 3 as d3, 4 as d4)

)

order by worker

WORKERD1D2D3D4
JohnProject_A
JohnProject_B
MaryProject_AProject_AProject_AProject_A
SteveProject_A
SteveProject_B
1 - 3

Post Details

Added on Sep 4 2020
2 comments
2,069 views