Home » Developer & Programmer » Precompilers, OCI & OCCI » PRO*C
PRO*C [message #305348] Mon, 10 March 2008 08:54 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi All,
I have 2 sql queries as follows. using those two queries I want to write a PRO*C program. I am new to PRO*C. So can you please help me to write this Program

queries
delete
from     detail d1
where    d1.c_change     in (select h.c_change
                                  from   support     isc,
                                         detail  cpd,
                                         head    h
                                  where  h.actdate       = to_date('12-dec-2008 00:00:00','DD-MON-YYYY HH24:MI:SS')
                                  and    h.c_change       = d.cost_change
                                  and    cpd.item              = isc.item
                                  and    cpd.supplier          = isc.supplier
                                  and    cpd.country = isc.id
                                  and    cpd.cost         = isc.cost)


delete
from     head h
where    h.act    = to_date('12-dec-2008 00:00:00','DD-MON-YYYY HH24:MI:SS')
and      not exists        (select 1
                            from   detail cpd
                            where  cpd.cost_change      = h.cost_change)


Please tell me how to write pro*c program for this...
Thank you.
Re: PRO*C [message #305349 is a reply to message #305348] Mon, 10 March 2008 08:57 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi all,

I have just purchased a parachute from ebay. I'm about to jump now. Any last minute advice I should be aware off?

Thank you.

Hint : Take some sort of course before jumping, or at least read a tutorial and then come back when you have a specific question.
Re: PRO*C [message #305350 is a reply to message #305349] Mon, 10 March 2008 08:59 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi am in learning stage...

Please help me... I have tried these two queries in ORACLE.Now I want ot put these two queries in PRO*C. That's why I need your help..

Thank you...
Re: PRO*C [message #305352 is a reply to message #305348] Mon, 10 March 2008 09:02 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Nope, we are not going to do your work for you. However, to get you started see http://www.orafaq.com/wiki/Pro*C

I suggest you also read the Oracle Precompiler manual.
Re: PRO*C [message #305356 is a reply to message #305352] Mon, 10 March 2008 09:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi at least give me an idea how to write DELETE command statemnt in PRO*C.


Re: PRO*C [message #305358 is a reply to message #305356] Mon, 10 March 2008 09:14 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
EXEC SQL DELETE FROM head h
               WHERE  h.act = to_date('12-dec-2008 00:00:00','DD-MON-YYYY HH24:MI:SS')
                 AND  not exists (select 1
                                    from detail cpd
                                   where cpd.cost_change = h.cost_change);

Re: PRO*C [message #305362 is a reply to message #305358] Mon, 10 March 2008 09:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi if u don't mind I have already written the program as follows as u mentioned....
for those 2 queries i wrote
#include <stdio.h>
#include <sqlca.h>
void main()
EXEC SQL < 1st queries>
exec sql <2nd query>
in this in 1st query I have 2 pass two parameters. If parameter="COST" first query will be execute..

if parameter=" gil" then

2nd query will be execute

here I am getting the problem.. How to pass paramaeters...


Re: PRO*C [message #305365 is a reply to message #305362] Mon, 10 March 2008 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without reading Pro*C/C++ Programmer's Guide, you will go nowhere.

Regards
Michel
Re: PRO*C [message #305367 is a reply to message #305365] Mon, 10 March 2008 09:36 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Thank you All.
Re: PRO*C [message #305520 is a reply to message #305367] Tue, 11 March 2008 03:01 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi ,
please give me one more idea for this issue..
I have select statement like

select A.ename,A.job,A.sal from EMP  A where A.EMPNO in 
           (select B.empno from emp B,emp A where B.hiredate=to_date('1-jun-83','dd-mon-yy')
             and  a.ename=b.ename
             and  a.deptno=b.deptno)


How to define it in PRO*C.
I define it as
EXEC SQL < select statement>.
But I need to define bind variables for all the columns . Please help me how to define parameters for this and how to start the program..

Thank you
Re: PRO*C [message #305530 is a reply to message #305520] Tue, 11 March 2008 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is clearly explained in documentation.

Regards
Michel
Re: PRO*C [message #305548 is a reply to message #305530] Tue, 11 March 2008 04:29 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,
I wrote the code as follows .But I am not getting.. Pls observe it and give me the solution for this..
#include <stdio.h> 
#include <sqlca.h>

void sqlerror(); 

EXEC SQL BEGIN DECLARE SECTION; 
char *connstr = "scott/tiger"; 
EXEC SQL END DECLARE SECTION; 

void main() { 
	EXEC SQL WHENEVER SQLERROR DO sqlerror(); 
	EXEC SQL WHENEVER SQLWARNING CONTINUE; 
	EXEC SQL CONNECT :connstr; 

	EXEC SQL WHENEVER NOTFOUND GOTO notfound; 
	EXEC SQL select A.ename,A.job,A.sal from EMP  A where A.EMPNO in 
           (select B.empno from emp B,emp A where B.hiredate=to_date('1-jun-83','dd-mon-yy')
             and  a.ename=b.ename
             and  a.deptno=b.deptno)



notfound:
	printf("Employee record not found in database.\n");
	return; 
} 

void sqlerror() { 
	printf("Stop Error:\t%25i\n", sqlca.sqlcode); 
	return; 
}


Thank you
Re: PRO*C [message #305607 is a reply to message #305548] Tue, 11 March 2008 06:23 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi when I am executing the following code..

#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "scott/tiger";
EXEC SQL END DECLARE SECTION;
void main()
 {
EXEC SQL CONNECT :connstr;
EXEC SQL select A.ename,A.job,A.sal from EMP  A where A.EMPNO in 
           (select B.empno from emp B,emp A where B.hiredate=to_date('1-jun-83','dd-mon-yy')
             and  a.ename=b.ename
             and  a.deptno=b.deptno);
}

I am getting the following error
i.e
ERROR IS
sample.pc[2]: EXEC:  not found.
sample.pc[3]: char:  not found.
sample.pc[4]: EXEC:  not found.
sample.pc[6]: 0403-057 Syntax error at line 6 : `(' is not expected.


please give me solution...


Re: PRO*C [message #305646 is a reply to message #305607] Tue, 11 March 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting the following error

When?

Regards
Michel
Re: PRO*C [message #305684 is a reply to message #305646] Tue, 11 March 2008 09:41 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

when I am running the script
"$sample.pc"
I am getting error as above mentioned.

Thank you.
Re: PRO*C [message #305686 is a reply to message #305684] Tue, 11 March 2008 09:46 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
What you have written is not a script, but source-code.
Source-code needs to be (pre-)compiled and linked into an executable before you can run it.

Re: PRO*C [message #305694 is a reply to message #305686] Tue, 11 March 2008 10:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi,
before compiling it I execute the following

$proc sample.pc

Then I got "sample.c" and "sample.lis"

after that i execute
"$sample.pc" Then I am etting the mentioned errors.
Please give me ideas if any modifications needed..or
please give me sample code for the above mentioned script..

Thank you
Re: PRO*C [message #305695 is a reply to message #305694] Tue, 11 March 2008 10:14 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Well, you're halfway Smile

You've taken the first step: pre-compiling.

Now you'll have to compile the "sample.c" file.
This will get you an object-file ( presumably called "sample.o" ), which you need to "link" with a bunch of libraries.

And NO, I don't know by heart which libraries and where they are residing.
Re: PRO*C [message #305697 is a reply to message #305695] Tue, 11 March 2008 10:26 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi Marcs,
where we'll find ".o" file. I got only .lis,.pc,.c files only.

Please tell me where i had mistake....

Thank u
Re: PRO*C [message #305698 is a reply to message #305695] Tue, 11 March 2008 10:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
MarcS wrote on Tue, 11 March 2008 16:14
Well, you're halfway Smile

You've taken the first step: pre-compiling.

Now you'll have to compile the "sample.c" file.
This will get you an object-file ( presumably called "sample.o" ), which you need to "link" with a bunch of libraries.

And NO, I don't know by heart which libraries and where they are residing.



Read the bold-part of my quote ...
Re: PRO*C [message #305815 is a reply to message #305684] Wed, 12 March 2008 02:11 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel please give me solution for this...


Thank you.
Re: PRO*C [message #305819 is a reply to message #305815] Wed, 12 March 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are demo programs and demo makefile in $ORACLE_HOME/precomp/demo/proc (or the like), study them, use them.
I can't help if you don't have the basics.

Regards
Michel
Re: PRO*C [message #305828 is a reply to message #305819] Wed, 12 March 2008 02:44 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
But I have already written the code right?Now i'll send u one code recently i have written. There i am getting only one error.
please correct it..
#include <stdio.h>
#include <sqlca.h>

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "scott/tiger";
int  c_change;
EXEC SQL END DECLARE SECTION;

void main() {
        EXEC SQL WHENEVER SQLERROR DO sqlerror();
        EXEC SQL WHENEVER SQLWARNING CONTINUE;
        EXEC SQL CONNECT :connstr;

        EXEC SQL WHENEVER NOTFOUND GOTO notfound;
        EXEC SQL SELECT cost_change
                 INTO   c_change
                 FROM   sup_head csh,sup_detail csd
                 WHERE  csh.sup_head=csd.sup_detail;

found:
        printf("%s is in department %i\n", c_change);
        return;

notfound:
        printf("costchange record not found in database.\n");
        return;
}

void sqlerror() {
        printf("Stop Error:\t%25i\n", sqlca.sqlcode);
        return;
}


error:
ssk_sample.pc[4]: 0403-057 Syntax error at line 4 : `(' is not expected.


please correct it. Then I will try to solve ...

Thank you
Re: PRO*C [message #305833 is a reply to message #305828] Wed, 12 March 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on how you compile, with which options and which environment.
Too many questions.

Regards
Michel
Re: PRO*C [message #305836 is a reply to message #305833] Wed, 12 March 2008 03:02 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
i got ssk_sample.c and ssk_sample.lis. then i run it at
$ssk_sample.pc
there I am getting the error.
I am doing this one in development side..
any more.. please ask me...

Thank u

[Updated on: Wed, 12 March 2008 03:02]

Report message to a moderator

Previous Topic: Database Links
Next Topic: Why to use Precompilers....................??
Goto Forum:
  


Current Time: Thu Mar 28 10:08:26 CDT 2024