Oracle Stored Procedures

[adshocker]

Hi all,

Just wandering how to be able to use Create new stored procedures option and Use existing stored procedures in the TableAdapter Configuration Wizard. It seems to be disabled all the time.

Here a sample screen shot.

 

[JohnH]

Try what mathgeek1729 describes down this thread. (add query and set properties)

 

[cjard]

I normally do:

New Query
Choose: DELETE Query
Type: DELETE FROM DUAL
Finish

Edit the propreties of the new query:
CommandTYpe = StoredProcedure
THEN Pick the Name from the list above
Parameter info is automatically loaded
Set Execute Mode to scalar if it is a function, nonquery if it is a procedure


NOTE THAT ONLY FUNCTIONS AND PROCEDURES THAT ARE NOT IN A PACKAGE ARE ACCESSIBLE HERE. If your proc is in a package, you HAVE to enter all the param info manually, and accept the IDE complaining that "Cant verify your command text. The command Text will still be set"

Note also that functions seem to gain 2 parameters similarly named.. one is a ReturnValue type direction, the other is an Output direction. They are both called like RETURN_VALUE? - i notmally delete the Output one, leaving behind the ReturnValue one

Nuisance, for sure...

 

[adshocker]

i see...

so there's no way to choose those other 2 options when starting when starting the TableAdapter Configuration Wizard. just curious.

also, when i created the new query and edited the properties, changed commandtype to storedprocedure, it didn't list the storedprocedure name in the commandtext. is it supposed to do that or no?

 

[cjard]

not tht I found, and I've been dicking with oracle for a long time now.. Their lastest ODP/ODAC/ODT apparently has better drag and drop integration with the surface, but I didnt see it when I installed it

 

[adshocker]

thanks...

i have one more concern with the tableadapter.

i created this procedure in oracle.

create or replace procedure create_user
(
	p_user_name in varchar2,
	p_password in varchar2,
	p_user_desc in varchar2,
	p_user_type in varchar2,
	p_user_stat in varchar2,
	p_role_name in varchar2,
	p_profile_name in varchar2,
	p_schema_name in varchar2,
	--p_created in date,
	--p_last_modified in date,
	p_last_login in date,
	p_last_pwd_changed in date
	--p_modified_by in varchar2
)
is
	v_user_id number;
	v_curr_date	date;
	v_curr_user	varchar2(30);
begin
	select sysdate, user
		into v_curr_date, v_curr_user
		from dual;

	select user_id.nextval
		into v_user_id
		from dual;

	insert into user_tbl
	(
	 user_id,
	 password,
	 user_name,
	 user_desc,
	 user_type,
	 user_stat,
	 role_name,
	 profile_name,
	 schema_name,
	 created,
	 last_modified,
	 last_login,
	 last_pwd_changed,
	 modified_by
	)
	values
	(
	 v_user_id,
	 p_password,
	 p_user_name,
	 p_user_desc,
	 p_user_type,
	 p_user_stat,
	 p_role_name,
	 p_profile_name,
	 p_schema_name,
	 v_curr_date,
	 v_curr_date,
	 p_last_login,
	 p_last_pwd_changed,
	 v_curr_user
	);
end;
/

i want to set this storedprocedure as my insert command for my tableadapter. is that possible?

 

[cjard]

Yep, but you'll have to map the columns to the parameters yourself


Same idea, make your TA
Edit the Insert Command to be type=StoredProcedure
Pick CREATE_USER from the list
Edit the parameters
For each parameter, set the SOURCE COLUMN property to the relevant column to pluck from the data table and apply to this parameter

ALl parameters must be mapped otherwise a "not all variables bound" or "number or types of arguments to CREATE USER not correct" error appears

Note also all parameters that have any kind of OUTPUT must have a size specified, otherwise an error like "no size set for output parameter" appears

 

[adshocker]

thanks...

having input parameters was ok... no errors/problems.

but its hard using the out parameters though. i'll be meddling with that some other time.

 

[cjard]

OK, well just remember the gotchas I've discovered over the time:

Functions need one of the duplicated return value provisions deleting. I usually delet ethe output one
Out parameters need a size specified
To work with a datatable (i.e. as a Sel Ins Upd or Del command) the columns that the parameters map to must be entered by you

 

[adshocker]

ok. thanks.