hagaiy
asked on
proc program returns ora-01458
hello.
i have got a proc program that worked good on oracle 7, i compiled it to work with oracle 8 and when i try to tun it i get ora-01458 .
the problem is in the line :
dbms_output.get_line (:outtxt,:status);
the declaration of the relevant varaibles :
exec sql
BEGIN DECLARE SECTION;
VARCHAR outtxt[255];
int status;
exec sql
END DECLARE SECTION;
does anyone knows whay it doesnt work ?
tx.
hagai.
i have got a proc program that worked good on oracle 7, i compiled it to work with oracle 8 and when i try to tun it i get ora-01458 .
the problem is in the line :
dbms_output.get_line (:outtxt,:status);
the declaration of the relevant varaibles :
exec sql
BEGIN DECLARE SECTION;
VARCHAR outtxt[255];
int status;
exec sql
END DECLARE SECTION;
does anyone knows whay it doesnt work ?
tx.
hagai.
ASKER
i have this call on my program, this is the full program :
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
VARCHAR ora_sql_command[500];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void main( int argc, char *argv[] )
{
EXEC SQL INCLUDE sqlca.h;
char *octo_env;
char *exec_str;
char *sql_cmd;
char *procname;
char *params;
int args_len;
int counter;
int buf_size;
int args_no;
char msg[200];
size_t buf_len, msg_len;
exec sql
BEGIN DECLARE SECTION;
VARCHAR outtxt[255];
int status;
exec sql
END DECLARE SECTION;
/* Finding which enviroment do we work in */
octo_env = getenv("OCTO_ENV");
if ( !octo_env )
{
printf ("ERROR: enviroment OCTO_ENV must be declared.\n");
exit(2);
}
strcpy (username.arr,"octo");
strcat (username.arr,octo_env);
username.len=strlen(userna me.arr);
strcpy (password.arr,username.arr );
password.len=strlen(passwo rd.arr);
/* Checking for the number of arguments */
args_no = argc - 1;
if ( args_no < 1 )
{
printf("No procedure name\n");
exit(2);
}
/* Connecting to the database */
exec sql
whenever sqlerror goto connect_error;
exec sql
connect :username identified by :password;
/* Finding the procedure-name */
procname = (char *) malloc ( sizeof(char) * strlen(argv[1]));
if (!procname)
{
perror("malloc(procname) failed");
exit(1);
}
strcpy (procname, argv[1]);
/* Finding the length of the arguments */
args_len = 0;
for (counter=2;counter<=args_n o;counter+ +)
{
args_len = args_len + strlen(argv[counter]) + 1;
};
args_len = args_len + 3;
/* Preparing the paramters string */
params = (char *) malloc ( sizeof(char) * args_len);
if (!params)
{
perror("malloc(username) failed");
exit(1);
}
if ( args_no > 1 )
{
strcpy (params,"(");
for (counter=2;counter<args_no ;counter++ )
{
strcat(params,argv[counter ]);
strcat(params,",");
}
strcat(params,argv[args_no ]);
strcat(params,");");
}
else
{
strcpy (params,";");
}
/* Making the executing string */
exec_str = (char *) malloc ( sizeof(char) * strlen(params)+strlen(proc name));
if (!exec_str)
{
perror("malloc(exec_str) failed");
exit(1);
}
strcpy (exec_str,procname);
strcat (exec_str,params);
/* Making the sql command to execute and executing it */
sql_cmd = (char *) malloc ( sizeof(char) * strlen(exec_str) + 15);
if (!sql_cmd)
{
perror("malloc(sql_cmd) failed");
exit(1);
}
strcpy (sql_cmd,"begin ");
strcat (sql_cmd,exec_str);
strcat (sql_cmd," end;");
strcpy (ora_sql_command.arr,sql_c md);
ora_sql_command.len = strlen(ora_sql_command.arr );
/* Enabling the buffer for dbms_output */
exec sql
execute
begin
/*buf_size = */
dbms_output.enable(1000000 );
end;
end-exec;
exec sql
whenever sqlerror goto exec_error;
exec sql execute immediate :ora_sql_command;
//exec sql
//whenever sqlerror continue;
/* Printing the output */
exec sql
execute
begin
dbms_output.get_line (:outtxt,:status);
end;
end-exec;
while ( !status)
{
outtxt.arr[outtxt.len]='\0 ';
printf ("%s\n",outtxt.arr);
exec sql
execute
begin
dbms_output.get_line (:outtxt,:status);
end;
end-exec;
}
/* Freeing the memory of the buffer */
exec sql
execute
begin
dbms_output.disable;
end;
end-exec;
exit(0);
/* Error in the connection */
connect_error:
fprintf(stderr,"%s: Cannot connect to the database\n",argv[0]);
exit(2);
exec_error:
buf_len = sizeof (msg);
sqlglm(msg, &buf_len, &msg_len);
printf ("%.*s\n", msg_len, msg);
exit(1);
}
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
VARCHAR ora_sql_command[500];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
void main( int argc, char *argv[] )
{
EXEC SQL INCLUDE sqlca.h;
char *octo_env;
char *exec_str;
char *sql_cmd;
char *procname;
char *params;
int args_len;
int counter;
int buf_size;
int args_no;
char msg[200];
size_t buf_len, msg_len;
exec sql
BEGIN DECLARE SECTION;
VARCHAR outtxt[255];
int status;
exec sql
END DECLARE SECTION;
/* Finding which enviroment do we work in */
octo_env = getenv("OCTO_ENV");
if ( !octo_env )
{
printf ("ERROR: enviroment OCTO_ENV must be declared.\n");
exit(2);
}
strcpy (username.arr,"octo");
strcat (username.arr,octo_env);
username.len=strlen(userna
strcpy (password.arr,username.arr
password.len=strlen(passwo
/* Checking for the number of arguments */
args_no = argc - 1;
if ( args_no < 1 )
{
printf("No procedure name\n");
exit(2);
}
/* Connecting to the database */
exec sql
whenever sqlerror goto connect_error;
exec sql
connect :username identified by :password;
/* Finding the procedure-name */
procname = (char *) malloc ( sizeof(char) * strlen(argv[1]));
if (!procname)
{
perror("malloc(procname) failed");
exit(1);
}
strcpy (procname, argv[1]);
/* Finding the length of the arguments */
args_len = 0;
for (counter=2;counter<=args_n
{
args_len = args_len + strlen(argv[counter]) + 1;
};
args_len = args_len + 3;
/* Preparing the paramters string */
params = (char *) malloc ( sizeof(char) * args_len);
if (!params)
{
perror("malloc(username) failed");
exit(1);
}
if ( args_no > 1 )
{
strcpy (params,"(");
for (counter=2;counter<args_no
{
strcat(params,argv[counter
strcat(params,",");
}
strcat(params,argv[args_no
strcat(params,");");
}
else
{
strcpy (params,";");
}
/* Making the executing string */
exec_str = (char *) malloc ( sizeof(char) * strlen(params)+strlen(proc
if (!exec_str)
{
perror("malloc(exec_str) failed");
exit(1);
}
strcpy (exec_str,procname);
strcat (exec_str,params);
/* Making the sql command to execute and executing it */
sql_cmd = (char *) malloc ( sizeof(char) * strlen(exec_str) + 15);
if (!sql_cmd)
{
perror("malloc(sql_cmd) failed");
exit(1);
}
strcpy (sql_cmd,"begin ");
strcat (sql_cmd,exec_str);
strcat (sql_cmd," end;");
strcpy (ora_sql_command.arr,sql_c
ora_sql_command.len = strlen(ora_sql_command.arr
/* Enabling the buffer for dbms_output */
exec sql
execute
begin
/*buf_size = */
dbms_output.enable(1000000
end;
end-exec;
exec sql
whenever sqlerror goto exec_error;
exec sql execute immediate :ora_sql_command;
//exec sql
//whenever sqlerror continue;
/* Printing the output */
exec sql
execute
begin
dbms_output.get_line (:outtxt,:status);
end;
end-exec;
while ( !status)
{
outtxt.arr[outtxt.len]='\0
printf ("%s\n",outtxt.arr);
exec sql
execute
begin
dbms_output.get_line (:outtxt,:status);
end;
end-exec;
}
/* Freeing the memory of the buffer */
exec sql
execute
begin
dbms_output.disable;
end;
end-exec;
exit(0);
/* Error in the connection */
connect_error:
fprintf(stderr,"%s: Cannot connect to the database\n",argv[0]);
exit(2);
exec_error:
buf_len = sizeof (msg);
sqlglm(msg, &buf_len, &msg_len);
printf ("%.*s\n", msg_len, msg);
exit(1);
}
hagaiy,
The question is: what's in the buffer that it's reading (get_line)? It seems empty.
The question is: what's in the buffer that it's reading (get_line)? It seems empty.
You buffer length is 2 byte. Increase it and will work.
Bhatti
Bhatti
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tx alot, that solved my problem.
This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_
LINES. Calls to these procedures are ignored if the DBMS_OUTPUT package is not
enabled.
If there are multiple calls to ENABLE, then buffer_size is the largest of the values
specified. The maximum size is 1,000,000, and the minimum is 2,000.
Syntax
DBMS_OUTPUT.ENABLE (
buffer_size IN INTEGER DEFAULT 20000);
Note: It is not necessary to call this procedure when you use the
SERVEROUTPUT option of Enterprise Manager or SQL*Plus.