CODEWITHSUNDEEP
plsqloracle-apex
execcr
execcr

Reputation: 17

How to get a row object from a function that return a table object in plsql?

i'm trying to get a single row and save it into a variable in PLSQL.

I'm using APEX and all is around the APEX_DATA_PARSE.PARSE() function. As the docs said, .PARSE() should return WWV_FLOW_T_PARSER_ROW for every row of results.

What i'm trying to do is to get only one row to be saved into a variable. This is My code: `

DECLARE
    r_columns_headers  WWV_FLOW_T_PARSER_ROW; --object
BEGIN
    DBMS_OUTPUT.ENABLE;
    BEGIN
        <<populate_headers>>
            select * into r_columns_headers
            from apex_application_temp_files f,
            table( apex_data_parser.parse(
                      p_content                     => f.blob_content,
                      p_add_headers_row             => 'Y',
                      P_SKIP_ROWS                   => 1,
                      p_max_rows                    => 500,
                      p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                      p_file_name                   => f.filename ) ) p
            where f.name = '43300947378776117/DATALOAD_Test_data_main_v032.xlsx' and p.line_number = 2;
        end populate_headers;
    DBMS_OUTPUT.PUT_LINE(r_columns_headers.id);
    end;

`

is just for test outside the main package that i'm writing. The error i get is PL/SQL: ORA-00947 on row 8, on the select * into r_columns_headers section. I don't know why i get not enough values, the fields are the same as they are the same type of object. The select return exactly one row, of a WWV_FLOW_T_PARSER_TABLE object. Not all columns are with data, someones are null, is this the problem? I'm just at the beginning of learning plsql. Thanks a lot

wwv_flow_t_parser_row

I'm trying to get a row into the r_columns_headers variable to access it in my program.

Upvotes: 0

Views: 383

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The "ORA-00947: not enough values" error is sometimes a bit confusing; in a PL/SQL context it can be backwards.

The problem is that you actually have too many values in your select list. You are doing:

select * into r_columns_headers

but you're joining two table expression, apex_application_temp_files as f and the unnested table as p. So the * is effectively doing:

select f.*, p.* into r_columns_headers

... and that object doesn't have fields for all the columns coming from f.

So you need to restrict it to the relevant data:

select p.* into r_columns_headers

... but that returns all of the object attributes as individual columns, not as a single object; but you can get the actual object with the value() function:

select value(p) into r_columns_headers

fiddle with a dummy object type and data as the real APEX types aren't available there.

Upvotes: 1

Related Questions