[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

BDE application using Oracle 9i stored proc causes error

Posted on 2007-10-01
15
Medium Priority
?
566 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:PAG_Promax
  • 10
  • 5
15 Comments
 

Author Comment

by:PAG_Promax
ID: 19996471
Oh yeah, I'm trying to run this by typing:

 exec sys.truncate_tbl('tablename');

In the TQuery's SQL.Text property.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 19996603
use a TStoredProc-compoent and use the execProc-method after setting thge procedure-name and the parameter

no bde problem!

meikl ;-)
0
 

Author Comment

by:PAG_Promax
ID: 19997150
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 19997300
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
 

Author Comment

by:PAG_Promax
ID: 20002990
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
 

Author Comment

by:PAG_Promax
ID: 20003070
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
 

Author Comment

by:PAG_Promax
ID: 20003074
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 20004565
looks like you try to run it in different (oracle) schemas

could this be?

meikl ;-)
0
 

Author Comment

by:PAG_Promax
ID: 20010596
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 20012059
???
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
 

Author Comment

by:PAG_Promax
ID: 20012110
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
 

Author Comment

by:PAG_Promax
ID: 20083496
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
 
LVL 27

Accepted Solution

by:
kretzschmar earned 2000 total points
ID: 20083866
sorry, not much time yet . . .

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

meikl ;-)
0
 

Author Comment

by:PAG_Promax
ID: 20089717
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
 

Author Closing Comment

by:PAG_Promax
ID: 31407984
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question