I Want to declare some host variables with global scope. Like this C variable:
MyFile.h
...
char str_var[20];
...
But i can't put SQL sentences in a include file. And if i do this:
MyFile.pc
...
EXEC SQL BEGIN DECLARE SECTION;
char str_var_h[20];
EXEC SQL END DECLARE SECTION;
...
... I can't see this variable from others sources, although I declare it like 'extern'.
i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.
View 2 Replies View RelatedI found this thread, and recently i want to embedded oci in pro*c for distributed transaction reason.
can pro*c deal with distributed transaction? i want to use mts in pro*c .
I am using dynamic Pl/SQL with ProC and having problems with cursor. I've to execute stored procedure dynamically and get the result of select list into the cursor. The EXECUTE command is working fine but the FETCH gets failed with error "ORA-01002: fetch out of sequence". I've read that cursor variable can not be used with dynamic SQL.
Stored Procedure:
------------------
ROCEDURE open_mod_cur (
curs IN OUT cur_type,
module_id IN varchar2)
[Code].....
I am running a pro*c its working fine but my connection gets disconnect to my db server so i want to continue my proc if connection between my machine & server reestablish again weather it starts from start or from where it was stooped because of connection break
View 9 Replies View RelatedI am trying to compile a pro c file. Rest of the pro c file is compiling fine. But one particular file is showing following error.
INTERNAL ERROR: Failed Assertion [Code=40706]
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
I am trying to update a table with a MERGE command using a Host Array and I am getting a unique constraint violation. According to the Oracle documentation "sqlerrd[2]holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of sqlca.sqlerrd[2] is undefined, with one exception. If the error occurred during an array operation, processing stops at the row that caused the error, so sqlca.sqlerrd[2] gives the number of rows processed successfully."
So when I catch the error and try to print out the offending row in the host array, it points to the incorrect row. If I change the MERGE command to just an INSERT, sqlerrd[2] now points correctly to the row in error. Is there an issue with MERGE in this respect? How can I get the correct row with MERGE?
can i have some real time code piece for bind variables steps and obndrv(...)
View 1 Replies View RelatedHow to fetch all the rows in a cursor in local variables(host variables) in PRO C . /*For x in cur is not working....*/
View 2 Replies View Relatedi have some confusion with bind and host variable.
View 4 Replies View RelatedExplain about Global variables in Plsql? What is use of these global variables and where do use these variables?
View 1 Replies View RelatedI have taken some procedures code from SVN repository.These procedures are split into different schema. There are some client and common parameters used in the from clause of table in these sp.for example
select *
from client.emp;
during the compling procedure, we replace the client value into some schema name.
select *
from scott.emp;
The client and common parameter should be different for every schema.
Is any possible to create global varaible for client and common parameter and subsitute value in it.i.e without editing client and common parameter?
Is it possible to connect with Oracle using C?
View 2 Replies View RelatedI am trying to give back data from a stored procedure written in C. I registered the functions as follows:create or replace procedure version(versioninfo OUT clob) as external name "version" library myLib language c with context parameters (context, versioninfo, versioninfo INDICATOR SB4); It compiles fine. The function being called look like this:
extern "C" DLLEXPORT
void version(OCIExtProcContext* context, OCILobLocator **out, sb4 *ind_out){
OCIEnv* envh;
OCISvcCtx* svch;
OCIError* errh;
OCIExtProcGetEnv (context, &envh, &svch, &errh);
std::string versioninfo = "104";
oraub8 maxChar = 10;
[code]...
If I execute the procedure with SQLDeveloper by pressing "play" it is getting executed but there is no result. If I try to execute it from an anonymous block it results in ORA-22275 instead of doing anything.
declare
res clob;
begin
-- the following doesn't work much
--dbms_lob.createtemporary(res,true);
version(res);
dbms_output.put_line(res);
end;
Actually I have to questions:
1.) Why does Oracle give me the error? In my opinion all requirements mentioned by the error description are met.
2.) Why is there no output when executing the function via SQL Developer? Is the usage of OCILobWrite wrong?
I am trying to describe an STP in a package, but it gives me an error.
e.g. In package ABC suppose there is an STP XYZ, I am trying to describe ABC.XYZ function but it gives me an error code 4043 and error message object XYZ.ABC does not exist.
My 10g pro*c application works perfectly, recently I upgraded it to 11g, but it keep crashing, why?
View 10 Replies View Relatedin my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.
View 6 Replies View RelatedI am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL
like this
my_procedure( q=>a1 , b=>'abc )
My program needs to connect to two databases and make operations in both databases:
Quote:connect to db1;
connect to db2;
select from table in db1;
update table in db2;
i would like to know whether it's possible, and how to do it ? how to specify for each query the database connection to use?
note that I can't use dblinks as i don't have enough access rights and privileges on neither of the databases.
How to report an Oracle bug? Is there webpage or email?
View 4 Replies View RelatedMy OS is Linux and I installed Oracle 10.2. Everything is fine.I can use sqlplus, exp, imp etc with no problem.Now I have created another linux user test in /home/test. I unzipped basic-10.2xxxxxx.zip (/home/test/instantclient_10_2) and exported LD_LIBRARY_PATH.I guess I have installed instant client in this way.
My testOra.cpp:
#include <occi.h>
int main()
{
return 0;
}
This test.cpp would not compile. It cannot find occi.
I declare a cursor for a table with 8000 records, when I fetch the cursor this message appear ORA-03113: end-of-file on communication channels when the fetch reach the 6500 element, What is the problem here?. All data are ok, not null fields.
other problem
How I can reuse a cursor, I declare a cursor for a table where code_part = 300, then I fetch all elements until end cursor, then I close the cursor, Then I declare the same cursor again for the same table where code_part = 359, but it is not successful, when I tried to fetch the cursor again the cobol program show me the last record for the first code, How I can restart the cursor or delete it or freed the cursor position?
I am inserting empno in a table1 and updating another table2 using table1 empno and getting ora-01427 error. I want to print empno for whis this error is coming. How to print that value?
View 1 Replies View RelatedCan i convert a .c file to .pc file?The .c file is generated by compiling .pc file. I mistakenly deleted the .pc file. I need the original .pc file.
View 2 Replies View Relatedhow to integrate SQLnet & c and I'm quite losted at the moment.
Searching with google gives some random stuff, which does seems to be irrelevant.some oracle db somewhere and need for good way to use that remote db (one solution seems to be using SQLnet).
If doing an insert into DATE type fields like below... how do I employ null indicator values with the TO_DATE sql to cope with NULL values for the End Date? I can test the NULLness of the pServiceRecord->itemTo value and set the indicator ind_dbToDate to -1 but I don't know how to incorporate this with the to_date syntax (if I can)?
EXEC SQL BEGIN DECLARE SECTION;
char dbFromDate[MAX_DATE_LEN];
char dbToDate[MAX_DATE_LEN];
short ind_dbToDate;
[code]...
I use a cursor to select records from a database table into a C structure as follows...
{
int iLoop = 0;
int iResult = 0;
[Code]....
The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...
// this is output later in another fuction using the following..
sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);
If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?
I have Multi-threaded application using pro*c to connect to oracle.
I am using the following piece of code to create a connection to oracle database and preserver the context of created connection.
struct sqlca * tempSqlca = new sqlca;
struct sqlca & sqlca = * tempSqlca;
EXEC SQL BEGIN DECLARE SECTION;
SQL_CONTEXT localContext;
[code].....
I am using the following piece of code to close the connection to the oracle database, I am using the context created in the previous step and passing it to the below code(connection.context)to release the connection.
if (connection.context == 0 || connection.ca == 0)
return true;
// Multithreading: get the communication area of the context
struct sqlca & sqlca = *(connection.ca);
EXEC SQL BEGIN DECLARE SECTION;
[code].....
When the above code is executed there is no exception thrown but the connection is not getting close. I am using the V$SESSION system views to monitor the connection created.
I there anything I am missing in my code for the connection closing to fail.
I am receiving the following error while inserting records.
"Oracle Error:ORA-12899: value too large for column "MFG_ADMIN"."GAGE_RESULTS"."COMP
whereas I am checking the length of all values before inserting and am sure that none of them are larger than column lengths.I did some research and found this error might be due to character set.
select * from nls_database_parameters where parameter like '%CHARACTERSET';
PARAMETER VALUE
------------------------------
----------------------------------------------------------------------------------------------------
--------------------
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8
how can this be related to my problem or if something else is causing this error.