Precompilers, OCI & OCCI :: How To Fetch All Rows In A Cursor In Local Variables
Jul 29, 2008How 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
ADVERTISEMENT
Precompilers, OCI & OCCI :: Pro* C Code To Fetch Cursor And Loop
Nov 18, 2009i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor?
EXEC SQL BEGIN DECLARE SECTION;
char str[64];
EXEC SQL END DECLARE SECTION;
/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR
[Code]....
Precompilers, OCI & OCCI :: Bind Variables API Obndrv()?
Apr 21, 2009can i have some real time code piece for bind variables steps and obndrv(...)
View 1 Replies View RelatedPrecompilers, OCI & OCCI :: ORA-01002 / Fetch Out Of Sequence
Apr 16, 2009I am back with one more issue. I am fetching records through a cursor from database using Pro*C and inserting some records as shown below:
EXEC SQL DECLARE cur_num CURSOR FOR
SELECT STUDENT_NUMBER
FROM STUDENTS
WHERE STDID = :temp_num
ORDER BY STUDENT_NUMBER;
EXEC SQL OPEN cur_num;
EXEC SQL WHENEVER NOT FOUND DO break;
[code]....
This code works fine sometimes but ends up with this error sometimes: ORA-01002: fetch out of sequence and also do I need to do null termination once i fetch data from the database like this:
Quote:EXEC SQL FETCH cur_num INTO :std_num ;
std_num.arr[std_num.len] = '�';
std_num is declared as varchar datatype.
Precompilers, OCI & OCCI :: ProC Host Variables As Global
Aug 25, 2008I 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'.
Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array
Sep 7, 2007I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
Precompilers, OCI & OCCI :: PLS-00642 / Local Collection Types Not Allowed In SQL Statements
Oct 2, 2008I have an anonymous PL/SQL procedure executed from OCCI like:
declare
type temp is table of emp%rowtype;
vemp temp;
begin
select * bulk collect into vemp
[code]...
and a cpp code like:
string stmt = "see above";
stmt->registerOutParam (1, OCCICURSOR);
stmt->execute ();
The "execute" gives me the error (of course): PLS-00642: local collection types not allowed in SQL statements
I cannot select from an PL/SQL table.But how to transfer these PL/SQL table "vemp" into my OCCI program?
Note: the first select is for information only, so I can not select the data in question from that directly.
Precompilers, OCI & OCCI :: Using Cursor In Pro Cobol
Oct 7, 2010I 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?
Precompilers, OCI & OCCI :: How To Use Cursor With Dynamic SQL In ProC
May 24, 2005I 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].....
Precompilers, OCI & OCCI :: Insert Multiple Rows Using Array Bind Feature?
Aug 18, 2010I am trying to insert multiple rows using array bind feature.
The following is working fine & inserting rows as expected.
char values[3][2] = { "1", "2", "3" };
ub2 lenp[3];
lenp[0] = lenp[1] = lenp[2] = 2;
[Code]...
OCIStmtExecute() is failing with the following error
Error - ORA-01480: trailing null missing from STR bind value
Precompilers, OCI & OCCI :: Using Pointer To Structure For Selecting Multiple Rows From Table
May 11, 2010I am working on Pro*C and i have a requirement where i need to select all the rows from a table into a c - structure variable. Since i get to know the no of rows in the table which is getting selected only at run time, i need to create a pointer variable to the structure and i'll allocate the size to it based on the count of rows in the table using malloc or calloc.I tried allocating memory using calloc and it does not show any error. But when i when the exec select statement run it shows an error.
Statements i have used:
struct common *comp;
struct common_ind *comp_i;
comp = (struct common*) calloc(rowcount, sizeof(struct common));
comp_i = (struct common_ind*) calloc(rowcount, sizeof(struct common_ind));
exec sql at db1 select * into :comp indicator :comp_i from tab1;
Error i get :
Stop Error: -2112
Stop Error: -1012
Stop Error: -1012
SQL & PL/SQL :: Local Variables Are Bind Variables
Apr 27, 2012Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH
Nov 1, 2012create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
SQL & PL/SQL :: Bind Variables / Unable To Fetch Value
Sep 8, 2011CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....
In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,
Precompilers, OCI & OCCI :: Connectivity Between C And Oracle?
Aug 12, 2008Is it possible to connect with Oracle using C?
View 2 Replies View RelatedPrecompilers, OCI & OCCI :: Keep Getting ORA-22275 From Extproc?
Sep 26, 2012I 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?
Precompilers, OCI & OCCI :: Describe STP In Package
Nov 26, 2009I 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.
Precompilers, OCI & OCCI :: 11g Pro*C Application Crashes While 10g Is OK
Jan 24, 2009My 10g pro*c application works perfectly, recently I upgraded it to 11g, but it keep crashing, why?
View 10 Replies View RelatedPrecompilers, OCI & OCCI :: Proc Compilation?
Mar 31, 2010i 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 RelatedPrecompilers, OCI & OCCI :: How To Get Columns As NUMBER(30)
Aug 13, 2008in 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 RelatedPrecompilers, OCI & OCCI :: Calling Procedure From PRO C?
Nov 26, 2012I 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 )
Precompilers, OCI & OCCI :: How To Connect To Two Databases
Aug 25, 2009My 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.
Precompilers, OCI & OCCI :: How To Report Oracle Bug
Jan 30, 2009How to report an Oracle bug? Is there webpage or email?
View 4 Replies View RelatedPrecompilers, OCI & OCCI :: How To Connect C++ And Oracle
Sep 17, 2008My 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.
Precompilers, OCI & OCCI :: Print Run Time Value?
Apr 26, 2012I 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 RelatedPrecompilers, OCI & OCCI :: Convert C To PC File?
Dec 11, 2010Can 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 RelatedPrecompilers, OCI & OCCI :: How To Integrate SQLnet
Apr 17, 2009how 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).
Precompilers, OCI & OCCI :: Indicator Values For DATES
Jun 1, 2010If 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]...
Precompilers, OCI & OCCI :: Using Distributed Transactions With ProC
Jul 7, 2011I 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 .
Precompilers, OCI & OCCI :: NULL Values In C Structures?
May 18, 2010I 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?