Any SQL gurus burng the midnight

[TheExcession]

oil?

I have some stored procedures that I'd like to execute from sqlplus but can't seem to get anywhere.

They are package as follows (copied fr omthe sql in file)

CREATE OR REPLACE PACKAGE getGroupPac AS

v_DEFAULT_GROUP_NAME groups.name%TYPE := 'DEFAULT';
v_DEFAULT_GROUP_ID groups.id%TYPE;
v_RETURN_GROUP_NAME groups.name%TYPE;
v_RETURN_GROUP_ID groups.id%TYPE;

PROCEDURE setDefaultGroupId;
FUNCTION getGroupIdForPPPUser(v_id pppUser.id%TYPE) RETURN groups.id%TYPE;
FUNCTION getGroupIdForPPPUser(v_userName pppUser.userName%TYPE) RETURN groups.id%TYPE;
FUNCTION getGroupNameForPPPUser(v_id pppUser.id%TYPE) RETURN groups.name%TYPE;
FUNCTION getGroupNameForPPPUser(v_userName pppUser.userName%TYPE) RETURN groups.name%TYPE;
FUNCTION getGroupIdForMES_PID(v_id mes_pid.id%TYPE) RETURN groups.id%TYPE;
FUNCTION getGroupNameForMES_PID(v_id mes_pid.id%TYPE) RETURN groups.name%TYPE;
END getGroupPac;



I'd like to call the FUNCTION getGroupIdForPPPUser(v_id pppUser.id%TYPE) where the User ID is '251' and get the group ID printed on the screen.

So far my attempts are woefull and produce lots of errors - could anyone get me on my merry way?

thanks
Ex

[RobDickinson]

sqlplus I'm not big on, I use TOAD to do my oracle work.

But it should be something like:

select
getGroupPac.getGroupIdForPPPUse(251)
from dual

?

[TheExcession]

RobDickinson said:

sqlplus I'm not big on, I use TOAD to do my oracle work. But it should be something like: select getGroupPac.getGroupIdForPPPUse(251) from dual ?

what's the 'from dual ' bit mean?

thanks
Ex

[Don]

TheExcession said:

what's the 'from dual ' bit mean? thanks Ex

Its a Oracle kludge to get over the fact that the Oracle SQL parser requires you to SELECT FROM a table. Other SQL databases allow the syntax without a FROM clause if all the SELECTed fields are local variables rather than table fields...

The "dual" table is "defined" as having a single record in it so your resultset will return one row...of local variables that aren't in "dual" at all! Bizarre - but true...