Home » SQL & PL/SQL » SQL & PL/SQL » Parameter type in a package
Parameter type in a package [message #188932] Tue, 22 August 2006 08:35 Go to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi All,

I have a doubt in parameterized procedures,

I have a table called tst_tbl with a column col_a of VARCHAR2(15).
Then I wrote a procedure as given below,
CREATE OR REPLACE PROCEDURE tst_prc(var_a IN tst_tbl.col_a%TYPE)
BEGIN
.
.
.
END

My problem is the procedure is executed even when I pass a string with more than 15 characters ( without errors). Kindly reply me whether it is valid or not???

Kindly help me in this issue.

Thanks
Vinod V
Re: Parameter type in a package [message #188940 is a reply to message #188932] Tue, 22 August 2006 08:55 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Well, it depends on what you want to do with this input parameter:

CREATE TABLE tst_tbl(col_a VARCHAR2(15));


CREATE OR REPLACE PROCEDURE PROVA(VAR_A TST_TBL.COL_A%TYPE) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('THIS IS THE PARAMETER ' || VAR_A);
  DBMS_OUTPUT.PUT_LINE('ABOUT TO INSERT...');
  INSERT INTO TST_TBL VALUES(VAR_A);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('FAILED: ' || SQLERRM);
END PROVA;


In this case, if you pass a value of the "correct" length, this code executes correctly. Otherwise, if you choose a value of the "wrong" length, this code fails.
On the other hand, if you consider:

CREATE OR REPLACE PROCEDURE PROVA(VAR_A TST_TBL.COL_A%TYPE) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('THIS IS THE PARAMETER ' || VAR_A);
  DBMS_OUTPUT.PUT_LINE('ABOUT TO INSERT...');

  /**** INSERT INTO TST_TBL VALUES(VAR_A); ***/

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('FAILED: ' || SQLERRM);
END PROVA;


you'll never get an error.

G.

[Updated on: Tue, 22 August 2006 08:57]

Report message to a moderator

Re: Parameter type in a package [message #189041 is a reply to message #188940] Tue, 22 August 2006 23:56 Go to previous messageGo to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi,

Thanks for your reply.
Yes I do face s similar kind of problem, but my question is how to avoid it??
Or let me put in this way - How to enable the data type length check in the parameter passed???

Thanks,
Vinod
Re: Parameter type in a package [message #189050 is a reply to message #189041] Wed, 23 August 2006 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can't. You cannot provide a max length for parameters.
Re: Parameter type in a package [message #189088 is a reply to message #189041] Wed, 23 August 2006 03:05 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You could test the length of the parameter, then raise an application error if it is longer than you want.
Jim
Re: Parameter type in a package [message #189139 is a reply to message #188932] Wed, 23 August 2006 06:23 Go to previous message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi All,

Thanks a lot for your efforts.

Regards,
Vinod V
Previous Topic: ORA - 03121 : no interface driver connected - function not performed
Next Topic: ping ip in oracle
Goto Forum:
  


Current Time: Fri Dec 02 14:22:52 CST 2016

Total time taken to generate the page: 0.20676 seconds