Home » SQL & PL/SQL » SQL & PL/SQL » Need help with passing parameter to procedure
Need help with passing parameter to procedure [message #310770] Wed, 02 April 2008 06:28 Go to next message
hadinatayp
Messages: 5
Registered: March 2008
Junior Member
I have functions like below

create or replace function scb_encrypt ( paramIn in varchar2)
return raw is

	input_raw     raw(2000);
	key_raw       raw(2000);
	encrypted_raw raw(2000);
	encrypted_string varchar2(1000);

begin

	input_raw:=UTL_RAW.CAST_TO_RAW(CONVERT(paramIn,'AL32UTF8','US7ASCII'));

	key_raw:=UTL_RAW.CAST_TO_RAW(CONVERT('DesignStreamDs','AL32UTF8','US7ASCII'));

	encrypted_raw := dbms_crypto.Encrypt
			 (
				src => input_raw,
				typ => DBMS_CRYPTO.DES_CBC_PKCS5,
				key => key_raw
			  );

	return(UTL_RAW.CAST_TO_RAW(encrypted_raw));

end scb_encrypt;
/	
	
create or replace function scb_decrypt ( paramIn in raw)
return varchar2 is
	
	key_raw		  raw(2000);
	decrypted_raw	  raw(2000);
	decrypted_string  raw(2000);

begin

	key_raw:=UTL_RAW.CAST_TO_RAW(CONVERT('DesignStreamDs','AL32UTF8','US7ASCII'));

	decrypted_raw := dbms_crypto.Decrypt
                   	 (
				src => paramIn, 
				typ => DBMS_CRYPTO.DES_CBC_PKCS5, 
				key => key_raw
			 );

	decrypted_string := CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');

	return(decrypted_string);

end scb_decrypt;
/
	


then i create a table and insert a value in it
create table test
(
	col1 raw(1000)
);

insert into test (col1) values (scb_encrypt('text to encrypt'));

now i want to retrieve the value that i've just inserted to decrypt it, i tried this
exec dbms_output.put_line(scb_decrypt(select col1 from test where rownum=1));

and i got error, it states that "cursor as seperator is allowed only with specific built-in functions"
does anybody know how to resolve it?

thx.
Re: Need help with passing parameter to procedure [message #310771 is a reply to message #310770] Wed, 02 April 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
for rec in (select col1 from test) loop
  dbms_output.put_line(scb_decrypt(rec.col1));
end loop;

Regards
Michel
Re: Need help with passing parameter to procedure [message #310994 is a reply to message #310771] Thu, 03 April 2008 03:29 Go to previous messageGo to next message
hadinatayp
Messages: 5
Registered: March 2008
Junior Member
begin
	for rec in (select col1 from test where rownum=1) loop
		dbms_output.put_line(scb_decrypt(rec.col1));
	end loop;
end;
/

when i try the code above i got error, stating that :
ora-28817 : pl/sql function returned an error.
ora-06512 : at " sys.dbms_crypto_ffi", line 67
ora-06512 : at " sys.dbms_crypto", line 41
ora-06512 : at " hadinata.scb_decrypt", line 12
ora-06512 : at line 3

does anyone know how to solve it?
thx.
Re: Need help with passing parameter to procedure [message #310999 is a reply to message #310994] Thu, 03 April 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first post your code with line numbers.

Regards
Michel
Re: Need help with passing parameter to procedure [message #311002 is a reply to message #310770] Thu, 03 April 2008 03:51 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
now i want to retrieve the value that i've just inserted to decrypt it, i tried this

exec dbms_output.put_line(scb_decrypt(select col1 from test where rownum=1));


and i got error, it states that "cursor as seperator is allowed only with specific built-in functions"
does anybody know how to resolve it?

You have declared the parameter as RAW and passing type of
SYSREFCURSOR.



Study SYSREFCURSOR
UTL_RAW.CAST_TO_VARCHAR2

[Updated on: Thu, 03 April 2008 04:15] by Moderator

Report message to a moderator

Re: Need help with passing parameter to procedure [message #311010 is a reply to message #311002] Thu, 03 April 2008 04:17 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is no more there, you are one day late. Wink

Regards
Michel
Previous Topic: Temporary Tables
Next Topic: Creating Job Thruough DBMS_SCHEDULER
Goto Forum:
  


Current Time: Sun Dec 11 00:26:51 CST 2016

Total time taken to generate the page: 0.09500 seconds