July 9, 2015 at 4:24 am
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. Please see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.
Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
Please advise.
ALTER PROCEDURE [dbo].[PKG_OBJ_API$CREATE_OBJ]
(
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@Arrlist varchar(max),
@P_ERR VARCHAR(4000) OUTPUT
@P_NEW_OBJ Cursor Varying OUT
)
AS
BEGIN TRY
--declare @P_ERR VARCHAR(4000);
declare @P_OBJ_ATTR PCS_SPEC_ATTR_VALUE
declare @OutputTable TABLE ( ATTR_NAME VARCHAR(256), ATTR_VAL VARCHAR (4000) )
insert into @P_OBJ_ATTR
(ATTR_NAME, ATTR_VAL)
select * from dbo.PipeStringToTable (@Arrlist)
select * from @P_OBJ_ATTR
------------------------------------------------------------------
DECLARE c_col cursor LOCAL
FOR
SELECT ATTR_NAME,
ATTR_DB_NAME,
ATTR_DEF,
CASE ATTR_TYPE
WHEN 'real' THEN 'number'
WHEN 'integer' THEN 'number'
WHEN 'string' THEN 'varchar2'
WHEN 'boolean' THEN 'varchar2'
WHEN 'timestamp' THEN 'DATE'
WHEN 'Date' THEN 'DATE'
END ATTR_TYPE
FROM PCS_TYPE_ATTRS
WHERE TYPE_NAME=@P_TYPE;
-------------------------------------------------------------------------
DECLARE c_table_name cursor LOCAL
FOR
select TYPE_DB_NAME from PCS_TYPES
where TYPE_NAME=@P_TYPE;
DECLARE @pj_id BIGINT;
DECLARE c_prjid cursor LOCAL FOR
select 1 from me_projects
where id=@pj_id;
------------------------------------------------------------------------------
Create table #L_ATTR_VAL(
RowID int IDENTITY(1, 1),
ATTR_DB_NAME VARCHAR(1000),
ATTR_VAL VARCHAR(1000),
ATTR_TYPE VARCHAR(10),
ATTR_DEF VARCHAR(256)
)
declare
@ATTR_DB_NAME VARCHAR(1000),
@ATTR_VAL VARCHAR(1000),
@ATTR_NAME VARCHAR(1000),
@ATTR_TYPE VARCHAR(10),
@ATTR_DEF VARCHAR(256),
@ATTR_NAME_IP VARCHAR(1000),
@ATTR_VALUE_IP VARCHAR(1000)
DECLARE @L_NAME_EXIST INT = 0;
DECLARE @L_OBJ_NAME varchar(256);
DECLARE @L_ATTR_NAME varchar(256);
DECLARE @L_TABLE_NAME varchar(50);
DECLARE @L_INS_STMT VARCHAR (MAX);
DECLARE @L_INS_STMT1 VARCHAR(MAX);
DECLARE @L_STMT VARCHAR(MAX);
DECLARE @L_STMT1 VARCHAR(MAX);
DECLARE @L_ATTR VARCHAR(20);
DECLARE @L_ATTR1 VARCHAR(20);
DECLARE @l_projinf INT =0;
DECLARE @l_projid INT =0;
DECLARE @l_pjid INT =0;
-------------------------------------------------------------------------------------------------
if @P_SCOPE='SCOPE_GLOBAL'
BEGIN
SET @P_ERR ='Global Objects Cant be created..'
Goto exception;
END
open c_table_name;
while @@FETCH_STATUS = 0
BEGIN
fetch c_table_name into @L_TABLE_NAME;
END
close c_table_name;
DEALLOCATE c_table_name;
---------------------------------------------------------------------------------------------------
open c_col
fetch next from c_col into @ATTR_NAME, @ATTR_DB_NAME, @ATTR_DEF , @ATTR_TYPE
while @@FETCH_STATUS = 0
begin
if (@ATTR_DB_NAME != 'ID')
BEGIN
insert into #L_ATTR_VAL (ATTR_DB_NAME, ATTR_TYPE, ATTR_DEF ) values(@ATTR_DB_NAME, @ATTR_TYPE, @ATTR_DEF )
END
Fetch next from c_col into @ATTR_NAME, @ATTR_DB_NAME, @ATTR_DEF , @ATTR_TYPE
END
CLOSE c_col;
DEALLOCATE c_col;
SELECT * FROM #L_ATTR_VAL;
DECLARE NameAndValue CURSOR LOCAL FAST_FORWARD FOR
SELECT ATTR_NAME, ATTR_VAL FROM @P_OBJ_ATTR;
OPEN NameAndValue;
FETCH NEXT FROM NameAndValue INTO @ATTR_NAME_IP, @ATTR_VALUE_IP;
While( @@FETCH_STATUS = 0)
BEGIN
DECLARE @NumberRecords int, @RowCount int
select ATTR_DB_NAME,ATTR_VAL, ATTR_TYPE, ATTR_DEF from #L_ATTR_VAL
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
IF @ATTR_NAME_IP = (select ATTR_DB_NAME from #L_ATTR_VAL WHERE RowID = @RowCount)
BEGIN
update #L_ATTR_VAL
set ATTR_VAL = @ATTR_VALUE_IP
WHERE RowID = @RowCount
/* SET @L_ATTR_NAME = I.ATTR_NAME;
DBMS_OUTPUT.PUT_LINE(l_attr_name||'=>'||P_OBJ_ATTR(N+1)); */
END
IF upper(@ATTR_NAME_IP) = 'NAME' AND @L_NAME_EXIST =0
BEGIN
SET @L_ATTR = @ATTR_NAME_IP;
SET @L_NAME_EXIST = 1;
SET @L_OBJ_NAME = @ATTR_VALUE_IP;
END
IF (@ATTR_NAME_IP) = 'PROJECT_ID' and @l_projinf=0
BEGIN
SET @L_ATTR1 = @ATTR_NAME_IP;
SET @l_projinf =1;
SET @l_projid = @ATTR_VALUE_IP;
-- DBMS_OUTPUT.PUT_LINE(P_OBJ_ATTR(n)||'=>'||P_OBJ_ATTR(N+1));
END
IF (@ATTR_NAME_IP) = 'CUSTOMER_PROJ_ID' and @l_projinf=0
BEGIN
SET @L_ATTR1 = @ATTR_NAME_IP;
SET @l_projinf =1;
SET @l_projid = @ATTR_VALUE_IP;
-- DBMS_OUTPUT.PUT_LINE(P_OBJ_ATTR(n)||'=>'||P_OBJ_ATTR(N+1));
END;
IF upper(@ATTR_NAME_IP) = 'USER_ID' AND @L_NAME_EXIST =0
BEGIN
SET @L_ATTR = @ATTR_NAME_IP;
SET @L_NAME_EXIST = 1;
SET @L_OBJ_NAME = @ATTR_VALUE_IP;
END
set @RowCount=@RowCount+1;
END
FETCH NEXT FROM NameAndValue INTO @ATTR_NAME_IP, @ATTR_VALUE_IP;
END
CLOSE NameAndValue;
DEALLOCATE NameAndValue;
IF @P_TYPE<>'User' AND @L_NAME_EXIST=0
begin
set @P_ERR = 'Name is a mandatory attribute';
Goto exception;
end
IF @L_NAME_EXIST=0
BEGIN
set @P_ERR = 'User ID is a mandatory attribute';
Goto exception;
END;
IF @L_OBJ_NAME IS NULL
BEGIN
SET @P_ERR =@L_ATTR+' cannt be Null';
Goto exception;
END
IF @P_SCOPE='SCOPE_PROJECT' AND @P_TYPE not in ('Project','Spares','Customer Project')
BEGIN
IF @l_projinf=0
BEGIN
SET @P_ERR = 'Project Id is a mandatory attribute';
Goto exception;
END
IF @l_projid IS NULL
BEGIN
SET @P_ERR ='Project ID cannt be Null';
Goto exception;
END
END
select * from #L_ATTR_VAL;
-----------------------------------------------------------------------------------------------------------------
set @L_INS_STMT='';
set @L_INS_STMT1='';
select ATTR_DB_NAME,ATTR_VAL, ATTR_TYPE, ATTR_DEF from #L_ATTR_VAL
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
WHILE (@RowCount <= @NumberRecords)
BEGIN
--DBMS_OUTPUT.PUT_LINE(L_ATTR_VAL(I).ATTR_DB_NAME||'-->'||L_ATTR_VAL(I).ATTR_VAL||'--->'||L_ATTR_VAL(I).ATTR_DEF);
SET @L_INS_STMT = @L_INS_STMT +','+ (select ATTR_DB_NAME from #L_ATTR_VAL WHERE RowID = @RowCount);
SET @L_INS_STMT1 = @L_INS_STMT1 +','+
CASE
WHEN ((select ATTR_TYPE from #L_ATTR_VAL WHERE RowID = @RowCount)='VARCHAR2') and ((select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is not null) THEN ''''
WHEN ((select ATTR_TYPE from #L_ATTR_VAL WHERE RowID = @RowCount) ='DATE') and ((select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is not null) THEN ''''
else ''
END
+
CASE
WHEN (select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is null and (select ATTR_DEF from #L_ATTR_VAL WHERE RowID = @RowCount) is null THEN 'NULL'
WHEN (select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is NOT null and (select ATTR_DEF from #L_ATTR_VAL WHERE RowID = @RowCount) is null THEN (select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount)
WHEN (select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is null and (select ATTR_DEF from #L_ATTR_VAL WHERE RowID = @RowCount) is NOT null THEN ''''+ (select ATTR_DEF from #L_ATTR_VAL WHERE RowID = @RowCount) + ''''
else 'NULL'
END
+
CASE
WHEN ((select ATTR_TYPE from #L_ATTR_VAL WHERE RowID = @RowCount)='VARCHAR2') and ((select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is not null) THEN ''''
WHEN ((select ATTR_TYPE from #L_ATTR_VAL WHERE RowID = @RowCount) ='DATE') and ((select ATTR_VAL from #L_ATTR_VAL WHERE RowID = @RowCount) is not null) THEN ''''
else ''
END
set @RowCount=@RowCount+1;
END;
PRINT @L_INS_STMT
PRINT @L_INS_STMT1
SET @L_STMT='';
select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
PRINT @L_STMT
EXEC (@L_STMT)
SET @L_STMT1='';
IF @l_projinf = 1
BEGIN
SET @L_STMT1 ='SELECT * FROM '+ @L_TABLE_NAME +' WHERE '+ @L_ATTR +'='''+ @L_OBJ_NAME +''' AND '+ @L_ATTR1+ '='''+ @l_projid+'''';
END
ELSE
BEGIN
SET @L_STMT1 ='SELECT * FROM '+ @L_TABLE_NAME +' WHERE '+ @L_ATTR +'='''+ @L_OBJ_NAME +'''';
END
PRINT @L_STMT1
END
exception:
RAISERROR (@P_ERR,-1,-1)
July 9, 2015 at 8:59 am
Not really having the time to delve all that deep into your procedure, I can only guess what the objective is. However, one thing that folks often trip over, is expecting a temp table to still exist after a stored procedure executes. This is not going to happen unless it's a GLOBAL temp table (##). Also, it appears your first cursor might not need to be a cursor at all. That variable will end up with the value from the last record in that query. If there is only just 1 record that will ever come back from the query, then you are better off with SELECT @variable_name = field_name FROM whatevertable WHERE whatevercondition...
I don't know if that helps, but if you can elaborate on what the objective here is, it will be much easier for us to help, as just reading through your proc is going to be a bit of a slog for anyone... And we don't even know what's supposed to happen...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2015 at 1:18 pm
This stored procedure is full of problems. The RBAR (Row By Agonizing Row) is extreme and I'm having some problems figuring out what you're trying to do.
You need to find which variable is staying as NULL which might generate the problem. With more help such as sample data and DDL for involved tables and functions, we could improve this procedure to be easier to understand and maintain.
The first 3 cursors that you declare are absolutely not needed as well as some other code.
July 10, 2015 at 8:46 am
narengc (7/9/2015)
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. Please see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
I think you're on the right track. You've narrowed down to what statement does it right?
If this were my procedure, I'd take each component part from above and investigate the value of it right at the point of use. If any one of these values or expressions are null they will cause your concatenation results to be null, so while it superficially seems like lots of work, its really a pretty direct route of debugging. So insert some debugging right before executing the above select statement is what I'd do!
Like so:
select 'ltable name', label1, @l_table_name val1
union
select '@L_INS_STMT', @L_INS_STMT
union
( etc etc etc )
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply