Home » SQL & PL/SQL » SQL & PL/SQL » Create SQL string (Oracle XE 3.0.1.00.08)
Create SQL string [message #314056] Tue, 15 April 2008 13:19 Go to next message
ljaf_1985
Messages: 3
Registered: April 2008
Junior Member
Hi,

In my application I have a selectList component, called P1_SELECTLIST. When a user chooses a value, say, 'Car,' I would like my report to return allvalues contained in subclasses of object type 'Car.' I have successfully setup a hierarchy table.

The problem lies when trying to put the selectList value into the following query:

SELECT p.show()
FROM ItemHierarchy_obj_table p
WHERE VALUE(p)IS OF(:P1_SELECTLIST);

The query doesnt not recognise P1_SELECTLIST as a valid type. I think i need to compose a sql string, any ideas?

Thanks
Re: Create SQL string [message #314131 is a reply to message #314056] Tue, 15 April 2008 18:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Since you cannot use a bind variable for an object type, I believe you will have to create a string concatenating the variable value and use some form of dynamic sql to execute it. Please see the simplified demonstration below.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE vehicles AS OBJECT
  2    (veh_id	VARCHAR2 (10),
  3  	MEMBER FUNCTION show RETURN VARCHAR2)
  4    NOT FINAL;
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY vehicles AS
  2    MEMBER FUNCTION show RETURN VARCHAR2
  3    IS
  4    BEGIN
  5  	 RETURN 'vehicles';
  6    END;
  7  END;
  8  /

Type body created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE car UNDER vehicles
  2    (car_id	VARCHAR2 (10),
  3    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY car AS
  2    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2
  3    IS
  4    BEGIN
  5  	 RETURN 'car';
  6    END;
  7  END;
  8  /

Type body created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE truck UNDER vehicles
  2    (truck_id  VARCHAR2 (10),
  3    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY truck AS
  2    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2
  3    IS
  4    BEGIN
  5  	 RETURN 'truck';
  6    END;
  7  END;
  8  /

Type body created.

SCOTT@orcl_11g> CREATE TABLE ItemHierarchy_obj_table OF vehicles
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO ItemHierarchy_obj_table VALUES (vehicles ('veh1'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO ItemHierarchy_obj_table VALUES (car ('veh2', 'car2'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO ItemHierarchy_obj_table VALUES (truck ('veh3', 'truck3'))
  2  /

1 row created.

SCOTT@orcl_11g> VARIABLE p1_selectlist VARCHAR2(30)
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :p1_selectlist := 'car'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    OPEN :g_ref FOR
  3    'SELECT p.show()
  4  	FROM   ItemHierarchy_obj_table p
  5  	WHERE  VALUE(p) IS OF (' || :p1_selectlist || ')';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

P.SHOW()
--------------------------------------------------------------------------------
car

SCOTT@orcl_11g> EXEC :p1_selectlist := 'truck'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    OPEN :g_ref FOR
  3    'SELECT p.show()
  4  	FROM   ItemHierarchy_obj_table p
  5  	WHERE  VALUE(p) IS OF (' || :p1_selectlist || ')';
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

P.SHOW()
--------------------------------------------------------------------------------
truck

SCOTT@orcl_11g> 


Re: Create SQL string [message #314367 is a reply to message #314131] Wed, 16 April 2008 08:28 Go to previous messageGo to next message
ljaf_1985
Messages: 3
Registered: April 2008
Junior Member
Hi,

I have tried to insert this code into my report region source:

VARIABLE p1_selectlist VARCHAR2(30)
VARIABLE g_ref REFCURSOR
EXEC :p1_selectlist := 'Car'

BEGIN
OPEN :g_ref FOR
'SELECT p.show() FROM ItemHierarchy_obj_table p WHERE VALUE(p) IS OF (' || :p1_selectlist || ')';
END;
/

PRINT g_ref

Although this keeps giving me errors. Is my structure correct here?
Thanks
Re: Create SQL string [message #314369 is a reply to message #314367] Wed, 16 April 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't see any error in your post.
Can you do it just like Barbara did?
Including code tags.

Regards
Michel
Re: Create SQL string [message #314370 is a reply to message #314056] Wed, 16 April 2008 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Although this keeps giving me errors.
ERROR? What error? I don't see any error.

My car is broken.
Please tell me how to fix it.
Re: Create SQL string [message #314377 is a reply to message #314367] Wed, 16 April 2008 08:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I was executing from SQL*Plus, so the variable declarations and exec and print command, everything except the anonymous pl/sql block, are SQL*Plus commands. You will need to create your variables and assign your values and display the results in whatever method your report uses. You said the problem was how to put the selectlist value into the query, so that it was I showed.
Re: Create SQL string [message #314378 is a reply to message #314056] Wed, 16 April 2008 08:43 Go to previous message
ljaf_1985
Messages: 3
Registered: April 2008
Junior Member
Yes thankyou.

I was just making sure it wasnt my structure which was wrong.
Previous Topic: varchar length problem in materialized view with my function.
Next Topic: Is it possible to trigger a capture data for v$sql or v$sql_bind_capture with following conditions?
Goto Forum:
  


Current Time: Sat Dec 10 08:50:04 CST 2016

Total time taken to generate the page: 0.06339 seconds