PL/SQL newbie 1

[euntair]

How would I run the procedure below in SQL+?

function get_password(v_account in varchar2) return varchar2;

function get_password(v_account in varchar2) return varchar2
is
results varchar2(70);
cursor c is select password from cascade.cxml_user where username=v_account;
begin
open c;
fetch c into results;
close c;
if results is null then
return get_password('default');
else
return results;
end if;
end;

 

[SantaMufasa]

How would I run the procedure below in SQL+?

First, Euntair,what you listed is not a procedure...it is a function. If the function already exists as an available user-defined function for your Oracle user, then you can "run" the function from either:[ul][li]Within a SQL SELECT, INSERT, UPDATE, or DELETE statement, for example:

SELECT get_password(<some_acct_num>) from dual;

[/li][li]Within a PL/SQL block, for example:

DELCARE
    v_hold     varchar2(20);
    ...
BEGIN
    ...
    v_hold := get_password(<some_acct_num>);
    ...
END;
/

[/li][/ul]There is a probable construction error in your function's code, however: You are recursively calling "get_password(<some_acct_num>)" from within the function itself:

...
        return get_password('default');
...

Is this what you really want? Such recursion is probably won't even pass syntax muster since "get_password" does not exist at the time "get_password" is syntax checked.


Let us know your follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[euntair]

I guess my first question would be... why do I need to issue a full sql statement to call a FUNCTION? The sql is already in the function.

I'm familiar with the command exec in SQL Server and have found it in PL/SQL. Does exec call functions/procedures and if so, how do I use it to call my get_password function in SQL+.

 

[Dagon]

You would have to do this.

var password varchar2(50)
exec assword := get_password('default')
print password

Because you are using a function rather than a procedure, you need to have a variable into which you can return the result. In the above example, I've declared a bind variable assword into which the data can be returned.

 

[euntair]

Thanks Dagon! With some changes I was able to get the db to spit out the data.