| Pasing recors to packages from select statements [message #8250] |
Wed, 06 August 2003 04:05  |
Steve Hurst
Messages: 3
Registered: August 2003
|
Junior Member |
|
|
I have a PL/SQL package that has as one of it's argurments a variable that %rowtype (associated to a view)
Is there a way of passing the row from a select statement to the package.
Package is declared as follows
Function FUN_TEST ( tabrec IN table1%ROWTYPE) RETURN AS VARCHAR2 ...
I want call it in a slect statement.
SELECT FN_TEST (??) FROM TABLE1 T WHERE T.FLD1= 1;
|
|
|
|
|
|
| Re: Passing recors to packages from select statements [message #8263 is a reply to message #8253] |
Wed, 06 August 2003 11:56  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
In procedural code it's easy:
SELECT * INTO r_myrecord FROM table1 WHERE rownum = 1;
v_someval := FUN_TEST(r_myrecord);
However in SQL you would need to define an object type. You can then use its constructor in SQL statements:
CREATE TYPE tab1_ot AS OBJECT
( id NUMBER
, name VARCHAR2(30)
, somevalue VARCHAR2(40) )
/
CREATE OR REPLACE FUNCTION fun_test
( p_inputrec TAB1_OT )
RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello ' || NVL(p_inputrec.name,'Mystery Caller');
END;
/
SELECT FUN_TEST(TAB1_OT(1,'William','Whatever')) FROM dual;
SELECT FUN_TEST(TAB1_OT(id, name, somevalue)) FROM tab1;
As for how to reference the current row as an object without explicitly calling the constructor and listing all the arguments - interesting question. It's probably possible...
|
|
|
|
| Re: Passing recors to packages from select statements [message #8360 is a reply to message #8263] |
Wed, 13 August 2003 04:21 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> how to reference the current row as an object without explicitly calling the constructor and listing all the arguments
To do this the table needs to be based on an object type, e.g:<pre style="color: navy; font-size: 9pt;">CREATE TYPE test_ot AS OBJECT
( id NUMBER
, name VARCHAR2(30)
, somevalue VARCHAR2(40) );
/
CREATE TABLE testtab OF TEST_OT;
INSERT INTO testtab (id, name, somevalue) VALUES (1, 'Tom', 'Banana');
INSERT INTO testtab (id, name, somevalue) VALUES (2, 'Jim', 'Hatstand');
INSERT INTO testtab (id, name, somevalue) VALUES (3, 'Jane', 'Umbrella');
COMMIT;
SQL> SELECT * FROM testtab;
ID NAME SOMEVALUE
----- --------------- --------------------
1 Tom Banana
2 Jim Hatstand
3 Jane Umbrella
3 rows selected.
SELECT VALUE(t) FROM testtab t;
VALUE(T)(ID, NAME, SOMEVALUE)
------------------------------------------
TEST_OT(1, 'Tom', 'Banana')
TEST_OT(2, 'Jim', 'Hatstand')
TEST_OT(3, 'Jane', 'Umbrella')
3 rows selected.
CREATE OR REPLACE FUNCTION testfunction
( p_test TEST_OT )
RETURN VARCHAR2
AS
BEGIN
-- Generate some string using p_test:
RETURN 'Hello ' || NVL(p_test.name,'?');
END testfunction;
/
SELECT TESTFUNCTION(VALUE(t)) FROM testtab t;
TESTFUNCTION(VALUE(T))
------------------------------------------
Hello Tom
Hello Jim
Hello Jane
3 rows selected.</pre>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
