We help IT Professionals succeed at work.

Run succefully in pl/sql tool but not successful in sqlplus ????

dinhchung82
dinhchung82 asked
on
1,072 Views
Last Modified: 2013-12-18
I have a procudure :

When i run it in pl/sql tool , it is successfully , it is output :

SQL> exec ows.balance(5243943010398953);
555425166

PL/SQL procedure successfully completed

But when i try to run in sqlplus , i am waiting for a long time and don't have reply :

SQL> exec ows.balance(5243943010398953);

and nothing returns

I also create a synonym for this procedure :
create synonym SD
  for OWS.BALANCE;

I call this procedure from my applicaiton ( written in .net )
Using sd ora ows.balance , i have this error :

ex = {"ORA-06550: line 1, column 22:\nPLS-00222: no function with name 'BALANCE' exists in this scope\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"}

{"ORA-06550: line 1, column 22:\nPLS-00222: no function with name 'SD' exists in this scope\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"}

Advise me please !
Thanks!


create or replace procedure balance(v_cardnumber in string ) is
startcode float;
startmess varchar2(30);
nwauthcode varchar2(30);
retrefnum varchar2(30);
csintkey varchar2(30);
qtype varchar2(30);
messcode varchar2(30);
vcardnumber varchar2(30);
cardexpire varchar2(30);
cardspc varchar2(30);
cardseqvn varchar2(30);
adddata varchar2(30);
controldata varchar2(30);
vacqbin varchar2(30);
vdevnumber varchar2(30);
vsiccode varchar2(30);
trcondition varchar2(30);
trattrlist varchar2(30);
vtrcountry varchar2(30);
trcity varchar2(30);
trdetails varchar2(30);
trdate varchar2(30);
vtranscurr varchar2(30);
tamount float;
vsettlcurr varchar2(30);
samount float;
chipdata varchar2(30);
action varchar2(30);
bertlvin varchar2(30);
authcode varchar2(30);
returncode float;
trncurr varchar2(30);
stlcurr varchar2(30);
trnamntstr varchar2(30);
stlamntstr varchar2(30);
stmtstr varchar2(30);
scriptstr varchar2(30);
scriptcode varchar2(30);
arpcrc varchar2(30);
extoutdata varchar2(30);
bertlvout varchar2(30);
docid float;
isfinished varchar2(30);
settldate varchar2(30);
nextchannel varchar2(30);
nextdomain varchar2(30);
nextnumber varchar2(30);
nextintkey varchar2(30);
nextcurr varchar2(30);
nextamount float;
 
begin
 
 startcode  := 0;
 startmess  := null;
  nwauthcode := null;
  retrefnum := null;
  csintkey := null;
  qtype := 'B';
  messcode := '01000B';
  vcardnumber := v_cardnumber;
  cardexpire := '1608';
  cardspc := null;
  cardseqvn := null;
  adddata := null;
  controldata := null;
  vacqbin := null;
  vdevnumber := null;
  vsiccode := null;
  trcondition := null;
  trattrlist := null;
  vtrcountry := null;
  trcity := null;
  trdetails := null;
  trdate := null;
  vtranscurr := null;
  tamount := null;
  vsettlcurr := null;
  samount := null;
  chipdata := null;
  action := null;
  bertlvin := null;
  authcode := null;
  returncode := null;
  trncurr := null;
  stlcurr := null;
  trnamntstr := null;
  stlamntstr := null;
  stmtstr := null;
  scriptstr := null;
  scriptcode := null;
  arpcrc := null;
  extoutdata := null;
  bertlvout := null;
  docid := null;
  isfinished := null;
  settldate := null;
  nextchannel := null;
  nextdomain := null;
  nextnumber := null;
  nextintkey := null;
  nextcurr := null;
  nextamount := null;
 
  auth.put_request_cbg
                      (startcode,
                       startmess,
                       nwauthcode ,
                       retrefnum,
                       csintkey ,
                       qtype ,
                       messcode ,
                       vcardnumber ,
                       cardexpire ,
                       cardspc ,
                       cardseqvn ,
                       adddata ,
                       controldata ,
                       vacqbin ,
                       vdevnumber ,
                       vsiccode ,
                       trcondition ,
                       trattrlist ,
                       vtrcountry ,
                       trcity,
                       trdetails ,
                       trdate ,
                       vtranscurr,
                       tamount,
                       vsettlcurr ,
                       samount,
                       chipdata ,
                       action,
                       bertlvin ,
                       authcode ,
                       returncode ,
                       trncurr ,
                       stlcurr ,
                       trnamntstr ,
                       stlamntstr ,
                       stmtstr ,
                       scriptstr ,
                       scriptcode ,
                       arpcrc ,
                       extoutdata ,
                       bertlvout ,
                       docid ,
                       isfinished ,
                       settldate,
                       nextchannel ,
                       nextdomain ,
                       nextnumber ,
                       nextintkey ,
                       nextcurr ,
                       nextamount);
DBMS_output.put_line(
 
substr(trnamntstr, 3, instr(trnamntstr, ';')-3) );
 
 
 end balance;

Open in new window

Comment
Watch Question

Author

Commented:
I find the problem ,
i add "commit;"  to my procedure so it can implement .
But i still have error when connect using my application:
My code is attached:

ex = {"ORA-06550: line 1, column 22:\nPLS-00222: no function with name 'BALANCE' exists in this scope\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"}

{"ORA-06550: line 1, column 22:\nPLS-00222: no function with name 'SD' exists in this scope\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"}

private string GetAcBalance(string acID)
        {
            try
            {
                OracleCommand objCmd = new OracleCommand("ows.balance", myConnection);
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("v_cardnumber", OracleType.NVarChar, 30).Value = acID;
                objCmd.Parameters.Add("trnamntstr", OracleType.Number).Direction = ParameterDirection.ReturnValue;
 
                objCmd.ExecuteNonQuery();
                string acBal = clsCommon.convertString(objCmd.Parameters["return_value"].Value);
                objCmd.Dispose();
 
                _error.errCode = bifError.bifOK;
                _error.errMsg = "";
                return acBal;
            }
            catch (Exception ex)
            {
                _error.errCode = bifError.bifEXCEPTION;
                _error.errMsg = ex.Message;
                return "0.0";
            }
        }

Open in new window

CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I already fixed the first error .
Set sereroutput on is only show the result . I find problem is Locked so that i add more "Commit" to procedure.
Deputy General Manager - IT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I solve my problem ,  thanks so much !
Error is in my procedure

Author

Commented:
Thanks so much !

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.