|
Hi all,
I have a problem with executing function in Oracle 10g.
Below mentioned is the function :-
FUNCTION UnpackArray
(
Source IN VARCHAR2 DEFAULT NULL,
Delimiter IN CHAR DEFAULT ','
)
RETURN reSourceArray0 PIPELINED
IS
SourceArray00 SourceArray0:=SourceArray0(NULL);
TYPE REFCURSOR IS REF CURSOR;
CURSOR0 REFCURSOR;
DelLen int;
Pos int;
Cnt int;
str int;
LEN int;
Holder VARCHAR2(255);
BEGIN
IF Source is null or Delimiter is null THEN
Return;
END IF;
IF RTRIM(LTRIM(Source)) = ' ' THEN
Return;
END IF;
SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL;
IF Pos = 0 THEN
BEGIN
INSERT INTO UnpackArray_TBL
( Data )
VALUES ( Source );
return;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END IF;
SELECT 1 INTO str FROM DUAL;
<< LABEL4 >>
WHILE Pos > 0
LOOP
BEGIN
SELECT Pos - str INTO len FROM DUAL;
SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL;
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
SELECT Pos + DelLen INTO str FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter), str) INTO Pos FROM DUAL;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END LOOP;
SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL;
IF length(RTRIM(Holder)) > 0 THEN
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END IF;
Return; LOOP
FETCH CURSOR0 INTO
SourceArray00.Data;
EXIT WHEN CURSOR0%NOTFOUND;
PIPE ROW(SourceArray00);
END LOOP;
CLOSE CURSOR0;
RETURN;
END;
While executing this am getting the error like:
Running "EDOCS"."UNPACKARRAY" ( SOURCE = admin, DELIMITER = ).
Parameter 'RETURN_VALUE': No size set for variable length data type: String.
can anybody help me to solve this...?
Thanks in advance.
|
|
|
|
|
step 1 : create a Table types named "DIVISION_ARRAY"
CREATE TYPE "DIVISION_ARRAY" AS TABLE OF VARCHAR2(12)
step 2 : execute the below procedures
CREATE OR REPLACE FUNCTION "UNPACKDIVISIONARRAY" <br />
(Source IN VARCHAR2 DEFAULT NULL,<br />
Delimiter IN CHAR DEFAULT ',')<br />
RETURN division_array PIPELINED IS<br />
l_list varchar2(32767) := Source;<br />
l_idx pls_integer;<br />
l_value varchar2(32767);<br />
BEGIN<br />
loop<br />
l_idx := instr(l_list,Delimiter);<br />
if l_idx > 0 then<br />
pipe row(substr(l_list,1,l_idx-1));<br />
l_list := substr(l_list,l_idx+length(Delimiter));<br />
<br />
else<br />
pipe row(l_list);<br />
exit;<br />
end if;<br />
end loop;<br />
return;<br />
end UNPACKDIVISIONARRAY;
step 3 : execute the below sql to see the result
Select column_value from TABLE(UNPACKDIVISIONARRAY('111,113'))
hope that this will help you.
regards,
Sumith Koshy Thankan,
OASYS Information Technology.
Dubai
|
|
|
|
|
Hi,
This has been reposted in MySql forum.
Jeff
modified on Thursday, September 17, 2009 10:48 PM
|
|
|
|
|
Jeff has moved my reply to the MySql forum.
This message will autodestruct in a few days, provided no one replies to it here.
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Friday, September 18, 2009 1:45 PM
|
|
|
|
|
|
|
Hii Everybody,
I have one doubt or fundamental unclarity about database.
I would like to know what happens or how does any RDMS database responds to multiple queries being requested from several clients on a table simultaneously ?
Example :
There is a table called customer which has all the required fields of a customer namely age, name. dob etc...
Now there are several clients around the world trying to query this table with several requests. Some clients are trying to insert customer details, some are trying to delete customer details and others are trying to read those customer details simultaneously on customer table.
The clients trying to insert, delete are admins while the clients trying to read are staffs.
In this case how would the table handle those multiple queries at the same time.Would it crash, behave differently etc.. ?
Aspiring Techie,
Vishnu Nath
|
|
|
|
|
|
This is, as the previous post said, its a complex subject, but you need to accept the fact that this is what databases do, and do very well, and they work - as long as you understand about isolation levels and transactions. At my current client there are upwards of a thousand users htting teh database with inserts, updates, delets and plain old reads all day long without any real problems, and this is commonplace.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks for giving out your suggestion.
Well databases must be having robust engine to handle the queries.
But its fuctioning and understanding is important while building a database driven web application.
Also was thinking how facebook, orkut would be using their databases to manage their user queries around the world.
Aspiring Techie,
Vishnu Nath
|
|
|
|
|
Vishnu Nath wrote: But its fuctioning and understanding is important while building a database driven web application.
So you need to do more research than you will get from asking in a forum. Buy a few books on your chosen database (SQL Server, Oracle etc) and investigate further.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I wanted to get some opinions on this. This is my first time I’ve been tasked with doing this. I need to deploy a SQL Server database. My question is this…is it better to script the creation of the database, or is it better to ship an already structured database?
Thank you in advance.
|
|
|
|
|
Restoring a database is certainly easier - but in terms of best practice, i'd script it!
|
|
|
|
|
I used to work for a software company and we would always ship a series of scripts along with our application to create the basic database structure and seed the lookup tables with basic information.
Advantage: Store your database scripts in a version control repository along with your applicaiton source code so that any given build of your source has a corresponding set of database scripts.
Also, when you have to upgrade the system from version x to y, you should be able to deliver scripts that can update the database schema accordingly.
|
|
|
|
|
Thank you very much for your input. I was thinking scripting, but I wanted to get some real-world information. I really appreciate it.
|
|
|
|
|
Please help me out, what are the types of deadlocks in SQL Server; and how are the ways to solve it?
Regards,
John.L.Ponratnam
|
|
|
|
|
I can only think of one type of deadlock, meaning that 2 or more processes are trying to get to the same set of resources. There are however many type of "Locks".
1) Shared locks
2) Exclusive locks
3) Update locks
4) Intent locks
Here is how a deadlock can occur:
Process 1
Locks resource A
Looking to lock resource B
Process 2
Locks resource B
Looking to lock resource A
Classic deadlock situation. The way to avoid this is to lock the resources in the same order, this way process 1 & 2 would never be able to lock each other out. Either process 1 or proccess 2 would be granted resource A, then lock resource B and continue on.
Most database engines solve the deadlock issue by choosing a "victim" and forcing a rollback of the transaction, thus releasing the locks owned by it. Typically once the victim has been choosen, the remaining processes can continue on with their work.
|
|
|
|
|
So doesn't google work for you? there are thousands of hits.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
these might be helpful
exec sp_who
exec sp_who2
exec sp_lock
exec sp_monitor
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Hi..
this is my table info
ATT_DATE NOT NULL DATE
SHIFT NOT NULL VARCHAR2(8)
EMP_ID NOT NULL NUMBER
DEP_ID VARCHAR2(20)
TIMEIN DATE
TIMEOUT DATE
EMP_NAME VARCHAR2(30)
TOTALDAYS NUMBER
i want to insert in my TOTALDAY feild the total record of an employe of one month and + total sunday in month
if this possible so please please help me.... 
|
|
|
|
|
With the table designed as it is, you are going to have terrible problems in getting the thing to work the way you want.
At the very least the TotalDays column should be in a separate table, but this is not the only problem.
There is not enough space in a forum like this to properly advise you, so I can only suggest thet you do some studying on DataBase Design, before proceeding further.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi,
I am using SQl 2005 for setting up a job which has 2 steps:-
Step 1: Executes a SP which sends out result to a file
Type : T-SQL
Command :
DECLARE @cmd varchar(2000),
SET @cmd = 'osql -E -S CONDOR -s";" -w2000 -d Arion -Q"EXEC dbo.ENRSP_Applicants" -o D:\Applicants.txt'
EXEC master..xp_cmdshell @cmd
Step 2: Mailing the txt file to the user
Type : Operating System(CmdExec)
Command :
"D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@yahoo.com "Applicants Report"
This works fine but the user want some modification to the current Job
* the email subject to include the date also
* Wants the result set to be dumped into an Excel file and to attach the file to the mail, if there is no
results returned no file should be atached
* If the Sp doesn't return a result set she wants a message and if it does she wants the message to be
another which includes date also.
Can all these be done , if so can you plz help me with it?
Thanks in advance.
Warm regards,
Payal
|
|
|
|
|
Firstly: is a CSV file acceptable? This opens just in Excel but is easier to create from SQL than an Excel file.
If an Excel file is required then you should probably use SSIS (which allows all of this winderfule logic to be implemented)
|
|
|
|
|
Can you tell me how to proceed(Using SQL job) with a CSV file please?
Thanks & regards
Payal
|
|
|
|
|
Hi,
I am trying to add a trigger on insert event on a table in SQL_Express.
ALTER trigger [dbo].[TR_Insert_New_Pair] on [dbo].[Vendors]
FOR INSERT AS
DECLARE
@ACCPACID varchar(12),
@VNAME varchar(60)
SELECT
@ACCPACID = (SELECT ACCPACVENDORID from inserted),
@VNAME = (
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
)
UPDATE dbo.Vendors SET
VENDNAME = @VNAME
WHERE
LTRIM(RTRIM(ACCPACVENDORID)) = ltrim(rtrim(@ACCPACID))
--This part works great (I've tested)
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
Is there something I am not seeing ????
Thanks
Kirill
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
