I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
ORDER BY in a SP
I'm getting a little frustrated... I'm trying to write a stored proc... the
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:
> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:
> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>
Monday, March 19, 2012
Oracle, SQL+ and calling a stored proc in a package
I have this code:
declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/
I get SP2-0552: Bind variable "RETVAL" not declared.
I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.
HELP!!?!?!
n/m
this worked:
declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:
declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/
I get SP2-0552: Bind variable "RETVAL" not declared.
I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.
HELP!!?!?!
n/m
this worked:
declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:
1) All PL/SQL variables:
declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
2) SQL Plus variables:
SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
3) Combination of SQL Plus and PL/SQL variables:
SQL> variable retval number;
declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.
But, using all 'variable' statements, I get this:
Wrote file afiedt.buf
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.
All I'm really trying to do is be able to print out the values of return_status and return_msg.
I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.
But, using all 'variable' statements, I get this:
Wrote file afiedt.buf
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.
All I'm really trying to do is be able to print out the values of return_status and return_msg.
I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:
SQL> @.afiedt.buf
That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...
well...
it gets better:
SQL> @.afiedt.buf
PL/SQL procedure successfully completed.
variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
DOH!|||Originally posted by nightowlky
ah...
well...
it gets better:
SQL> @.afiedt.buf
PL/SQL procedure successfully completed.
variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/
I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/
I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!
The sweet smell of success!
Thank you, Andrew!
Thank you!
:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :
-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/
Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql
Hope this helps ...|||I'll try that, too.
As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.
As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:
SQL> variable a number
SQL> exec :a := 123
PL/SQL procedure successfully completed.
SQL> print a
A
----
123
declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/
I get SP2-0552: Bind variable "RETVAL" not declared.
I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.
HELP!!?!?!
n/m
this worked:
declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:
declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/
I get SP2-0552: Bind variable "RETVAL" not declared.
I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.
HELP!!?!?!
n/m
this worked:
declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:
1) All PL/SQL variables:
declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
2) SQL Plus variables:
SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
3) Combination of SQL Plus and PL/SQL variables:
SQL> variable retval number;
declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.
But, using all 'variable' statements, I get this:
Wrote file afiedt.buf
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.
All I'm really trying to do is be able to print out the values of return_status and return_msg.
I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.
But, using all 'variable' statements, I get this:
Wrote file afiedt.buf
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.
All I'm really trying to do is be able to print out the values of return_status and return_msg.
I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:
SQL> @.afiedt.buf
That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...
well...
it gets better:
SQL> @.afiedt.buf
PL/SQL procedure successfully completed.
variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
DOH!|||Originally posted by nightowlky
ah...
well...
it gets better:
SQL> @.afiedt.buf
PL/SQL procedure successfully completed.
variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/
I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/
I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!
The sweet smell of success!
Thank you, Andrew!
Thank you!
:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :
-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/
Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql
Hope this helps ...|||I'll try that, too.
As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.
As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:
SQL> variable a number
SQL> exec :a := 123
PL/SQL procedure successfully completed.
SQL> print a
A
----
123
Monday, March 12, 2012
Oracle stored proc with OUT parameter
Hi,
I am calling an Oracle stored proc which contains an IN and an OUT parameter also.
To the stored proc, I pass two reports parameteres. I get following error when I execute the report:
PLS-00306: wrong number or types of arguments in call to <Procedure name>
Where am I going wrong?
TIA,
Tanmaya
Out parameters for Oracle stored procedures are not supported. Only an OUT REF cursor is supported. Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.
-- Robert
Subscribe to:
Posts (Atom)