Am 29.03.2012 20:28, schrieb ExecMan:

If your application provides a delimited, comma separated list of IDs, which
you can syntactically use directly as an IN-list in a query, do so in a
dynamic SQL statement. Something like that, not nice, but you get the idea:

CREATE TABLE "PS"."ARTICLES"
( "ARTICLE_ID" NUMBER(12,0),
"ARTICLE_TITLE" VARCHAR2(100 BYTE),
"AUTHOR_ID" NUMBER(12,0),
"EDITOR_ID" NUMBER(12,0)
);

ALTER TABLE "PS"."ARTICLES" ADD CONSTRAINT "ARTICLES_UK1" UNIQUE
("ARTICLE_TITLE");
ALTER TABLE "PS"."ARTICLES" ADD CONSTRAINT "ARTICLES_PK" PRIMARY KEY
("ARTICLE_ID");

ALTER TABLE "PS"."ARTICLES" MODIFY ("EDITOR_ID" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("AUTHOR_ID" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("ARTICLE_TITLE" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("ARTICLE_ID" NOT NULL ENABLE);

INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('1', 'Erster Artikel', '1', '2')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('3', 'Dritter Artikel', '2', '3')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('2', 'Zweiter Artikel', '1', '1')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('4', 'Vierter Artikel', '3', '2')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('5', 'Fünfter Artikel', '4', '4')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('6', 'Sechster Artikel', '5', '1')

COMMIT;

DECLARE
p_author_count NUMBER;
p_editor_count NUMBER;
p_user_list VARCHAR2(255) := '3, 5';

BEGIN
DBMS_OUTPUT.ENABLE(1000000);

EXECUTE IMMEDIATE
'BEGIN
SELECT (SELECT COUNT(*)
FROM articles a
WHERE a.author_id IN (' || p_user_list || ')
) AS author_count,
(SELECT COUNT(*)
FROM articles a
WHERE a.editor_id IN (' || p_user_list || ')
) AS editor_count
INTO :1, :2
FROM dual;
END;'
USING OUT p_author_count, OUT p_editor_count;

DBMS_OUTPUT.PUT_LINE('author count: ' || p_author_count);
DBMS_OUTPUT.PUT_LINE('editor count: ' || p_editor_count);

END;

author count: 2
editor count: 1

So user_ids frin the list '3, 5' occur 2 times as author and 1 time as editor.

Instead of EXECUTE IMMEDIATE, you could also use a weak ref cursor (OPEN
cur_hndl FOR 'SELECT ...' || p_user_list etc. etc.)

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

On 30/03/12 03:35, ExecMan wrote:


Your approach is complete and utter madness and a total breach
of the 1NF - no repeating elements.

You should have a table
CREATE TABLE Book
(
Book_ID int -- seq not ISBN - not all books published
ISBN CHAR(13)
Title VARCHAR2(500)
Publisher VARCHAR2(100)
Format BOOLEAN // HB/SB
Pages int
&c
)
CREATE TABLE Contributor
(
Contributor_ID -- sequence
Name, Address, Country, Phone, Mobile, Email... &c
)

CREATE TABLE BOOK_Author
(
Book_ID FK references Book.Book_ID
Contributor_ID FK references Contributor
Contributor_Function -- Writer, Editor, ProofReader...
)

So, any book can have 1 or many contributors and any contributor can
have 0, 1 or many books.

This approach is far more scaleable - Google Publisher database schema,
and I'm sure that nobody will have proposed doing what you want to do.

I can't believe that this hasn't been pointed out to you.

Go to forums.oracle.com, sign up (it's free) and ask your design
questions there.

HTH,


Paul....