Call Interface :: Sql Statement Works In SQLPLUS But Will Not Pass Checking In PRO*C
Apr 22, 2013
I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in
[URL]
This has a query which will run in SQLPLUS but fails with PRO*C
:~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long
Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg
PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX
when i try to connect with sqlplus or toad then i get an error
Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:Users>sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly C:Users>
and i want to tell what i do before it-my all files were be converted in .FLT then i shorted out this problem after that when i tried to connect with sqlplus or toad then i get above error.and i have restarted my system but throwing same error.
I have a two node RAC on 11.2.0.3. We are using SCAN Listener here. On my node 1, i can connect to the database using tns alias: The alias looks something like:
sqlplus system/actdbasystem@cusms ----- works sucesfully on the prod server. Even the same prod alias copied to second tnsnames.ora in the cluster and run, it works sucesful too. But the issue is, when we copy the same alias onto any other servers and try to connect give's the following error message:
I have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".
QUERY: Select last_name, first_name from Member where .... FOR UPDATE OF LAST_NAME;
The other thing to note is this SQL query works fine via sqlplus in a 10g environment.
ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view VIEW: select * from otherschema.member@connection_identifier;
This view was created in this manner to allow the user account access to the underlaying table without creating explicit permissions.
"ORA-01017: invalid username/password; logon denied" when attempting to log on to db, in three different applications. Error is observed in Toad, Crystal Reports, and MS ODBC Administrator connect test. However, same db credentials work in SQLPLUS.
Customer is attempting to log into reporting database hosted by my company, and using Cisco VPN client.I am unable to reproduce this at this time, on my machine, using same credentials and VPN profile.
Here is what we know/tried:
1. PING HOSTNAME is successful on CUSTOMER’S machine
2. TNSPING HOSTNAME is successful on CUSTOMER’S machine
3. SQLPLUS is successful with USER2/(pw)@HOSTNAME.int on CUSTOMER’S machine
4. We corrected timeout error on CUSTOMER’S side, by adding TCP.CONNECT_TIMEOUT=1000 in SQLNET.ORA (Also observed extremely slow performance on CUSTOMER SIDE)
5. We corrected dns suffix problem on CUSTOMER’S side, observed in NSLOOKUP by adding: IP ADDRESS AND HOSTNAME to Windows HOSTS file and commenting out default suffix COMPANY.com from SQLNET.ORA file
6. VPN configuration and VPN credentials are good.
We tested CUSTOMER’S VPN profle, CUSTOMER’S VPN credentials, and user USER2/(pw)@HOSTNAME on my machine (WIN7). I am able to maintain persistant connection to HOSTNAME for hours
7. TNSNames entries are good. Able to connect to db in Toad, and Crystal, on CUSTOMER’S machine using different Oracle user, USER3 (only has grant connect). Unable to logon to db, with Oracle user USER1, or USER2. (USER2 was created to try to resolve login problem w USER1, but issue still exists.)
8. CUSTOMER has Windows VISTA, does not have admin privileges, has Oracle 10 installed for client. Using same Cisco VPN client I have installed.
I am completely new to anything with Oracle or OCI,
Also, basic information:
Language: C Oracle DB version: 11g
Trying to use OCI
I am trying to write a client that connects to an Oracle database and gets some information (obviously). On the OCILogon() step, I need the username, password, and db address. However, someone said there will be a tnsnames.ora file, and that should be enough.I've searched online and I have not been able to find anything that relates OCILogon() to tnsnames.ora, so here are my questions:
1) Can OCILogon() use tnsnames.ora at all? 2) Is there any other function that I should use instead of OCILogon() that would work with the tnsnames.ora
Actually i am not using OCI directly. I have a Delphi application in which I would need to clone an open db connection. Not only username, password, and target database (this is easy and i already do that).
What I would need is also inherit context variables and global temporary tables content status at the moment in which the cloning operation takes place. It seems a very difficult, if not impossible, thing to achieve.
We have a occi application which is linked dynamically to occi lib on Solaris. Now during build we linked it with occi of Oracle 10.2.0.4 client but we want the same build to be able to run under Oracle 10G client as well as Oracle 11G client. But it seems that occi is not forward not backward compatible as name of dynamic occi lib has a version in it.
I am using OCI and instant_client (11.2.0.3.0) in my C++ program (on Windows 7) to connect remotely to 11G database. While doing a connection using server name as well as servername:port/service_name as connection strings, I am getting error of ORA-12504 TNS:listener was not given the SERVICE_NAME in CONNECT_DATA. According to instant client FAQ & white paper at [URL]..., no tnsnames.ora is required for instant client. Then why could I be getting this error? I ensured that only the instant client directory is in Path environment variable. There was a ODBC driver installed on this same host.
I wrote a simple OCCI program and ran valgrind with it. I got some memory leak in the program. I tried to search around but couldn't get any answers. I have tried to compile the program using gcc version 4.5, and icpc version 12.0.4 and they both gave the same errors. The OS I am using is RedHat Linux 6.1.
int main() { oracle::occi::Environment *theOCCIEnvPtr = NULL;
[Code] .......
and one of the leaks is :
==9744== 32 bytes in 1 blocks are definitely lost in loss record 61 of 224 ==9744== at 0x4A07279: malloc (vg_replace_malloc.c:263) ==9744== by 0x62D9147: slzsetevar (in /apps/oracle_client/libclntsh.so.11.1)...
We have code that occasionally errors with ORA-12151, with tracing indicating some funk surrounding OCI calls to OCIPing(), OCIBreak(), and OCIReset(). What the code is trying to do here is release a connection from a pool (our implementation, not OCIPool) and before releasing it trying to make sure nothing is actively running on the connection etc. The environment is created in THREADED mode (ie not non-blocking).
Reading the documentation its not really clear to me if OCIBreak() and OCIReset() are really intended to be called while in THREADED mode.
Is it a bad idea to call break/reset as part of a general connection-reset routine?
The basic control flow (minus error handling etc) is:
I install Oracle instant client 11g in my Windows 7(32bit), and use MSVC6++ to test program occidml.cpp, compile is no problem, but when I run it, it crash on create Connection. I have tried to link with different version of OCCI library ( v8,v9), but all crash on create Connection.
I am trying to fetch the data from system table "FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$" in Oracle Express edition 10.2.0.3 database, using the OCI library on Windows with C++
This table has a TIMESTAMP column of date type.
I've the following query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') order by TIME_STAMP asc;
For this, first I am preparing the query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > :PKVAL order by TIME_STAMP asc;
Before calling OCIExecute(), I am setting the PKVAL buffer to following value:
I want to monitor the stream activity with strmmon tool. How to compile the strmmon.mk? I was seeing with visual studio command prompt but it does not work properly.
In Oracle 10.2 installation there are OCI headers in rdbms/public directory: 10.2.0/rdbms/public/ocidem.h 10.2.0/rdbms/public/ocidfn.h 10.2.0/rdbms/public/ocikpr.h 10.2.0/rdbms/public/oci.h 10.2.0/rdbms/public/oci1.h ...
What should be installed additionally to have OCI headers in rdbms/public in Oracle 9.2.0?
I am having an issue to connect my C++ application to my Oracle DB. When my code calls
oracle::occi::Environment::createEnvironment(),
I get an error "ORA-01804: failure to initialize timezone information".
The application works on other people's Windows machine with their Oracle DB. But it doesn't work from my machine connecting to their Oracle instance. So I guess the issue does not come from my instance but rather from my Oracle dlls maybe?
I work with oracle 10g, on windows 7. sometime when i try to start my sqlplus in prompt command this message display.
C:UsersSony>sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly
But the solution people have proposed into the forum are:
1 - check the environment variable Oracle_home and modify if it ending by ""
2 - or try tho run my promt command as Administrator.
I try the both, but only the second works. while now i don't understand why it works when i start the promt command as Administrator and why it no works when i don't run it like administrator. Another thing is i am the only user of my computer, i have only one session (administor session).
In my application, when I passed a Type Array as parameter to the stored procedure and when the Type Array is large enough(normally 3000 type objects， each objects has about 15 attributes), the OCIObjectSetAttr performance deteriorates large enough from below 1 millisecond to 1~3 miliseconds.
I want to run parallel queries of 16 sqlplus jobs through shell script.i don't know to pass values dynamically from shell script to sqlplus.
here i am hard coded the values but i want to pass the values dynamically. and how i will know each job is running successfully.if any job is fail the it will exit from script.
I would like to store user-id, password and the TNS-Entry in a small text file and pass them to a script to log in and execute another script on the database from Windows.
Example of the text file samp.txt that stores user info:
I ran one long running update statement in my sqlplus session, where it took hours together since we have millions of rows on the table.lock the system and left the office , the next day I found my system is rebooted, hope the session would have performed the long running update statement.
Now, I need to issue a commit for the session, since my system is rebooted I don't have opened session with me. so, How to issue commit to the session, which I have issued long running update statement.
I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?
ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");
Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?
