BDE application using Oracle 9i stored proc causes error

Hi Folks,

I'm trying to run a stored procedure that basically truncates a table.  The stored procedure was taken from https://metalink.oracle.com/metalink/plsql/f?p=130:14:811190565386901617::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,205297.1,1,0,1,helvetica

It runs perfectly well out of PL/SQL, but when I try and run it out of a Delphi application using the BDE, I keep getting this error:

---------------------------
Error
---------------------------
General SQL error.
[Oracle][ODBC]Syntax error or access violation.
---------------------------
OK  
---------------------------

It been granted execute for public.

I don't know very much about Oracle, so bear with me on this.

I have a feeling it's a BDE problem.  Just to get this out as I always get told to get rid of the BDE  __I CANNOT CHANGE THE FACT THAT THE DELPHI APPLICATION USES THE BDE___

Any help on this would be appreciated.

Many thanks,

PAG
PAG_PromaxAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
sorry, not much time yet . . .

could you post your configuration (owner-user of the procedure/call-user of the procedure)?

meikl ;-)
0
 
PAG_PromaxAuthor Commented:
Oh yeah, I'm trying to run this by typing:

 exec sys.truncate_tbl('tablename');

In the TQuery's SQL.Text property.
0
 
kretzschmarCommented:
use a TStoredProc-compoent and use the execProc-method after setting thge procedure-name and the parameter

no bde problem!

meikl ;-)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PAG_PromaxAuthor Commented:
Hi meikll,

I tried using one of them as well, but the same thing happens.  Could this be an issue with the BDE and Oracle 9i do you think?

PAG.
0
 
kretzschmarCommented:
hmm,

>Could this be an issue with the BDE and Oracle 9i do you think?
don't think so, maybe more in issue of the used driver

what driver do u use?
how looks your stored procedure? (i have no metalink-account)

meikl ;-)
0
 
PAG_PromaxAuthor Commented:
Hi meikl,

This is the stored procedure:

create or replace procedure truncate_tbl(table_name in varchar2)
  authid definer
as
  cursor_id integer;
begin
  cursor_id := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_id, 'TRUNCATE TABLE ' || table_name, dbms_sql.v7);
  dbms_sql.close_cursor(cursor_id);
exception
  when others then
    dbms_sql.close_cursor(cursor_id);
    raise;
end truncate_tbl;
/

grant execute on truncate_tbl to public;
commit;

And the Oracle ODBC driver I'm using is 10.01.00.02 to a Oracle 9i database.

Cheers,

PAG
0
 
PAG_PromaxAuthor Commented:
This is the error I get when I try calling the stored procedure through a TStoredProc:

---------------------------
Project1
---------------------------
Key violation.

[Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TBL' must be declared
ORA-06550: line 1, colum

General SQL error.

7:
PL/SQL: Statement ignored.
---------------------------
OK  
---------------------------
0
 
PAG_PromaxAuthor Commented:
And this is how I've defined the TStoredProc:

object StoredProc1: TStoredProc
  DatabaseName = 'Pb'
  StoredProcName = 'TRUNCATE_TBL'
  Left = 32
  Top = 20
  ParamData = <
    item
      DataType = ftString
      Name = 'TABLE_NAME'
      ParamType = ptInput
      Value = 'CLMDETBAK'
    end>
end
0
 
kretzschmarCommented:
looks like you try to run it in different (oracle) schemas

could this be?

meikl ;-)
0
 
PAG_PromaxAuthor Commented:
HI meikl,

Thanks for the reply.  The TStoredProc found the stored procedure when I dropped down the StoredProcedure list property.  I didn't type it in myself.  Wouldn't that indicate it was in the same schema?  (Sorry, I'm really knew with Oracle).

Also, the user I'm logging in as is a member of the same schema that this stored proc is in.  If I log into PL/SQL using that user, I can execute the stored proc fine.

Thanks for all your help.

PAG.
0
 
kretzschmarCommented:
???
usual this errormessage come directly from the oracle server
--------
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_TBL' must be declared
ORA-06550: line 1, colum
--------
so my thought is, that the connected user didn't see the procedure

you could test this by creating a public synonym and assigning the execute-privelege
(just for testing)
-------
CREATE PUBLIC SYNONYM TRUNCATE_TBL FOR [SchemaOfOwnerHere].TRUNCATE_TBL
/

GRANT EXECUTE ON [SchemaOfOwnerHere].TRUNCATE_TBL TO PUBLIC
/
-------

this both lines should be executed from the SystemDBA or  the User of ProcedureCreator (SchemaOwner)

let me know if the problem persists

meikl ;-)
0
 
PAG_PromaxAuthor Commented:
Above I ran this : grant execute on truncate_tbl to public;
So I should change this to make sure its actually for that schema that the user is in?

Thanks very much :)
0
 
PAG_PromaxAuthor Commented:
Hi again Meikl,

I've come back to this again now I've got some more time on my hands, and I still cannot get it to work.  I've run the create synonym and grant execute statements, but it's still not letting me do it.

Any ideas at all?

Cheers,

PAG
0
 
PAG_PromaxAuthor Commented:
Hi meikl,
Sorry, I dont know what you mean.  I know nothing about Oracle at all.  I'll see if the DBA can send it to me.

PAG.
0
 
PAG_PromaxAuthor Commented:
Hi meikl,

This project has been discontinued.  As you put effort into helping, I've given you the points with an A-Grading.

Cheers,

PAG.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.