Re: Embedded PS/SQL in Pro*C program

You need to use the USERID option as well as SQLCHECK=full when you precompile. If the program connects with scott/tiger, then the command would be USERID=scott/tiger. It isn't case sensitive. If you are using our makefile (proc.mk) then add the USERID parameter to the PROFLAGS macro.

In article <36s46k$7sm_at_gaia.ucs.orst.edu>, mickel_at_OES.ORST.EDU (Paul Mickel) writes:
|> Greetings,
|>
|> I am having a problem with embedding a PL/SQL block inside of a Pro*C program.
|> The program is supposed to delete from a table and repopulate it from another
|> machine. My problem is that the Pro*C precompiler is expecting a host variable
|> name in the 'delete' statement where you normally specify the table to delete
|> from. I lokked in the manuals, and it seems I have done it right unless there
|> is something not specified there. I also attempted to compile the example pro-
|> gram that does this type of thing, and it to failed to compile, with the same
|> error. I have included the program and a script of the output when I attempt to
|> compile the code. I would appreciate any suggestions anyone might have. I am
|> a newer person at Pro*C programming (I do mostly Forms and Menu development),
|> so keep that in mind when answering, please. I will summarize any answers that
|> I get.
|>
|> Version Numbers:
|>
|> Oracle Server 7.0.12.1.0
|> PL/SQL 2.0.14.0.1
|> Pro*C 1.5.6.2.1
|>
|> Thanks in advance. Please send responses to mickel_at_oes.orst.edu.
|>
|> Paul
|>
|> PROGRAM START
|> ===============================================================================
|> /******************************************************************************
|> Program: tdist
|> Programmmer: Paul Mickel
|> Creation Date: 19 Sept 1994
|> Description: This program is designed to distribute information about
|> printers from a central machine to the rest of the UNIX
|> systems at TWCA. Specifically, it is used to maintain infor-
|> mation about printers on plant.
|> ******************************************************************************/
|>
|> #include <stdio.h>
|> #include <stdlib.h>
|> #include <values.h>
|> #include <sys/types.h>
|>
|> #define FALSE 0
|> #define TRUE 1
|> #define BSIZ 4000
|> #define TABV 8
|> #define MAXSIZE 2048
|> #define USER "FOOBAR"
|> #define PASSWD "BYE_2"
|> #define DBSTRING "t:twcau03:prog"
|>
|> /******************************************************************************
|> declare the SQL variables used to interact with the database. right now, only
|> two are used, username and passwd.
|> ******************************************************************************/
|>
|> EXEC SQL BEGIN DECLARE SECTION;
|> VARCHAR username[30];
|> VARCHAR passwd[30];
|> EXEC SQL END DECLARE SECTION;
|>
|> EXEC SQL INCLUDE sqlca;
|>
|> main(argc, argv)
|> int argc;
|> char **argv;
|> {
|> char error_string[MAXSIZE];
|>
|> strcpy(username.arr,USER);
|> username.len = strlen(username.arr);
|> strcpy(passwd.arr,PASSWD);
|> passwd.len = strlen(passwd.arr);
|>
|> EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
|>
|> EXEC SQL CONNECT :username IDENTIFIED BY :passwd;
|>
|> fprintf(stdout,"Connected to Oracle as user %s\n", username.arr);
|>
|> EXEC SQL EXECUTE
|> BEGIN
|> UPDATE ptr_hosts SET host_system = 'TO_HELL_WITH_U'
|> WHERE host_system = 'FOO';
|> END;
|> END-EXEC;
|>
|> sqlerror:
|> fprintf(stdout,"\n\n% .70s \n",sqlca.sqlerrm.sqlerrmc);
|> EXEC SQL WHENEVER SQLERROR CONTINUE;
|> EXEC SQL ROLLBACK WORK RELEASE;
|> exit(1);
|> }
|> SCRIPT START BELOW
|> -------------------------------------------------------------------------------
|>
|> Script started on Tue Oct 4 10:17:05 1994
|>
|> pmickel_at_karl [284] make -f proc.mk tdist
|> /usr/oracle/product/7.0.12/bin/proc ireclen=132 oreclen=132 select_error=no sqlcheck=full iname=tdist.pc
|>
|> Pro*C: Release 1.5.6.2.1 - Production on Tue Oct 4 10:17:11 1994
|>
|> Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.
|>
|>
|> Precompiling tdist.pc
|> 55 END-EXEC;
|> 55 ..^
|> PCC-S-0061: Error at line 57, column 14. PLS-201: identifier 'PTR_HOSTS' must be declared
|> 55 END-EXEC;
|> 55 ..^
|> PCC-S-0061: Error at line 57, column 7. PLS-0: SQL Statement ignored
|> *** Error code 1
|>
|> Stop.
|> pmickel_at_karl [285] exit
|>
|> script done on Tue Oct 4 10:17:14 1994
|> ===============================================================================
|>
|>
|> --
|> Paul Mickel Internet: mickel_at_oes.orst.edu
|> Systems Developer II UUCP: ..!hplabs!hp-pcd!orstext!mickel
|> Teledyne Wah Chang - Albany Albany, OR 97321 USA
|> Disclaimer: My employer never claims my opinions unless they makes a profit.
Received on Tue Oct 04 1994 - 19:14:59 CET